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”
XLCubed has supported a drag/drop interface for creating reports against Tabular Analysis Services since the first release of the new engine. It lets users easily create reports which run DAX queries on the cube, and we’ve often seen very good performance at customers when MDX against Tabular was a cause of long running reports.
So when we were approached at SQL Pass in Seattle by some attendees who had a SSAS Tabular performance issue we were optimistic we’d be able to help.
In this case the business wanted to retrieve thousands of rows from the cube at the transactional level, and the first approach had been to use PivotTables in Excel. To get to the lowest level they cross-joined the lowest levels of all the hierarchies on the rows section which would give the right result, but performance was terrible, with several queries taking 20 minutes or more and others not returning at all.
We hoped using an XLCubed table running DAX would be the solution and created the same report in the designer. Sadly while performance was a little better it was still far from acceptable; the model was large, and the number or columns combined with their cardinality meant that a lot of work was being done on the server.
XLCubed’s DAX generator was trying to cross-join all the values from each column, which had worked well for our other customers. But when there are a dozen columns including the transaction ID things do not go so well. DAX in itself is not a magic bullet and SSAS Tabular models can hit performance problems on low level data – we needed a new approach.
After some investigation we discussed the issue and our thinking with our friends at SQLBI and determined that instead of cross-join we wanted an option to use Summarize() instead as this only uses the rows in the database, and it can access columns related to the summarized table which were required for the report.
As the customer’s report had the transaction ID in it the result wasn’t aggregated, even though we were using summarize. But we wanted to add true transactional reporting too, using the Related() function.
Finally, SQL 2016 adds a couple of new functions, SummarizeColumns() and SelectColumns(), both of which are useful for this type of reporting, but offer better performance than the older equivalents.
The end result in XLCubed is a new option for DAX tables to allow users to set the type of report they want to run, and some internal changes so that XLCubed will automatically use the most efficient DAX function where they are available.
A beta was sent to the business users and the results were fantastic. The report which had run for several minutes now completed in a few seconds, and 20 minutes was down to 15 seconds – we had some very happy users!
The changes will be in the next release of XLCubed so that all our customers can benefit from the improvements. It’s always nice when a customer request helps improve the product for everyone.
A sample of the syntax change is included below
EVALUATE FILTER ( ADDCOLUMNS ( KEEPFILTERS ( CROSSJOIN ( VALUES ( 'Customer'[Education] ), VALUES ( 'Product'[Color] ) ) ), "Internet Total Units", 'Internet Sales'[Internet Total Units], "Internet Total Sales", 'Internet Sales'[Internet Total Sales] ), NOT ISBLANK ( [Internet Total Units] ) ) ORDER BY 'Customer'[Education], 'Product'[Color]
EVALUATE FILTER ( ADDCOLUMNS ( KEEPFILTERS ( SUMMARIZE ( 'Internet Sales', 'Customer'[Education], 'Product'[Color] ) ), "Internet Total Units", 'Internet Sales'[Internet Total Units], "Internet Total Sales", 'Internet Sales'[Internet Total Sales] ), NOT ISBLANK ( [Internet Total Units] ) || NOT ISBLANK ( [Internet Total Sales] ) ) ORDER BY 'Customer'[Education], 'Product'[Color]
SQL server 2012 has recently been released to manufacturing, and at XLCubed we’re well placed to take advantage of everything that is new in 2012.
SQL 2012 delivers Business Intelligence under the ‘BISM’ umbrella (Business Intelligence Semantic Model). BISM comes in different flavours though:
- BISM Multi-dimensional
- (Latest version of Analysis Services as we know it)
- BISM Tabular
- In-Memory Vertipaq
- Direct Query
For client tools, BISM Multi-dimensional is largely the same as connecting to existing versions of Analysis Services, with MDX being the query language. For XLCubed we can leverage what we already have in that respect, and the transition is seamless.
BISM tabular is different though. If you choose to deploy in-memory to Vertipaq, client tools can still use MDX, and as such don’t need significant change, other than to handle the tabular rather than hierarchical data environment. However if the deployment is Direct Query (for example for real-time BI), the only available query language is DAX.
There are best use cases for the different deployment options, but it’s fair to say there is a degree of confusion in the space at the moment about the relative merits of each. We’ll try to shed some light and guidance here over the next weeks and months. As a product though, it’s important for us to support and extend the full range of 2012 BI deployment options, and to make these available and accessible to our customers. That’s exactly what we’ve done for version 7.
XLCubed v7, which releases next month, is a client for both MDX and DAX, and as such provides one consistent client interface in Excel and on the Web which can access any of the SQL 2012 deployment models for BI. We are also adding a much richer relational SQL reporting environment.
We are really pleased with some of the beta feedback we’ve had to date, and if you’d like to trial the beta version contact us at firstname.lastname@example.org .
We’re looking forward to releasing the product next month, and will be previewing it at SQL Server Connections next week in Vegas.