SQL Server 2012/Denali | PowerPivot

In my last post I wrote about using delta values instead of full snapshots. However, the amount of data is identical if we using a sparse snapshots instead. For this purpose, the source data from my last post has just to be converted to absolute values as shown below:

Only delta values		Only snapshots (changes)

So the amount of data is not really changed. But with the sparse snapshots the computations get a lot more difficult. If we have delta values (left table) we can simply sum up all deltas to a given date and this works for all of our related tables (for example products). With sparse snapshots (right table) we have to find out the last value per product and then add the results up to the total, so we have to do a calculation per product before.

Before we get to the calculation, here is the very simple source data model that I used:

It took me some time to figure out the following solution and I’m pretty sure that there must be an easier method. So feel free to experiment and write comments.

Here is the final code:

Stock:=
    SumX(
        Summarize(
            ‘Product’
            ,  ‘Product’[Product]
            , "LastStock"
            , calculate(
                Sum(‘Stock’[StockLevel])
                , dateadd(
                    LastDate(‘Date’[Date])
                    ,-floor(
                        LastDate(‘Date’[Date])
                        –
                        MaxX(
                            Filter(
                                Summarize(
                                    DatesBetween(‘Date’[Date], date(2000,1,1), LastDate(‘Date’[Date]))
                                    ,’Date’[Date]
                                    , "X"
                                    ,  calculate(Sum(‘Stock’[StockLevel]), ALLEXCEPT(‘Date’, ‘Date’[Date]))
                                )
                                , not isblank([X])
                            )
                            , ‘Date’[Date]
                        )
                        , 1
                    )
                    ,DAY
                )
            )
        )
        ,
        [LastStock]
    )

This is how are data looks like without the computation from above:

Since the data is sparse we only see the stock at dates where there are changes. For the aggregation I used the Sum function (which doesn’t make much sense here for the date aggregates) and you also see that the row total over the products only takes products into account when there are changes.

And here is the resulting Excel pivot table using the calculation from above:

As you can see the value for Quickerstill starts with 20 then drops to 18 at January 3, then to 15 at January 5 etc. Also the totals are correct now.

The remaining part of this post is about the formula from above, so it’s up to you to decide if you want to continue reading. The most important point here is that the calculation is much easier when working with delta rows or delta rows with intermediate snapshots (for example each first day of a month, quarter, year).

Details of the calculation

The first question is about the way we’re doing the calculation. Wouldn’t it be easier and faster to have them on the rows and therefore persisted (is ‘persisted’ the right term when talking about an in-memory database??)? Well, if you look at the screenshots above, the only reason we’re doing all this is because we need calculated values on “rows” that do not exist. In the last screenshot there is no row for January 31, but the value has to be computed: For Notate it’s the value of January 1 and for Quickerstill it’s the value of January 21. So we have to use a calculated measure in the model.

To makes things easier here, let’s start with a single product (Quickerstill). I want to calculate the last date for which I have a value. To do this I filter the date range from a very early date (2000/1/1 here) to the last date in the current context to those values, where the sum of the stock amount (any aggregate would do) is not blank, then take the biggest date (max function). This is the code:

Step1:=
    MaxX(
        Filter(
            Summarize(
                DatesBetween(‘Date’[Date], date(2000,1,1), LastDate(‘Date’[Date]))
                ,’Date’[Date]
                , "X"
                ,  calculate(Sum(‘Stock’[StockLevel]), ALLEXCEPT(‘Date’, ‘Date’[Date]))
            )
            , not isblank([X])
        )
        , ‘Date’[Date]
    )

If I include the original measure and this calculation in a pivot table, this is the result:

For each day our calculation returns the last date with data. For example the calculated date for January 12 is January 6 as this was the last day with data before January 12. It seems we are already close to the solution but please keep in mind that the calculation from above would not work for more than one product (for example if the products are not filtered) as the date for each product has to be different. If we filter the pivot table from above for product Notate, the results would look totally different. Here are the first rows:

But let’s ignore this for a second and keep the filter on Quickerstill. The next task would be to calculate the stock level at the calculated date. This sounds easy and the following formula was my first approach:

