Kyligence Cloud Model Design Principles Part 3 - “Perfect” Index

If a Leadership Dashboard is Not FAST or LIGHTNING-FAST, then it’s BROKEN.

Looking for a battle-tested solution to improve Leadership Dashboard Performance? Keep reading. Hunting for SSAS Alternatives on AWS or Azure or Google Cloud? Keep reading. Interested in learning how to meet an established query performance SLA like 95%+<3s & slowest<10s? Keep reading.

In this blog, we’ll look at the second building block for a “PERFECT” Query Performance — “Perfect Index”. If you have not read the previous blogs of this series, please do click the following links — Part 1, Part 2.

What is a “Perfect” Index?

At the core of Kyligence product is Apache Kylin, an open-source distributed OLAP engine. In Apache Kylin’s dictionary, an Index is a physical dataset precomputed to accelerate SQL queries. The closest analogy to this concept is a materialized view generated from a SQL query. This physical view can resolve both this ancestor SQL query and all of its sub-queries. So a fully-loaded data model is like a box holding hundreds of materialized views, and each can answer one or more query patterns. Kyligence’s query planner will carefully choose the most time-effective & cost-efficient index out of all the usable indexes to fulfil a query. A perfect index comprises the exact same dimensions and measures of a query, no more than that. Consider the following SQL Example:

A “Perfect” Index for this query looks like this:

As a general rule, if an index perfectly matches a query pattern, it should produce the lowest possible latency. Otherwise, a query becomes slower as the difference between the query pattern and index increases. However, trying to precompute indexes for every possible query pattern is unrealistic since model building time increases as the number of indexes grows. Here is a solution to achieve a balance between reasonable model building time and terrific query performance. Let me introduce another concept I “coined” — Query Pattern Tiering.

Why Query Pattern Tiering?

Not All Queries Are Created Equal !

Each has its own temperature !

Each temperature group deserves a “personalized” strategy tailored to its unique needs.

Tier 1 — Hot-Warm Patterns

The majority of Hot/Warm Patterns come from existing dashboards, executive reports, and critical business metrics. Those patterns are not subject to frequent changes and have a large user base. In this case, creating one index per pattern would be the safest way to keep stakeholders and customers happy.

Best Practice

Two options are listed down below to generate indexes for Tier 1 Patterns; however, the second is highly recommended for Kyligence data modellers, which saves your time and offers better flexibility when it comes to pattern changes:

• Option 1: Manually create one Aggregate Group for every query pattern
• Option 2(Recommended): Use Kyligence AI Modeling Engine to bulk create indexes

Tier 2 — Warm-Cold Patterns

Warm/Cold patterns are less critical or less frequently hit user queries compared to the first tier. So it is recommended that data modellers first categorize those queries into a handful of query groups and then manually create one aggregate group per query group to generate “medium-sized” indexes for all dealdot query patterns. This suggests that the tradeoff has been made on fewer indexes & smaller cube sizes over query performance.

Example Index:

Part of SQL Queries This Example Index Can Fulfil:

Tier 3 — Cold Patterns

Cold Patterns are the least popular query patterns within a timeframe, but there is still a good chance that users will issue them over time.

Best Practice

Here are three options to deal with this category of patterns:

• Option 1: Create a few “Super Fat” Indexes to cover all cold patterns by joining dimensions in Aggregate Groups.
• Option 2: Rely on Safety Net Index only when your business has less strict requirements on performance for those cold queries.
• Option 3: Load a copy of data into Kyligence Tired Storage to leverage the high-performance MPP engine — Clickhouse to maintain a consistent sub-second query experience for non-aggregation or ad-hoc queries. For aggregation queries, Clickhouse’ performance won’t be as fast as Kyligence indexes, but it is way faster than other query engines or data warehouses out there.

What is Safety Net Index?

Safety Net Index is a necessity in a Kyligence data model. It serves as the final guard to avoid push-down queries that are oders of magnitude slower than other queries hitting a Kyligence data model.

Safety Net Index comprises all dimensions and measures used in a model. Therefore, any query hitting this index is expected to be slower than other spot-tuned queries.