Materialized Views Make Me Wish I Could Time Travel

Prior to becoming a consultant living the dream and helping others find success in Slate, my work in higher ed admissions required a focus on pulling numbers.

We had an executive-level report built in Slate which we delivered Monday mornings to our institution’s leadership. The problem with this report, however, was that it was extremely complex, with goal calculations for every program and a variety of data points that each member of the executive team deemed “extremely important.”

Failure to render

Unfortunately, more often than not the report would timeout upon generation, resulting in blank reports delivered to recipients. As a result, I fielded lots of grumpy phone calls. At the time, our team did everything we could to make the report run more efficiently, including checking, rechecking, and rebuilding our query bases and configurable joins.

After multiple embarrassing instances in which the report failed to render, we were left with no choice but to chop the report up into different versions and flood the leadership’s inboxes with several emails instead of the one master report to rule them all.

I recently encountered a client with similar woes, only this time the culprit was a ton of Ping data. The client was simply trying to report on the efficacy of a specific marketing campaign. Prior to engaging with SIG, the client had tried and failed to get even simple query results to successfully render because their Ping data table contained over 40 million rows. As a large state institution, these numbers were to be expected; so, what could we do?

Materialized views to the rescue

After discussing various options with the client, they followed our primary recommendation to leverage a newer Slate feature called “materialized views,” with this Ping data scenario serving as a test case for proof of concept.

As defined in Slate’s Knowledgebase, a materialized view is “a static, point-in-time snapshot of a result-set.” Essentially, a custom query is created with a scheduled snapshot time in which results are saved separately from the original data table and can then be leveraged in other reports and queries serving as its own query base.

Unlike dynamic queries pulling live data from entire database tables, new queries using materialized views as their base only pull from the latest snapshot, so there’s no additional wait time for filters to recalculate.

What was the impact? Using a new materialized view query base we configured for them, we were able to generate query and reports in seconds with no more timeouts. The client was able to glean the important data they needed and make informed decisions on where to put their future dollars. It was a win for the client and a reminder of my early frustrations with generating reports in Slate.

The next time you’re struggling to cope with unwieldy amounts of data, remember to check out this feature. If you want to learn more about how SIG can help you build impactful reports and use your data and Slate more efficiently, give us a call.