Step2 (with error):=
    calculate(
        Sum(‘Stock’[StockLevel])
            ,MaxX(
                    Filter(
                        Summarize(
                            DatesBetween(‘Date’[Date], date(2000,1,1), LastDate(‘Date’[Date]))
                            ,’Date’[Date]
                            , "X"
                            ,  calculate(Sum(‘Stock’[StockLevel]), ALLEXCEPT(‘Date’, ‘Date’[Date]))
                        )
                        , not isblank([X])
                    )
                    , ‘Date’[Date]
                )
    )

The code in blue is the exactly the code from above which gives the filter context for the calculation of the sum of the stock level values. However, this results in an error:

Semantic Error: A function ‘MAXX‘ has been used in a True/False expression that is used as a table filter expression. This is not allowed.

I love the ‘This is not allowed’ here. Actually there is no reason why it isn’t and I’m not feeling like I’m doing something illegal here. Maybe it’s also just a limitation of the CTP 3 beta release that I am currently working with. I also tried to wrap the MaxX function in a DateAdd with 0 days but this is also ‘not allowed’.

But there is a way which can be found after some time of experimenting. I take the number of days between the last date from the context and the date from the calculation in blue and use this result to correct my last date. Sounds confusing? Let’s start with the number of days. Here is the next step of the calculation:

Step2:=
    floor(
        LastDate(‘Date’[Date])
        -
        MaxX(
            Filter(
                Summarize(
                    DatesBetween(‘Date’[Date], date(2000,1,1), LastDate(‘Date’[Date]))
                    ,’Date’[Date]
                    , "X"
                    ,  calculate(Sum(‘Stock’[StockLevel]), ALLEXCEPT(‘Date’, ‘Date’[Date]))
                )
                , not isblank([X])
            )
            , ‘Date’[Date]
        )
        , 1
    )

Again the code in blue is the last date calculation from above (step 1). Let’s take a look at the result of this calculation:

As you see, the new calculation tells us, how many days we have to go back in time to find a value. For example for January 14 we get a value of 8 meaning we have to go back 8 days to January 6 to find a value. Now we can wrap this in the calculation of the stock value and for some reason that I don’t understand, this is not illegal anymore although I’m doing exactly the same as I did before. Here is the calculation:

Step3:=
    calculate(
        Sum(‘Stock’[StockLevel])
        , dateadd(
            LastDate(‘Date’[Date])
            ,-floor(
                LastDate(‘Date’[Date])
                –
                MaxX(
                    Filter(
                        Summarize(
                            DatesBetween(‘Date’[Date], date(2000,1,1), LastDate(‘Date’[Date]))
                            ,’Date’[Date]
                            , "X"
                            ,  calculate(Sum(‘Stock’[StockLevel]), ALLEXCEPT(‘Date’, ‘Date’[Date]))
                        )
                        , not isblank([X])
                    )
                    , ‘Date’[Date]
                )
                , 1
            )
            ,DAY
        )
    )

Again the code from the last step is colored blue (step 2). Now we’re pretty close to the final formula. Let’s check the result when including both products:

Now the result for each product is already working correctly. But the total is still not correct as you can see from the line marked in red. For January 2 the last date with data is the same for both products (January 1), therefore the value is correct. But for January 3 there are different days for the last stock value, so we only see one product in the total.

However, the remaining part is not difficult. We simply summarize (group) by product and take the sum. Again, the code in blue is the last code from the step before (step 3):

Stock:=
    SumX(
        Summarize(
            ‘Product’
            ,  ‘Product’[Product]
            , "LastStock"
            , calculate(
                Sum(‘Stock’[StockLevel])
                , dateadd(
                    LastDate(‘Date’[Date])
                    ,-floor(
                        LastDate(‘Date’[Date])
                        –
                        MaxX(
                            Filter(
                                Summarize(
                                    DatesBetween(‘Date’[Date], date(2000,1,1), LastDate(‘Date’[Date]))
                                    ,’Date’[Date]
                                    , "X"
                                    ,  calculate(Sum(‘Stock’[StockLevel]), ALLEXCEPT(‘Date’, ‘Date’[Date]))
                                )
                                , not isblank([X])
                            )
                            , ‘Date’[Date]
                        )
                        , 1
                    )
                    ,DAY
                )
            )
        )
        ,
        [LastStock]
    )

