If you have slow queries when reporting table format data in Excel from PowerBI.com, Azure Analysis Services or Tabular SSAS, this could be just the performance boost you need.
Excel Pivot Tables query Power BI and Analysis Services using MDX, and sometimes the MDX they generate is not ideal. XLCubed Grids also query using MDX, but in many cases the MDX is syntactically different and will perform better (a key reason why some customers initially chose XLCubed).
However, when it comes to table format reporting on Power BI (or Azure Analysis Services / Tabular SSAS), MDX queries of any sort can sometimes be slower than we’d like. Response times can be particularly slow where the tables have lots of columns and are large in size.
DAX queries handle this scenario very well, and XLCubed Tables give non-technical users the ability to design this type of report quickly, easily and flexibly. The performance improvement will of course vary by your data source and the specifics of your query, but some comparative timings are below, based on the example from the screenshots when connecting to a Contoso demo model held in PowerBI.com.
So there is a very considerable performance improvement! In these examples the DAX approach is 3.5 to 4 times faster. We all know that time moves slowest when waiting for a report to retrieve, so any speedup is welcome, but this is a huge improvement.
If the scenario sounds familiar, try the four-step process below:
That is very much the vanilla starter for 10, but of course you can use additional XLCubed capabilities to make it a flexible re-useable report, e.g. adding slicers. Two other interesting additions are:
Thanks to Marco Russo who posted on this general topic recently and prompted me to remind users of the approach in XLCubed. For the more technically inclined among you , maybe you’ve been working with Marco & SQLBI and have some super-slick DAX which outperforms our generated syntax in a specific scenario… Here you can use a ‘custom query’ where you specify the DAX manually, or more flexibly get it from an Excel cell to allow for dynamic filtering etc.
Discover how Fluence can help your organization plan better and close faster with more confidence.