Skip to content

22.2 Dynamic Views

A dynamic view is a table defined by an SQL SELECT in the project – like a database view, but maintained in Modeller. Use it when the data warehouse doesn't have the table you need: to combine tables, filter rows, add calculated columns or reshape data.

In the Warehouse diagram a dynamic view looks like any table, and is used the same way.

Creating a Dynamic View

  1. WAREHOUSE → Dynamic views → New (Ctrl+Alt+D; on HOME before build 20353), or right-click the Warehouse background → Create Dynamic View….
  2. In the editor tab, write the SELECT statement. Other dynamic views can be used by their name in double quotes, e.g. FROM "ACINTA_DV Customer".
  3. Fill in Name, and if needed Schema, Data source (default: the staging database) and Physical name.
  4. Click OK.

New views are called ACINTA_DV 1, ACINTA_DV 2… until you rename them. Preview... shows the SQL Modeller will run.

Modeller checks the SQL against the database. If it's invalid, choose Edit to correct it or Save and close to keep it – an invalid view gets a red title bar and a red field list in the diagram, and can't be used in queries.

Open an existing view with WAREHOUSE → Edit (HOME before build 20353), or right-click it → Edit Dynamic View.

How a Dynamic View Is Used

Dynamic View manifestation (in the view's properties, or as the default in the data source options) decides how Acinta uses the SQL:

Manifestation How
Common Table Expression (CTE) Included in each query as a WITH clause – the usual default
Derived table Included as a sub-query
Database view Modeller creates a real view in the database
Materialized The result is stored in a table and refreshed on a schedule – fast, but not always current. See 27.2 Materialized Dynamic Views and Caching.

If a database view created by Modeller differs from the definition in Modeller, Modeller asks which version to keep (Keep Modeller version / Keep DB version).

Converting

Both are on WAREHOUSE → Dynamic views (HOME before build 20353), or on the right-click menu.

  • Convert to DV turns a table into a dynamic view – for a database view, its SQL is copied in. Useful to adjust a view without changing the database.
  • Convert to table points a dynamic view to a physical table. The view's SQL is discarded without asking.

Tips – Dynamic Views

  • Keep views simple and well named – they're part of the model others maintain.
  • Check performance with large tables: a complex view used as CTE runs with every query; consider Materialized.

Common Issues – Dynamic Views

Problem: The SQL you entered is invalid and cannot be used in queries. - Solution: Correct the SQL – the message from the database tells what's wrong. Test the SELECT in SQL Explorer first.

Problem: Cancel in the editor didn't undo a new name. - Solution: Changes to Name, Physical name and Data source apply right away. Change them back, or don't save the project.