Microsoft OLAP Blog by Hilmar Buchta

Sparse Snapshots in DAX / BISM Tabular

2012-01-08T13:30:00.001+01:00

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):

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.

Stock levels as delta rows in DAX / BISM Tabular

2011-11-19T12:19:00.001+01:00

SQL Server Denali | PowerPivot

A common way to compress large amounts of snap shot data is to store delta values instead of each snapshot value. This makes sense, if the data does not change every day. But for a good query performance you might not want to aggregate data over long periods.Therefore it makes sense to have a regular absolute snapshot value in the data and to use deltas between those snapshots. The work needed to create the periodic snapshots is usually done in the ETL process.

For my example, I’m using the following data table with absolute snapshot values as the first row of the month and deltas afterwards:

For example, for our product Quickerstill we start with a stock level of 20 boxes at the beginning of January 2011. Then, at January 3, 2011 we sold two of them (-2) and again on January 5 another 3 boxes (-3).

The goal is to create a measure that gives us the current stock level at each date (also on the dates between the delta rows).

In order to do so, we need a ‘real’ date dimension, so we have a separate date table that is linked to our facts. This is how this simple model looks like:

The measure DeltaStock is the original stock value from our table above (mixture of snapshots and deltas).

In order to perform the desired computation we can simply use the month-to-date formula as each day’s value computes as the sum from the first day of the month to the current day (including all deltas).

This is the simple formula we’re using to compute the stock at each day:

Stock:=TOTALMTD(SUM([DeltaStock]),'Date'[Date])

It’s amazing how simple this calculation is. Let’s take a look at the result. In order to see the effect, I added the original stock column together with the new computed column.

As you can see, we now have a stock for each day that computes correctly from the mixture of snapshot and deltas values.

Of course we could also do the calculation without the absolute snapshot values in between. In this case we have to aggregate the values from the very beginning up to the current date. First let’s take a look at the source data without the absolute snapshots:

The data is pretty much the same as in the first approach. Only the rows for the absolute snapshots are missing (apart from the first initial values).

In this case the calculation would look like this:

Stock:=
SumX(
     DATESBETWEEN(
        'Date'[Date]
        , date(2000,1,1)
        , lastdate('Date'[Date])
     )
     ,calculate(
         Sum('Stock'[DeltaStock])
         ,ALLEXCEPT('Date','Date'[Date])
      )
)

The calculation is still pretty simple. However I would prefer the option with the snapshot values in between for performance reasons and because these snapshots can be easily created in ETL (if they are not delivered from the source system).

Just two more remarks before I finish this post. The first one is about the DatesBetween range in the formula above. I’m using 2000/1/1 as the start date. However if you take a look at the returned dates only the existing rows from our date table are returned if they are matching this date range. So we could also write 1900/1/1 without risking to end up with a lot of rows.

The other remark is about the future time. Since our calculation takes the last value as the value for all the future, you will find values for all entries of the date dimension. This might not be wanted. In this case you can wrap the calculation from above inside an if statement to check the date:

StockClipped:=
if(
    firstdate('Date'[Date])>Now()
    , Blank()
    ,SumX(
       DATESBETWEEN(
          'Date'[Date]
            , date(2000,1,1)
            , lastdate('Date'[Date])
         )
         ,calculate(
           Sum('Stock'[DeltaStock])
           ,ALLEXCEPT('Date','Date'[Date])
       )
     )
)

With this modification (which also works with the formula for the absolute intermediate snapshots from above) values are only shown for periods that are over or have at least started. So the formula would return a value for the full year 2011 once the year has started. If you only want to see values for periods that have ended, you can replace the function ‘firstdate’ with a ‘lastdate’.

Excel Services Scorecard as Windows Desktop Gadget

2011-10-23T15:44:00.001+02:00

SharePoint 2010 | SQL Server 2008 | SQL Server 2008R2 | SQL Server 2012 (Denali)

Early this year I stumbled across a very interesting Windows desktop gadget, that is capable of showing an Excel Services element on the Windows desktop. Here, element can be a certain named region, a pivot table or a chart in an Excel Services document. The technology for showing this element is the Excel Services REST API (REST stands for Representational State Transfer).

You can find the article about the desktop gadget as well as the download link for the gadget itself here. Installation and configuration of the gadget is pretty well explained on the linked site, so I can keep this short here.

After adding the gadget to your desktop, the gadget still needs to be configured.

By clicking on the gadget configuration icon, the configuration dialog is displayed:

Workbook	The URL to your Excel Services workbook, for example http://srv1/PowerPivot/Gadget.xlsx
Show in gadget	Here you can pick from any named region, pivot table or chart that should be displayed in the “expanded” state of the gadget (flyout)
Thumbnail	Here you can pick from any named region, pivot table or chart that should be displayed in the normal state of the gadget. This is what you see on your desktop first
Refresh	refresh interval of the gadget

So the simple idea is to create a nice pivot table and to use this as the basis for the gadget. For my example I created a simple pivot table based on the Finance perspective of the Adventure Works OLAP cube. For the pivot table I have the year on the filter, the sales amount and the operating profit KPI as the data and the departments on the rows.

If we use this large table in our gadget, the thumbnail view gets pretty much “microscopic”:

So, for the thumbnail (default) view you should choose a much smaller area. The flyout (detail view) is much better, but the KPI indicators and the filters are not shown:

So, here are a few tips and tricks you can use to make the gadget look nicer.

1. Use time filters for current year, current month etc.

Of course we want our gadget to always show the latest values. Because of the auto refresh interval, we don’t have to care about this. But usually we will also use a time filter to restrict the data to a specific week, month etc. Since we don’t see the filter, the idea is to have this set automatically to the current time.

This can be easily done in the Excel Pivot Table by using date filters. Unfortunately, these filters don’t work in the filter area, so we have to place the time hierarchy on the rows or columns. Then we can apply a filter (for example current year) as shown here:

For Adventure Works this would not result in any data since the sample date is only available for the years shown in the screenshot. However, in real life scenarios, this would be a good choice for the filter.

2. For thumbnail view, convert the pivot table to formulas

Formulas are much easier to handle and to format, compared to a pivot table. Remembering the very narrow available space for the thumbnail view, having full control of the layout is important. You may even want to hard-code the thumbnail view. Let me explain what I mean. In order to have the connection name at a single place, we start in a blank sheet by putting the connection name in a named cell called “OLAPConnection” (feel free to choose a different name):

As the next step, let’s construct the current date member. From Management Studio, we can see that the MDX name of the time members looks like this (may be different in your cube, this example is taken from the Adventure Works sample database):

[Date].[Calendar].[Calendar Year].&[2001]
[Date].[Calendar].[Month].&[2001]&[1]
[Date].[Calendar].[Date].&[20010101]

Here we have January 1, 2001. This is easily constructed using Excel’s time functions. So we simply add these fields to our Excel sheet

Label/Name	Cell	Formula
Today	B3	=Now()
Year	B4	="[Date].[Calendar].[Calendar Year].&[" & Year(B3) & "]"
Month	B5	="[Date].[Calendar].[Month].&[" & Year(B3) & "]&[" & Month(B3) & "]"
Day	B6	="[Date].[Calendar].[Date].&[" & 10000Year(B3)+100Month(B3)+Day(B3) &"]"

Of course you could also add fields for the previous month, the week etc., just depending on the needs of your scorecard. I named the cells B4 as MDXYear, B5 as MDXMonth, B6 as MDXDay. This is how the result looks like:

I had to fake the current date in order to see some values. Therefore I replaced the formula for today with this one:

=Date(2004, Month(Now()), Day(Now()))

Of course, you won’t want to do this in a real life scenario but since the sample dataset contains no data for 2011 I had to use this “time machine formula”.

Before you start wondering what all this is good for, let’s query some data. For example, let’s assume that we want to see the operating profit (which is on the account ‘Operating Profit’) for the current year. So this would by our Excel formula:

=CUBEVALUE(OLAPConnection,"[Measures].[Amount]","[Account].[Accounts].[Operating Profit]",MDXYear)

In order to show this value in our gadget, I placed it on a new sheet and adjusted the column width and height a little bit.

In order to get a nice flyout, I also created a simple pivot chart in the Excel sheet, showing the operational profit during the year. After saving the Excel file, our gadget now looks like this:

And here is the flyout:

3. (Conditional) background colors and fonts are preserved in the REST API

In order to include a kind of traffic light approach, we already found out that Excel indicators are not yet supported in the REST API. However, conditional formatting is supported, so you can easily create a scorecard like the follow:

Also KPI indicators can be created using special characters, for example from the WingDings font as shown in the following example:

For this example, I used a separate table of banding ranges and an Excel VLookup to find the appropriate color for the indictor. Here, 1=green, 2=yellow, 3=red. In the Excel cell I used conditional formatting to choose the text color appropriately. But as we always want to display a certain element (here, the diamond from the WingDings font), I used a custom format for each of the cells, so the number (1, 2 or 3) is not shown but only one character. The corresponding character for the diamond is “u”, so the custom format looks like this:

And of course, there is a lot more you can do with all these formatting, cube functions etc.

3. Make changes to the source code of the gadget

You can extract the gadget or modify the source code of the gadget yourself. After installing the gadget, the extracted sources can be found here:

%LOCALAPPDATA%\Microsoft\Windows Sidebar\Gadgets

After modifying the sources, the gadgets need to be switched off and on in order for the changes to apply. The main file is the gadget.html here. For example you could change the link “By Excel Services” to point on your SharePoint server. To do so, a simple change in the source is needed:

Before:
<tr><td id="dockedTitle" width="100%">
<a id="leftDockedTitleLink" href="http://blogs.msdn.com/cumgranosalis/pages/excel-services-windows-7-gadget.aspx">By Excel Services</a>
</td><td>

After:
<tr><td id="dockedTitle" width="100%">
<a id="leftDockedTitleLink" href="http://srv1/PowerPivot/Forms/AllItems.aspx">All reports</a>
</td><td>

I also changed the background color here, so this is how the result looks like:

As you can see, you can do a lot of interesting things with this simple but very powerful desktop gadget.

4. Use parameters

If you want to use the same Excel Services file for more than one user you may want to pass parameters from each individual instance of the desktop gadget to the Excel Services file. This is also possible, however there are some things to take care of.

The syntax for passing a parameter is

Ranges('cellname')=value

Here, cellname is a named cell in Excel that we want to pass the value to. This is how it is entered in the desktop gadget, if our named cell is named ‘value1’ and we want to pass ‘xyz’:

Multiple parameters may be passed by separating them with an ampersand, for example

Ranges('value1')=xyz&Ranges('value2')=42&Ranges('value3')=01

This could be our corresponding view in the desktop gadget:

I just added a field that concatenates all three parameters to show that the Excel Services sheet is recomputed based on the passed values.

As you can see, all parameters are passed to the gadget. However, numeric parameters are considered as numbers in Excel, so 01 was changed to 1. If you want to prevent this, add a ' in front of each text.

This is how the parameters should look like when you want to make sure, text is passed as text (and not converted to a number or date) in Excel:

Ranges('value1')='xyz&Ranges('value2')=42&Ranges('value3')='01

You should also be aware, that this string is passed to the URL “as it is”. So you have to encode all characters that are not allowed in a URL.

For example, if you want to pass the year 2006 from AdventureWorks, the unique name of the date member would be:

[Date].[Calendar].[Calendar Year].&[2006]

In order to pass this to the gadget, you have to encode blanks, the square brackets and the ampersand. So the result would look like this:

Ranges('parDate')=%5BDate%5D.%5BCalendar%5D.%5BCalendar%20Year%5D.%26%5B2006%5D

This is very difficult to read. Therefore I recommend just to pass the key (2006 in this case) as the parameter and to construct the unique name in Excel using a formula like

="[Date].[Calendar].[Calendar Year].&[" & parDate & "]"

Of course, there are a lot more things you can do with this simple, yet powerful Windows desktop gadget. Like with most other gadget you may also place more than one instance of the gadget on your desktop, so you can have different scorecards. You can also add links to your Excel sheet, so that you can jump directly to a dashboard for a specific key performance indicator. Just start playing with the gadget and see how powerful und useful it is.

Custom Aggregates in DAX / BISM Tabular (part 2)

2011-09-25T15:33:00.001+02:00

SQL Server Danali | PowerPivot

This post combines two ideas

The calculation of a moving average (last 30 days) as shown in a previous post
Custom aggregations

What I want to show here today, is how we can influence the way our calculation works on higher levels. For this purpose I’m showing three different approaches:

The monthly (quarterly, yearly) total is defined as the last day’s moving average of the given date range.
This corresponds to the original approach from my post about moving averages
The monthly (quarterly, yearly) total is defined as the average of all the moving averages on day level.
The monthly (quarterly, yearly) total is defined as the average of the sales amount of all days of that period (the moving average is only to be calculated on date level)

Of course, there are many more possibilities but I think the methods shown here, can be used for many other aggregation requirements.

Here are the three aggregations side by side.

Although the values on higher levels of aggregation differ a lot, the daily values are identical (we just wanted to change the rollup method, not the calculation on a detail level).

Let’s start with the first one:

1. The monthly (quarterly, yearly) total is defined as the last day’s moving average of the given date range

This is shown here for our measure “Sales Amount(30 avg)”:

As shown in my previous post, the formula may look like this:

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]
)

Since we use the expression LastDate(‘Date’[Date]) the last date of each period is used for the moving average – exactly what we wanted.

Average at month level as the average of the moving averages (day level)

In this approach, the monthly aggregate has to be calculated as the average of all the daily moving averages of that month. The picture shows what this means:

This might look pretty difficult. However, for our calculation we simply have to wrap the existing calculation in another Summarize – function.

This is the formula I used:

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

The blue part of the formula is exactly the same is in the first calculation. We’re just wrapping this in an additional Average function. Why does this still work on a day-level? Simply because the outer average computes the average of a single value in this case.

So, with just a minor change to the formula, we changed the method of aggregation quite a lot.

3. The monthly (quarterly, yearly) total is defined as the average of the sales amount of all days of that period

This sounds quite simple but in this case we have to distinguish two calculation paths:

day level
month level and above

The following picture shows the calculation for the monthly aggregate:

In order to split our computation paths we need to find out if we are on day level or not. Usually the IsFiltered(…) DAX function can be used for this purpose. However, since we have some columns with date granularity (date, day of month, day of year) in our date dimension, we would have to write something like IsFiltererd(‘Date’[Date]) || IsFiltered(‘Date’[Day of Month]) || …

To simplify this, I simple used a count of days in the following code. If we count only one day, we’re on day level. Of course, the count is the more expensive operation, but for this example, I leave it that way (the date table is not really big).

Sales Amount (30d avg 3):=
if (
Count('Date'[Date])=1,
AverageX(
    Summarize(
      datesinperiod(
        'Date'[Date]
        ,LastDate('Date'[Date])
        , –30
        , DAY
      )
      , 'Date'[Date]
      , "SalesAmountSum"
      , calculate(Sum('Internet Sales'[Sales Amount]), ALLEXCEPT('Date','Date'[Date]))
    )
    ,[SalesAmountSum]
)
,
AverageX(
    Summarize(
      'Date'
      ,'Date'[Date]
      ,"SalesAmountAvg"
      , calculate(Sum('Internet Sales'[Sales Amount]), ALLEXCEPT('Date','Date'[Date]))
    )
    ,[SalesAmountAvg]
)
)

Again the blue part of the formula is exactly the same as in our first approach. This part is taken whenever we’re on a day level. On higher levels, the aggregate is computed as a simple (not moving) average of all daily values.

So, using the concepts of my previous post we were able to change the aggregation method to meet very sophisticated requirements.

Custom Aggregates in DAX / BISM Tabular (part 1)

2011-09-11T15:30:00.001+02:00

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.

Moving Averages in DAX vs. MDX

2011-09-04T15:54:00.001+02:00

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:

MDX	DAX
Avg( LASTPERIODS( 30 , tail( descendants( [Date].[Calendar].currentmember ,[Date].[Calendar].[Date] ) ,1 ).item(0) ) , [Measures].[Internet Sales Amount] );	AverageX( Summarize( datesinperiod('Date'[Date] , LastDate('Date'[Date]),-30,DAY) ,'Date'[Date] , "SalesAmountSum" , calculate( Sum('Internet Sales'[Sales Amount]), ALLEXCEPT('Date','Date'[Date]) ) ) ,[SalesAmountSum] )

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

MDX

DAX

with
member SalesAmountAvg AS
Avg(
    LASTPERIODS(
      30
      , tail(
        descendants(
          [Date].[Calendar].currentmember
          ,[Date].[Calendar].[Date]),1
        ).item(0)
    )
    , [Measures].[Internet Sales Amount]
)

select
{
[Measures].[Internet Sales Amount]
, SalesAmountAvg
} on 0,
descendants([Date].[Calendar].[All Periods],,LEAVES) on 1

from [Adventure Works]