Now, this does the trick and we end up with the final screenshot as shown at the beginning of this post.

If you like you can play with the formula by downloading the sample work book here (right click on the link, then choose ‘Save as…’). You will need the PowerPivot Add-In CTP 3 or later in order to open the workbook.

SQL Server Denali | PowerPivot

Custom aggregates can be created using cube scripts in BISM multidimensional (SSAS OLAP cubes). How can we do this with BISM tabular? In many cases, simple DAX calculations can solve this for us.

I’m referring to the example of my previous post about semi additive measures. Let’s say we’re monitoring the stock of two products we’re selling. For the totals we want to see the average stock over time. At least once a month we’re taking a snapshot of the stock. If we have more than one snapshot per month, the monthly total computes as the average of those snapshot. For aggregation above the month level we want to take the average of the monthly averages. At first, this looks like we only have to use average as the aggregation function. But the average of averaged values is not identical to the average of all values. Let’s take a look at this source data table:

For product Notate we have a single measurement of 50 pieces in February. For product Quickerstill we have 8 distinct measurements with an average of 50 pieces in February. However, when we look at the total average for Quickerstill, the 8 distinct measurements in February result in a higher weight of the February average and therefore in a higher total average of 44 instead of 30:

The Average Stock measure in this example is the same semi additive measure as in my previous post and computed like this:

AVG Stock:=Sum([Stocklevel])/DISTINCTCOUNT([Date])

The requirement for the custom aggregate means, that we also want to see a total average of 30 for product Quickerstill (20+50+20=90, 90/3=30). This requirement is somewhat unusual as the computation above gives the “correct” average of all values. One interpretation is that the weight for computing the average of two or more months is not influenced by the number of measurements within the month.

We can achieve this in a way that is very similar to the semi additive calculations from my last post. Here is the resulting formula:

Special AVG Stock:=AVERAGEX(SUMMARIZE(‘Stock’,[Year],[Month],"AvgStock",AVERAGE([Stocklevel])),[AvgStock])

This formula simply summarizes the average stock at a grouping level of year and month. Then, in a second step, it takes these values and computes the average of them. By doing so, we have broken the aggregation into two layers. First we average by month, then we take the average of those values.

Here is the resulting table using the new aggregate:

And after expanding the February values (2nd month) we clearly see the how our custom aggregate works:

Of course, this is just a simple custom aggregate but it is remarkable that we didn’t need any kind of cube script with scope-statements to achieve this but only a very simple DAX expression.

SQL Server 2008 | SQL Server 2008R2 | SQL Server Denali | PowerPivot

Yes, I’m a supporter of equal rights of DAX and MDX. And like many others, I can’t wait to have BISM multidimensional (aka OLAP Cubes) supporting DAX so that we can use project Crescent on top of all those nice cubes. But back to my topic. My last post was about moving averages in DAX and I was so sure I blogged about calculating them in MDX before… but I didn’t. This is not fair.

On the other hand, Mosha Pasumansky, the godfather of MDX, wrote an excellent and very complete article about this topic and I can only suggest reading it. It doesn’t only cover simple moving averages but also weighted and exponential ones. Also Bill Pearson wrote a very good step-by-step guide about this topic. You can find it here and I can only suggest reading it.

So, basically there is no need for me to write another article about this. Therefore this will be a very short blog post… ah, I just remembered something I may write about. Mosha and Bill both investigated on the calculation of moving averages within a query. In the context of a specific query, things are sometimes easier compared to the situation where you create a cube measure that has to work under different query conditions. For example, you cannot be sure which hierarchy has been used.

The first thing that comes into mind is the wizard for adding time intelligence. This wizard does a pretty good job. The main result is a short piece of code that is inserted into the cube script. This piece of code looks similar to the following example:

Scope(
       {
         [Measures].members
       }
) ;

/*Three Month Moving Average*/ 
  (
    [Date].[Calendar Date Calculations].[Three Month Moving Average],
    [Date].[Month Name].[Month Name].Members,
    [Date].[Date].Members
  ) 
    =
  Avg(
       ParallelPeriod(
                       [Date].[Calendar].[Month],
                       2,
                       [Date].[Calendar].CurrentMember
       )
       : [Date].[Calendar].CurrentMember
       *
       [Date].[Calendar Date Calculations].[Current Date]
  ) ; 
