AdventureWorks DW 2008, cube browser different result vs Excel 2007

Question

Hi

I am testing a calculation that comes with the AdventureWorks DW 2008 SSAS sample project: the Internet Radio To All Products.  When I use the cube browser within Visual Studio, and I drag that calculated measure to the screen, and then select a dimension, say, Products, I get a split of All products and their respective contribution to the sales of ALL productions.   I can then filter for two products I am interested in, and I get:

AWC Logo Cap                 29.69%
Fender Set- Mountain        70.31%

which is their contribution of each of those products to the total sales for both products.  that is great! and is exactly what i want.  So basically the filter is dynamic, so if i select only a few products it recalculates the percentage over the total of the SELECTED products.

However, when I go and try to view the same calculated measure and dimension in Excel 2007 (including the filter in the dimension for the two products in question), I get:

AWC Logo Cap                 0.07%
Fender Set- Mountain        0.16%

which is the contribution of each of those products to the total of ALL products, regardless of the filter!!!......  which is not what i want!!

How can I have Excel 2007 behave in the same way as the cube browser in Visual Studio and calculate the percentage of contribution of each product over the total sales of the SELECTED products only??

Thanks for you help

---

Javier Guillen

Answer 1

Excel 2007 generates a different MDX query than the cube browser - you can confirm this by tracing queries with SQL Profiler. Assuming that you're using AS 2008, you can try the Dynamic Set approach to defining the calculated measure, as discussed in this blog entry:

MDX in Katmai: Dynamic named sets


Here's my modified version of [Internet Ratio to All Products], and the result in an Excel 2007 pivot table :
 
Create Dynamic Set CurrentCube.[VisualProducts] as

[Product].[Product].[Product].Members;

Create Member CurrentCube.[Measures].[Internet Ratio to All Products]

As [Measures].[Internet Sales Amount]

/

Aggregate(

[VisualProducts],

[Measures].[Internet Sales Amount]

),

...

Row Labels	Internet Ratio to All Products
AWC Logo Cap	29.69%
Fender Set - Mountain	70.31%
Grand Total	100.00%

---

- Deepak

Answer 2

Thank you Deepak, your solution works very well.

One weird thing is that the new calculated measure works in Excel 2007 (as I want) but it doesnt work in the cube browser (thats ok, its just weird).  the cube browser displays

AWC Logo Cap                 0.07%
Fender Set- Mountain        0.16%

even if i change the component property of "calculate totals based on", "visible items only" or "all items", in both cases it returns the same result.  so it loses its dynamic behavior in the cube browser, but it accomplishes what i need in Excel 2007.

Only bad thing is that it makes it difficult to debug, as both clients behave differently....

---

Javier Guillen

Answer 3

"..  it makes it difficult to debug, as both clients behave differently .." - I agree that this is confusing. The cube browser uses a session set with VisualTotals(), so the value of the [All Products] member is adjusted. Conditional logic could be added to try and compensate (see below) - not sure whether it will work in all scenarios:


Create Hidden Dynamic Set CurrentCube.[VisualProducts] as
[Product].[Product].[Product].Members;

Create Member CurrentCube.[Product].[Product].[Visual] as
Aggregate([VisualProducts]),
VISIBLE=False;

Create Member CurrentCube.[Measures].[Internet Ratio to All Products]

 As [Measures].[Internet Sales Amount]
    /
    iif(
      (Root([Product]), [Measures].[Internet Transaction Count]) <
      ([Product].[Product].[Visual], [Measures].[Internet Transaction Count]),
      (Root([Product]), [Measures].[Internet Sales Amount]), 
      ([Product].[Product].[Visual], [Measures].[Internet Sales Amount])
    ),
...

---

- Deepak