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.

ℹ️ Why it matters A calculation defined in one report is copied, changed and forgotten. A calculation defined in the model has one definition: change it once and every visual using it follows, and column masking and row-level security apply to it like any other column.

What the Model Is Made Of

LayerWhat it doesWritten in
ObjectsThe datasource's tables, as they appear in the attributes list.From the source
RelationshipsHow objects join, so a visual can combine attributes from several of them. See Relationship Manager.Point and click
Calculated objectsA new table defined by a query over the datasource's other tables, rebuilt on every refresh. Below.SQL
New objectsAn object you create to hold your own attributes, not tied to a source table. Below.Point and click
Custom attributesNew 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
GovernanceColumn 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.

⚠️ Imported datasources only Calculated objects are available on datasources whose data PersivX imports and stores (files, APIs, Google Sheets and similar), not on live database connections. For a live database, use a custom attribute or a query-based visual instead.

Creating One

  1. Open the datasource in the report builder. At the top of the attributes list, click Add data from more sources.
  2. Choose Calculated Object.
  3. Give it an Object Name.
  4. Write the query in Calculated Object (Query). Refer to the datasource's tables in square brackets, for example [orders].
  5. Click Verify & Save. PersivX runs the query first, so a query with an error isn't saved.
Example: one row per customer and month
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.

  1. At the top of the attributes list, click New Object, give it a name and click Save.
  2. 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.