End Scope ;

The result can be intuitively used by end users as they simply have to choose in which kind of view the data should appear (actual, three month moving average or any other calculation generated by the wizard, for example year-to-day or year-over-year growth). Also, this computation is focusing on the data dimension, not the specific measure, so it can be used for any measure in the cube.

In my last post I used a DAX calculation that computed the moving average based on the last date in the current interval. We can do pretty much the same in MDX by “translating” the DAX formula to MDX. Here is the calculation for a cube calculated member:

CREATE MEMBER CURRENTCUBE.[Measures].SalesAmountAvg30d AS 
Avg(
  LASTPERIODS( 
       30
       , tail(descendants([Date].[Calendar].currentmember,[Date].[Calendar].[Date]),1).item(0)
  )
  , [Measures].[Internet Sales Amount]
);

After defining this measure we can use it in a query or within a pivot table. Here’s the result from a query:

select {[Measures].[Internet Sales Amount], [Measures].[SalesAmountAvg30d]} on 0,
descendants([Date].[Calendar].[Calendar Year].&[2003])  on 1
from [Adventure Works]

If you compare these values with the values from my last post you see that the values are absolutely identical (just the order of the values differs because of the way I wrote the query). Here are both definitions side by side:

Again, the idea (approach) is the same in both cases, therefore both definitions are similar. However, in my opinion the DAX syntax is a bit harder to read in this case. Especially the CALCULATE(…, ALLEXCEPT(…)) makes it harder to understand. In MDX, we rely on attribute relationship for this purpose but in DAX we need to “break” the context manually.

Now, let’s do some performance tests. In order to compare performance I used these queries

Both queries return exactly the same results (you may add an “order by ‘Date’[Date]” at the end of the DAX query in order to have the dates returned in the same order as from the MDX query).

For the MDX queries I cleared the cache before running the queries. I changed the number of days (number of days to include in the average, written bold, red in the queries above) and got the following results. For number of days = 0 I took out the calculation and left only the sales amount as aggregate. Time was measured in seconds using SQL Server Management Studio (on a virtual machine, old hardware).

When looking at these results I was somewhat surprised. Not about the situation that the DAX query took longer to execute. Please keep in mind that I’m running the queries on an early preview of the product so I suppose there is still a lot of debugging and internal logging going on here. We will have to wait for the final product to make a comparison. What surprises me was the fact that the DAX query time did not go up significantly with higher values of n. For the MDX engine I was pretty sure that it would perform this way because we have mature and a well built cache behind it. So, although we’re increasing the number of computed cells dramatically (with higher values for n), the MDX query performance should almost be constant as we have a lot of overlapping calculations here. But also the current DAX engine performs in the same way that shows how very well the DAX engine is implemented. This is a pretty good result and we can expect a lot of performance from this new DAX query engine.

SQL Server Denali | PowerPivot

Alberto Ferrari already wrote about calculating moving averages in DAX by using a calculated column. I’d like to present a different approach here by using a calculated measure. For the moving average I’m calculating a daily moving average (over the last 30 days) here.

For my example, I’m using the PowerPivot workbook which can be downloaded as part of the SSAS Tabular Model Projects from the Denali CTP 3 samples.

In this post, I’m developing the formula step by step. However, if you are in a hurry, you might directly want to jump to the final results below.

With calendar year 2003 on the filter, date on columns and sales amount (from table Internet Sales) in the details, the sample data looks like this:

In each row’s context, the expression ‘Date’[Date] gives the current context, i.e. the date for this row. But from a calculated measure we cannot refer to this expression (as there is no current row for the Date table), instead we have to use an expression like LastDate(‘Date’[Date]).

So, in order to get the last thirty days we can use this expression

DatesInPeriod(‘Date’[Date],LastDate(‘Date’[Date]),-30,DAY)

We can now summarize our internet sales for each of those days by using the summarize function:

Summarize(
  DatesInPeriod(‘Date’[Date],LastDate(‘Date’[Date]),-30,DAY)
  ,’Date’[Date]
  , "SalesAmountSum"
  , Sum(‘Internet Sales’[Sales Amount])
)

