Dynamic views package Slate data in a way that's more accessible to external tools, such as Tableau, PowerBI, and data warehouses. They are distinct from materialized views because they reflect data in real time.
How they work
A dynamic view is a virtual table created from the result-set of a query. Slate generates this result-set in real time every time the view is referenced—hence dynamic.
The view is stored in random access memory, consuming computational resources—but not space—in your database.
When to use them
Dynamic views should be:
- Called infrequently
- Contain very few formulas or calculated fields
If your exported data are formula-intensive, complicated, or need to be referenced frequently, make a materialized view instead.
Advantages over regular queries
Dynamic views are more efficient for exported data because they reference only those columns specific to the export.
Regular queries retrieve data from multiple tables across your Slate database. Each table that the query references contains more columns than are relevant.
Creating a dynamic view
The view must have a name unique among other tables in your database. This includes standard tables such as person and any custom datasets or entities.
To create a dynamic view:
- From the main navigation, select Queries / Reports.
- Click New Query. A pop-up appears.
- Enter a computer-friendly name: No spaces, no special characters except for underscores.
- Add exports and filters as desired. These exports are the basis of a custom table upon which queries can later be run.
- From the Edit Query page, select Schedule Export. A pop-up appears.
- Configure the following settings:
- Destination: View (Dynamic)
View: Enter the name of the query following view://
The name entered here must match the name of your query.
- Click Save.
The view is created as a table in the build schema and follows the naming convention build.[queryname]
Querying on the dynamic view
To access the dynamic view table with a direct SQL connection, users must have the Direct SQL Access and Query permissions.
Once the dynamic view has been created, the snapshot data is stored on a table (as defined in the Schedule Export settings) in the database. This table can be referenced via direct SQL access, or by Custom SQL snippet parts in a standard query in Slate:
select [Name], [Event Name] from build.[queryname]