27.2 Materialized Dynamic Views and Caching¶
A materialized dynamic view stores its result in a table and refreshes it on a schedule. Queries then read the stored result instead of running the view's SQL each time – much faster for complex views, at the price of data that's only as fresh as the last refresh.
Making a View Materialized¶
Select the dynamic view and set Dynamic View manifestation to Materialized. Then:
| Setting | What it does |
|---|---|
| Schedule | When the stored result is refreshed, e.g. every night |
| Only mapped fields | Store only the columns used in the model (on by default) – less data, faster refresh |
| Indexes on materialized table | Indexes created on the stored table at every refresh |
Without its own schedule, a view follows the data source's Schedule for materialization of Dynamic Views (DATA SOURCE → Options → Dynamic Views).
How Refreshing Works¶
- When a view's stored result has expired, the next query refreshes it and waits.
- If it expired less than 5 minutes ago, the old result is used while a fresh one is built in the background.
- Several users never refresh the same view at the same time.
- With Run materialization schedules on Saturn Server (on by default), Acinta Server refreshes the views on schedule, so users rarely wait.
On SQL Server, stored results go into Database for materialized Dynamic Views and Schema for materialized Dynamic Views (data source options) – by default a temporary area. Stored tables are named DSMat_….
Datashop's Job Monitor → Cache tab shows each stored view's status (Fresh, Expired, Updating…), expiry and size – see 19.2 Job Monitor and Performance.
Current Data Instead of the Stored Result¶
New in build 20306.
A materialized view shows the data as of its last refresh. When a refresh failed, is late or the data is out of date, Datashop Expert users can query the view through its SQL instead, and see current data.
The data source option Allow bypassing materialization (Modeller, data source options → Dynamic Views, right after Run materialization schedules on Saturn Server; off by default) allows it. It needs:
- a database that supports common table expressions (CTEs): SQL Server, PostgreSQL, MySQL 8 or later, or MariaDB 10.2 or later;
- Datashop Expert, and the option switched on for the data source the query runs on – otherwise the request is ignored.
Acinta Server always uses the stored results.
[!NOTE] Current data is slower: the view's SQL runs every time instead of reading the stored table.
In Datashop¶
| Where | What |
|---|---|
| Workbook: arrow on Refresh → Current data in this workbook | The workbook reads current data. Saved with the workbook; can be undone and redone. |
| Workbook: arrow on Refresh → Current data in all workbooks (this session) | All workbooks read current data until you close Datashop. Not saved. |
| Drill-through window: Current data (bypass materialization) | Runs the drill-through again on current data, or on the stored results when you clear it. It starts in the mode the workbook or session uses. |
Switching either Refresh option refreshes the whole workbook. Both are only shown to Datashop Expert users and only work on data sources with the option on.
Materialization Info¶
When a drill-through on a metric uses materialized views, a panel above the data shows one line per view – also views used inside other views, and one line per partition for partitioned views. For example:
Sales: refreshed 26-10-2026 08:15:02 in 12.3 s, 1,234,567 rows, 85.2 MB, by
Other states: not materialized, refresh in progress, materialized table is empty (database restarted?) and is refreshed on next use, and Does not expire.
If the last refresh failed, a line Last refresh failed follows. Other users see it too as long as an older stored table exists. The next successful refresh clears it.
The panel and the Current data check box only appear in a drill-through on a metric – not when you browse a table or type SQL yourself in SQL Explorer. Browsing a table in Datashop always reads the stored table.
In Modeller¶
Right-click a materialized view in the Object Tree or the diagram:
- Browse underlying SQL runs the view's SQL and shows current data – the SQL Explorer tab title ends in (SQL). Always available in Modeller; no option needed.
- Browse Table still reads the stored table.
In both, the SQL starts with comment lines with the materialization info – Modeller shows no panel.
Query Result Caching¶
Independent of dynamic views, Caching time (seconds) (data source options → Optimization) lets Acinta reuse identical query results for that long. 0 turns it off.
Tips – Materialized Dynamic Views and Caching¶
- Materialize views that are slow and used often; leave fast or rarely used ones as CTE.
- Schedule refreshes after the data warehouse is loaded, e.g. 06:00 after a nightly load.
- Tell users when data is refreshed, so they know how current it is.