And finally, we’re using the DAX function AverageX to compute the average of those 30 values:

Sales Amount (30d avg):=AverageX(
  Summarize(
    DatesInPeriod(‘Date’[Date],LastDate(‘Date’[Date]),-30,DAY)
    ,’Date’[Date]
    , "SalesAmountSum"
    , Sum(‘Internet Sales’[Sales Amount])
  )
  ,[SalesAmountSum]
)

This is the calculation that we are using in our Internet Sales table as shown in the screenshot below:

When adding this calculation to the pivot table from above, the result looks like this:

Looking at the result it seems that we don’t have any data prior to January 1, 2003: The first value for the moving average is identical to the day value (there are no rows before that date). The second value for the moving average is actually the average of the first two days and so on. This is not quite correct but I’m getting back to this problem in a second. The screenshot shows the computation for the moving average of at January 31 as the average of the daily values from January 2 to 31.

Our calculated measure also works fine when filters are applied. In the following screenshot I used two product categories for the data series:

How does our calculated measure work on higher aggregation levels? In order to find out, I’m using the Calendar hierarchy on the rows (instead of the date). For simplicity I removed the semester and quarter levels using Excel’s pivot table options (Show/Hide fields option).

As you can see, the calculation still works fine. Here, the monthly aggregate is the moving average for the last day of the specific month. You can see this clearly for January (value of 14,215.01 also appears in the screenshot above as the value for January 31). If this was the business requirement (which sounds reasonable for a daily average), then the aggregation works fine on a monthly level (otherwise we will have to fine tune our calculation and this will be a topic of am upcoming post).

But although the aggregation makes sense on a monthly level, if we expand this view to the day level you’ll see that our calculated measure simply returns the sales amount for that day, not the average of the last 30 days anymore:

How can this be. The problem results from the context in which we calculate our sum, as highlighted in the following code:

Sales Amount (30d avg):=AverageX(
  Summarize(
    datesinperiod(‘Date’[Date],LastDate(‘Date’[Date]),-30,DAY)
    ,’Date’[Date]
    , "SalesAmountSum"
    , Sum(‘Internet Sales’[Sales Amount])
  )
  ,[SalesAmountSum]
)

Since we evaluate this expression over the given dates period, the only context that is overwritten here, is ‘Date’[Date]. In our hierarchy we’re using different attributes from our dimension (Calendar Year, Month and Day Of Month). As this context is still present, the calculation is also filtered by those attributes. And this explains why we the current day’s context is still present for each line. To get things clear, as long as we evaluate this expression outside of a date context, everything is fine as the following DAX query shows when being executed by Management Studio on the Internet Sales perspective of our model (using the tabular database with the same data):

evaluate (
    Summarize(
        datesinperiod(‘Date’[Date],date(2003,1,1),-5,DAY)
        ,’Date’[Date]
        , "SalesAmountSum"
        , Sum(‘Internet Sales’[Sales Amount])
    )
)

Here, I reduced the time period to 5 days and also set a fixed date as LastDate(…) would result in the last date of my date dimension table for which no data is present in the sample data. Here is the result from the query:

However, after setting a filter to 2003, no data rows outside of 2003 will be included in the sum. This explains the remark above: It looked like we only have data starting from January 1, 2003. And now, we know why: The year 2003 was on the filter (as you can see in the very first screen shot of this post) and therefore it was present when calculating the sum. Now, all we have to do is to get rid of those additional filters because we’re already filtering our results by Date. The easiest way to do so, is to use the Calculate function and apply ALL(…) for all attributes for which we want to remove the filter. As we have some of those attributes (Year, Month, Day, Weekday, …) and we want to remove the filter from all of them but the date attribute, the shortcut function ALLEXCEPT is very useful here.

If you do have an MDX background you will wonder why we don’t get a similar problem when using SSAS in OLAP mode (BISM Multidimensional). The reason is that our OLAP database has attribute relationships, so after setting the date (key) attribute, the other attributes are automatically changed too and we don’t have to take care about this (see my post here). But in the tabular model we don’t have attribute relationships (not even a true key attribute) and therefore we need to eliminate unwanted filters from our calculations.

So here we are with the …

Final results

