SQL Server 2012/Denali | PowerPivot

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

Only delta values		Only snapshots (changes)

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

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

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

Here is the final code:

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

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

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

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

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

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

Details of the calculation

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SQL Server Denali | PowerPivot

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

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

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

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

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

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

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

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

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

Here is the resulting table using the new aggregate:

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

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

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

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.

Der Startpunkt befindet sich auf der Seite http://www.microsoft.com/WindowsAzure/dallas/.

Wie melde ich mich bei “Dallas” an?

Zunächst benötigt man eine Windows Azure Subscription. Dazu muss man sich derzeit nur mit seiner Windows Live Id anmelden (http://www.microsoft.com/windowsazure/offers/).

Anschließend landet man auf der Katalog Seite und kann hier die verschiedensten Daten “subscriben”. Exemplarisch sei hier der Dienst von Associated Press (AP) dargestellt.

Auf der folgenden Seite lassen sich dann die Service Parameter einstellen. Hier werden dann die für den Zugriff benötigten Informationen dargestellt:

• Account Key
• Unique User ID
• Service URL

Diese werden für einen Zugriff benötigt.

Der Analyse Button öffnet das PowerPivot AddIn von Excel 2010.

Anschließend stehen die Daten im Addin zur Verfügung und können mit den lokalen Daten verknüpft werden. Auch die Aktualisierung der Daten ist so möglich.

Weitere Beiträge:

• PowerPivot und Codename “Dallas”
• PowerPivot
• Consuming “Dallas” Data with PHP (Extern)

PowerPivot ist eines der neuen Microsoft BI Features. Es handelt sich um ein Excel Add-In, dass sich für Microsoft Office Excel 2010 (leider nicht für Excel 2007) installieren lässt.

Eine sehr gute Einführung in PowerPivot finden Sie unter: http://www.powerpivot.com

Mit PowerPivot kann sich der Anwender Daten aus den unterschiedlichsten Datenquellen laden und diese miteinander verknüpfen. Anschließend können die Daten in einem Pivot Fenster analysiert werden. Die Darstellung erfolgt in der gewohnten Excel Pivot Methode. Neu sind die Slicer (In der deutschen Version mit dem holprigen Wort Datenschnitt übersetzt), mit denen man Daten seht intuitiv und schnell filtern kann.

Das Ergebnis einer Analyse kann mitsamt den zugrundeliegenden Daten danach auch auf die zentrale Zusammenarbeits-Plattform SharePoint 2010 bereitgestellt werden. Das hat den Vorteil, dass ganze Teams auf die so bereitgestellten Daten und Analysen zugreifen können. Hier können die Daten dann auch automatisiert nachgeladen werden. Dazu wird auf dem SharePoint ein Ausführungsplan hinterlegt. Im SharePoint besteht die Möglichkeit die Zugriffe auf diese Analysen auszuwerten.

Technologie:

Microsoft greift auf eine InMemory Technologie zurück, die auch in anderen BI Produkten Einzug gefunden hat (z.B. Qlikview). InMemory bedeutet, dass alle Daten im Speicher des Rechners abgelegt werden. Diese Technologie ermöglicht es sehr große Datenmengen sehr platzsparend abzulegen und eine beeindruckende Performance zu gewährleisten. In den meisten PowerPivot Demos sieht man 100 Millionen Datensätze, die in Millisekunden sortiert oder gefiltert werden.

Datenquellen:

Zu den Datenquellen zählen zahlreiche Standard Datenbanken wie SQL Server, Oracle, Sybase oder Teradata sowie OLAP Cubes. Zusätzlich können aber auch Daten lokaler Dateien wie Excel oder Access kombiniert werden. Eine weitere sehr nützliche Datenquelle sind die Daten von Reports, die mit dem Microsoft Report Builder erzeugt wurden.

PowerPivot Video: