Measure tests and preview

A measure test checks that a measure keeps returning the result you expect. The tests live in the Tests pane of the measure editor, and the Preview pane runs the measure itself so you can see its rows. The editor's lower panes are Description, Tests (<n>) and Preview; opening a pane expands the editor if it is collapsed.

What a test checks

A test has a kind, and each kind asks a different question of the measure:

Kind	What it checks
Expected value	The result matches a value you enter. The value can be a Number, Text, True / false or Null; a null value means the test expects a NULL result.
Invariant	A rule always holds: Never negative, Never null, Between min and max, Rows sum to one, or Total equals the sum of its rows.
Oracle SQL	The result matches your own source-of-truth query, written over the model's BI table names. Pick the Value column to compare and, for grouped results, Key columns to match rows; they default to the group-by names.
Shape (runs without errors)	The measure runs without errors, over the grain you pick: Card (no group-by), Grouped rows, or Grouped rows and totals.

Tests use one of three grains: Card (no group-by) checks a single value, Grouped rows checks one row per group, and Grouped rows and totals also checks the grand total; a Shape test must have one of them, and the grouped grains need group-by columns.

Expected value, Invariant and Oracle SQL tests can carry a Tolerance (absolute) and a Tolerance (relative). A comparison passes when the gap between the two values is within the larger of the two allowances: the absolute tolerance, or the relative tolerance applied to the expected value.

A test is created by a person or by the AI; an AI-authored test carries an AI chip on its row. For the editor around the panes, see Measure editor; for the measure library and its folders, see Measures.

Add a test

Open the measure and choose the Tests pane. The tab counts the measure's tests, for example Tests (3).
Choose Add test and fill the form: Name, Kind, any Group by columns (up to 10), and a Row limit (the placeholder reads 500).
Fill the fields for the kind you picked. A grouped Expected value test picks the row it checks with Row where <column> is; leave it on Any (the only row) when the query returns a single row.
Save with Add test (or Save test when you edit an existing test). Cancel closes the form, and Edit <name> reopens it.

A draft measure cannot hold tests yet; the pane reads Save the measure to add tests. Save the measure first.
The spec behind the form is a JSON object: use Edit as JSON to edit it directly and Edit as form to switch back.
The editor refuses an incomplete test: Enter a test name.; an expected test without a value answers Enter the expected value. or The expected value must be a number.; a broken spec in JSON mode answers The spec must be a JSON object. or The spec is not valid JSON: <message>.
A test row shows its state: Never run before its first stored result, and Ran <relative time> once it has one. An AI-authored test shows the AI chip. Expand a stored result to see its SQL and, for an oracle test, its Oracle SQL.
While the tests load, the pane reads Loading tests...; a measure with no tests shows No tests yet.
Delete <name> asks Delete test "<name>"? first. Deleting a test that changed elsewhere is quiet: the list refreshes with the newer version.

Run tests

To run one test, choose Run <name> on its row; it runs straight away.
To run a measure's tests together with the tests of the measures that depend on it, choose Run with dependents.
To run every test in the model, right-click Measures in BI tables and choose Run all tests. The run is queued and works in the background; the app follows it and refreshes the tests when it finishes.
To run against SQL you have not saved yet, turn on Against the unsaved SQL; the run uses the expression in the editor.

While a run is going:

Only one model-wide run works per model at a time. Starting another joins the run that is already going; the message for a second start reads Tests of this semantic model are already running.
When the runner is busy with other work, the message reads The test runner is busy. Try again.
When the run finishes, the message reads Tests finished: N pass, N fail, N error.; it appears as a failure notice when something did not pass.

The Tests pane notes Without row-level security: tests run without row-level security, so a test can check rows a viewer would not see in a report. See Row-level security and View as.

The Health tab runs all of a model's tests too; see Check, document and trace a model.

Read the results

Each test shows its last stored result:

pass, fail or error; a test with no stored run yet shows as Never run.
A finished model-wide run can also mark a test as not run, when the run's scope held more tests than it covered.
A fail says what did not match: a value differs, something is missing in the measure or missing in the oracle, a key is duplicated, or a rule is violated.
A stored result that is too long to keep is shortened and marked as cut short.
A stored result records who ran it, when it ran and the measure version it checked. Saving a change to a test clears its stored result, so the test shows as Never run until it runs again.
Two runs leave no stored result: a run you made against unsaved SQL, and a run whose measure or test changed while it was going. Save first, then run again.
If a measure is deleted, its tests are hidden and come back when the measure is restored.

