THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Using SSAS 2005/2008 as PowerPivot Data Source: Calculated Members

A general problem of any cube is the way PowerPivot handles calculated members. From a technical point of view, it is absolutely correct that PowerPivot imports the result of a calculated member evaluated at the granularity of the query result. However, from the user point of view, it is not pretty clear why importing a calculated member it imports a value and not the formula. If your users are used to browse cubes, and because calculated member are defined at the cube level and not on the client side, users often don’t see the difference between a “native” measure and a calculated one. For example, if a calculated member shows the value of an indicator (for example, the ratio of sales per phone call), when a user imports that indicator in PowerPivot for Country, City, Year, Month, Day and Product, he will see a wrong number looking at this indicator browsing data and selecting only the total per Year, without a Month/Day detail.

The user would expect to import also the logic of a calculated member into PowerPivot and not just the resulting value, even if from a technical point of view the actual behavior is correct. Moreover, the fact that end users see correct data in the PowerPivot table might induce him to not check how that column calculation will behave while browsing a PivotTable.

We can reproduce the typical scenario also by using AdventureWorks cube. If you look at Figure 1 you can see data selected for import:

  • Geography.Country.Country
  • Geography.[State-Province].[State-Province]
  • Reseller Average Sales Amount (calculated member)
  • Reseller Sales Amount
  • Reseller Order Count

image

Figure 1 – Query Designer importing data by Country and State-Province

The Reseller Sales Amount and Reseller Order Count measures are used to calculate the Reseller Average Sales Amount measure. We know that into the cube there is a definition like:

[Reseller Average Sales Amount] = SUM( [Reseller Sales Amount] ) / SUM( [Reseller Order Count] )

However, the end user cannot see this definition in any place of the wizard and might be difficult for him to find the [Reseller Order Count] measure, which is into a different folder (Reseller Orders instead of Reseller Sales). Moreover, in this case both terms are visible measures, but often (and it happens also to the customer) one or more measures used to make a calculation are hidden to the end users, making it almost impossible to find the measure to use in the calculation. You can see in Figure 2 that another user imported data from the same data source, but using only the Country and not the Province-State attribute.

image

Figure 2 – Query Designer importing data by Country only

When the user compares the two tables aggregated by Country in PivotTable, he sees different values, like you can see in Figure 3. Despite the tentative to use Average to display a meaningful value for the Grand Total, all the values for all the Countries are different.

image

Figure 3 – PivotTable of the two tables imported in PowerPivot

If you make the calculation by dividing the Reseller Sales Amount by the Reseller Order Count, you can see in the column D of the worksheets shown in Figure 4 that the number is calculated correctly for Grand Total in both cases. Because the extraction was made at the Country level, also the values for each Country are right in the SalesByCountry pivot table, but values are different in SalesByProvince PivotTable because each Country shows the average of the value returned from the cube for each province.

image

Figure 4 – Differences between averages of PowerPivot measure (column C) and calculated measure (column D)

Thus, only the definition of a measure with a DAX expression can display right information. This is a very simple calculation and is a widely spread issue in the cubes of the customer.

Final consideration

I hope to see improvements in this area in future versions of PowerPivot. By now, it is necessary to instruct users about how to build the calculation in PowerPivot, providing them the necessary raw measure that allow building a DAX expression that produces the same result.

Published Thursday, August 19, 2010 8:25 AM by Marco Russo (SQLBI)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Nissha said:

Great info! It saved me a lot of headaches.

October 30, 2012 8:12 PM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement