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:
- In Excel, add an XLCubed connection to Power BI / Azure Analysis Services / Tabular SSAS
- Add a new Table from the ‘Grids & Tables’ menu option, use the default ‘Tabular Analysis Services’ query Type
- Select the columns you want to see, and set any required filters in the dialog shown below
- Press ok!
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:
- Filtering based on an Excel range: This lets the users quickly type a date, product code or sales rep name into a cell rather than going through a dialog. In the report designer screenshot you can see that this report is filtered by the content of cell $G$2, and we’ve simply picked that cell (or could be a range of cells) in the range picker in the UI.
- Additional calculations: The central model is the best place for core calculations, but users need flexibility to add their own where required. You can simply ‘Add Calculation’ to add an additional column at any position, and specify a standard Excel formula referencing other columns in the table, or external cells. These are row-dynamic with the table itself and let users do the type of simple calculations which they need to on the central data model with no additional training.
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.
