Achieving analytics nirvana: Query performance without the complexity
- Blog
- Uncategorized
Query performance issues are the one problem that companies of every size have an equal chance of running into
Query performance issues are the one problem that companies of every size have an equal chance of running into with their business intelligence and analytics deployments―that was one of the conclusions of the ’22 BARC survey on The Most Common Business Intelligence Problems. And amongst larger organizations, which often have greater data volumes and more complex data models, they found poor performance is the number one issue for analytics headaches.
Poor performance—like waiting for dashboards or dashboard components to load or reports to run hurts everyone. That’s a death knell for analytics success—whether you’re delivering analytics to your team or embedding it in your product.
Sifting through the SQL queries generated by some analytics tools can be like trying to decode hieroglyphics. While manually optimizing the SQL with complex data models gets torturous, quickly becoming hard to read, debug, and test―risking the integrity of results. Not to mention poor queries can be costly in terms of database load. In other cases, it can mean standing up an intermediate solution that sits between the analytic tool and the database to try and boost performance (think costly headache).
Simply, analytics teams should be focused on rolling out more dashboards, more insight, and more value, not buried in query tuning, testing, database optimization, and debugging.
The goal is to achieve strong query performance to delight consumers and drive engagement without bringing those in charge of delivery, to their knees in never-ending manual query optimization and trying to decode obtuse SQL statements, so everyone can focus on what matters.
Getting there requires overcoming multiple issues. Data volumes are of course greater than ever, as is data model complexity. This is compounded by the fact that there are simply so many data warehouses, each with its own SQL nuances.
There has to be a better way. There is, and we just patented it.
It’s called the Sisense Analytic Engine. We think it’s at the nexus of easy-to-achieve query performance and clear query transparency that organizations are searching for.
The Sisense Analytic Engine. Your personal SQL wizard.
When we started designing Analytic Engine, we started with a simple premise, “make achieving incredible analytics performance incredibly simple”. Of course, a critical design goal was to improve dashboard load times and ensure strong performance. And we of course wanted to dramatically improve the time to widget rendering.
But we also while making it simple for consumers and analysts to review a highly readable query, not complex SQL, so they have confidence in the outcome, which was our second design premise, “fast queries are no good if their results can’t be validated.”
In a nutshell, make achieving the best performing queries simple and transparent, even with data model complexity, and no matter how much data security was in play.
Think of the Analytic Engine as your own personal SQL optimization wizard, who is working quietly behind the scenes, to turn every query into the best possible most efficient query―painlessly.
Here’s where it gets fun. In Sisense, you’ll see a very readable JAQL expression, that expresses exactly the meaning of your query, showing dimensions, aggregations, and filters in play. That provides transparency and readability.
For example, something like this:
But here’s the cool part. Behind the scenes, Analytic Engine will convert it to highly optimized SQL, and it uses a vast range of strategies and cues to generate the best possible. For example, it collects information from widget fields, filters, and reference data, compares different query plans and their cost of execution, the best ways to apply joins and filters, and improves how calculated measures are executed. That’s the patented part.
Benchmarking query acceleration delivered Analytic Engine
But we started this post with query performance, and the need to improve it painlessly. And with Analytic Engine, we’re seeing up to 5-10x faster queries, even with complex data security rules in play.
And with these results, there’s no need to wade through SQL, no need for database optimization, or other techy tweaks. Every query starts out as clearly defined relational algebra, so you start with a highly readable query for every database, every time, and Analytical Engine turns it into great SQL.
We’re confident these kinds of results really are a game-changer in analytics—if you want to learn more, reach out and connect with us.