define
measure 'Internet Sales'[SalesAmountAvg] =
AverageX(
    Summarize(
      datesinperiod('Date'[Date]
      , LastDate('Date'[Date]),-30,DAY)
      ,'Date'[Date]
      , "SalesAmountSum"
      , calculate(
          Sum(
          'Internet Sales'[Sales Amount])
          ,ALLEXCEPT(
          'Date','Date'[Date])
        )
    )
   ,[SalesAmountSum]
)

evaluate (
addcolumns(
    values('Date'[Date])
    ,"Internet Sales Amount"
    , SumX(relatedtable
       ('Internet Sales'),[Sales Amount])
    ,"SalesAmountAvg",
    'Internet Sales'[SalesAmountAvg]
)
)

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).

	n=0	n=10	n=30	n=50	n=100	n=1000
MDX	1	3	3	3	3	4
DAX	0	9	9	9	9	12

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.

Moving Average in DAX / BISM Tabular

2011-08-21T15:26:00.001+02:00

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).

Semi additive measures in DAX / BISM Tabular

2011-08-13T17:17:00.001+02:00

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).

Parallel hierarchies in a parent-child structure

2011-07-30T13:30:00.001+02:00

SQL Server 2005 | SQL Server 2008 | SQL Server 2008R2

This post is about a problem I faced some years ago. The source system was SAP with user defined hierarchies, in this case within the cost center and cost type tables. Parallel hierarchies are well supported in SQL Server BI but in this case, users in SAP could define multiple hierarchies on their own and they wanted these hierarchies to be also available in the OLAP cube. For example, costs associated with the cost center 1000 should be analyzed as shown below:

All costs that are booked on cost center 1000 have to appear in the hierarchy as shown in the sketch. And end-users may also be able to create new hierarchies (for example to analyze a certain project). Of course there may be better ways to model this but in this case we had basically two tables for the cost centers:

table CC (Cost Center)
table CCG (Cost Center Group)

Table CC contains all cost centers (for example the above cost center 1000) together with additional information (like name, responsible person etc.) while table CCG contains the hierarchy. in CCG we basically find two columns:

Node name
Parent node name

Node	Parent Node
1000	Internal_HR_DE
Internal_HR_DE	HR_DE
HR_DE	Germany
1000	Marketing_DE
Marketing_DE	Germany
Germany	Corporate
Marketing_DE	Marketing
Marketing	Corporate

Facts (in this case actual or planned costs) are associated with the cost center number (for example 1000). Usually, parent-child hierarchies may be used in this case where we have a very dynamic structure and we do not know the number of levels. However, parent-child may only be used if each node has at most one parent. But here we find the cost center 1000 having two parents (Internal HR Costs DE and Marketing_DE). The same situation exists with the Marketing_DE node (having parents Marketing and Germany).

The solution I’m presenting here is to create additional nodes until each node only has one parent. This is possible as each node of a parent-child hierarchy in SSAS has a name and a key property. So, the name will be identical, while the key will be different. In order to show the process, let’s add internal keys to each of the hierarchy elements.

What we have to do now is to create additional nodes for every node that has more than one parent. Let’s start with the ‘Marketing_DE’ node:

The additional node gets a new (internal) key, in this example the number 8. But there is still a node with multiple parents: the cost center 1000. Let’s also transform this into separate nodes:

After this step, each node has at most one parent and therefore the structure can be modeled as an SSAS parent-child hierarchy.

But now, we have to think about the fact rows. Without the hierarchy, facts would have been associated to the cost center by using the internal key (surrogate key), so for example 1000 € that are booked on cost center 1000 would appear in the fact table like

DateKey	CostCenterKey	…	Amount
…	…	…	…
20110630	1	…	1000
…	…	…	…

But now, we have to associate this single fact row to three rows in the dimension table (as the cost center 1000 appears three times now). Therefore we have to use a many-to-many approach, so we add another table, a so called bridge table with the following rows:

CostCenterKey	CostCenterDimKey
…	…
1	1
1	9
1	10
…	…

For technical reasons, our fact table has to be linked to a dimension (of flat cost centers), which is also used by the bridge table. This is shown in the following image:

The most difficult part here is to “normalize” the parent-child structure. One way to do this is to use a stored procedure. Here is the code I used. Within this procedure, the following tables are used:

masterdata.Costcenter

the flat table of cost centers (only leaf-level). The key field is the cost center number (for example 1000 for our cost center from above)

masterdata.CostcenterGroup

the hierarchy structure as shown above

ods.CostcenterGroupExpanded

Output table: the expanded tree containing the fields of the table masterdata.Costcenter plus the following additional fields:

CostcenterKey	the new generated surrogate key
ParentKey	the key of the parent node
Level	a technical field used during iteration

Here is the code:

CREATE PROCEDURE [dbo].[ExpandCostcenterGroup]
AS
    SET NOCOUNT ON
    truncate table ods.costcenterGroupExpanded

    declare @level int
    declare @affectedrows int
    declare @totalrowcount int

    set @level=0

    insert into ods.costcenterGroupExpanded(costcentergroup,Parentgroup,Description1,Description2,Responsibility,AccountingArea)
     Select distinct costcenterGroup, Parent,Description1,Description2,Responsibility,AccountingArea from masterdata.costcentergroup

    -- Initialize all keys
    update ods.costcenterGroupExpanded
    set ParentKey=(select min(costcenterkey) from ods.costcenterGroupExpanded where costcenterGroup=c.Parentgroup)
    from ods.costcenterGroupExpanded as c
    where not c.ParentGroup is null

    set @affectedrows=1

    while @affectedrows>0
    begin

        Set @level=@level+1

        set @totalrowcount=(select Count(*) from ods.costcenterGroupExpanded)

        insert into ods.costcenterGroupExpanded(costcentergroup,Parentgroup,ParentKey,"level",Description1,Description2,Responsibility,AccountingArea)
        select distinct cparent.costcentergroup, cparent.Parentgroup,cparent.costcenterKey,@level,cparent.Description1,cparent.Description2,cparent.Responsibility,cparent.AccountingArea
        from ods.costcenterGroupExpanded as cparent inner join ods.costcenterGroupExpanded as cchild on
        cparent.Parentgroup=cchild.costcenterGroup
        where cparent.ParentKey!=cchild.costcenterKey
        and cchild."Level"=@level-1

        set @affectedrows=@@rowcount

    end

    return

To keep things simple, I truncate the output table CostcenterGroupExpanded here. However, there is a drawback with this approach: The surrogate keys may change after changes of the imported source tables. This will result in a problem for example for the Excel users. If you’re using filters like ‘show this element only’, only the key is stored.

In order to avoid this you will need to store the mapping and the assigned surrogate key separately. Here it is necessary not only to store the combination of cost center/parent cost center/surrogate key but the whole branch up to the root instead. If you look at the example above you will find two entries of ‘Cost Center 1000’ –> ‘Marketing_DE’, so this is not unique. You have to store the full path up to the root for each node (not only for the leaf-nodes) to make it unique:

Path	Given Surrogate Key
1000 –> Internal_HR_DE –> HR_DE –> Germany –> Corporate	1
1000 –> Marketing_DE –> Germany –> Corporate	9
1000 –> Marketing_DE –> Marketing –> Corporate	10
Marketing_DE –> Germany –> Corporate	7
Marketing_DE –> Marketing –> Corporate	8
…

In order to store the full path up to the root level I recommend using a hash code (MD5 for example) as this is easier to handle as a long list of node names. In this case our additional key store table would look like this

PathMD5	Given Surrogate Key
417913d10ef49f5ff90db9db9f3d2569	1
8e27be6b156a52016e01dc049bc39126	9
52b1bcaec016e09d4086f37e63814aa5	10
…

The sample code above does not manage this key store table so the keys may change a lot on each load. But for practical purposes you will have to add this key management to make sure the same node always gets the same key.

Same measure in different granularity

2011-07-03T17:43:00.000+02:00

SQL Server 2005 | SQL Server 2008 | SQL Server 2008R2

In an earlier post I wrote about handling different granularity in a dimension-fact relationship. This time I want to get back to this topic from the end-user perspective. To illustrate this, I use a very simple data model here:

For this model, we have daily revenue data and monthly revenue plan data. Here is the link from the measure groups to the dimensions:

In this example, both measure groups only have one measure: revenue. Since measures are a dimension of its own in the cube, measure names have to be distinct, so I named the measures “Revenue” and “Plan Revenue”.

If your cube contains more data from other measure groups, things can soon be getting confusion with many measure groups. This post is about two common solutions in order to reduce the number of measure groups here.

Two approaches are shown here:

Combining both measures in one measure group by using a calculated measure
Introducing a scenario dimension

Before we start, one remark. The last post was about using translations in the cube the add more flexibility regarding the naming of the measures. Since the measures are clearly associated with a measure group you could be tempted to use the same name for both measures (which is possible for translations). This is how the result would look like in the cube browser:

However, when using a Pivot client like Excel you usually don’t see the measure group anymore. A simple analysis could look like this:

So from your Pivot table you cannot tell which is the actual and which is the plan revenue. Therefore I recommend not to use the same name for different measures in the different measure groups.

Combining both measures in one measure group by using a calculated measure

One possible option is to add a calculated measure the the Revenue measure group. In order to do so, I renamed the plan revenue to “Plan Revenue Internal” and set its visibility to hidden. Then the calculation could be as shown below:

This is how the cube looks like in the cube browser:

By “copying” the plan measure into the revenue measure group you only have one visible measure group left, while both values are still separated by the name.

As I started the post about the different granularity, how does this look like at the detail level? The following screen shot shows the date dimension at the day level:

Assuming you have set the “IgnoreUnrelatedDimensions” property of the plan revenue measure group to false, only actual values are displayed here, no plan values. So this approach works really well.

Introducing a scenario dimension

Another common way for implementing this situation is to use a scenario dimension. The scenario dimension contains two entries: Actual and Plan. The data from the fact table FactOrder is linked to the scenario member ‘Actual’ while the data from the FactPlan table is linked to the scenario member ‘Plan’. This can be easily done in the data source view (DSV) as shown below for the plan table:

The fact tables are linked to the scenario dimension in the data source view:

The scenario dimension should be set IsAggregatable=False (as it makes no sense to aggregate actual and plan data). Also we should provide a default element. This is shown in the screenshot below:

For the cube, we only want to have one single measure revenue which is neither of the two existing measures. Therefore we make both existing measures invisible. In order to distinguish them from visible measures, I prefixed them with an underscore:

Both measures have their Visible-property set to false. The only visible measure in this case has to be a calculated measure that takes the data from one of the two invisible measures depending on the chosen scenario. Since our scenarios are distinct and not aggregatable, we can simply add both measures (as one of the two is always zero):

Choosing this model for implementing the different granularity, the cube shows only one measure:

If the scenario dimension is not used, actual values are shown. You can still easily display both values by putting the scenario dimension on one of the axis:

If you compare this screenshot with the one from above you will see the same values, only the presentation is a little bit different (first option had two measures, last option one measure and a scenario dimension).

Summary

Both options shown here enable us to have the same measure in two or more measure groups of different granularity. The second option with the scenario dimension looks a little bit more tidy from a technical perspective. However, sometimes the first option is easier to use, especially when you add other calculated measures with formulas that are combining two or more scenarios (for example a measure like ‘Actual to plan ratio’). For those measures it’s not possible to assign them to one scenario. On the other hand, choosing the scenario dimension can dramatically reduce the number of visible measures in your cube, which makes the cube easier to understand for end-users. So depending on the requirements and the structure of the data, one of the two options will be the best to choose.

Valid characters for measure names

2011-06-05T17:41:00.000+02:00

SQL Server 2005 | SQL Server 2008 | SQL Server 2008R2

The name for measures in a cube should be clear and easy to understand to avoid confusion for the end-users. However, not all characters are allowed for cube measures. Here is a list of characters, that is not allowed:

. , ; ' ` : / \ * | ? " & % $ ! + = ( ) [ ] { } < >

Some of these characters are allowed for calculated measures although I wouldn’t recommend this as it reduces the readability of the your MDX queries (see below).

For example, you cannot give the name “Avg. Sales Amount” to a built-in cube measure (because of the dot). Also sometimes the end-users want to see the unit with the measure name and one frequently used notation for this is to use square brackets like “Utilization [%]” (here the square brackets and the percent-sign violate the list of allowed characters).

On the other hand, many special characters are allowed. The following characters are just an example of the long list of symbols within the normal text:

Ø © α € £ ¥ ® ™ ± ≠ ≤ ≥ ÷ × ∞ µ

For example, it’s quite common to use the Ø-sign as a symbol for the average and you can name your measure “Revenue Ø”. In order to do so, one simple way is to open Microsoft Word and use the Insert Symbol dialog as shown below:

After inserting the symbol into a blank Word document you can simply copy it to the clip board and then paste the symbol to the measure name:

The new measure name is also displayed correctly in OLAP clients like Microsoft Excel:

However, the special symbols might be more complicated to enter in certain situations where you cannot easily pick the measure from a list. For example, in MDX you also have to enter these character:

select {[Measures].[Revenue],[Measures].[Revenue Ø]} on 0,
[Dim Date].[Calendar Week].[Year] on 1
from [OLAPSample1]

For this reason I recommend measure names that only consist of simple characters and numbers, for example [AvgRevenue] instead of [Revenue Ø].

But how can we help our end-users to see the measures in the way they expect them to see? One simple solution is to use the cube translation for this purpose (Enterprise feature, you cannot do this on a Standard Edition). Within the cube translation you are not restricted to specific characters, so you can also use all of the above characters, while still keeping a simple technical name for the measure.

To show this, I added some more measures to the cube:

On the translation tab of the cube we can now add the displayed names for those measures:

And as expected, Excel correctly displays the “translated” measure names:

Using translations for the measure names also gives the flexibility to change the name of measures if required without also changing all the queries and calculations that are based on this cube (all queries use the built-in names that are now hidden from the end-user). So, translations are a good method to add a façade to your cube’s measures while also adding some more flexibility in the naming of the measures.

Although we can now be very flexible with the naming of the measures, I still recommend to keep things simple. In general, the unit of the measure is usually clear from the name and the format of the values (for example a percentage value). In this case you should avoid showing the unit with the measure name as it is only redundant in this case.

The next post will be about handling similar measures in different measure groups of different granularity (for example revenue as actual and as planned value). In this case you might question yourself how to name these measures properly. With translations you might be tempted to give two measures the same display name (which is possible!). Without anticipating too much of the next post, I don’t recommend to do this as can be very confusing to work with such a cube.

Profit calculation for churn prevention data mining models (part 3 of 3)

2011-05-01T11:50:00.001+02:00

SQL Server 2005 | SQL Server 2008 | SQL Server 2008R2

The last two posts were about cost optimization for a churn prevention campaign. We analyzed the following four options:

Trivial option (only using average return rate and average customer value)
Data Mining (using average customer value but individual return rate based on a mining model)
Value driven approach (using average return rate but individual customer value)
Combination of method 2 and 3 (using individual return rate and value)

As been said in the last post, the results may differ a lot from case to case. Especially the effectiveness of option 2 and 3 depend a lot on the available information for data mining (for option 2) and the variance of the customer value (for option 3). If both methods give some improvement, then the combination can be expected to be the best choice.

However, we always looked at a scenario where the trivial approach gave almost no insight. What I mean is, that for the trivial approach there is not much difference between giving every customer a voucher compared to giving no customer a voucher (costs between $12,500 and $13,750 in this example). In other words the line chart showing the costs for the trivial example was almost a horizontal line. Here is the corresponding chart from the first post (click on the image to see a larger version):

Now, let’s change our basic parameter a little bit. The following table shows the old and the new parameters:

	Old scenario	New scenario
Avg. Customer Profit	$25	$50
Avg. Churn Rate	50%	80%
Voucher costs (prevention costs)	$10	$5
Over all Churn Rate with prevention	15%	5%

Here are the resulting chart (I’ve copied the chart for the old scenario from my last post):

Old scenario	New scenario

While we clearly see the effect of the optimization in the old scenario (left chart), as the purple line (combined method) has a significant minimum compared to the blue line (trivial approach), in the new scenario (right chart) the minimum of all three methods is almost identical. The trivial method (blue line) has it’s minimal costs at the right end point of the line but although the other methods perform better in the middle range of the chart, they cannot deliver values that are much lower than the right end point of the blue line (for my sample data, even the combined model performs only about 1.2% better than the trivial model from our first approach in this case).

In general, a good optimization is more easy to achieve, if the trivial method has no clear decision (line is almost horizontally oriented). In the new scenario, the profit for each customer is high compared to the prevention costs and the prevention campaign is extremely efficient. In such a case you cannot expect much optimization from your value- or data mining driven approach.

So the first thing to remember from this post is to first check the trivial approach. This is the approach that is almost instantly available for most situations, even if the values are just estimations. If the result is more like the blue line in the left chart (horizontally oriented) it is very likely that you can achieve a significant optimization. If it is more like the blue line in the right chart (either falling or rising) you might only want to check further improvements if the costs for getting the churn score or the individual value are not too high. Otherwise you would risk doing an expensive optimization project with the result, that no optimization is possible.

