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.
We have met a case that an e-commerce company had run Apache Kylin over 21 nodes, they turned to KyBot and looking forward to a diagnose about their cluster health status and advices on Cube optimization. KyBot analyzed a five-day time span diagnostic pack, which including 98 Cubes as well as over 7000 Queries, ultimately identified the bottleneck of query process and optimized it.
98 Cube, its total size is 871GB and with a quite low expansion rate; over 86% queries are completed within 1 second, 98% queries are completed within 5 second. Besides, average query performance goes greatly.
The medium of building’s time consumed is below 15mins, which is in the normal range.
Select CATEGORY_LV1, sum(order_amt) as order_amt, sum(payment_amt) as payment_amt, sum(discount_amt) as discount_amt, sum(shipping_fee) as shipping_fee, sum(tax_amt) astax_amt, sum(coupon_amt) as coupon_amt, count(distinct CUSTOMER_ID) as uv, count(distinct SHIPPING_AGT_ID) as shipping_agt, count(distinct province_id) as province from t_sales_order WHERE PART_DT > ’20160901’ and PART_DT < ’20161001’ group by CATEGORY_LV1 order by CATEGORY_LV1
There are 8 dimensions displayed on the SQL executing process page, showing that 8 dimensions within a Cube has been involved in the SQL execution. Only the PART_DT is an effective filtering dimension and the CATEGORY_LV1 is a working aggregation group dimension, the rest dimensions are mandatory to participate execution yet not matchable for the case, causing a low match degree overall(25%). Uses are capable to cancel this mandatory setting or use joint instead.
The filtering dimension PART_DT is at the end of the index combination; considering that front dimensions have an ultra high cardinality that leading a costly filtering time consuming and inefficient query, we sincerely suggest users update ranking of index dimensions.
As the SQL executing life cycle analysis diagram shows, the blue part refers to SQL works on parallel scanning among multiple storage nodes, the green part refers to SQL executions on query nodes. The green part is much longer, suggesting that executing query bottleneck is on query nodes. Due to the reason before, we advise users to reduce data post aggregation pressure or improve query nodes performance.
Add a Hierarchy aggregation group, which includes CATEGORY_LV1 and CATEGORY_LV2; and add a Joint aggregation group, which includes SELLER_ID and SHOP_NAME to cancel the defaulted mandatory aggregation group.
Queries hit a more matchable Cuboid, leading to a great improvement on query efficiency, and query node’s running time has been shorten to 0.4 seconds.
According to KyBot’s analysis, correspondingly, we optimized OLAP cube and increased internal storage of query nodes. Comparison tests show that the query efficiency is significantly improved. Next, we will continue to optimize Cube according to the further analysis.
Learn about the fundamentals of a data product and how we help build better data products with real customer success stories.
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.