The use of dynamic and materialized views is an effective way to make Slate data available to external tools like Tableau, over a direct SQL connection.
A dynamic view is a virtual table based on the result set of a query. Every time a view is referenced, the database engine generates the result-set in real time from the underlying query.
A typical query retrieves data from multiple tables in the database. Each table referenced by the query likely contains many columns that are not of interest for a particular purpose. Views allow the creation of virtual tables that are completely customized to include only those columns that are desired, helping to simplify querying.
A materialized view is a database object that stores the results of a query. Unlike a dynamic view, it provides a static, point-in-time, snapshot of the result set, which can be generated (refreshed) on demand, or within the selected delivery window(s) (similar to scheduled data exports).
Materialized views are generally appropriate for queries that do not require real-time results and contain complex logic. They can be used to compile data for third-party reporting platforms such as Tableau or PowerBI, or to serve as a staging area for data to be extracted from Slate for an Operational Data Store or Data Warehouse.
We still encourage all partners to use the standard Query Builder within Slate, whenever possible. For most processes, this is sufficient in returning results in a timely and consistent manner.
- Select Queries / Reports on the top navigation bar.
- Create a New Query.
- Add exports and filters as desired. Remember that a completely custom table is being constructed upon which queries may be run afterwards.
- From the Edit Query page, select Schedule Export.
- Configure the following settings:
- Destination: Select View (Dynamic) or View (Materialized)
- View: Defaults to view:// for dynamic views and table:// for materialized views
- Enter view://mysampleview , replacing mysampleview with the desired name, for a dynamic view, or table://mysampleview for a materialized view. The view will then be created as a table in the build schema, and will follow the naming convention build.[mysampleview]
- Notification: Specify the conditions under which a notification email should be sent
- Requested Delivery Window: The time frame during which the view should be refreshed
- Requested Weekdays: The days of the week during which the view should be refreshed
For a materialized view to update automatically based on the selected windows, set the Status to Active. If left Inactive, the Refresh View button must be clicked manually to update the materialized view.
Once the materialized 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.[mysampleview]
In order to access the table with a direct SQL connection, users must have the Direct SQL Access permission as well as Query permission.