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.Continue reading “Faster Excel Queries For Power BI & Azure Analysis Services”
Version 9 of XLCubed introduced Pivot-views, which allow our powerful grids, small multiples and slicers to be driven directly from lots of different relational sources.
We also added support for Power BI as a data source, and many of our customers are now using XLCubed on Power BI data for a better in-Excel experience and slice and dice.
Many of you will already be familiar with the data refresh options in Power BI, essentially either a scheduled refresh process, or through the Enterprise Gateway.
With pivot-views we wanted to ensure that the data refresh process is very straightforward, and to enable business users to benefit from them without worrying about scheduling data flows or getting involved with discussions about gateway configuration. Fundamentally, the data will automatically update when the report is opened or if any of the query parameters change (either by updating a slicer or if an Excel cell-based parameter value changes).
This means the report builder doesn’t have to worry about the latest view of the data as that is all handled automatically without any custom code, manual intervention or IT assistance.
Here is how it works in detail.
First insert a grid and select a relational database as the source.
We are presented with the query designer, we can pick a table and setup a parameter by entering “@productgroup”:
This gives us an option to link that parameter to a cell or directly to a slicer:
We can now design the grid we want to see:
And view the result.
Now, if I update the driving cell:
The query is re-run and the grid (and anything else driven from the data has updated to reflect the new query results):
This automatic handling of the data refresh continues once the report is published to XLCubedWeb. Whether accessing from Excel, a browser or the XLCubed Mobile App, the report is based on current data without the need for any additional handling or refresh processing… One less thing for the report designer to worry about, one less thing to go wrong!