Maximize Business Value by Optimizing Queries with Kyvos

Every organization today wants to extract insights in as near real-time as possible. They are looking to gain the intelligence they need to drive better decision-making, improve customer experiences, and attain a competitive edge in the market. To achieve these goals, they need the ability to blend analytical queries and transactional data. But with rising data volumes, it’s becoming challenging for organizations to get answers to their business questions on time. By the time they get answers to critical business questions, the situation on the ground is changed, and all the efforts go in vain. One of the significant reasons behind delayed insights is poor query performance. A query that previously took seconds or minutes is now taking hours to bring back the result due to billion-scale datasets. Slow-running queries can have a major impact on different areas of business. Let’s take the example of the retail industry where you need real-time insights to make crucial decisions –

Imagine you are a leading retail chain. You want to use your data to handle multiple supply chains, manage the importing and shipping logistics worldwide, predict accurate demand for products, and have insights if a specific product is underselling. But with hundreds of stores, thousands of products, an online marketplace, and thousands of daily visitors, your store had details on almost thousands of transactions per day. Analyzing data at this scale could take hours or days to bring back the results due to poor query performance. In this case, you don’t have time to wait for the results, and you base your decisions on your instincts which could be wrong, making it impossible to capitalize on many real-time business opportunities. And you have to face the mishaps such as wrong products on the shelves, damaging sales, and subpar customer experience. This could also increase stock warehousing costs and incur steep fees for extra product runs and last-minute shipping.

Going through all this massive information to try and make sense of what’s happening in your organization and what to expect going forward can be a nightmare for retailers. By the time you get a hold of things, you’re likely to fix problems retroactively instead of planning effectively for the coming months. In this case, query optimization can get you the speed of thought analytics on all your data whenever needed. It could become a serious game changer in slashing overheads and maximizing profits.

Now the question is what is causing your queries to slow down? And why do you need to include query optimization in your business strategy?

What is slowing down your Queries? How can Kyvos Help in Query Optimization?

Slow queries can negatively impact your business performance. Today business teams must operate quickly to act on opportunities and identify problems. They don’t want to wait for hours or days for the queries to return the vital insights needed to make data-driven decisions. Therefore, query optimization is no longer a luxury and should be part of your business strategy.

Enhancing query performance in the OLAP world mostly depends on how the data is pre-aggregated. Therefore, cube designs are an essential part of the process to achieve the desired performance. A solution like Kyvos uses its revolutionary Smart OLAPâ„¢ technology to design an optimized high-performant OLAP cube by enabling you to understand your data and query patterns before the cube is built and create designs that deliver the performance that you need. But before we get into how you can optimize queries, let’s look into the challenges that are contributing to poor query performance and how Kyvos solves all these challenges –

#Challenge 1: Growing Data Volumes

Global data generation is expected to increase to more than 180 zettabytes by 2025. Organizations are struggling to keep up their analysis with the ever-growing data. Despite considerable investments in modern business intelligence tools to enhance their decision-making efficiency, they often report performance issues when data volume exceeds a certain limit. Every time a user runs a query, the BI tools process a massive amount of raw data. The queries that must return results in seconds take minutes or hours, delaying business decisions or pushing managers to rely on their intuitions. By the time manager receives the report, the information becomes irrelevant, and the organization is deprived of the profit it might otherwise have been able to generate.

To overcome this, many organizations go for OLAP aggregation solutions. Considering a billion-scale dataset with millions of cardinalities and hundreds of dimensions, building aggregates on such data volume is not easy. The simplest option to go with while building a cube is to include all possible combinations with all the dimensions and evaluate them against each other. In this case, the cube size will increase and might impact performance. Therefore, for query optimization, it is crucial to maintain a balance between the query response times and the cube size. The more complex your data is, the more challenging it becomes to achieve this balance. This brings us to a crucial question – how to design an optimized cube that can deliver high query performance while maintaining the cube size?

Solution: Build smart aggregates with Kyvos

Kyvos uses its revolutionary Smart OLAPâ„¢ technology to enable aggregation at scale, as it does not work on the principle of a limited number of expected queries or partial aggregation. Instead, Kyvos builds massively scalable cubes only for the combinations relevant to the business users, queried more frequently, preventing any combinatorial explosion, and mitigating unnecessarily redundant computation. In addition, Kyvos comes with a Smart Recommendation engine that helps you decide memory configurations, partitioning strategy, materialization strategy, and physical view of the cube. This way, you can build highly optimized cubes and store data in a granular form, supporting in-depth analysis across hundreds of dimensions and measures with interactive response times.

#Challenge 2: Inefficient Query Engines

Typically, queries involving the processing of billions of rows become too slow for interactive analytics. In such cases, organizations invest in query brokers that prepare aggregate tables based on user queries and cache them. The warm queries may work fine, but cold queries are slow as they are delegated to the underlying data warehouse or external query engine. In most such cases, the query engines often slow down when analytical complexity increases due to data explosion.

Solution: Kyvos provides native support for SQL and MDX to query its cubes

Kyvos comes with built-in SQL and MDX engines that support a more robust dialect of MDX and SQL and deliver much higher performance than external ones.

#Challenge 3: Complex Calculations

Today businesses need to assess metrics of previous years, months, or weeks to make comparisons and understand the effect of sudden spikes, seasonal fluctuations, and more. If a business is registering millions of daily transactions, then analyzing years of data consumes a lot of resources and time, resulting in slow query performance.

