Excel Your KPIs with AI Copilot Start for free today
Your AI Copilot for Data
Definitive Guide to Decision Intelligence
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.
Discover the 7 top AI analytics tools! Learn about their pros, cons, and pricing, and choose the best one to transform your business.
Discover operational and executive SaaS metrics that matter for customers success, importance, and why you should track them with Kyligence Zen.
Unlock the future of augmented analytics with this must-read blog. Discover the top 5 tools that are reshaping the analytics landscape.
What website metrics matter in business? Learn about categories, vital website metrics, how to measure them, and how Kyligence simplifies it.
Unlock potentials of analytics query accelerators for swift data processing and insights from cloud data lakes. Explore advanced features of Kyligence Zen.
Unlock power of data storytelling in business. Learn how to convey insights using narrative and visual representations, examples, and benefits.
Explore these exceptional cloud analytics tools. Assess their pros, cons, and pricing to pinpoint the optimal one for your business.
Learn what natural language query is and how it transforms your data analytics. Explore examples of natural language queries in Kyligence Zen.
Discover how AI shapes banking, healthcare, and data analytics sectors. Get insights into the future of industry disruption to guide your decisions.
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
A complete product experience
A guided demo of the whole process, from data import, modeling to analysis, by our data experts.
Q&A session with industry experts
Our data experts will answer your questions about customized solutions.
Please fill in your contact information.We'll get back to you in 1-2 business days.