Tag: Calculations

DAX Performance tips– lessons from the field

XLCubed/ / Data

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

Before:

 

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]

After:

 

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]

Mind the gap!

XLCubed/ / Grid & Report Formatting, Reporting & Analytics

Today’s blog is going to show you how to use XLCubed’s custom calculation functionality to create column breaks in a grid.  Imagine that you have a report that shows you Reseller Sales across Product Model Categories over a 12-month time period.

 

 

 

There’s nothing wrong with this report but don’t you think it would be nicer if there was a way to separate out each quarter block ie put in a divider column between March and April, June and July, September and October.  That would make it much easier to read and show clearly where each quarter period started and ended.

So let’s start by creating a custom calculation.  Click the highlighted icon and give your custom calculation a name – let’s call it ColBreak.  It’s connected to the Date.Calendar hierarchy.

 

Now in the Expression area enter  a blank string starting and ending with ” (double-quote).  Click OK.

To insert this into our report we now go to the Hierarchy Editor for Calendar Date – expand the All member and you will see ColBreak.

Drag this across and insert it into the report.  We will insert it after March, June and September and click OK.

 

 

The report now looks like this:

 

Now let’s format this column break so that the we don’t see ColBreak appearing as a column heading.   You need to right-click to get XLCubed’s right-click menu and then choose Format This Member.

We will choose white for the Font colour before clicking OK.

The report now looks like this with clear demarcations between each quarter:

 

 

 

Solve order shenanigans

XLCubed/ / Reporting & Analytics

Today I’m going to blog about a problem we recently solved in a client’s cube, an error in the Mdx script that’s very easy to make if you aren’t careful.

We’ll run a simple example in AdventureWorks (what else?) to demonstrate the issue.

The client had already added a calculation to their cube to show year-on-year growth. The formula is:

Create Member CurrentCube.[Measures].[Delta to PrevYear] as
(
    ([Measures].[Internet Sales Amount])
    -
    ([Measures].[Internet Sales Amount],
        ParallelPeriod(
            [Date].[Calendar].[Calendar Year],
            1,
            [Date].[Calendar].CurrentMember
        )
    )
)
/
    ([Measures].[Internet Sales Amount],
        ParallelPeriod(
            [Date].[Calendar].[Calendar Year],
            1,
            [Date].[Calendar].CurrentMember
        )
    )
, Format_String = "0.00%";

(some error checking removed for clarity)

This screenshot shows a couple of simple XLCubed Grids showing the real value, and below the percentage change. I have added in an Excel calculation to show the results are as expected.

Later during the cube development, the client added a calculated member in their Product dimension, one that gives a total excluding one of the product categories.

To replicate this I’ll add a calculation for “All Ex Bikes”:

Create Member 
CurrentCube.[Product].[Product Model Categories].[All Products].[All Ex Bikes]
as
(
    ([Product].[Product Model Categories].[All Products])
    -
    ([Product].[Product Model Categories].[Category].&[1])
);

And if we run the report again we get the following.

Notice the cell I’ve highlighted. The “All Ex Bikes” calculation works fine on the normal measure, but it gives totally the wrong number for the percentage calculation. What’s going on?

The problem is that in the cell highlighted Analysis Services has two calculations to think about when working out the result.

  • Compare this year to last year
  • Get the “Grand Total”, and subtract “Bikes”

As the number returned is 1.85% we can see that Analysis Services has chosen the second option, “Grand Total” – “Bikes”.

What we really want is for the calculation to be done by getting the subtotal, and then doing the percentage change based on that.

Fortunately the fix was a simple one. Analysis Services will run the calculations in the order they are found in the Mdx Script, so to fix the issue we simply moved the new “All Ex Bikes” definition up above the percentage calculation.

Now the number returned matches our expectations.

Pass/Solve Order can be a complex topic, so you may need to be careful.

In this case the number is totally wrong, so it was easy to spot, but some bugs will be much more subtle, so watch out!