Sales Amount (30d avg):=AverageX(
  Summarize(
    datesinperiod(‘Date’[Date],LastDate(‘Date’[Date]),-30,DAY)
    ,’Date’[Date]
    , "SalesAmountSum"
    , calculate(Sum(‘Internet Sales’[Sales Amount]), ALLEXCEPT(‘Date’,'Date’[Date]))
  )
,[SalesAmountSum]
)

And this is our final pivot table in Excel:

To illustrate the moving average, here is the same extract of data in a chart view (Excel):

Although we filtered our data on 2003 the moving average for the first 29 days of 2003 correctly takes the corresponding days of 2002 into account. You will recognize the values for January 30 and 31 from our first approach as these were the first days for which our first calculation had a sufficient amount of data (full 30 days).

SQL Server Denali | PowerPivot

Semi additive measures, i.e. measures that have to be aggregated differently over different dimensions, are commonly used in BI solutions. One example could be stock levels. Of course we don’t want to sum them up over time, but only over product, location etc. For the time, a different aggregation is used, for example average or last value.

The following example shows how to implement some of the most commonly used semi additive measures in DAX.

In my example I’m using PowerPivot (Denali edition), but the same calculations can be used in a BISM Tabular model in Visual Studio.

In order to keep things simple, I’m using just a short table of test data:

As you see, we only have two products with monthly stock levels in 2010 and 2011.

Although not needed for my semi additive measures, I created additional columns in my PowerPivot sheet for convenient reasons: Year, Month, Day (using the corresponding DAX-function with the same name). I also set the newly created columns, as well as the Stocklevel column to hidden (it makes no sense to sum up the stock level). Although the date information is kept in the same table as the data to keep things simple for this example, I encourage to build a separate date dimension table here (similar idea as with a date dimension in a multidimensional model).

Finally, I created a hierarchy named ‘Calendar’ on my newly created date columns:

Now we’re ready for the semi additive measures.

Average (over time)

Let’s start with an easy one, the average over time. Since we can easily compute the distinct count of our date values, we can simply add up the stock level and divide it by the distinct count. In my example the formula looks like this:

Avg Stock:=Sum([Stocklevel])/DISTINCTCOUNT([Date])

Last value (over time)

In order to compute the last value, the DAX function LASTDATE comes in handy. Here is the formula:

Last Stock:=CALCULATE(SUM([Stocklevel]),LASTDATE(‘Stock’[Date]))

Min/Max value (over time)

For min/max we have to be a little bit more tricky. In the approach I’m showing here, I’m grouping the table by date by using the SUMMARIZE function and the SUM aggregation. Then I’m using the function MINX or MAXX to find the minimal or maximal value.

Here are the two formulas:

Max Stock:=MINX(SUMMARIZE(‘Stock’,'Stock’[Date],"SumByDate",SUM(‘Stock’[Stocklevel])),[SumByDate])

Max Stock:=MAXX(SUMMARIZE(‘Stock’,'Stock’[Date],"SumByDate",SUM(‘Stock’[Stocklevel])),[SumByDate])

To understand these formulas you can see the effect pretty well after restoring the PowerPivot workbook to a SSAS server in tabular mode. After doing so, we can create a query to show the result of the inner SUMMARIZE function using this DAX query:

evaluate(
SUMMARIZE(‘Stock’,'Stock’[Date],"SumByDate",SUM(‘Stock’[Stocklevel]))
)

Here’s the result:

The MinX or MaxX function simply takes the lowest/highest value from this table.

Now let’s see, how this looks like in Excel. The following screenshot shows the calculations in my PowerPivot sheet:

Here’s the result in Excel

And of course, the aggregations also work correctly when filtering the data as shown below (single select on product and multi select on months):

Another cool feature is that besides DAX we can still use standard MDX to query our SSAS tabular model, for example:

select
{[Measures].[Avg Stock],[Measures].[Last Stock],
[Measures].[Min Stock],[Measures].[Max Stock]} on 0,
[Stock].[Calendar].[Year] on 1
from [Model]

A final word about empty (missing) rows: The above calculations need a value of zero as the information that there is no stock at that month. If the value is left blank (no source data row at all), the month itself is treated as missing (interpretation more like we didn’t have this product in our portfolio at all).