By Use Cases
By BI Tool
Subscribe to our newsletter>
Get the latest products updates, community events and other news.
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.
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.
Not All Queries Are Created Equal !
Each has its own temperature !
Each temperature group deserves a “personalized” strategy tailored to its unique needs.
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.
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:
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 query patterns. This suggests that the tradeoff has been made on fewer indexes & smaller cube sizes over query performance.
Part of SQL Queries This Example Index Can Fulfil:
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.
Here are three options to deal with this category of patterns:
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.
Come to see the Next Generation of SQL Query Engine
Learn how to achieve alternatives to SSAS.
In this article, we’ll dive into the unified Metrics Platform at Beike, introduce Beike’s practice of building the Metrics Platform infrastructure using Apache Kylin and some real use cases at Beike.
Learn Kyligence Cloud model design principles and how to use Kyligence Cloud to build models.
99 Almaden Boulevard Suite #663
San Jose, CA 95113
+1 (669) 256-3378
Ⓒ 2022 Kyligence, Inc. All rights reserved.
Already have an account? Click here to login