Build the Common Data Language with the Metrics Platform Start Now
By Use Cases
By BI Tools
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 dealdot 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.
Kyligence Zen intelligently manages data in the retail industry. Read to learn how to develop the "North Star Metric" system to track goals and progress.
Kyligence introduces the deployment of OLAP on top of Azure, including data sources, features, benefits, and prerequisites. Learn more about Kyligence for Azure.
What's OLAP on big data? What're its benefits? Here's everything you need to know about OLAP.
Learn how one big fast-food brand leveraged Kyligence capabilities and implemented precision marketing to maximize profit opportunities.
99 Almaden Boulevard Suite #663
San Jose, CA 95113
+1 (669) 256-3378
Ⓒ 2023 Kyligence, Inc. All rights reserved.
Already have an account? Click here to login
您还可以在云平台中 部署 Kyligence
直接获得 30 天免费试用
请填写真实信息，我们会在 1-2 个工作日内电话与您联系。