Materialized views make Slate data easily accessible to external tools, such as Tableau, PowerBI, operational data stores, and data warehouses. They are distinct from dynamic views because their data update on a schedule of your choosing.

How they work

A materialized view is like a scheduled data export that stores its results in your database. They are a static, point-in-time snapshot of a result-set.

This snapshot can be refreshed on demand or within a delivery window. (Contrast this with dynamic views, which update with any change in the table data).

If you were to create a materialized view of an entire Slate table, it would theoretically duplicate that table in your database (a huge computational undertaking). In reality, there is a 15-minute processing limit: if your materialized view takes more than 15 minutes to render, a time out error occurs.

When to use them

Materialized views are appropriate for queries that:

• Contain complex logic

• Are formula and calculation heavy

• Do not require real-time results

If your exported data are simpler and require results that are as recent as the last query update, make a dynamic view instead.

Advantages over regular queries

Creating a materialized view

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.

Additionally, column names must be unique within the same view. If two columns share the same name, the view will fail to build.

To create a materialized view:

Your title goes here

1. From the main navigation, select Queries / Reports.

2. Click New Query. A pop-up appears.

3. Enter a computer-friendly name: No spaces, no special characters except for underscores.

 

4. Add exports and filters as desired. Remember that a completely custom table is being constructed upon which queries may be run afterwards.

5. From the Edit Query page, select Schedule Export.

6. Configure the following settings:

   • Destination: View (Materialized)

   • View: Enter the name of your query following table://
     
     The name entered here must match the name of your query. 

   • 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

6. Click Save.

The view is created as a table in the build schema and follows the naming convention build.[queryname]

Querying on the materialized view

Required Permissions

To access the materialized view table with a direct SQL connection, users must have the Direct SQL Access and Query permissions.

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.[queryname]