There is still one open question from the last post and that is about the value for the voucher. Up to this point our success rate for making customers return by using a voucher was a fixed average value based on a test sample. Of course, this does not appear to be sensible: Customer with a high value might even be annoyed by a cheap voucher. Also, customers with a high churn value (likely to go away) might not respond to our voucher campaign in the same way as customers with a low churn rate (customers that are likely to return no matter if there is a voucher or not). So it’s time to add more reality to the model. To do so, we’ll have to analyze the data from our customer test samples (first post) in more detail. We only analyzing by customer value (an advanced model could also analyze by churn score, although a bigger test sample is required then). The following table shows the range for the customer profit, the voucher that was associated with that range and the number of customers who did not return although they received the voucher:

Profit Rage	Number of customers in sample	Value of Voucher	Churn rate (not returning)
0-10	78	0	31%
10-15	55	3	21%
15-25	447	5	17%
25-35	275	10	10%
>35	145	15	7%

With this extra information, we can calculate the costs for the voucher and the probability to prevent the customer from going away more precisely. I’m still using the sort order from my combined model. Here is the result in the chart view. In order to make the difference easier to see I changed the minimum for the y-axis (don’t be fooled by the different presentation):

The newly created model is named ‘Variable Model’ in this plot (line color cyan). Because of the different bands the line is not as smooth as for the other models. However it turns out, that this approach is the best one, based on my sample data. Also you’ll notice that the end points of the cyan line differ from the other lines’ endpoints because the costs for the vouchers are no longer a constant. Here are the detailed results from all the approaches:

	Trivial Model	Profit Model	Churn Score Model	Combined Model	Variable Model
Minimal costs	12,500.00	11,761.15	11,817.60	11,228.95	10,999.31
Improvement	0.00	738.85	682.40	1,271.05	1,500.69
Improvement %	0.0%	5.9%	5.5%	10.2%	12.0%

After the three posts now it’s time for a short summary:

Summary

For cost optimization it’s important to first analyze the actual situation. Optimization is not always possible and before starting an expensive project, it’s better to look at the parameters (as shown above, see remarks for the ‘trivial model’). If you decide for optimization, data mining is not the only option. A value driven approach is as important as a mining model in many scenarios, unless all your customers share the same value. And it’s important to know your parameters as good as possible. In my example, the test sample of customers with and without vouchers was very important. Also keep in mind, that the success rate of a voucher (or any other method of prevention) is not a constant, but depends at least on the customer value (usually computed based on the orders of the past) and the likeliness of the customer to turn away (churn score, usually computed by a mining model). The combination of all these parameters is the key to making the optimization methods more efficient.

And I should also add a warning remark that applies to all the approaches we did here: We always try to model the behavior of the customers in the future based on data from the past. The first four approaches are all based on the same modeling idea (only the last model with the variable voucher is based on different pre-conditions). The only difference is the subset of customers that are addressed (different sort order). However, in all these cases the effect of the optimization may be different in reality and with every model it is important to also validate and constantly refine the model.

Profit calculation for churn prevention data mining models (part 2 of 3)

2011-04-09T18:59:00.001+02:00

SQL Server 2005 | SQL Server 2008 | SQL Server 2008R2

In part 1 of this mini series of data mining posts I showed how data mining can be used to optimize a churn prevention campaign by selecting the optimal sub sample of customers to receive a voucher. For this optimization I used a data mining model (without going into detail about the model itself). However, developing such a model is not always an easy task and can result in relevant additional costs. This post first looks at an “alternative” method which you might find easier to implement in the first step. In fact, when facing any kind of optimization or prediction problem, data mining should not be the only option you’re taking into account.

With our data mining approach we were able to transform the average churn rate of the customers (used by the trivial model first) into a customer specific churn rate. By sorting the customers by this individual churn rate we could address those customers first, for which is was most likely that they will not return. This made our investment more efficient.

For the next approach, we will ignore this individual churn score and go back to the average churn score of 50% from the example of my last post. But now we will be looking at a customer specific profit. Depending on your data, profit may be replaced by some other value measure for the customer (revenue, customer life time value etc.).

In the same way as in the previous approach, we can simply sort our customers by their value in descending order and address the customers with the highest value first. Again, the warning from my last post also applies here: The customers with the highest value might not respond to the $10 voucher in the same way as customers with a lower value. I will get back to this point later.

In the same way as before we can calculate the costs per customer:

Option	Costs
Option 1: Customers gets no voucher	[average churn rate] x [customer profit]
Option 2: Customer gets voucher	[average churn rate] x (1- [prevention success rate]) x [customer profit] + [cost of a single voucher] = [average churn rate] x [over all prevention success rate] x [customer profit] + [cost of a single voucher]

For my sample data, the chart (now including the trivial approach, the churn score approach and the new profit based approach) looks like this:

As you can see, for my test data both approaches (green and red line) perform almost in the same way. However, this highly depends on your customer base and I adjusted my data in a way not to prefer one of the two approaches. If there is a low variance in your customer values (almost the same value for every customer) the method based on the value will perform almost as bad as the trivial approach (as it gives no benefit then). If the variance is high, the value based method might easily outperform a purely data mining driven score calculation.

Another good idea would be to combine both methods. Instead of ordering the customers by value or churn score we could sort them by the product [customer values] x [churn score]. In this case both parameters are variables and the calculation looks somewhat like this:

Option	Costs
Option 1: Customers gets no voucher	[churn score] x [customer profit]
Option 2: Customer gets voucher	[churn score] x (1- [prevention success rate]) x [customer profit] + [cost of a single voucher]

For my sample data the chart output of all four methods shows that the combination is superior (which should be expected in most real world scenarios):

In my example the optimizations using only profit (value) or only churn score give an improvement by about 5.5% to 6% while the combination (purple line in the chart) gives about 10.2%.

For all of these samples we used the same parameters. How does the whole picture changes, if the parameters are changed (for example the average return rate)? And what may happen if we also adjust the last fixed parameter in this model, the value for the voucher itself? This will be the topic of the third and last part of this series.

CeBIT 2011 in Hannover from March 1-5, 2011

2011-02-27T16:30:00.001+01:00

The CeBIT in Hannover, Germany is a great time to learn about new technology, share information and discuss about new topics. As for the last years we (ORAYLIS GmbH) are also present and I would be glad to welcome you at our booth that is located within the Microsoft area: Fair Hall 4, Booth 26 / P30. I will be at the booth from March 2 (Wednesday) to March 5 (Saturday).

Hope to see you at the CeBIT!

Profit calculation for churn prevention data mining models (part 1 of 3)

2011-02-27T16:18:00.001+01:00

SQL Server 2005 | SQL Server 2008 | SQL Server 2008R2

Data Mining is frequently used to optimize costs in marketing scenarios. Most marketing campaigns result in costs and we want these campaigns to be as efficient as possible.

To illustrate this process let’s assume we’re operating a web shop. Customers need to register before they can order goods and therefore we know how many customers are returning. While we also spend some afford to win new customers, for this example we will focus on keeping the existing ones and making them buy more in our shop.

From our web shop system we know, that 50% of our customers place only one single order and do not return (within a given period of time, for example within a year). Marketing suggests to offer our existing customers a benefit in order to make them return, let’s say a $10 voucher.

The Simple Approach

While we will analyze more sophisticated models later, for now we will take a look at a very simple approach, that does not take differences between individual customers into account. Therefore, we are looking at average values. For example, the average profit for each order is $25.

Having just the information from above, we cannot decide if the idea of the marketing department is a good one. But we could run a test sample. Let’s say we give the voucher to a random sample of 500 customers. Now we can compare customers who got the voucher with customers who didn’t. After this test we find out that

Group 1 (Customers with voucher):	15% do not return
Group 2 (Customers without the voucher):	50% do not return (as we already knew)

So, based on our test sample, the voucher is pretty effective to keep customers returning. We can also calculate the success rate using this formula:

The success rate is impressive, but is it also sensible from a cost perspective? To answer this, let’s look at two options that we have:

We give vouchers to none of our customers
We give vouchers to all of our customers

For both options, we want to calculate the total costs, including costs from loosing existing customers. For all of the following examples we assume that we only have 1,000 customers and that we are looking at a specific time frame (for example a year). Let’s start with the first option. If we don’t give vouchers to any of our customers, the only costs come from loosing customers and therefore loosing profit. Since 50% of our customers are not returning and the average profit from each customer was $25, we loose 1,000 x 50% x $25 = $12,500 in this scenario. Now let’s look at the second option: From our test sample above we know that only 15% of the customers do not return after receiving a voucher. In the same way as for the first option we can calculate the loss value as 1,000 x 15% x $25 = $3,750. But for this option we also need to add the costs for the vouchers, which is 1,000 x $10 = $10,000. So the total costs for option 2 are $3,750 + $10,000 = $13,750. To sum things up:

Option		Costs
1	vouchers to none of our customers	$ 12,500
2	vouchers to all of our customers	$ 13,750

Of course there would also be the option to offer half of our customers a voucher, but this would just mean that we take the average of the two cost values above as shown in the following chart:

In this chart, option 1 corresponds to the left endpoint of the line while option 2 corresponds to the right endpoint of the line. So, although our vouchers are very successful, they don’t make sense here as they result in higher costs. Of course we could think of changing the value for the voucher but we would have to run another test to see how our customer base responds to the new voucher level (I’ll get back to this point in part 3 of this series). In most cases, the random sample method from above would have been done using different groups with different values for the vouchers. The effect on our success rate is usually not linear, so it’s worth finding the best value for a voucher. But we’ll keep things simple here and assume that the $10 voucher is already the best we could do.

Data Mining

Did I mention data mining in the title? So far we didn’t do any data mining with on our data. But maybe data mining gives us an option to optimize the costs in our scenario?

The main difference between our analysis from above and the data mining approach (and some other options that I will come to) is that now we are looking at the individual customer. In the first approach we just had an average customer drop-off rate of 50%. With data mining, we’re trying to predict the individual rate/score per customer. To do so, we have to set up a mining model that is trained with the knowledge we have about our customers (for example from the web registration form) and the individual buying behavior in the past. This model can then be used to predict returning customers. As this post is about cost optimization, we’re not actually building the model here but we just look at the output. So let’s assume that every customer now has a churn score associated with the customer record and that the model is validated and tested and performs well (see my other posts about back testing mining models).

We can now calculate the individual costs per customer for our two options from above:

Option	Costs
Option 1: Customers gets no voucher	churnscore x [average profit]
Option 2: Customer gets voucher	churnscore x (1- [prevention success rate]) x [average profit] + [cost of a single voucher]

For the first option, the calculation is simple. We only take the expectancy value of the profit loss here. If the individual churn score is for example 30% (0.3) then the costs in this calculation are 30% x $25 = $7.5. Please note that although we are looking at a data mining approach here, I’m still using the average profit of the customer. We will change this later to see the additional effect.

For the second option, we’re using our prevention success rate (70% from above). Even with the voucher, some customers will still not be returning. And as in the example from above, we have to add the costs of the voucher itself. If the individual churn score is 30% (0.3 as in the example above), then this case would result in the following costs: 30% x (100%-70%) x $25 + $10 = $12,25.

For my example I’m using randomly generated test data (with a modified random function). As long as our average rates from above match with the score we’re using here, it is obvious that both approaches give the same result for the two decisions from above: no customers gets a voucher and every customer gets a voucher. So how do we use our churn score now? The answer is simple: Instead of addressing for example 50% of the customers we could now address 50% of the customers with the highest churn scores as we assume that it is more likely for them to go away.

The chart from above may now look like this:

You can clearly see the effect of the optimization here (green line). As we’re talking about costs, the lower the better. The green line showing our data mining approach has a minimum at appox. 22%. The value at that point is $11,818. So, using the data mining approach we could save $682 based on our sample set of 1,000 customers, compared to the total costs of $12,500 for the trivial approach. This is an improvement of about 5.45%. Remember though, that we’re only looking at sample data here, so for your scenario the output may be totally different. Also, customers with a high churn rate might not respond to our vouchers in the same way as customers with a lower churn rate.

In the next part of this post we’ll focus more on the profit value of this scenario.

BOL Community Article about Back-Testing Data Mining Results

2011-01-31T10:18:00.001+01:00

SQL Server 2008 | SQL Server 2008 R2

In addition to my blog posts about back-testing Data Mining Results (see part 1, part 2 and part 3) I also wrote a Books Online Community Article that covers the topic at a more detailed level. The article can be downloaded here: http://msdn.microsoft.com/en-us/library/gg557481.aspx. The sample data and solution is available for download. The link is provided in the article.

You will find all the Community Articles for SQL Server 2008 here: http://msdn.microsoft.com/en-us/library/cc872864(SQL.100).aspx

Cumulated Gains Chart and Lift Chart in SSRS

2011-01-16T10:43:00.001+01:00

SQL Server 2008 | SQL Server 2008 R2

This post is about reproducing some of the mining charts that are built into Business Intelligence Development Studio using Reporting Services. A common use case might be that you periodically train your model and the results of the training should be published as html reports.

For my example I use the Adventure Works Targeted Mailing mining structure. We want to plot the results from the “TM Decision Tree Model”. First let’s start with the lift chart. In BIDS, the chart for “Bike Buyer=1” looks like this:

In order to reproduce the chart, we’re going to use the SystemGetLiftTable stored function in SSAS. Since we need to do some computations, I prefer to use SQL, so we have to define a linked server first:

EXEC sp_addlinkedserver
@server='SSAS_AW',
@srvproduct='',
@provider='MSOLAP',
@datasrc='Adventure Works DW 2008R2'

The query for loading the results from the lift chart looks like this:

SELECT percentile,
VALUE,
CASE
WHEN percentile >= 100 * CONVERT(FLOAT, totalattributevaluecases) /
totalcases
THEN 100
ELSE percentile / (( CONVERT(FLOAT, totalattributevaluecases) /
totalcases ))
END idealmodel
FROM Openquery(ssas_aw,
'CALL SystemGetLiftTable([TM Decision Tree], 2, ''Bike Buyer'', 1)') AS
derivedtbl_1

For the chart, we use the percentile of the lift table as category group (x-axis) and choose these entries for the values (y-axis):

Sum(Value)
This is the actual lift curve for our mining model
Sum(Percentile)
As this is the same value as on the x-axis, this gives the random guess model line (linear)
Sum(IdealModel)
The case-when-end statement above reflects the curve for the ideal model (linear function from 0 to 1 between 0 and totalattribute/totalcases)

This is how the chart looks like in the SSRS designer:

There are different names for this kind of a chart. For this post I’m using the naming conventions as shown here: http://www2.cs.uregina.ca/~hamilton/courses/831/notes/lift_chart/lift_chart.html, so the chart is labeled “Cumulated Gains Chart” instead of lift chart (BIDS).

So, here is the resulting Cumulated Gains Chart from my SSRS report:

As you can see, the chart looks pretty much the same as the chart that was displayed in BIDS.

Another useful chart is the actual lift factor of the model, often referred to as the ‘lift chart’. For this chart, we use the same SSAS function but we need to compute the lift in our query:

SELECT percentile,
VALUE / percentile AS lift,
1 AS randomguess
FROM Openquery(ssas_aw,
'CALL SystemGetLiftTable([TM Decision Tree], 2, ''Bike Buyer'', 1)') AS
derivedtbl_1

This is how our chart looks like:

Finally, we also want to reproduce the classification matrix (often referred to as confusion matrix). This is how the matrix looks like in BIDS:

To get these results, we use the function SystemGetClassificationMatrix as shown below:

SELECT predictedvalue,
SUM(CASE actualvalue
WHEN 0 THEN [COUNT]
ELSE 0
END) actual_0,
SUM(CASE actualvalue
WHEN 1 THEN [COUNT]
ELSE 0
END) actual_1
FROM Openquery(ssas_aw,
'CALL SystemGetClassificationMatrix( [TM Decision Tree], 2, ''Bike Buyer'')'
) AS derivedtbl_1
GROUP BY predictedvalue

The second parameter of the function SystemGetClassificationMatrix means
1 – training data
2 – test data
3 – both (training and test data)

I used a simple matrix on my report to display the result from the query above and applied some coloring in order to distinguish error cases from correct cases:

To complete this post, I’d like to point out that you can also query your model cases directly, using a query like

SELECT *, IsTestCase() As TestCase FROM [TM Decision Tree].CASES

This query returns all the cases from the TM Decision Tree model with an additional column “TestCase” that contains true, of the case belongs to the test data set. In order to run this query you need to enable the drill-through option for the mining model. You can find out more about the SSAS mining functions in this post: http://www.bogdancrivat.net/dm/archives/14

Do you trust your Data Mining results? – Part 3/3

2010-12-06T10:32:00.001+01:00

SQL Server 2005 | SQL Server 2008 | SQL Server 2008 R2

This is the third and last post of my Data Mining back testing mini series. For my first posts I made the assumption that no action is taken based on the prediction. Although the mining process marked some of our contracts with a high probability for being cancelled, we did nothing to prevent our customers from doing so.