Let’s take an example – imagine you want to analyze your customers’ current and previous year data using a SQL-based model to understand buying patterns, product sales, etc. For that, you need to fire complex queries with multiple joins, such as outer joins and cross-apply, which becomes a huge performance issue. In this case, the SQL-based model aggregates two years of data to execute two queries in parallel, and the same data is scanned twice. Subsequently, the result set needs to be joined for further analysis. As a result, complex calculations are done at runtime, which increases the cost, and performance suffers as the size of your data increases.

Solution: Kyvos simplifies complex analysis

In Kyvos, calculation logic is defined in a unified semantic layer. It is designed to extract calculation logic from business users and deliver results quickly for complex calculations on a billions-scale dataset. Being a MOLAP-based model, most aggregates are calculated in advance. In the above-stated example, while comparing the two years of data, each year’s aggregates are already calculated. The only thing handled at run time is the comparison itself. This is why you get the results instantly without any delay in query performance.

#Challenge 4: Concurrent users

In this era, when businesses are adopting the democratized culture and providing data access to everyone across the entire business ecosystem, they encounter performance issues around user concurrency. Executing the same query on a table with millions of records requires more time than performing the same operation on the same table with only thousands of records. Since every data warehouse has concurrency limitations, with democratized access, users feel comfortable asking multiple questions with varied resource requirements, thus running up against the concurrency limitations. A lot of concurrent transactions can degrade performance. As a result, queries end up in the queue, and users have to wait for hours to obtain reports.

Solution: Scale up to thousands of users with Kyvos

Kyvos can handle thousands of concurrent users without slowdowns or any impact on query performance. You can scale out to add more users, and the infrastructure can handle high concurrency without exploding your costs.

#Challenge 5: Ad hoc Queries:

Ad hoc queries enable business users to get insights whenever needed. Usually, cloud data warehouses work on the partial caching mechanism for frequently used datasets and preserve the results so that whenever you run a query, you get instant results. But when you run an ad hoc query that is not cached, it has to go through all the heavy lifting of reading the data from storage, computing for results, and finally presenting the results. This leads to increased response time and costs.

Solution: Get instant response to ad hoc queries with Kyvos

Kyvos materializes the data for speed so that you get instant responses on heavy queries run across hundreds of dimensions and measures. It allows you to drill down, slice and dice, query, filter, and explore cloud-scale datasets without degrading query performance. Kyvos’ intelligent query engines and robust querying mechanism ensure that you get sub-second responses for all queries, whether standard, ad-hoc, cold, or hot.

#Challenge 6: Inefficient Use of Resources:

Cloud resources do not come cheap. If not planned properly, you will end up paying way more than you anticipated. A good cloud strategy must involve scaling resources as needed. Scheduled scaling makes it possible to scale resources at specific preset times or intervals. It allows you to pre-provision adequate capacity for anticipated demand. You can also configure schedules to reduce capacity when the demand decreases.

Solution: Kyvos optimizes resource utilization

Designed for the cloud, Kyvos makes it easy for you to reduce resource consumption during lean periods and deliver consistent performance during spikes. You can pre-schedule querying resources in response to predictable load changes and optimize your costs. Additionally, our build-once-query-multiple-times approach cuts down query processing costs on the cloud. Once the cubes are built, the resources consumed per query are minimal as queries are served directly from the cube, and you don’t have to go back to the data lake or your data warehouse to process the information.

Now that we know how Kyvos solves query optimization challenges faced by organizations, it’s time to look into some additional features of Kyvos that help optimize queries.

Some Additional Feature of Kyvos That Makes Query Optimization Easy for Businesses

Kyvos comes with a machine learning powered Smart Recommendation Engine that brings in the intelligence required to build optimized cubes by helping you understand your data and query patterns. Armed with smart recommendations based on your usage patterns, you can build smarter aggregates and optimized cubes. Let us now look at some of the features of Smart Recommendation Engine that power this smartness –

Advanced-Data Profiling:

The data profiling feature helps you to understand your data before building the cube. It helps you to get information on the cardinality of dimensions, the minimum-maximum range, the number of unknowns, value distribution, invalid values, and many other useful parameters. Now you can determine dimensions and measures according to the use cases for building the cube and set aside unnecessary ones that are usually added as they are available in the table/dataset. These insights help you to build an optimized cube and reduce cube build time. It also helps improve query performance by enabling you to decide if columns should be taken as attributes, as levels, or if they are required.

Partitioning Strategy:

Identify the partitioning of raw data and user query patterns to decide the best cube partitioning strategy. This feature helps you to

• Configure your partitions to a higher level of aggregation (quarterly, monthly) rather than a more granular level (day).
• Define sub-partitions on columns that are frequently used filter columns in reports.
• Avoid adding high cardinality columns as sub-partitions.

These strategies enable efficient processing of replacing partitions of the cube whenever underlying data in corresponding Hive partitions are modified. This way, queries are run on only the required partitions, and you get faster query responses.

Physical and Logical View of Cube:

This feature is used when your business requirement is not covered in the recommendations of cube design. Use the physical view while designing the cube to get best practice recommendations and a logical view feature for business requirements.

Query Analyzer:

This feature helps you to analyze cube and query performance by using statistical information about usage, user interaction patterns, performance trends, query response patterns, and mostly used dimensions and measures. Using this feature, you can analyze live queries from the Activity Monitor and also drill down to more granular level details such as:

• Total query count,
• Average response time,
• Minimum response time,
• Maximum response time,
• Total users,
• Tracking queries by users,
• Most used dimensions,
• Least used dimensions, not used fields,
• Top 100 slow-running queries, dimensions, and measures used in filtering.

These insights can help you in optimizing queries and cubes.

Kyvos can solve the major barrier to real-time and interactive analytics by optimizing your queries. It’s high time to adopt a solution like Kyvos that enables you to take proactive, preventative measures to optimize queries.