From Sluggish to Fast and Furious Reports
Our journey led us to collaborate with a SaaS enterprise grappling with sluggishness in their reporting infrastructure within Power BI and Google BigQuery. Their data dancing in millions of rows – a single fact table having close to 300 million rows of data, and a complex many-to-many relationship in the star schema of the database.
Power BI, their chosen visualization platform, struggled under the weight of complex data, while Google BigQuery, their data repository, faced inefficiencies in query performance, consuming exorbitant resources and driving up costs. Adding Row-Level-Security for report distribution to their architecture was definitely going to kill them.
The Art of Optimization, and Frugality
Our team, armed with a suite of expertise and tools, delved deep into the core of their architecture. We identified areas ripe for optimization and crafted a meticulous plan to breathe new life into their reporting ecosystem.
- Aggregation tables to streamline data access routes between BigQuery and Vertipaq
- Leveraging DAX Studio and Fiddler to test server timings and run times
- A paradigm shift in BigQuery optimization using data marts to slash query times due to data pre-computation
- Schema and model changes between normalized and denormalized structures to enjoy performance trade-offs
The Quantum Leap to Efficiency
The results were astounding—a query once gobbling up 66GB in memory, running multiple times a day, now transformed into a lean, scheduled data mart refresh. This optimization led to an unparalleled reduction in query costs, as the refresh of a single data mart proved more cost-effective than numerous memory-intensive queries.
Not only did our intervention breathe new life into their reporting system, but it also translated into substantial cost savings during their implementation of RLS for report distribution through Power BI Embedded.
