Materializing Views

👤 This documentation is intended for SQL Users. SQL Users may need to reach out to their Site Administrators to ensure underlying tables in Views are cached.

Note: Materializing views is only available with the Sisense Data Engine.

Views can be materialized with the Sisense Cache, Managed Warehouse, or Data Engine on Your Warehouse. When a materialized view is referenced in a query, Sisense for Cloud Data Teams pulls the "saved" materialized result set rather than running the contents of the view as a subquery. Leveraging materialized views in queries can contribute to significant performance gains when used strategically, and is especially recommended for queries experiencing long runtimes and timeout errors.

Materialized views must be written in Redshift-compatible or Snowflake-compatible syntax depending on the cache infrastructure being used. Once a view has been materialized for the first time, it can be queried almost immediately.

Materialization with Cache and Managed Warehouse

For a view with Cache or Managed Warehouse to materialize, users must confirm with their Site Administrators that the underlying tables are cached. By default, the Materialize View switch is on, and views are materialized automatically. Caching views stores a copy of the view results in the Sisense Data Engine, however, if an immediate version of the view is preferred, the switch can be turned off and the view will run as a subquery when referenced in other queries.

After clicking "Save", the view will queue for materialization. Hover over the information dot to check its status.

Materialized views are kept up-to-date in the background to make sure the data is always fresh. Views will refresh on an interval of every 1 hour or every 3 hours, as automatically determined by the query runtime, provided that the materialization criteria have been met (see below).

‍

Materialization with Data Engine on Your Warehouse

For a view with Data Engine on Your Warehouse to materialize, view queries must run on the warehouse datasource or leverage Python/R code in its definition. The warehouse source option will appear with a dark gray checkmark in the data source selector. By default the materialization setting is set to None. Non-materialized views are queryable as normal subqueries when referenced in other queries.

To enable a materialization strategy, use the radio button selector to set a materialization interval or schedule. The schedule will use a time of day that relates to the timezone set in the site preferences.

When reviewing a Materialized View with Data Engine on Your Warehouse, information about the materialization is available on mousing over the information status icon.

Materialized views with Data Engine on Your Warehouse will only materialize on the cadence defined by the Interval or Schedule settings. To manually refresh the view, the refresh button at the top right of the edit view page can be used to rematerialize the last-saved version of the view.

Keeping Track of Views

The "i" next to the view's name display information such as when the view was last refreshed, when the view was last modified, and how many charts and views depend on this View.

In the list of all views, the colored dots will give quick summary statuses. Green checks indicate successfully materialized views, grey clocks indicate views that are in the process of caching, red circles indicate cache failures, and no icons indicate views that are not to be materialized.

Once a view starts to materialize, the info icon turns into a grey dotted info icon:

Once it has successfully materialized, the info text turns green:

A red dotted info icon indicates a view failed to materialize on the first run due to an error:

A solid red info icon indicates that a view that was previously materialized failed to update its data during a subsequent refresh. The last materialized version is still stored on the Sisense Cache:

A solid grey icon indicates the underlying data has been updated and the view is awaiting its next refresh:

Materialization Criteria

In order for a view to successfully materialize without R or Python code attached, the following conditions must be met:

Site administrators must ensure that all underlying tables referenced in the view are cached
If on the Redshift Cache naming conventions and syntax must follow Redshift guidelines, outlined in Redshift's documentation page
If on the Snowflake Cache, naming conventions and syntax must follow Snowflake guidelines, outlined in Snowflake's documentation page
On the Snowflake Cache, every column must be uniquely and explicitly named/aliased. (e.g. count(1) as column_name rather than just count(1))
The view cannot refer to any filters
The view cannot contain any duplicate column names
The view must not contain comments at the end of the SQL code. Note that comments can be safely place anywhere else in the SQL (beginning or middle of the code)

A complete debugging guide can be found on the Sisense for Cloud Data Teams Community.

In order for a view to successfully materialize with R or Python code attached, the following conditions must be met:

SQL must be able to run on either the Sisense Data Engine (the Sisense Cache) or directly on any database connected to the site
The view cannot refer to any filters
The view cannot contain any duplicate column names
The view must not contain comments at the end of the SQL code. Note that comments can be safely placed anywhere else in the SQL (beginning or middle of the code)

More information on integrating R and Python code can be found on the documentation page here.

Note: R and Python Integration is an add-on feature. Site administrators can contact their Account Manager for additional information.

Timeout Mechanics

Unlike charts, Sisense views have a 30 minute timeout window, allowing additional capabilities to run and save results from computationally expensive operations. The preview in a view runs on a 4 minute timeout window; however, the view continues to run in the backend until 30 minutes have passed. If a view has exceeded the 30 minute materialization window or ran into any other errors, a red x will appear by the name of the view in the View menu. If R or Python code has been added to the view, the code block is also given 30 minutes to run.