Folding solutions
Power BI query performance can be a bit like knowing what to take on vacation and the order to place it in the bag. The concept in Power BI of only taking what you need is called Query Folding. It’s the art of making your data smaller. Query Folding leads to better performance and lower costs.
Why? Because Query folding will push down transformations, aggregations and joins to the data source where they can be executed more efficiently. That’s where you’ve set up indexes and partitioning already. It makes sense to let your data source do the heavy lifting.
Right? This in turn reduces the load on Power BI and improves processing speed because less data is received and prepared by Power BI.
Limitations of standard connectors
Standard connectors and database drivers are ok for normal, simple use cases. However, when dealing with large or complex data sources, standard connectors and drivers may not support query folding. Instead, Power BI will try to do it for you, and it will do that by pulling all your data across. This will put more memory and processing load on Power BI.
Power BI may not know what type conversions your data source can support and once again convert it for you, possibly incorrectly. Further, your data source interface may be an API for which there is no connector.
This is where custom connectors and drivers provide a solution to better performance and data conversion.
Use case with a painful plot twist
Consider a sales report. What I really need is a report of total sales by month by region, period.
I start creating the report and I’m going to do it all in Power BI. Why not? I don’t need to think too much, and I’ll test it with a few rows of data. Life is good! So, maybe I do need that entire measures table in case I later want to calculate sales by day. Or I need that stores table so I can produce sales by individual store.
Wait!
Once I scale the data and see how slowly my report is running, I realize, with a little foresight, pulling all those millions of rows into a Power BI capacity and aggregating there wasn’t such a good idea.
Maybe I can get the data source to do that crunching for me? Just give me the 12 rows of month end sales totals by region.
We probably have a really cool SQL script we run directly on the data source already, and know it works. Now I have to translate that into M language! Is Power BI going to do that as efficiently? Plus, I am consuming vast amounts of storage which I am paying for twice. Once in my data source and again in Power BI. Why would I do that?
A Custom Connector Solution
Data Folding seems like a great idea. How can I determine what gets pushed down?
That’s the tough part. Power BI needs to know what it can push down to the data source and what it has to do for you. How does it do that? The connector and the database driver work in tandem to tell Power BI what the data source can support: aggregations, joins, type conversions etc.
In addition, some queries get too complex to write in M language. If it’s easier to write a native query then the connector has to support that, but not all do.
A custom connector with a compatible database driver can provide a solution.
It provides full control over interactions with the data source. It can optimize how data is fetched. It can provide native query support.
Conclusion
At Improving, we write custom connectors and database drivers. We can drill down to your data source, but we don’t stop there. We can implement data sources too.
Using Power BI custom connectors with Query Folding, native query support and compatible drivers can result in faster reports and lower costs than off the shelf solutions.
Now time to grab that small bag, get that prime spot on the bus and be first on the beach!
Ready to learn more? Reach out to us.
