Reusable Data Model
Define your tables, joins, calculations and business rules once on a datasource, and every report built on it uses them. The model is written in SQL, which most data teams already know, plus no-code rules for everyone else.
What the Model Is Made Of
| Layer | What it does | Written in |
|---|---|---|
| Objects | The datasource's tables, as they appear in the attributes list. | From the source |
| Relationships | How objects join, so a visual can combine attributes from several of them. See Relationship Manager. | Point and click |
| Calculated objects | A new table defined by a query over the datasource's other tables, rebuilt on every refresh. Below. | SQL |
| New objects | An object you create to hold your own attributes, not tied to a source table. Below. | Point and click |
| Custom attributes | New columns and measures: Select Query (SQL, including aggregates such as SUM), Decision Table (no code), Parameter, Query Expression and What If. | SQL or no code |
| Governance | Column masking and row-level security on the model's columns, applied in every report. | Point and click |
Calculated Objects
A calculated object is a table you define with a query over the other tables in the same datasource: a cleaned-up customer list, a monthly summary, or two tables combined into one. It appears in the attributes list like any other table, marked with a calculator icon, and is used in visuals like one.
Creating One
- Open the datasource in the report builder. At the top of the attributes list, click Add data from more sources.
- Choose Calculated Object.
- Give it an Object Name.
- Write the query in Calculated Object (Query). Refer to the datasource's tables in square brackets, for example
[orders]. - Click Verify & Save. PersivX runs the query first, so a query with an error isn't saved.
SELECT customerNumber,
DATE_TRUNC('month', orderDate) AS orderMonth,
COUNT(*) AS orders
FROM [orders]
GROUP BY customerNumber, DATE_TRUNC('month', orderDate)
- It refreshes with the datasource. The table is rebuilt from the query each time the datasource refreshes, so it never goes stale.
- Changing it: open the object's menu in the attributes list and choose Update Query.
- The query runs on PersivX's own engine over the imported data, so use its SQL (DuckDB) rather than your source database's.
New Objects
A new object is a place for attributes that don't belong to any one source table, such as a set of company-wide KPIs that combine several tables.
- At the top of the attributes list, click New Object, give it a name and click Save.
- Add custom attributes to it with + New Attribute, referring to columns of any table in the datasource.
Report builders then find those measures in one place, under a name that means something to the business.
Compared With a Formula-Language Model
Many BI tools define their model in a formula language of their own. PersivX defines the same kinds of things in SQL, plus no-code decision tables:
- Calculated tables are calculated objects.
- Measures and calculated columns are custom attributes, written in SQL or as decision tables.
- Relationships, parameters, what-if values and security live in the model too.
Not built in yet: ready-made time-intelligence functions (year-to-date, same period last year), which you write in SQL, and a way to mark a model or measure as certified.
