Features
Platforms
Services
Deployments
By Use Cases
By BI Tool
Overview
Customer Success
About
Partners
This is the last feature you can leverage to boost query performance. Let’s make the final touches to a Kyligence Data Model! If you have not read the previous blogs of this series, please go to the following links — Part 1, Part 2, Part 3, Part 4.
As defined in Part 2, a segment represents a physical folder under the hood. To fully leverage the segment-level folder pruning capability, it is recommended to size a segment according to real usage patterns.
For example, if most business users fetch quarterly data in a query, three months’ data per segment will assure great query performance. If most data consumers query annual data each time, then load 12-month data into a segment. Bottom Line — The less scattered segments, the faster a query runs.
A performance test has been run to demonstrate how QPS(Query Per Second) is tripled from 4+ to 12+ by just changing from 1-month data per segment to 6-month data per segment. In this test, most queries are aggregating quarterly or half-year data per query.
Best Practice: Merging small segments into larger ones on a regular basis is a good practice to ensure a consistent query performance over time.
If you have been practising building models using Kyligence products or Apache Kylin, you may have realized that one of the trickiest parts of this whole process is to KNOW user query patterns in order to create the “perfect” indexes and “perfect” layout.
Good News — we have a Pythonic way of discovering the unknown knowns from your SQL/MDX query history. We can heat-map your query patterns using a Python script plus a visualisation tool like Tableau or PowerBI. If you are interested, hit the Clap & Share buttons, so I will be more motivated to share it with you.
Hope this step-by-step model design guide has helped you successfully “WHOA” your stakeholders with the lightning-fast query performance.
If you have developed your own best practices, please feel free to share them in the comments. I’d love to see your creative techniques.
If you have any questions, please leave your comments down below. Always happy to help!
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.
Already have an account? Click here to login