These results also feed the status dot on the measure's row in BI tables; see Measures.

Limits inside a test

Group by: up to 10 fields.
Filters: up to 50 filters, of which up to 5 are value filters. The operators are equals, not equals, greater, greater or equal, less, less or equal, and between.
Row limit: between 1 and 500, default 500. A query that returns more rows than the limit makes the test an error; add filters.
Oracle SQL: the query's result is capped at 500 rows, and more rows make the test an error.
Expected value: without a picked row, the test's query must return exactly one row.

Quotas and time budgets

A measure holds up to 50 tests; a semantic model holds up to 2,000.
A single run gets about one minute. A run with dependents gets about two minutes for at most 50 tests; tests outside that scope report as not run, with the reason more than 50 tests in scope.
A model-wide run is followed for up to 30 minutes, and a finished run's status stays readable for about 10 minutes. Each test keeps its stored result after that.

When a test is refused

A test named "<name>" already exists on this measure. Choose another name or delete the other test.
This measure test was changed by someone else. Reload and try again. Someone saved a change after you opened the test; reload to see their version, or overwrite it with yours.
invalid measure test: shape tests need grain card, group_by or totals
invalid measure test: expected tests need an "expected" value (null expects NULL)
invalid measure test: between needs min at most max
invalid measure test: oracle tests need sql and valueColumn
invalid measure test: tolerance values must be zero or positive
invalid measure test: a measure can have at most 50 tests

Preview a measure

The Preview pane runs the measure itself and shows its rows.

Open the measure and choose the Preview pane.
Optionally set Group by (up to 3 columns) to group the rows; a grouped preview shows up to 100 rows.
Choose Run preview. The control is off while the expression is empty and while a preview runs.

While it runs, the pane reads Running the measure....
A failed run shows its message with an expandable Executed SQL.
The preview runs the expression as it stands in the editor, saved or not, and it does not save the measure.
The preview respects your row-level security. When the policy hides every row from you, the state reads Row-level security hides every row from you. Unlike the preview, measure tests run without row-level security.

Questions, answered

Can I run one test without running the whole model?

Yes. Choose Run <name> on the test's row; it runs on its own, straight away. Use Run with dependents when the measures that use it should be checked too.

What is the difference between a fail and an error?

A fail means the check did not hold: a value differs, a row is missing, a key is duplicated, or a rule is violated (a Shape test fails when the measure does not run cleanly). An error means the test could not run or compare at all, for example when the query returned more rows than the test's row limit.

Can I run a measure's tests against SQL I have not saved yet?

Yes. Turn on Against the unsaved SQL in the Tests pane and run; the tests use the expression in the editor. Such a run is not stored as the test's result, so save the measure and run again to keep one.

What happens if I start a run while one is already going?

Only one model-wide run works per model at a time. Starting another joins the run that is already going instead of starting a second one.

Why does a test show "Never run"?

No stored result exists for it yet. A new test shows Never run until its first run finishes, and saving a change to a test clears its stored result, so it returns to Never run.

Why do some tests report as not run after a run finishes?

The run's scope held more tests than it covers. A run with dependents covers at most 50 tests, so the rest report as not run; run them in a smaller scope.

How long does a test run take?

A single run gets about one minute, and a run with dependents gets about two minutes for at most 50 tests.

Why did a run not update a test's stored result?

Two kinds of run leave no stored result: a run against unsaved SQL, and a run whose measure or test changed while it was going. Save the measure, then run again.

I deleted a measure. Are its tests gone?

No. A deleted measure hides its tests, and they come back when the measure is restored.

Who can create and run measure tests?

Creating and running tests, and reading run status, need edit access to the semantic model. If you can only view the model, you can see its tests but you cannot run them. See Who can do what in DataPrism.

Why did my test change not save?

If someone changed the test after you opened it, your save is refused as a version conflict. Reload to see their version, or overwrite it with yours. The refusal message is listed under When a test is refused.

Can I preview a measure before saving it?

Yes. The Preview pane runs the expression as it stands in the editor, saved or not. Running a preview does not save the measure.