Dynamic and Materialized Views

Dynamic and materialized views are effective ways at making Slate data available for external tools, such as Tableau, PowerBI, and data warehouses. This is because they can be configured to only include columns that are specific to your export. In comparison, regular queries retrieve data from multiple tables across your Slate database. In doing so, each table referenced by the regular query likely contains many columns that are not of interest for the external tool you are exporting to.

 

That said, there are a few considerations to make when choosing whether a dynamic or materialized view works best for your situation: 

Dynamic Views

A dynamic view is a virtual table based on the result-set of a query. Every time the view is referenced, the Slate database engine generates the result-set in real time from the underlying query. From here, the view is stored in random access memory. This means that, while the view still consumes computational resources, it does not consume space on your database. Because dynamic views are ran and stored in random access memory, they should not be overly complex, called excessively, or contain a heavy number of formulas and calculations.

Materialized Views

Materialized views are generally appropriate for queries that contain complex logic, are formula and calculation heavy, and do not require real-time results. Unlike dynamic views, they provide 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. They can be used to compile data for third-party reporting platforms or serve as a staging area for data to be extracted from Slate for use in an Operational Data Store or Data Warehouse.

Materialized views are created only when the view is exported and the results are stored in your database. In theory, if you were to create a view of an entire table, it would essentially duplicate that table in your database. There is, however, a 15-minute processing limit. If your materialized view query consumes more than 15 minutes to render, a time out error will occur.

Best Practice

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.

Create a View

  1. Select Queries / Reports on the top navigation bar.
  2. Create a New Query.
  3. Add exports and filters as desired. Remember that a completely custom table is being constructed upon which queries may be run afterwards.
  4. From the Edit Query page, select Schedule Export.
  5. 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.

 

IMPORTANT

The views must have a unique name that do not coincide with the names of other tables in your instance. This includes standard table such as person, as well as any custom datasets or entities you may have in your instance.

Was this article helpful?
6 out of 7 found this helpful