Of course, this is not the real life scenario. Usually our goal is churn prevention (or some other action based on the mining results). However, any action we take will (hopefully) change the behavior of our customers. Or to be more precise, we expect that less customers are really cancelling their contract. This is always a challenge for our mining process in total as the prevention may “dry out” our mining source data. After a while we might not have any non-influenced customers left with a high probability for cancelling the contract. A test group would help but in many scenarios this is not wanted (because the customers are just too important to risk loosing them just for the sake of some statistical validation). Another method would be to leave the influenced customers out of the training set. This doesn’t disturb our prediction but do we still know our prevention is successful enough?

For our back testing process, we can also add the expected prevention rate to our Monte Carlo procedure. In this case we’re not only trying to validate the model but also the prevention rate. On the other hand, if our test fails we’re not sure if it’s the model or our assumed prevention rate that is actually wrong.

For the following I assume that every customer with a churn probability greater then 35% gets prevention. From the past we know that 90% of our preventions are successful. Here are the results:

Old model (no prevention included):

New model (including prevention):

As expected the curves have shifted to the left. In the new model (including the prevention) the number of customers cancelling the contract is significantly lower than in the old model.

We can still use the same methods as shown in the first post to derive the threshold value for our test. This is the curve for the alpha and beta error (again: for beta for choose an alternative model that goes off by 3%).

Again, here are some values for the threshold T, alpha and beta:

T	Alpha	Beta
1580	88.7	0.0
1600	75.6	0.1
1620	56.7	0.4
1640	36.5	1.6
1660	19.5	5.1
1680	8.4	13.3
1700	3.0	27.0
1720	0.9	46.0
1740	0.2	65.7

For example with T=1680 we get alpha<10% and beta<14%. But as being said above, if our model fails the test we’re now not sure anymore if it is the model or the prevention rate. Again, the best way to avoid this uncertainty would be to use test groups. For example, we could exclude a random sample of 10% of all contracts with a churn score above 35% from prevention. Then we’re able to compare the behavior of the random sample (test group) with the customers that received prevention benefits. This test group enables us to measure the effectiveness of our prevention activity. And of course you could set up a statistical test in the same way we described here, to proof that the test group really supports a certain success rate.

To wrap things up, depending on the products, processes, the way new customers are acquired etc., the real-life data mining problems may be much more difficult than presented in this small blog series. Anyway, the methods described here, like the Monte Carlo algorithm or the statistical hypothesis test, are the main building blocks for achieving a reliable understanding how well the model performs. So, depending on the actual scenario, the above mentioned methods may be combined, adjusted and repeated to get the desired results.

This also ends this mini series about back testing. The basic methods are more or less ubiquitous and may also be used for several similar problems, for example to do the back testing for a financial risk model. By adjusting and combining the methods, a specific business requirement may be modeled into a reliable test. And as long as our model conforms to the test, we can have enough trust in our data mining models to use them as the basis for business decisions.

Do you trust your Data Mining results? – Part 2/3

2010-10-10T13:09:00.001+02:00

SQL Server 2005 | SQL Server 2008 | SQL Server 2008 R2

While the first part of this post was more about the idea and interpreting the results of the test, this part shows how to implement the Monte Carlo test.

First, we need a table with the predicted data mining probabilities. This is the output of the PredictProbability function from your mining result query. I’m using the same source data as in my previous post here. If you like you can easily create your own table and populate it with random probability values in order to test the code for the simulation below:

CREATE TABLE [dbo].[Mining_Result](
[CaseKey] [int] NOT NULL,
[PredictScore] [float] NULL
) ON [PRIMARY]

declare @i int=0

while (@i<10000) begin

insert into Mining_Result(CaseKey, PredictScore)
values(@i, convert(float,CAST(CAST(newid() AS binary(4)) AS int))/2147483648.0/2+.5)

set @i=@i+1

end

Don’t be confused by the convert(…cast…cast newid()…) expression. This is just my approach to calculate a random number within an SQL select statement.

Next we need a table for storing our Mining results:

CREATE TABLE [dbo].[Mining_Histogram](
[NumCases] [int] NOT NULL,
[Count] [int] NULL,
[Perc] [float] NULL,
[RunningPerc] [float] NULL,
CONSTRAINT [PK_DM_Histogram] PRIMARY KEY CLUSTERED
(
[NumCases] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Then this is how we’re doing our Monte Carlo test:

truncate table Mining_Histogram;

declare @numtrials int = 10000;
declare @cnt int;
declare @lp int;

set @lp=0;

-- perform a monte carlo test:

while (@lp<@numtrials) begin

select @cnt=COUNT(*) from Mining_Result where PredictScore >
convert(float,CAST(CAST(newid() AS binary(4)) AS int))/2147483648.0/2+.5
if exists(select NumCases from Mining_Histogram Where NumCases=@cnt)
update Mining_Histogram set [Count]=[Count]+1 Where NumCases=@cnt
else
insert into Mining_Histogram(NumCases,[Count]) values (@cnt, 1)
set @lp=@lp+1;

end

I’m using the same trick for the random numbers as shown above. In this example, we’re doing 10,000 iterations. For each iterations we compute the number of cases for which the Predicted Score is higher than a random number. For example, if for a certain case the predict score is 0.8 it is more likely that a random number between 0.0 and 1.0 is below the score than for a prediction score of 0.1.

Next, we’re filling the gaps in our histogram table with zeros to make the histogram look nicer:

declare @min int;
declare @max int;
select @min=MIN(NumCases), @max=MAX(NumCases) from Mining_Histogram

set @lp=@min;
while (@lp<@max) begin
if not exists(select NumCases From Mining_Histogram Where NumCases=@lp)
insert into Mining_Histogram(NumCases,[Count]) values (@lp, 0);
set @lp=@lp+1
end

Finally we’re computing the row probability and the running total using this T-SQL:

declare @maxcount float;
select @maxcount=SUM([Count]) from Mining_Histogram;
update Mining_Histogram Set Perc=[Count]/@maxcount;

declare @CaseIdx int
declare @perc float
declare @RunningTotal float =0

DECLARE rt_cursor CURSOR FOR select NumCases, Perc From Mining_Histogram
OPEN rt_cursor

FETCH NEXT FROM rt_cursor INTO @CaseIdx, @perc

WHILE @@FETCH_STATUS = 0
BEGIN
SET @RunningTotal = @RunningTotal + @perc
update Mining_Histogram set RunningPerc=@RunningTotal Where NumCases=@CaseIdx
FETCH NEXT FROM rt_cursor INTO @CaseIdx, @perc
END

CLOSE rt_cursor
DEALLOCATE rt_cursor

After running the simulation this is how the plots of the result look like (using my own values). The first plot shows the value of the field NumCases on the x-axis and the value of the field perc on the y-axis. The second plot has the same x-axis but shows the RunningPerc field on the y-axis:

These two plots look very much the same as the plots from my last post (although I used C# code there to generate the histogram data).

If you used the randomly generated scores from above for testing, you will notice the peak being around 5000 cases (instead of 2800 cases in my example).

And if you like a smoother version of the density function (as all the teeth and bumps mainly result from Monte Carlo approach), you could use this SQL query to compute a moving average:

declare @minrange int=0
declare @windowsize int = 50

select @minrange=Min(NumCases) from Mining_Histogram

SELECT H.NumCases, AVG(H1.[Count]) [Count], AVG(H1.Perc) Perc
FROM Mining_Histogram H
left join Mining_Histogram H1 on H1.NumCases between H.NumCases-@windowsize and H.NumCases

where H.NumCases>@minrange+@windowsize

group by H.NumCases

In order to do the histogram computation automatically with prediction query I recommend putting the code in an SSIS script component. I would also use another type of random number generator. This also allows you to set the seed for the random number generator. For my implementation I used an asynchronous script component that first loads all cases into memory (ArrayList collection), then performs the Monte Carlo test on the in-memory data and then writes the results back to the output buffer. This allows you do work with more scenarios and to log the progress during the loading and testing phase of the component.

I’m planning to write a Books Online Community Technical article on this topic. This article will be more detailed regarding the implementation. I will post a link to this article in my blog then.

Do you trust your Data Mining results? – Part 1/3

2010-09-19T18:18:00.001+02:00

SQL Server 2005 | SQL Server 2008 | SQL Server 2008 R2

Data Mining has been built into SQL Server since version 2005 and it’s quite comfortable and wizard-driven to design your mining models. However, Data Mining is not much about the toolkit but more about data preparation and interpreting the results. Without a proper data preparation, the algorithms will fail in really predicting or clustering the data. And the same is true for the interpretation of the results. But before we can start interpreting the data, we have to trust the results. At the design time of each mining model we can use test case holdouts, lift charts and cross validation to see if the model is robust and meaningful. But most of our prediction models try to predict a future behavior based on the knowledge of today and the past. What if there are significant changes in the market that are not already trained into our models? Is our model still correct or are we missing an important variable?

Today’s post is about implementing a back testing process to validate the mining results. For our example, we use a churn score prediction model. Think of a telecommunication company: Each customer has a 12 months contract that can be cancelled by the customer at the end of the period. If the contract is not cancelled, it’s continued for another 12 months. We want to predict how many customers are going to cancel their contracts during the next 3 months (the 3 months latency has to be build into our training set, but this is a different topic). To make things more simple in the first step let’s assume the company is not going to use the mining results (no churn prevention) but just waits 3 months to compare the reality with the prediction. That’s what we’re doing during a back testing.

So let’s assume that we did a churn prediction 3 months ago. Our results are returned from our data mining model as a table like this:

CaseKey	Churn (Churn Prediction)	ChurnProbability (Churn Probability %)
1	true	87.4
2	false	7.1
3	false	1.7
4	true	50.2
5	false	11.3
6	false	16.0
7	false	6.9
8	false	1.8
9	false	2.6
10	false	18.7
…	…	…

For my example I have 30,000 contracts (cases). Churn prediciton = true means that this contract (case) is likely to be cancelled by our customer (predicted by the mining model). In my dataset this is true for 2010 cases. I left some more columns out here, but usually you are also looking at the support and other measures.

Now, 3 months have passed and we want to check how good our initial data mining model was. As said below we didn’t do anything to prevent our 2010 cases from above to cancel their contract. Now, looking at our CRM system reveals that actually 2730 customers of those 30,000 cancelled their contract. What does this mean? We expected 2010 to cancel but in reality it was 2730. Does this mean our model is wrong? Or can we still rely on the model?

The clue to answering this question is to compute how likely it is, that

a) Our model is correct and
b) We see 2730 customers to cancel their contract

Just to avoid confusion, we’re not looking at the error cases within our prediction (as we would do with a Receiver Operating Characteristics analysis) but we try to validate the model itself.

If the Churn probability is the same for each case we could use a binomial test validate the model(see one of my very first blog posts about this topic). Another way to do this computation is to run a Monte Carlo scenario generator against our data from above. Basically, the test works like this

Do N loops (scenarios)
- Set the number of cancelled contracts x for this scenario to zero initially
- Look at each case
  - Compute a random number r and compare this number with the ChurnProbability p
  - If the r<p count this case as cancelled (increment x)
- increment the number of occurrences of x cancellations

I’m showing an implementation of this approach in my next post but for today let’s just concentrate on interpreting the results. For my example I used a SSIS script component to actually perform the Monte Carlo test. I used 30,000 scenarios and ended up with the following result:

As you can see, most of my scenarios ended with approx. 2800 cancellations (peak in the chart). This might be the first surprise. Assuming the mining algorithm was right, there are still much more cancellations happening than being predicted in the predicted churn column. How can this be? Well, actually the predicted value follows a very simple rule: It switches at 50%. This is a strong simplification of the true distribution. So instead of looking at the predicted values you should better look at the expectancy value:

Predicted value	Expectancy value
select count(*) from Mining_Result where churn=1	select SUM(ChurnProbability) from Mining_Result
Result: 2010	Result: 2784

As you can see, the expectancy value matches our distribution histogram from above. In most situations, the expectancy value differs from the value count. This is highly dependent on the distribution of the probability values. For example, doing the same test with the bike buyer decision tree model in Adventure Works I got 9939 cases with a predicted value of 1 for the BikeBuyer variable. Here the expectancy value is about 9135, so in this case it is lower than the number of predicted cases.

Back to our histogram from above. We can easily replace the number of cases by the percentage value of the total cases. This results in the probability density function. In order to proceed we have to use the aggregated density function. For our example, this function looks like this

This function tells us the probability for seeing less than a certain number of cancellation. As expected, the probability to see less then 30,000 cancellations is 100% (as we only have 30,000 customer who could cancel). On the other hand, the probability to see less than 0 cancellations is 0%. Again it may be a surprise to see that actually the probability for seeing less than 2600 cancellations is close to 0 (from the graph above). How does this look around our real number of 2730 cancelled contracts? Here is the extract from the table:

NumCases	TotalProbability %	1-TotalProbability %
…	…	…
2726	7.9	92.1
2727	8.3	91.7
2728	8.7	91.3
2729	9.1	90.9
2730	9.5	90.5
2731	9.9	90.1
2732	10.3	89.7
2733	10.8	89.2
2734	11.2	88.8
…	…	…

In this table, the total probability is the aggregated probability from our chart above and means the probability for seeing less than NumCases cancellations while 1 minus total probability means the probability to see more than NumCases cancellations.

From this table you can see that the probability for seeing more than 2730 cancellations is still about 90.5%. Now let’s look at the area between 2740 and 2920 cancellations (to reduce the number of lines I’m only showing every 20th row):

NumCases	TotalProbability %	1-TotalProbability %
…	…	…
2740	14.4	85.6
2760	28.4	71.6
2780	46.5	53.5
2800	65.1	34.9
2820	80.7	19.3
2840	91.0	9.0
2860	96.6	3.4
2880	98.9	1.1
2900	99.7	0.3
2920	99.9	0.1
…	…	…

While it is still likely (86%) to see more than 2740 cancellations, it becomes more and more unlikely with higher the value gets for the cancellation. And seeing more than 2900 cancellations is very unlikely (less than 1%). Of course, this only refers to the case, that the model is operating correctly.

In order to make our back testing an easy procedure we want to define a simple threshold T. Our model passes the test as long as there are no more cancellations than T. If the model does not pass the test, we have to re-validate the variables and check the overall state of the model. We do not want to do this too often. Therefore, the probability for our model being correct and still not passing the test should be less than 10% (remember that we will run the mining prediction over and over again). Now we need to find a proper value for T.

From our table above we can see that T is close to 2840 cancellations. We can query the correct value from our histogram table:

select MIN(numcases) from mining_histogram where 1-TotalProbability<0.1

The result is T=2838 for my example. So our 2730 cancellations from above are definitely below our test threshold T and therefore our model clearly passes the test.

Now that we’ve set a threshold to the condition “model correct and cancellations>2838”, what about the situation in which our model is incorrect. In this case we would assume that the real probability for a customer cancelling the contract is higher than predicted by our model. This is of course only one assumption we could make. Depending on the conditions and the environment, the definition of the “wrong model” can be different. In any case we have to define a “wrong” or alternative model.

For our example, this is how our model looks like with a 3% higher probability for cancellation (blue line).

For this chart, we have to create a separate histogram table for our alternative (wrong) model and also calculate the results in our Monte Carlo process. Again, we can read the probability for the condition “model is wrong but passes the test” from our histogram table. In our case it’s about 24.4%.

If you’re not interested in a more statistical view of our test you can now skip to the conclusion below.

Otherwise you probably already know that there are two possible mistakes we could make here:

model is correct but fails the test, usually referred to as type 1 or alpha error (false positive)
model is incorrect but passes the test, usually referred to as type 2 or beta error (false negative)

	Model is correct	Model is incorrect (alternative model is correct)
Test is negative, meaning the model passes the test	correct result(probability=1-alpha, so called specificity of the test)	type 2 error / beta error
Test is positive, meaning the model does not pass the test	type 1 error / alpha error	correct result (probability=1-beta, so called power or sensitivity of the test)

In order to get a better understanding of our test situation we can plot alpha and beta together into one chart:

The green line shows the probability for more than N cancellations in our correct model. The red line shows the probability for less than N cancellations in our wrong model. The bigger our threshold gets (the more we get to the right side in the diagram) the

lower our alpha gets (lower risk for the type 1 error “model is correct but does not pass the test”)
higher our beta gets (higher risk for type 2 error “model is incorrect but does pass the test”)

Here are some sample values for different values of T

T	Alpha	Beta
2780	53.5	1.8
2790	44.3	3.1
2800	34.9	5.2
2810	26.5	8.4
2820	19.3	12.7
2830	13.5	18.6
2840	9.0	25.9
2850	5.7	34.1
2860	3.4	43.5
2870	1.9	53.0
2880	1.1	62.2

If we want to keep alpha below 5% you can see that beta will over 35%. On the other hand, if we try to keep beta below 5% alpha we will over 35%. The “best” values for alpha/beta or not simply the value for T where alpha is almost equal to beta (here T=2826, alpha and beta approx. 16%) but depends on the error that you are wanting to minimize. In our example above we demanded alpha<10% which resulted in T=2830 and beta being about 25%.

In our case the reason for keeping alpha low would be to avoid readjusting the model too often (causing costs). On the other hand, keeping beta low reduces the risk of working with a wrong model and potentially loosing more customers. Basically this decision has to be made before actually defining the threshold value T.

Conclusion

For this example we defined a very simple test (number cancellations < 2838) which satisfies these two criteria:

It is unlikely (<10%) that our model does not pass the test although it is correct
It is unlikely (<25%) that our model passes the test although it is wrong (goes off by 3%)

While the 1st criteria means we’re not loosing more customers as expected, the 2nd criteria means we’re not spending too much budget on fine tuning the model.

It should be stated that the above calculations are done on modified (randomized) data. In practical life it can be more difficult to find a proper test for the model and also the tradeoff between error 1 and 2 can be much higher.

BI.Quality 2.0 released on Codeplex

2010-09-17T17:25:00.001+02:00

SQL Server 2005 | SQL Server 2008 | SQL Server 2008R2

Today we released the new version of BI.Quality on Codeplex. The new release contains many features from the wishlist (check the release notes for details):

Consistent table interface within one test case e.g. to create tables via local files or query

Unlimited number of queries within one test case

New XmlTableWriter and XmlTableReader to export/import a table

New CsvTableWriter and CsvTableReader to export/import a table

Assert for query execution time

Consistent delta within Asserts, absolute or relative

Extended error handling, e.g. the AssertTable breaks no more after the first failure.

Extended example test suite with all best practice test cases based on Adventure Works DWH 2008

All changes are fully downwards compatible to Version 1.0.0

Complete refactoring of the codebase for better extendibility

Improved error messages and self tests

There are only minor changes between 1.9.7 and 2.0.0. Some fixes include the enhanced downwards compatibility for release 1.0 test cases although it is recommended to use the 2.0 syntax for developing new test cases.

Data Mining on a small amount of data

2010-08-28T16:00:00.001+02:00

SQL Server 2005 | SQL Server 2008 | SQL Server 2008 R2

Data Mining is usually associated with finding previously unknown patterns in a large amount of data. But also a small amount of data may contain patterns, that are difficult to spot. In order to illustrate this, let’s look at the following situation. A customer with 80 stores wants to understand why some stores perform better than others. This is especially important before setting up new stores. It would be great to estimate the performance of the new store before building it up. This would make it much easier to decide on future store locations. Also, it might be useful for optimizing the performance of the existing stores. In order to find out about this, the customer collects some data per store as shown in the following table:

Criteria	Description
Sales area	Area for sales (in square meters)
Total working hours per week	Total working hour for all staff members per week (avg over the last 3 months)
Total opening hours per week	Total hours that the store is open per week
Location	E.g. City center, City or Outskirts
Location type	E.g. Mall, Plaza or separated
Store interior status	Condition of the store, e.g. modern, average or old
Store age in months	How old is this store
Parking facilities	Is it easy to park near the store? E.g. values include good, average, bad
Average parking costs per hour	What are the average parking costs per hour? Zero means free parking
Number of competitors within 10 minutes walk distance	Number of competitors within 10 minutes walk distance
Number of competitors within 15 minutes driving distance	Number of competitors within 15 minutes driving distance
Buying Power	Buying power of the people how live near the store ranging from very low to very high.
Sales amount per week	Average over the last three months

The data can be retrieved by querying the IT systems (for example HR and ERP), by using a survey (usually the staff in the store knows about the competitors and parking facilities around) or by using external market research data (for the Buying power). In my case, the data is just generated sample data:

After gathering all the data, things got more complicated as expected. How do you “score” each store? Which of the parameters are most relevant? Even if we’re only having 80 rows of data, it is not at all easy to see the dependencies.

For this example I’m using the Microsoft Office Data Mining Add-In so we can do all the data analysis using Excel. Since it’s Excel everything should be very easy. We want to use the Microsoft Decision Tree algorithm (Icon “Classify” from the ribbon bar):

The process is pretty easy. We have to decide which attribute we want to predict (Sales amount per week) and the wizard does all the rest. Now, here is the complete resulting decision tree:

No branches? What did we do wrong? Well, here are a few steps we should have taken, before simply invoking the decision tree. The most important thing is the proper preparation of the data:

1. Create relative measures, not absolute ones

The decision tree is capable of detecting rules like “if A then B” or “if A then not B” or even complicated combinations. However, dealing with continuous values is more difficult. The decision tree does not work quite well with rules like “if A is multiplied by 2, B is multiplied by 1.5” but tries to branch this as “if A is > 20 then B > 15”, “if A is >10, then B >7.5”. This might be especially true with input variables like our sales area or opening hours.

To quickly analyze the relationship we can use Excel’s Scatter chart type. Let’s start with the store size:

From this chart it seems reasonable to calculate sales by square meter instead of taking the absolute sales amount as there seems to be a more or less linear relationship between the sales and the store size.

Now let’s have a look at the influence of the opening hours. Again we’re using Excel’s Scatter chart:

As the trend line shows, the relationship seems to be a little bit logarithmic. However, let’s assume it’s also linear. Therefore we’re going to create an additional column in our spreadsheet computing sales by square meter and opening hours. This is the formula for our new column “Relative Sales”:

=[@[Sales Amount per week]]/[@[Sales Area]]/[@[Total opening hours per week]]

Of course, you would like to also check the influence of other variables, for example the age of the store:

This one looks pretty scattered, so we’re just taking the age of the store as an input variable.

But there is another relative measure we should create: The average number of sales persons in the store. We’re simply using this formula:

=[@[Total working hours per week]]/[@[Total opening hours per week]]

2. Make the input parameters discrete if possible

While it’s always a good idea to use discrete values for our input parameters, it almost becomes a must if you’re not having many rows of data. An example for a good discrete value is our location as it can only take these values: City center, City or Outskirts. The fewer the number of buckets, the better is it. If you’re not getting any results, try making the data more simple by choosing less buckets for your discrete values.

But look at our newly created columns for the relative sales or the average staff:

For our Mining purpose these are too many distinct values and although our decision tree will try to cluster them, we should do this in advance. Therefore we can either use Excel formulas or we could use the functionality of the data mining add-in: The “Explore Data” wizard:

After selecting the table and the column, the wizard analyzes the data and proposes some buckets as shown below:

For my purpose, I reduce the number of buckets to three (be brave!). By clicking the “Add new Column” button the resulting values are added as an additional column to our table:

I’m doing the same for the average staff members (4 buckets), the total opening hours (4 buckets) the store age in months (5 buckets) and the parking costs (4 buckets):

Our number of competitors is here 0-3 and 0-5, so we leave this data unchanged (not too many buckets).

So let’s try again with our prepared data set. Now, our decision tree looks like this:

As you can see there are only three influences identified by the mining algorithm here:

So, if parking is free, then the parking facilities are important while if parking is not free, the opening hours are important. This is a good start for looking for new store locations.

Another nice tool from Excel’s data mining add-in is the prediction calculator which can be found on the “Analyze” ribbon:

This one creates a ready to use input sheet in which you can enter the values of a potential new store and Excel immediately computes the likeliness for high sales:

This calculator is based on a different mining model (Logistic Regression). You can also see the impact of the input values on your sales:

In our case, smaller stores seem to perform better. The high value at the opening time below 41.5 hours may indicate that our computed column from above might not be well designed. And while there are quite a lot of stores with a sales area of less than 57 square meters there is only one single store which is already opened for 52 months: Our first store, which is definitely special (always equipped in the most modern style and only selected staff members are chosen to work there). Maybe you want to take this out of the data before doing the analysis.

So, after the mining you have to review your results properly. And of course you should also verify the other methods of making sure, your model is working fine (lift chart, case support as from my last post etc.).

How much support do you need for your Data Mining results?

2010-07-13T12:35:00.001+02:00

SQL Server 2005 | SQL Server 2008 | SQL Server 2008 R2

When querying your SSAS mining model you may have noted the prediction function predictsupport which is supplied by most of the mining algorithms, for example for the decision tree. The mining algorithm does not only predict a certain variable and gives the prediction score but also tells us how many cases were used to base this decision on (case support). You might have the feeling that cases with a lower support are not that reliable compared to cases with a higher support. This post is about how to determine the needed support for a given model.

To make this theoretical topic a little bit more practical, let’s look at actual data. For this post I’m using the targeted mailing decision tree model (TM Decision Tree) of the Adventure Works SSAS sample database. While you can do the same process described here with a data mining query result, I’m looking at the model itself instead. In order to do so I run the following query on my SSAS database:

SELECT FLATTENED [NODE_CAPTION]
,[NODE_DISTRIBUTION]
,[NODE_SUPPORT]
,[NODE_DESCRIPTION]

FROM [TM Decision Tree].content

WHERE IsDescendant('', '000000001') AND [CHILDREN_CARDINALITY]=0

I left a lot of interesting columns out here to save the space for the query result. There are many more fields you can query (some depending on the mining algorithm).

By selecting “children cardinality equals zero”, we only get the child elements from our decision tree. And since we used the flattened keyword our NODE_SUPPORT element is returned as multiple lines here (one line per possible value, try removing FLATTENED from the query above to see the difference). So this is how the result looks like:

Since our Bike Buyer attribute can have three states (Missing, 0=no bike buyer, 1=bike buyer) we get three lines per node in our flattened decision tree model query. For example the first three lines belong to the node labeled “Year Income < 58000” which can be found at the top of the lower half in the decision tree model viewer:

If you look at the details of this node in the mining model viewer, you will see the following values:

This is almost exactly what we have in our table above. For this node that Mining algorithm found 1362+465=1827 cases that matched the conditions of the node. You can see the full list of conditions in the NODE_DESCRIPTION column (which I left out here). In my case this is

Number Cars Owned = 2 and Region not = 'Pacific' and Yearly Income < 58000

So from all the rows that matched this criteria 1362 had a Bike Buyer variable value of 0 and 465 had a value of 1 during the training of the model. The probability is then computed by the number of the cases, for example 100*465/1827 gives approx. 25.46%. Actually, the decision tree calculates the probability in a different way. For many cases this is very close to the quotient above, but there are differences. If you want to be exactly sure to control the method of calculation you may want to calculate the probability score on your own (for the example above this means to calculate 100*465/1827 instead of taking the probability provided by the mining algorithm).

If we use this model for prediction, probabilities above 50% will be mapped to the positive result. For our example, we want to predict the “Bike Buyer” variable. For cases that fall into the node displayed above, the result will be 0 (as the probability for zero is greater than 50%) meaning that these contacts are unlikely to buy a bike.

In order to proceed, I focus on the prediction value of Bike Buyer = 1. I copy the results from our query above to Excel and removed all lines for result 0 or missing. I also did some formatting of the table. These are the first rows of the resulting table.

In order to decide whether this support is good or bad, we have to decide on which basis we want to do the decision:

Decision based on the predicted value
Decision based on the predicted values probability

In the first case, we’re only using the predicted value from our mining. In our example it would be a prediction of the Bike Buyer variable as a value of true or false. Our concern would be that our support does favor the alternative decision with a high probability.

In the second case, we’re taking the real prediction probability for some further calculation, for example for the back testing (I will discuss this during my next posts) or for calculation expectancy values of our prediction (for example expected costs).

Let’s start with the first case here.

1. Decision based on the predicted value

This case is based on some rule to derive the prediction variable from the prediction score and optionally further attributes. SSAS does this on a very simple basis by changing the decision at 50%. But the rule can be very different. You may consider all rows with probability >30% as potential bike buyers or you may include your estimated revenue. You could also use the profit analysis that is built into SQL Server Data Mining to decide on the rule. But in any case, for your mining results you are only interested on the output of the rule (not on the “real” probability), in our case “true” or “false”.

For our example, I’m using the “default” rule of 50%. In order to understand the effect of the support, let’s look at our concerns. First we start with a case having a probability higher than 50% (or whatever our threshold is), for example take a look at line 3 of the Excel table from above. This case has a probability of 61.28% meaning that 65 our of 106 rows had the Bike Buyer variable set to 1.In this case, our concern is:

The real probability for “Bike Buyer =1” is 50% or lower but still the random sample of data we used during training resulted in 65 positive rows.

If this happened, our model would be choosing the wrong decision for all rows that are matching the node’s criteria.

In order to deal with this concern statistically, I’m doing a simple and common trick here (maybe you should allow yourself some minutes to think about that). Let’s change the concern to

The real probability for “Bike Buyer =1” is exactly 50% but still the random sample of data we used during training resulted in 65 or more positive rows.

For this concern we can simple use Excel’s binomial function to compute the probability:

1-BINOM.DIST(65,106,0.5,TRUE)

I used the Excel 2010 function here. If you’re using Excel 2007 or before, the function name is BINOMDIST. The above function returns a value of 0.7%. So it is very unlikely that our model training was based on a wrong decision.

For the lines with a probability of less than 50% our concerns are just the other way round (of course you could also take the same rules as above and look for “Bike Buyer=0”). For our example I reference the first line in the table above:

The real probability for “Bike Buyer = 1” in this specific node is 50% or higher but we still see 465 positive rows in our 1827 cases.

Again, I’m transforming this to:

The real probability for “Bike Buyer = 1” in this specific node is exactly 50% but we still see 465 or less positive rows in our 1827 cases.

In this case, we have to use this function to compute the probability:

BINOM.DIST(465,1827,0.5,TRUE)

Again the probability is very low (almost exactly 0%).

Let’s add this calculation to the Excel-table from above:

The formula for E7 is

=IF(C7<0.5,BINOM.DIST(B7,D7,0.5,TRUE),1-BINOM.DIST(B7,D7,0.5,TRUE))

This formula is then copied for all lines below.

How do we read this table? To keep things simple, low values in the last column are good (as in our two example values from above). The closer the predicted probability gets to 50% (or in other words, the weaker the prediction gets) the more support is needed to make it relevant.

How do you work with this table? Let’s say you want to be 95% sure that the support is strong enough to prevent our mining system from being trained for the wrong response. In this case you would filter out nodes from the table above that have a value of 5% or above in the last column. This is the result

In this case we would have filtered out 608 from 12939 cases used for the support (approx. 4.7%). What are you going to do with these cases? Well, this depends on your mining question. Typically you would consider these nodes to be bike buyers if you want to minimize the risk of loosing potential customers or you would consider them as not being bike buyers if your goal is to reduce costs (by excluding these cases from the mailing). In any case, you will have to ignore the response from the data mining process if you do not want to risk being misled.

In our case it is ok to drop 608 cases, but what do you do if all cases have a very poor support? In this case, you should review your model carefully. Seeing a lot of cases with a low support usually means that your model is “over trained”. For example, your decision tree model has too many nodes. Mining works best when the real world can be simplified. For the decision tree you might try to increase the required support or set a higher value for the complexity penalty parameter.

The more you want to be sure that your case support is good enough the more cases you will have to drop. For our example here are a few values:

2. Decision based on the predicted values probability

In this case we’re not so much interested in a simple decision (bike buyer true or false) but more about the probability for each case. This also means we’re having to consider different concerns (although similar compared to the ones above). The whole process is very similar to the one above and if you are not too much interested in the outcomes you can just skip to the conclusion.

Basically, we only need to replace the 50% in the example above with an alternative model’s probability derived from the predicted probability. However, as described above, our concerns differ in the cases the favor a positive or negative result. So for our example, we’re considering a cut-off at 50% again.

Let’s start with the lines having a probability higher than 50%. For example, let’s look at the 3rd line from the table above. The probability found during training was 61.28% that is, 65 of the 106 rows hat the Bike Buyer variable set to 1.

In this situation our concern is that the real probability is less than 61.28% but still the random sample we used during training resulted in 65 positive rows. Ok, if the probability is still 61.279% this wouldn’t make much difference. Let’s try with a difference of 3%, so our concern (already transformed as described above) is:

The real probability for “Bike Buyer = 1” in this specific node is exactly 58.28% but we still see 65 or more positive rows in our 106 cases.

Again we’re using Excel’s binomial function to compute the probability:

1-BINOM.DIST(65,106,0.5828,TRUE)

The above functions returns a value of 23.26%. Although it is below 50% it is still significant (compare this to the 0.7% from above).

For the lines with a probability of less than 50% our concerns are just the other way round. For our example I use the first line from the table above as a reference again:

The real probability for “Bike Buyer = 1” in this specific node is exactly 28.46% but we still see 465 or less positive rows in our 1827 cases.

To compute this in Excel you have to take the following formula:

BINOM.DIST(465,1827,0.2846,TRUE)

In the case we get a result of 0.22% meaning it is extremely unlikely.

I added this calculation as an additional column to my Excel table from above. This is the result:

The formula for E7 is

=IF(C7<0.5,BINOM.DIST(B7,D7,C7+$B$4,TRUE),1-BINOM.DIST(B7,D7,C7-$B$4,TRUE))

This formula is then copied for all lines below.

Again we can now filter the table for whatever sureness we would like to have. If we want to be 80% sure that your case support does not favor a model that is 3% off, we would filter all lines from the table above that are having a value higher than 20% in the last column.

A lot or nodes do remain in this case (a lot more than in our discrete example from above): In this case we would have to ignore about 19.4% of our predictions.

Here are some sample results for the cases that need to be left out:

As expected, the more you want to be sure the more cases you have to drop. Also the less tolerance you allow for your model, the more cases you have to drop. So it’s up to you to find the best mix of security and usability of the model.

How to use an Excel 2003 file as a datasource for an SSAS cube

2010-07-11T11:17:00.001+02:00

SQL Server 2005 | SQL Server 2008 | SQL Server 2008R2

Ok, usually you wouldn't want to use an Excel file directly as a datasource for an SSAS cube in real life scenarios. But just in case you'd like to set up a quick demo without bothering to create a new database using Microsoft SQL Server or maybe Microsoft Access, the ability of sourcing your cube from an Excel file could be more than welcome. Just imagine you want to try some design ideas. While working with SQL server databases you would end in a large amount of test databases or you would need to backup/restore your databases all the time to test different scenarios. With Excel as a source for your cubes you could put your test data right into your SSAS solution. In order to modify the datasource you can simple make a copy of your Excel file (for backing up the older version) or of your solution instead of caring about databases. And even if you don't have the databases installed you can use any of your testing solutions by just opening the solution as the data becomes part of the solution.

Sounds good, doesn't it? But how can you do so? First, when trying to set Excel as an OLE-DB source you will notice that it just isn't there.

But what we can do, is to use the Microsoft Jet 4.0 OLE DB Provider. So that's where we start. The next dialog asks us to provide the database file name.

If you click on the 'Browse...' button you will notice that the selection is limited to .mdb-files or .accdb-files as the Jet OLE-DB provider as usually used with Microsoft Access databases. So we just change our file type selection to 'All files' and pick our Excel file.

Now, if you click on 'Test connection' you will get an error message like the one below:

Our Jet OLE-DB provider still believes, that we are connecting to a Microsoft Access database file and therefore it cannot connect. So here comes the really important step. We open our connection again, click on the 'Edit' button to edit the connection string and then we switch to the 'All' tab of the connection properties.

As shown in the screenshot we have to set the extended properties to 'Excel 8.0;HDR=Yes;IMEX=1;'.

Excel 8.0 stands for Excel 2003 (I couldn't get Excel 2007 to connect properly using 'Excel 9.0', so I stayed with the Excel 2003 format here). 'HDR=Yes' means that our Excel tables contain headers.

After that, a click on 'Test Connections' gives the desired result:

Of course, we now need to build up our Excel-file. Each "source table" sits on its own sheet. You can easily build up some time dimension or use Excel functions like RAND() to create random fact data or VLOOKUP(...) to link your tables with testing data to each other.

Although this is not at all useful for real life situations (as we would extract the data from the Excel sheet using ETL tools or simply not storing the source data in Excel at all), this might still be useful in order to set up a quick and dirty example solution and play around by modifying the source data (add columns, use different formats etc.) without the need to work on a 'real' database.

10 Tips for every SSAS developer

2010-06-12T11:51:00.001+02:00

SQL Server 2005 | SQL Server 2008 | SQL Server 2008 R2

Within BIDS, our development environment for SSAS applications, the Cube Wizard does a great job. With the wizard you get all the dimensions, the cube and the proper links between the cube and the dimensions. It even takes care of role playing dimensions and names them accordingly. But although it does a great job, the created cube is not ready for the end users. Recently I saw some cubes being just in the state where the wizard left them. So today’s post is about the most essential steps to perform after the cube wizard has finished creating the cube. I don’t go into too much detail here. Some of the topics have been addressed in other posts. Maybe I’m writing about other topics in upcoming posts. So the following list is more like a check list to verify your development.

1. Make the cube user friendly / do not use technical names

While the operational databases belong to a technical area, the OLAP solution has to be clear and easy to understand for the end users. Avoid technical names, use abbreviations only if they are commonly known and well documented and don’t prefix dimensions with ‘Dim’ or ‘D_’ or something like that (same with the fact-tables). The user wants to analyze sales by time not FactSales by DimTime.

2. Check all dimensions for their attributes

The cube wizard cannot know which attributes are important for a dimension. Check each attribute carefully. Delete attributes that are not needed at all. It does not make much sense to develop for future requirements. This only makes the cube harder to understand for the end users. Also, think about the usage of the attributes. For example, having an attribute “OptIn” for a CRM cube with members yes and no may not be ideal for usage in a pivot table as you would only see yes or no on the column/row. It is better to have “With OptIn” and “Without OptIn” as members because here, the meaning is immediately clear. Of course, numeric flags like gender being 0 or 1 also do not make much sense. Members should be clear and readable.

3. Create appropriate attribute relationships and create meaningful hierarchies

Attribute relationship-settings and hierarchies are the key to build a robust OLAP solution that enables query developers to write good MDX queries and also results in a better performance of the cube. However, attribute relationships can be tricky, so make sure you fully understand this topic. You should also use tools like BIDSHelper’s dimension health check to make sure that your attribute relationship really matches the source data.
For further explanation see http://ms-olap.blogspot.com/2008/10/attribute-relationship-example.html or http://ms-olap.blogspot.com/2008/11/turning-non-natural-hierarchy-into.html

4. Provide properties as needed (not every attribute of your dimension is needed as an attribute hierarchy!)

Define only those fields of your dimension table as attributes that you need either for hierarchies or as a filter or for pivot axis. Fields that are just informational (like telephone number of the employee for example) don’t make much sense as attribute hierarchies. Set the property “AttributeHierarchyEnabled” to false for these attributes. They are then shown grayed-out in the dimension designer. However, you can still use them in your OLAP tool, for example in Excel. It is important to understand how your attribute relationships are defined here.

5. Set the format for measures and calculations

The standard format for numbers is just a simple number format. This usually looks ugly in the cube browser or other frontend tools. It’s not a big deal to set the format for every measure. For example, you could use “#,##0.00” for numbers to get a nicely formatted two digit number representation. You should also do this for calculated measures and cube calculations.
For further explanation see http://ms-olap.blogspot.com/2009/11/how-to-define-excel-compliant-format.html

6. Set the proper dimension type. Especially, define a time dimension

Setting the dimension type of your time dimension to “time” makes it much easier for your OLAP client (like Excel) to provide special time dependent filtering options. Also, if you’re planning to use semi-additive measures, the time dimension must be marked accordingly.
Furthermore you also need to qualify the type of the time dimension’s attributes (using Years, HalfYears, Quarters, Months and Date). This is necessary for MDX functions like YTD(…), MTD(…) and ParallelPeriod(…) and for the “Add Time Intelligence” wizard.

7. Define the default measure for the cube

The default measure is used, when no measure is on the axis or the slicer (where - expression) of the query. If not set, SSAS takes the first measure in the first measure group. This can easily change if new measures or measure groups are created in the cube. Queries that rely on the default measure (which is in turn not a good practice) will then result in a different result. That’s why it is important to set a default measure.

8. Define the default member for dimensions/attributes which are not aggregatable

Similar to the default measure for the cube, each dimension has a default member that is used, if the dimension is not explicitly present in the query. For example, think of a dimension ‘Scenario’ with members like Actual, Forecast, Midterm plan etc. It does not make sense to aggregate the different members so you will usually set the IsAggregatable property to false. In this case you don’t have an All-element and you should provide a default member (in our example it would usually be ‘Actual’). If not specified, the first member is used which could again lead to errors if new members are created or the member names change. Keep in mind, that there are three ways to specify the default member: in the dimension itself, in the cube script (also useful for role-playing dimensions with different names) and in the security role definition.

9. Create a drill-through action for every measure group and make this the default action

Using a drill-through action shows the detail data behind a given cell. However, the default drill-through method only shows the name information of the dimensions’ key attributes. Often enough we would see meaning less surrogate keys here which do not make sense for our end users. By defining a drill through action the developer can set the displayed attributes for each dimension making the result of the drill through query much more readable. In addition, making this action the default action also works well with clients like Microsoft Excel. Here, a double-click on a cell triggers the default action.

10. Properly deal with unrelated dimensions

By default, values on unrelated dimensions are shown as the All-member of the unrelated dimension. For example, in Adventure Works, measures from the Internet Sales measure group are not linked to a reseller (obviously). However, if you try to analyze internet sales by reseller (for example by reseller type) you will see the same value (the total of sales) for each reseller. This is confusing. The property to control this behavior is the IgnoreUnrelatedDimensions setting in the measure group properties. When setting this to ‘false’ values for unrelated dimensions are not shown anymore. This is much easier to understand for the end users.
For further explanation see http://ms-olap.blogspot.com/2010/04/properly-showing-values-for-unrelated.html

Whats’ next?

So that’s all? Of course not. First, all the tips from above do not prevent a bad architecture or cube structure. A good architecture is important for each Business Intelligence solution and there are many good books (for example Kimball’s “The Data Warehouse Toolkit”) out there about setting up such an infrastructure. In general, building an SSAS solution on top of a well made dimensional model instead of some OLTP systems is a good idea.

And of course, the tips from above are not complete in any sense. If you asked someone else, you may get different tips. For example, it’s also important to set up the cube security or to define some time intelligence for easier analysis (like YTD-computations or year-over-year growth etc.). It is also a good practice to simplify complex cubes with many measure groups and dimensions using perspectives to make them easier to understand by the end user. And as many measures/calculations are not self-explaining just from their name, it is a good idea to link an html-document with a specification or helpful text with the member. You can use a cube action for this.

However, I guess you all have many more good ideas with best practices and most important tips. If you like, I would be very happy if you could share these tips with me and - who knows – maybe there will be a post like “25 tips for every SSAS developer” in the near future.

By the way, this is the second anniversary of my OLAP blog. It was quite an interesting time so far and I’m looking forward to more topics to write about. The main location of this blog is http://ms-olap.blogspot.com. The posts are mirrored to http://oraylis-olap.spaces.live.com/ and also reposted to the new blog system of ORAYLIS at http://blog.oraylis.de (here you can find more interesting posts about Sharepoint, Business Intelligence in general and much more). My posts are also readable by using some blog aggregators like http://www.ssas-info.com/ which I really recommend to learn more about SSAS, MDX and OLAP.

Self-Service BI, PowerPivot and the future of traditional BI (DWH, OLAP, MDX)

2010-05-24T14:38:00.001+02:00

SQL Server 2008R2 | Excel 2010/PowerPivot

Since I’m using the recently released Microsoft PowerPivot Add-In for Excel 2010 and when reading the rumors about the future of traditional OLAP and MDX there are some questions about the big picture of a BI-environment including self-service functionality. Basically, the BI world of the past had excepted the idea of a central data warehouse having a meta data layer such as OLAP to perfectly present the information to the end users. What about this new player PowerPivot then? How does this fit into the picture? Is there still a future for things like OLAP, MDX, central data warehouses or do we only need to roll out self-service BI functionality to every desktop PC? Some people have asked me questions about my point of view here and although I’m not a Microsoft representative I’d like to share my personal opinion with you:

Self-service BI tools are not and will never be a replacement for traditional BI-systems but a great enhancement for them.

Simple dashboard build using only Excel 2010 with PowerPivot. No centralized BI needed anymore?

In other words, including self-service BI functionality to your BI-system will increase the possibilities and the analytical power of the end users. But if you are cutting costs for centralized BI solutions believing to replace them with self-service tools, you’ll end up with even higher costs as suddenly the work that has only been done once in the centralized BI system will be performed redundantly, in an inconsistent way, error-prone and with much more working time needed.

In order to understand this, let’s look at just some advantages, a centralized BI-system can offer, which cannot be replaced by a decentralized self-service BI tool like PowerPivot:

Combination of data from multiple source in a consistent and time-saving way
If this is done on a user per user approach, it is very likely that different users are getting to different results. Also, this work is highly redundant. Imagine different departments getting to the IT in order get “their” data exported, then trying to combine it into a single data store. Often enough, this job requires additional mapping tables (customers, articles etc. may have different ids in different systems). We are supporting customers with multiple ERP-systems (due to mergers) and the mapping can be quite complicated. But even if it is just a single source of information, mapping between different tables has to be done and requires skills and knowledge about the data models. For example, if you forget to consider a key field the result may differ significantly. Or think about the need to exclude rows with a certain status, because this means they’re cancelled. One end user might know about this, the other might not.
This means that you will still need your sophisticated ETL processes, a proper front room model, slowly changing dimensions and all the stuff we know from our typical BI projects.

Consistent use of common calculations considering approved business rules
In many cases, our current ETL processes include complicated business processes for doing calculations and data mappings. Key measures and performance indicators have to be calculated in a consistent way. Business rules are the backbone for the company’s information system. If different departments are comparing apples and oranges, there is a lot of space for confusion and wrong decisions. Think of a simple example, like reporting the revenue. Are warranty adjustments considered? What about partial payments or commissions? What about discounts (for example staff discounts)? For all those aspects it has to be decided to include or exclude them into a certain key measure and also which relation to the time dimension is correct. Is revenue for a partial payment considered as a sale (full amount) at the date of purchase or are the real payments (cash flow) considered? If every user has to make these decisions it is very unlikely that everybody is doing the same calculations. Comparing results for different departments (think of a sales meeting for the different product managers) will then get very difficult.

Security can only be implemented in a central data store
Some real world scenarios are currently looking like this: The IT department exports data for other organizational structures, manually filtering out the data that is not intended for the recipient. This might work with very simple security structures, but with more security roles, user dependent security or more information recipients, this would lead to an enormous amount of work for exporting all the data. And if there are changes to the companies security model, all the exports have to be considered again. Having a central OLAP solution makes it easy to define the security roles and access rights in a central place using the business view on the data. For example, in OLAP you can restrict a user to see the cost centers for which she or he is responsible – OLAP takes care about all related data (for example automatically filtering the cost facts to these cost centers). There is no need for a huge amount of data exports as users can retrieve the data and information they need and IT only has to make sure that the data is available and secured.

The need for management reporting
Management needs an overview about some or all business units. Having the information (especially the calculations, KPIs etc.) in a decentralized environment makes it very difficult to get this management reporting in a simple, time saving way. It is more likely, that IT has to do special exports which are then processed by the controlling department to build the management reports. This could result in controlling spending all the time in doing data management, not information management and controlling. Also, in this scenario, the data from the management reporting will most likely differ from the data of the departments. Just imagine the CEO going to a some product manager saying “Hey, you’re product profitability is –5%” and the product manager says “No, it isn’t. Look at MY report. Here it reads +3%” and actually neither of them could say which result was the right one…

Then, after all, if the central BI environment is so important, do we really need decentralized self service BI? Well, not every user will need self-service BI but for some it can be a real time saver or give them a lot of analytical power. Here are just two important scenarios:

The end user wants to analyze information by special properties of the data which are not present in the centralized data warehouse. Just think of product managers. Each product has a different target group, special conditions in the market and therefore potentially certain aspects that are different from product to product and therefore from product manager to product manager. Those information might only be relevant for certain products. Having this in a central data warehouse would be confusing as the information has no meaning for most of the products. Allowing each department to cover the specific needs of their work while still providing the central information being available in the data warehouse is the best way here.
The end user wants to combine the centrally provided information with other sources of data, for example information that has been purchased/acquired from external sources and which is not complete in means of geography, time etc. Think of a marketing department planning a campaign. In order to do so, they want to analyze sales data in conjunction with external data for purchasing power. The external information was only purchased once for the region where the campaign is planned. This kind of data cannot be loaded into the central data warehouse. But with self-service BI it can be analyzed side-by-side with the centrally provided sales data.
Or think of one department trying to improve product quality by changing some of the parameters during the production cycle for some of the production batches. These changes are tracked in some other system (let’s say Excel) but not in the central DWH as they do only apply to this single line of production. Self-service BI allows us to analyze changes in the parameters together with data from the central data warehouse side by side (for example quality control data in this case).

So my opinion is that the traditional centralized BI systems really benefit from self-service BI functionality. However, self-service BI can never replace traditional BI. But what about technical aspects, especially the future of MDX? MDX is the query language for multidimensional databases (used by many vendors). In PowerPivot we can do a lot of the calculations using the new expression language called DAX. Will DAX be a replacement for MDX? Absolutely not! DAX is meant to bring analytical power to Excel users. It looks similar to Excel functions and in fact many Excel functions can be used. Its strength is simplicity. Although one could imagine to extent the expression based DAX language to query functionality (in MDX you can write both, queries and expressions) this would also complicate the use of DAX which is clearly not intended. Even with today’s Excel, many users only know about the operators +, –, / and * and the SUM function (advanced users know about SUMIF…). In order to have end users, even power users, being able to leverage the power of a self service BI solution, the calculation functions have to be as simple as possible. This is the idea of DAX. However, when defining complex queries, building highly sophisticated business logic into calculations or KPIs, implementing ease-of-use like KPI trends, OLAP actions, drill through queries, navigation in hierarchies (DAX has no hierarchies) MDX has everything that’s needed here. Including this functionality into DAX would only make it complicated and more difficult to understand and use. Of course, this is only my opinion, but I’m sure that MDX will still be used for what it is used today and DAX expressions will be used for self-service calculations and mappings that are intended to be done by none-technical people.

When using client-side technology to create any kind of informational insight we have to monitor this process carefully. There has to be a process to maintain business requirements and implement changes to the central BI-system to avoid the negative effects mentioned above. As with PowerPivot, it is also possible to monitor which workbooks have been used and which data sources have been queried (if the PowerPivot sheets are published to Sharepoint 2010). I think this is also important to really understand, if the self-service BI tool is used in a way it is intended to be used or if some analysis requirements that should have been part of the central BI solution are now starting to be solved in multiple departments redundantly.

So again, we will see a co-operation between the centralized BI-system and self-service BI solutions, as well as between MDX and DAX. Self-service BI and DAX are not the fox in the chicken-house of traditional BI but they are extending the vision and scope of BI-systems of today and in the future.

Analyzing the number of visits per customer

2010-05-16T12:51:00.001+02:00

SQL Server 2008 | SQL Server 2008 R2

OLAP is perfect for analyzing fact records that are mapped to dimensions. However, what can be done, if the reference to the dimension changes depending on the selected period? I’m not talking about slowly changing dimensions here, but really about the period of selection.

Think of the following example: A manager wants to analyze the number of visited customers for his sales force. He wants to see, how many customers have been visited once, twice and so on during the last three months (or any other time period).

In order to do so, we need a dimension “Visits” (with the number of visits, for example 0, 1, 2, …) but we cannot map the visits against this dimension because this mapping depends on the selected period. For example, one single customer was visited once in January and once in February. If the selected period is January, the we need to see one customer being visited once. If the selected period is January and February together, we would need to see the customer being visited twice.

In SQL Server 2008 we can solve this by not linking our Visits dimension to the measure groups. The calculations are then performed by a cube script. For our example, we’re using a very simple data model:

The fact table FactVisits contains one row per visit (linked to date and customer). Note that the Visits dimension is not linked to the fact table. The source table for the visit dimension looks like this.

For the cube, we need to calculate the entries for the Visits dimension. To do so, I created an additional measure (Customer Count) based on some other value I found in the source fact table (the value will be overwritten using the cube script later). Usually you would create an additional column in the data source view (value 0) to source this measure from. Here is the definition of my Customer Count measure:

In order to calculate the number of customers for each visit count, I created the following cube script:

scope ([Visits].[Visit].[Visit],[Customer].[Customer].[Customer],[Measures].[Customer Count]);
this=iif(([Measures].[Visit Count])=CInt([Visits].[Visit].currentmember.properties("KEY")),1,NULL);
end scope;

The script is computed on leaf level of the visits (visit count dimension). In the cube script, the reference to [Visits].[Visit].currentmember.properties("KEY") results in the VisitID (number of visits). If the number of visits (measure [Visit Count]) is equal to the number of visits found in the visit dimension, this is counted as one. Note, that I used NULL instead of 0. This makes it easier to analyze, which customers have been visited for a given number of times, as null values can be suppressed (see last screenshot of this post).

So, let’s check the results up to this point.

The calculation now works for every single line but currently the totals are still wrong. The correct value for the totals can be computed using a dynamic set (to allow Excel multi selects) like this:

CREATE HIDDEN DYNAMIC SET CURRENTCUBE.[DynaVisits] AS [Visits].[Visit].[Visit];
([Visits].[Visit].[All])=Sum(existing [DynaVisits]);

I also want to count more than ten visits on a special dimension element (ID 999 in the table above). This can be achieved with the following cube script:

scope ([Visits].[Visit].&[999],[Customer].[Customer].[Customer],[Measures].[Customer Count]);
this=iif(([Measures].[Visit Count])>10,1,NULL);
end scope;

Testing the results (here using Microsoft Excel) shows the correct calculation for the grand total as well as for other levels of the visit count hierarchy:

The way our calculations works can be best observed when looking at a single customer.

In this case we had two visits of our customer in 2008 and one visit in 2009 giving a total of three visits for 2008 and 2009. Note that the actual fact is shown as a dimensional property here.

We can also apply multi select filters in Excel. Here is another sample screenshot filtering only customers with less than 4 visits during the last three months in 2009 (this is the ‘(multiple items)’ filter for the calendar). The data is now also analyzed by the sales representative (attribute of the customer). Note, that the grand totals are still correct:

As mentioned before it is also possible to see which customers are listed here by including the customers in the pivot table. This is shown in the following screenshot (filter to visit count = Zero).

However, the drill through action would still return all customers (due to the definition of my measure) as the value is only based on a computation. So the drill through should be disabled here.

In this case, I simply checked the measure in order to enable the drill through action:

Of course, the example is still a very simplified one. Usually you would need to know the customer base, so you’re not counting new customers as not being visited during the last years.

Pie chart with ‘others’ category (collected data)

2010-04-25T12:22:00.001+02:00

SQL Server 2005 | SQL Server 2008

Pie chart with too many categories don’t make much sense. The following screenshot shows the order count from the AdventureWorks OLAP database by subcategory (no selection on date here for this example) as a pie chart:

Fortunately, SQL Server 2008 Reporting Services adds a feature to collect all slices below a certain threshold (either as a fixed value or as a percentage) as shown below:

To get this result you have to check the custom attributes (properties) of the chart series (either by selecting the pie itself or by choosing the chart series in the property box picker)

In our example I collected all slices with a value of less than 3% to one single slice with the name ‘Other’. You can even show the other values as an exploded pie chart (although I think it’s more confusing).

In cases where you want to show just a certain number of slices (instead of using a threshold) or if you are using Reporting Services 2005 which doesn’t support the collected slice, you may do the collection by MDX:

WITH
SET SelectedSubCategories AS
TopCount
(
Order
(
[Product].[Subcategory].[Subcategory]
,[Measures].[Order Count]
,DESC
)
,10
)
SET OtherSubCategories AS
[Product].[Subcategory].[Subcategory] - SelectedSubCategories
MEMBER [Product].[Subcategory].[Other] AS
Aggregate(OtherSubCategories)
SELECT
[Measures].[Order Count] ON 0
,NON EMPTY
{
SelectedSubCategories
,[Product].[Subcategory].[Other]
} ON 1
FROM [Adventure Works]
CELL PROPERTIES VALUE;

The idea is quite simple. First, you create a set with the number of slices you want to see (I called it SelectedSubCategories here). Then you can simply get all other categories using a set minus operation (I called it OtherSubCategories here). Finally you create the ‘Other’ member in the dimension as an aggregate of the last set.

Of course, you can even make the parameter for the number of slices a report parameter so the user can choose how many slices are shown in the diagram.

Properly showing values for unrelated dimensions (IgnoreUnrelatedDimensions)

2010-04-04T19:59:00.001+02:00

SQL Server 2005 | SQL Server 2008

There have already been some posts about unrelated dimension handling, but I think, the topic is worth being visited again. So this is yet another unrelated dimension post (yaudp).

A good summary about this topic can be found in this post by Benny Austin, however there are some drawbacks not being mentioned there and my post today is focused on those drawbacks and how to deal with them.

If you are short on time here is a quick summary of the following text:

Setting IgnoreUnrelatedDimensions to false can make cube browsing easier to understand, but

Take care with default values in unrelated dimensions: As the dimension is not related, no value will be shown.
Take special care of dimensions with the IsAggregatable property set to false (no ‘All’ element): If such a dimension is unrelated, setting IgnoreUnrelatedDimensions to false has no influence for this dimension
Role playing dimensions are considered as different dimensions. If one dimension role is related and another role is unrelated, the setting IgnoreUnrelatedDimensions=false will not show data for the unrelated role(s) of the dimension.
Calculated measures associated with a fact table do not depend on the IgnoreUnrelatedDimensions setting of the measure group they have been assigned to but only to setting of the measure groups that they are generated from.

If you say ‘yes, I knew all this’ I suggest stop reading here. If not, here is the detailed explanation of the above bullet points.

Setting IgnoreUnrelatedDimensions to false can make cube browsing easier to understand…

For my samples I’m not using Analysis Services (as we have to do some changes to the model) but a very simple OLAP model that looks like this:

Think of the model as being used for a web shop. We want to sell the six products from the fabulous novel “The Deadline” by Tom DeMarco using a web shop and compare our sales with the number of web site visitors for a given day. In order to see some interesting effects of the IgnoreUnrelatedDimensions topic, we have a role playing time dimension DimDate for our web site orders (order data and delivery date).

So, here is the dimension usage screenshot:

As you can see, the measure group for our web site visits is not linked to delivery date or product. Therefore we get the following result if we want to analyze web visits by product:

Most people would find this result confusing. If you are not absolutely aware of the dimension usage, seeing the same number of web visits for every product could lead to a wrong understanding.

A common work around for this behavior is to set the IgnoreUnrelatedDimensions property of the measure group Web Visits to false as shown below:

This causes cell results from unrelated dimensions to disappear as shown in the following screenshot:

This looks much better. So why shouldn’t we always use this?

Take care with default values in unrelated dimensions

The first problem with our design appears if we define a default member other then ‘All’ for the product dimensions. I am a special fan of Quickerstill, so let’s make this the default product:

Please keep in mind that there are other methods of setting the default value (cube script, role security definition) that will have the same effect. Since our web visit measure group is not related to the product, we now get now value returned when we simply use the measure “Number of visits”:

Same happens for the following MDX query:

select [Measures].[Number Of Visits] on 0
from [OLAPSample]

The reason is that although we don’t have the product dimension in our pivot table, the default member still acts as a filter and as you can see from the previous screenshot of the pivot table, there is no value for ‘number of visits’ for the product members. The only way to get a value for the Number of visits is now to explicitly set the product dimension to its all member. I’m using MDX here in order not to get confused by side effects caused by the way the built-in pivot component generates the sums:

select [Measures].[Number Of Visits] on 0,
[Product].[Product].allmembers on 1
from [OLAPSample]

Take special care of dimensions with the IsAggregatable property set to false…

After looking at the following example I would have expected that removing the ‘All’ member from the dimension would result in no values are shown in any case. Removing the ‘All’ member is simply done by setting IsAggregatable to false:

But now the query above results in the following:

select [Measures].[Number Of Visits] on 0,
[Product].[Product].allmembers on 1
from [OLAPSample]

As expected, we don’t see the total any more. As I haven’t expected, the result is exactly the same as if we had set IgnoreUnrelatedDimensions to true (default). So in this case the setting for IgnoreUnrelatedDimensions is ignored.

I would still doubt the need for the default member in many cases. It could be an indicator that the fact table isn’t properly modeled (if we have to exclude certain fact values ‘by default’) or they might be able to be replaced by calculated measures.

Also, the problem with the default member does only exist, if the dimension is not related. In some cases you can fix this. For example, if you have a scenario dimension (actual, plan, forecast etc.) with different fact sources and with a default of ‘actual’ you can link all the fact sources to the matching dimension member (although your actual data source does only contain one scenario).

Unrelated role playing dimensions…

In our sample we have two roles for the time dimension (order date and delivery date). However, web visits are only linked to one of them (order date). Therefore analyzing the web visits by the other (unlinked) role has the same effect as with any other unlinked dimension: The values are suppressed. For the following screenshot, we took the Year from the delivery date dimension:

Calculated measures…

Regarding the IgnoreUnrelatedDimensions setting, calculated measures are always calculated with the settings of the measure groups used in the calculation. The ASSOCIATED_MEASURE_GROUP attribute has no influence here. For example, let’s simply “copy” the measure “Total Price” from the order measure group to the web visits measure group and vice versa:

CREATE MEMBER CURRENTCUBE.[Measures].[Total Price (2)]
AS [Measures].[Total Price],
FORMAT_STRING = "#,##0.00",
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact Web Visits' ;

CREATE MEMBER CURRENTCUBE.[Measures].[Number Of Visits (2)]
AS [Measures].[Number Of Visits],
FORMAT_STRING = "#,##0",
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact Order' ;

The following screenshot shows, that each of our calculated measures (Total Price (2) and Number Of Visits (2)) behaves exactly in the same way like the original measure although they are part of a different measure group:

Conclusion

Setting IgnoreUnrelatedDimensions can be a good way of improving readability of olap results. However, things are getting tricky with unrelated dimensions that are having default members. In some cases you can get rid of the problems by changing the design (splitting fact tables, using calculated measures, relating unrelated dimensions if possible). Especially with non-aggregatable dimensions the behavior becomes strange as IgnoreUnrelatedDimensions doesn’t work at all in this case.

Effects of attribute relationship settings on calculations

2010-03-14T17:39:00.001+01:00

SQL Server 2005 | SQL Server 2008

This post is again about attribute relationship. I recently saw a very good presentation by Michael Mukovskiy, a colleague and friend of mine, regarding attribute relationship and its influence on calculated members.

In order to keep things simple, I start with a very simple date dimension having the following attributes and relations:

Let’s assume we also have a measure group “Sales” with a measure “Quantity”. For our cube we also want to display percentage of the sales with respect to the year (eg. January: 10%, February: 12% etc.). In order to do so, we need the quantity per year and for our simple example I just do the computation for this (the percentage can easily computed then).

To do so, we use the following cube script:

CREATE MEMBER CURRENTCUBE.[Measures].QuantityFullYear
AS ([Measures].[Quantity], [Date].[Year].currentmember),
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Sales' ;

Opening the cube browser, one can see something like this:

For this post it’s only important that the calculated member defined above computes correctly on every level of the date hierarchy: For each year we’re getting the total of the full year no matter what level in the date hierarchy we have on our axis. It’s sufficient to use [Date].[Year].currentmember in the calculation to do the trick. However, in order to understand the following example we have to look at the computation a little bit more precisely.

So let’s take a look at one of the query cells:

Before our computation takes place, May 1, 2009 is selected on the date hierarchy so this is the context of our calculation. Because of our attribute relationship, this also results in changes for the other date attributes as shown below:

You can verify this easily by defining calculated members that rely on each of the levels (eg. member QuarterName As [Date].[Quarter].currentmember.name).

It might be a little bit surprising that a simple calculation like

(
[Measures].[Quantity],
[Date].[Year].currentmember
)

really gives the full year’s value because we’re also in the context of a specific month (May) and quarter (Q2) and even day (1). So one could assume that we would have to write our calculation like this:

(
[Measures].[Quantity],
[Date].[Year].currentmember,
[Date].[Quarter].[All],
[Date].[Month].[All],
[Date].[Day].[All]
)

As we saw from our example above, this is not necessary (although it gives the same result). The reason for this is that the reference to a specific member in the Year attribute again changes the context for our computation and in this case this results in all attributes preceding the Year attribute (in our case: Quarter, Month an Day) to be changed to All.

In more detail, the following rules apply for single attribute context changes (in this example the context change happens for the Month attribute):

As we can see from the first rule shown here, a context change to a specific member results in all attributes that precede the changed attribute in the attribute relationship to be changed to All. So this does the trick for our computation. It doesn’t matter that we’re actually changing the year to the same value it had before as the context was 2009 and currentmember also gives 2009, so we’re changing from 2009 to 2009. It’s still a Any/all –> specific value change and therefore all preceding attributes are changed to All.

Up till now, this was only the prerequisite for this post. So now, it’s getting more interesting. Let’s assume everybody’s happy with our cube and it is used for some months. Nobody really remembers how our calculated member is defined and everything works correctly.

Then, one user likes to have a calendar week attribute included in the date dimension. Of course this is easily done and now our attribute relationship looks like this:

Of course we could also model an attribute relationship between Week and Year (at least for some definitions of the calendar week…) and also define a hierarchy for this. But for our simple example let’s continue without.

So, we only changed the date dimension and deploy our cube because we would expect our calculated member from above to work properly after this changed (hey, we didn’t touch it). So, let’s take a look at the pivot table we used above:

As you can clearly see, our calculated member still works fine for the year, quarter and month level but not for the day level of our hierarchy. In order to understand what went wrong here, let’s again take a look at a specific date, eg. May 1, 2009. Instead of giving the full year’s values of 1501, we only get 21 here. The context change to this specific date also results in our week attribute to change (as it depends on the day). In my method for computing the calendar week, it computes to week 18. The following screenshot shows the calendar week together with the day:

For the cell of May 1, 2009 our calculated member QuantityFullYear is computed in the following context:

Attribute	Context
Day	May 1, 2009
Month	May
Quarter	Q2/09
Year	2009
Week	18

Now the expression ([Measures].[Quantity], [Date].[Year].currentmember) is evaluated. Since [Date].[Year].currentmember is now 2009 (based on the context we’re in), we have a context change like in rule 1 above (although it’s again changing to the same value 2009 –> 2009). This forces all attributes that precede the year attribute to change to All. But our week does not precede the year, as it is kind of a branch like shown below (sometimes the visualization of BIDS helper is easier to understand compared to the built-in functionally):

So for our computation, the measure Quantity is evaluated in this context:

Attribute	Context
Day	All
Month	All
Quarter	All
Year	2009
Week	18

This means, we’re only getting the aggregated quantity of week 18 which can be easily proofed by looking at our last pivot including the week (4+2+6+0+0+3+6=21).

Although we made no changes to the calculation itself, it doesn’t work properly anymore after our change for the attribute relationship. This is just another example that shows, that you really need to take care of your attribute relationships and also need to fully understand the consequences on calculations. Even worse, it is hard to find such problems as our change to the cube happened at a totally different part so nobody expects the calculation to fail afterwards.

I also recommend establishing test queries to assert the functionality of all computations. Such test queries can be run from the ETL process and check, if all computations are still working after loading data into the cube (kind of a unit test idea).

For our problem with the computation, there are at least two possible solutions. In some cases you can simply create the missing attribute relationship. In our case we could create a relationship between the week of the year and the year attribute (assuming the definition of the calendar week allows doing so). Our attribute relationship for the date dimension would look like this then:

In this case, the relationship between the week and the year forces the week to its All member when referencing [Date].[Year].currentmember.

If you cannot create such a relationship, we have to force the week to the All member manually in the calculation::

CREATE MEMBER CURRENTCUBE.[Measures].QuantityFullYear
AS ([Measures].[Quantity], [Date].[Year].currentmember, [Date].[Week].[All]),
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Sales' ;

The result for both solutions gives the desired result on every hierarchy level of the date dimension:

Data quality in BI projects

2010-03-13T10:02:00.001+01:00

SQL Server 2005 | SQL Server 2008 | SQL Server 2008 R2

There is a new testing suite for BI solutions named BI.Quality available at codeplex: http://biquality.codeplex.com/. The suite is based on NUnit and supports quite a lot of different testing methods. What I like most is the ability to automatically compare result sets from different sources, for example SQL and MDX. This allows you to define test scenarios your system must meet after the OLAP cube has been loaded. Or think of migration projects where you can still run queries against the old system and compare the results with the new system. As everything can be automated, tests can be run on a regular basis (for example directly after every cube processing) to make sure, that all facts are really complete and correct.

Here are some screenshots:

More info can be found at http://biquality.codeplex.com/.

Disclaimer: Although it is an open source project the current development is done by ORAYLIS (my employer).

Solution for SSAS 2008 multi-selects in Excel (Dynamic Sets)

2010-02-14T17:06:00.002+01:00

SQL Server 2008

Being able to select multiple dimension elements in an MDX client requires some care in designing calculations for the cube. However, with SQL Server 2005 it was really difficult to design calculations in a way that work fine with Excel 2007. The reason for this is that Excel 2007 uses sub cubes for filtering and sets in SQL Server 2005 did not reflect sub cubes. So this article is about multi-select friendly queries in SSAS 2008 using dynamic sets.

There have been some posts about the problems around multi-selects. Of course you cannot use a currentmember reference in your calculation (if a set is in the where condition, there is no single current member) but use sets in most cases. However, also sets do not react on where conditions in an MDX statement by default. You have to add the EXISTING keyword to get the desired result. In order to illustrate this, we’ll start with a very simple calculated measure. Our measure should just return the number of days being selected as the filter for our date dimension. I’ll use the good old Adventure Works example database for my tests here.

with

MEMBER CountDays_TooSimple
AS DrillDownLevel([Date].[Calendar].[Date], [Date].[Calendar].[Date]).count

MEMBER CountDays_Using_Existing
AS DrillDownLevel(existing [Date].[Calendar].[Date], [Date].[Calendar].[Date]).count

select {CountDays_TooSimple,CountDays_Using_Existing} on 0

from [Adventure Works]

where [Date].[Calendar].[Calendar Year].&[2003]

The output looks like this:

As you can clearly see, the expression set expression without the EXISTING keyword (used for the measure CountDays_TooSimple) did not react on the filter (resulting in all 1188 days that are stored in the Adventure Works database), while the calculation with EXISTING (used in the measure CountDays_Using_Existing) did. Ok, this has nothing to do multi selects (as we only selected a single year) but it illustrated how we could use EXISTING to adjust sets to the given scope. The behavior is basically the same with multi selects. So, if we change the where condition to January and February we also get the right result for our measure that uses the EXISTING-method:

…
where {[Date].[Calendar].[Month].&[2003]&[1],[Date].[Calendar].[Month].&[2003]&[2] }
…

However, and this is where things are getting complicated, our measures still depend on the way how we implemented our filter. If we turn the filter into a sub cube, then the result looks different:

with

MEMBER CountDays_TooSimple
AS DrillDownLevel([Date].[Calendar].[Date], [Date].[Calendar].[Date]).count

MEMBER CountDays_Using_Existing
AS DrillDownLevel(existing [Date].[Calendar].[Date], [Date].[Calendar].[Date]).count

select {CountDays_TooSimple,CountDays_Using_Existing} on 0

from (
select {[Date].[Calendar].[Month].&[2003]&[1],[Date].[Calendar].[Month].&[2003]&[2] } on 0
from [Adventure Works]
)

Now, even our calculation that used the EXISTING keyword to filter the set does not work anymore:

Since Excel 2007 uses sub cubes to filter queries, this behavior is exactly how Excel would return this calculation (see below for the complete example in Excel).

So, how can this be solved? In SQL Server 2008 there is a new feature, called dynamic named sets (or dynamic sets). The good thing about dynamic sets is, that they also react on sub cubes filters. So let’s look at the following calculation of the same measure:

with

DYNAMIC SET [CountDaysDynaSetDays] AS [Date].[Calendar].[Date]

MEMBER CountDays_Dynamic
AS DrillDownLevel(CountDaysDynaSetDays, [Date].[Calendar].[Date]).count

select {CountDays_Dynamic} on 0

from (
select {[Date].[Calendar].[Month].&[2003]&[1],[Date].[Calendar].[Month].&[2003]&[2] } on 0
from [Adventure Works]
)

At first glance, nothing has really changed here. We simple declare the reference to our set (level here) [Date].[Calendar].[Date] as a dynamic set and used the set name in the calculation instead. For the result, I also included the two other measures from above in order to show the difference:

This shows how dynamic sets can be used to react on multiple selects provided as a sub cube. I didn’t show it here, but our calculation using the dynamic sets (CountDays_Dynamic) also works fine with a simple where clause.

In order to check if this calculation also works in Excel, let’s transfer all the measures above to a cube calculation. We simply add them at the end of the Adventure Works’s cube script:

CREATE MEMBER CURRENTCUBE.CountDays_TooSimple
AS DrillDownLevel([Date].[Calendar].[Date], [Date].[Calendar].[Date]).count ,
VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.CountDays_Using_Existing
AS DrillDownLevel(existing [Date].[Calendar].[Date], [Date].[Calendar].[Date]).count ,
VISIBLE = 1 ;

CREATE DYNAMIC SET CURRENTCUBE.[CountDaysDynaSetDays] AS [Date].[Calendar].[Date] ;
CREATE MEMBER CURRENTCUBE.CountDays_Dynamic
AS DrillDownLevel(CountDaysDynaSetDays, [Date].[Calendar].[Date]).count ,
VISIBLE = 1 ;

For the cube browser, everything looks as expected. For the following screenshot I also selected January and February 2003. Please note, that also the calculation using the EXISTING keyword works fine here:

Now let’s open Excel and try there:

As Excel uses a sub cube method, our second measure (CountDays_Using_Existing) fails here (giving the total number of days). But the calculation using the dynamic set still works fine in all scenarios.

So, if you’re facing problems with multi selects you should also think of dynamic sets as one possible option to circumvent the problems.

Also, dynamic named sets can also be a solution for performance optimizations (see Mosha's blog for example).

Do not set error configuration for KeyDuplicate to IgnoreError

2010-01-30T10:22:00.001+01:00

SQL Server 2005 | SQL Server 2008

Some posts ago I wrote about the error message ‘A duplicate attribute key has been found when processing’. At the end of the post I suggested not to set the error configuration for the KeyDuplicate to IgnoreError (unless you are in a prototyping scenario).

Some people asked me about this (as it seems to be a nice and easy solution, similar to ‘on error resume next’ in VBA which I also wouldn’t recommend).

The main reason for me is that I definitely prefer getting a processing error instead having wrong values in the cube which could be the consequence.

For example, let’s take a look at a date dimension:

DateID	Month	Year
1	January	2008
2	February	2008
3	March	2008
4	April	2008
5	May	2008
6	June	2008
7	July	2008
8	August	2008
9	September	2008
10	October	2008
11	November	2008
12	December	2008
13	January	2009
14	February	2009
15	March	2009
16	April	2009
17	May	2009
18	June	2009
19	July	2009
…	…	…

For this simple dimension you can see that months appear more than once (once per year). This means, that the following attribute relationship is wrong (assuming that we used each of the columns above as key for the respective attribute):

Having set the KeyDuplicate to ‘ReportAndStop’ results in SSAS failing to process the dimension, so we are instantly aware that something is wrong. Here is the setting in BIDS (has to be set per dimension):

And here is the error message if you do a ‘Process Full’ on the dimension:

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'DimDate_x0024_', Column: 'Month', Value: 'April'. The attribute is 'Month'.

With this error message you may fix the problem and get things right. However, if you set the KeyDuplicate to ‘IgnoreError’ you will get no feed back from the system at all. However, if you take a look at the dimension you will find that each month is only associated with one of the years (in my case 2008).

A user might not realize this and although I have fact values for all years, my cube now only shows values for 2008:

The correct values in my case would be as follows:

So in this situation we would have totally wrong values in the cube without even getting an error message and this is the main reason for me to have KeyDuplicate set to ‘ReportAndStop’. In my trivial example, the error would be easily detected by the users but as cubes are getting more complex you might not notice some missing and some wrong values. Another source for an attribute relationship violation could be a modeling error for an SCD-2 dimension. If, for example, you relied on the attribute relationship between product and product group and forgot to model this properly although the data source keeps historic changes, than you might see the sales on the wrong product group. Again, with the ‘ReportAndStop’ option you would immediately know that there is an issue with your dimension.

Different granularity in a single dimension

2010-01-10T12:56:00.001+01:00

SQL Server 2005 | SQL Server 2008

Handling different granularity (for example actual and plan values) can get a little bit complicated. Of course there are standard methods, like splitting up the less granular data in order to meet the finer granularity. Or you could use a parent-child structure as this allows you to store data at different levels in the tree-like structure. Or you could supply ‘unknown’ elements to map the less granular information.

For this post I want to show a different approach. Usually for each dimension we are linking all fact tables that refer to this dimension to the same key in the dimension table (the dimension’s primary key). However, the data source view also allows us to link facts to different key columns in the same dimension table.

In my simple scenario I have time dimension (called DimDate) and two fact tables: Order and Order Plan. The orders are on a daily basis while the order plan is on a monthly basis. We want to link both fact tables to the same time dimension as shown below:

The link from the fact order table to the time dimension DimDate (marked as 1 in the sketch above) is the usual link from the fact table to the primary key of the DimDate table. The time dimension is on daily granularity and so are the order facts. But for the plan value fact table FactOrderPlan, the link to the time dimension is realized by using two key columns: Year (as the year number, eg. 2008) and month (as the month number, eg. 11 for November), so the link in the data source view looks like this:

For our cube it is important to specify the right granularity attribute. While the order table is linked to the Day (granularity attribute), we link the order plan fact table to the month attribute and define the proper key mappings for that.

Now, the dimension usage looks like this:

We also defined proper attribute relationship for the time dimension accordingly to build up a year->quarter->month->day hierarchy.

So let’s take the first look at the cube created by this method:

This first look is as expected. As long as we are at a common granularity level shared by both fact tables, we can see the values correctly. Also, the aggregation of both fact sources works fine (although they are at different granularity).

Now, let’s drill down to the day level which is not present in our planning data:

The behavior here is exactly the same as for other unrelated dimensions! The value of the nearest matching hierarchy is taken for the levels below. Sometimes this behavior of the cube confuses the users, but we can still change this behavior by changing the parameter IgnoreUnrelatedDimensions:

Now, the planning values below the month granularity level have disappeared:

However, if you want to create a calculated measure that is also based on the planning values, you should be aware of the fact, that the values are simply not existing any more at the day level. For example, let’s define a calculated measure PlanFulFillment using the following expression:

[Measures].[Amount] / [Measures].[Amount Plan]

At the day level, the measure Amount Plan does not exist, so this results in computation errors:

You could still use the non-empty behavior for the calculate measure (set to “Amount Plan”) in order to have these computations disappear. However, if you want to refer to the monthly value, you can simple use the ValidMeasure MDX function that is always helpful in conjunction with IgnoreUnrelatedDimensions=false. So after defining our calculated measure as [Measures].[Amount] / ValidMeasure([Measures].[Amount Plan]) the result looks like this (at day level, the monthly values for the planning data is taken)

So, surprisingly enough (at least for me), everything behaves exactly like we wanted it to do and this makes the approach to an alternative in some scenarios. Again, please check your attribute relationship carefully and also spend some time on testing the result as the approach can get dangerous for more complicated attributes structures.

I also checked this design with more attributes and parallel hierarchies in the time dimension (for example calendar week) and more fact tables (for example production plan) and the aggregation was still correct. Having IgnoredUnrelatedDimensions set to false is helpful here to clearly see, which fact is selected at the right granularity level.

Following is an example with three fact tables (one at day level, one at month level and one at week level):

A different approach to modeling units

2009-12-21T15:21:00.001+01:00

SQL Server 2005 | SQL Server 2008

While units are a built-in functionality in some OLAP databases, we need to take care of them in SSAS on our own. Usually I model units as a dimension so that the facts are linked to the unit they belong to. However, units usually must not be aggregated (like adding up meters with liters), and therefore we would set the IsAggregatable property to false. The user first has to choose a unit before the result is displayed. In some cases this may not be that clear. For (local) currency for example the values are aggregatable as long as the currency is consistent which may or may not depend of the currency unit. As long as the selection (no matter of how the filtering was done) leads to a single currency it would be possible to display the result in local currency. Whenever there is more than one currency involved, the result cannot be shown.

Taking the usual approach with the non-aggregatable unit dimension you may find a situation like below:

For this example we have stores aggregated to some kind of store group with sales. As the currency is a non-aggregatable dimension you don’t see totals for the rows (just for the columns). Assuming we set our default member for the currency unit dimension to some currency not included (or to unknown like I did), you don’t see any value at all, if the currency unit is not included in the query:

So everything’s fine with this approach. But usually we don’t analyze by currency and if we simply put it on a filter, we might miss values of the other currencies like in the following screenshot of the same sample cube (the value for ‘Other’ gives no hint that there might be other sales here too):

So the idea could be to tell the cube to display the currency values as long as the displayed cell contains only one currency unit. For other cells we can only display a warning. The following screenshot shows the final result:

Please note, that although we don’t have the currency unit included in this pivot table on either the axis or the filter, the cube knows that certain cells only result in EUR values and therefore can be aggregated while other cells consist of more than one local currency and therefore cannot be aggregated.

To explain how this can be done, let’s first look at the model of our sample cube:

The currency unit is linked to the fact table using its primary key UnitID. For the cube we include this key is a measure to the cube… hey, this sounds weird, why should we use the surrogate key as a measure?? Well, we even use it twice, once aggregated by the Min function and one aggregated by the Max function.

Now, for any cell, where “Minimum CurrencyID” equals “Maximum CurrencyID” we are sure that we only have one currency unit in this cell. This can be used in a calculated member. I set the visibility of the original measure Amount to hidden and add a calculated member like this:

CREATE MEMBER CURRENTCUBE.[Measures].Amount
AS iif([Measures].[Minimum CurrencyID]=[Measures].[Maximum CurrencyID],[Measures].[AmountLocal],NULL),
FORMAT_STRING = iif([Measures].[Minimum CurrencyID]=[Measures].[Maximum CurrencyID],
strtomember("[Currency].[Unit].&["+CStr([Measures].[Minimum CurrencyID])+".]").properties("Format String"),
""),
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact Sale' ;

As you can see we also take the format string (e.g. “$”#,##0.00) from the currency table to display each currency properly.

For the above screenshot I modified this code a little bit and returned the value 0 instead of NULL. I also used the text “multiple units” as the default format string for this value 0. I prefer the NULL value though as it is “more” accurate.

As long as you’re using Excel 2007 compliant format strings (as describe in one of my previous posts), everything should display properly in Excel too as shown in the following screenshot:

Other clients can also leverage the formatting defined in our cube. For example in Reporting Services you could use the above formats if you refer to the FORMATTED_VALUE property instead of the VALUE property. So instead of

=Fields!Amount.Value

we would use:

=Fields!Amount.FormattedValue

Here’s a simple report based on the sample cube: