### Question

• Dear Experts, I need help with a scenario

I have a fact table with data as follows.

 Product ProductQuantity ProductCompnent ProductComponentPrice A1Cycle 100 Tyre 10 A1Cycle 100 Tube 5 A1Cycle 100 Seat 20 A1Cycle 100 Chain 30 A1Cycle 100 Handle 10 A1Cycle 100 Bell 50 A1Cycle 100 Break 10 B1Cycle 200 Tyre 10 B1Cycle 200 Tube 5 B1Cycle 200 Seat 20 B1Cycle 200 Chain 30 B1Cycle 200 Handle 10 B1Cycle 200 Bell 50 B1Cycle 200 Break 10

The fact table is linked to Product dimension (Containing a Product-Hierarchy and an attribute hierarchy Product ) and ProductComponent Dimension. FactCount is count measure in measuregroup for simple row count in fact.

Tricky part is : PrductQuantity is on product level/grain - meaning What is total quantity of product.  In above example A1Cycle has ProductQuantity of 100 whereas B1Cycle has productQuantity of 200 (It is repeated on all rows of a product)

I have created  a simple Cube with Product dimension attached to this measuregroup with aggregation SUM, When I create a pivot using Product dimension and ProductQuantity measure

Product    ProductQuantity   FactCount

-------------------------------------

A1Cycle      700                     7

B1Cycle       1400                   7

Total           2100                    14

The expected output should be (I don't need FactCount measure, I am just showing it for illustration)

Product    ProductQuantity        FactCount

------------------------------------------

A1Cycle            100                  7

B1Cycle             200                 7

Total                 300                 14

I cannot change aggregation type from SUM to MAX etc, as If i put a filter choosing A1Cycle and B1Cycle it should show me 100 + 200 = 300 as result (and not 200 which would be if i take aggregation as MAX)

I have ProductCompnent dimension also related to this measuregroup.

When I create a report with Filter on ProductCompnent = Tyre and Product on Rows it looks like follows, which is correct output

(ProductCompnent = Tyre )

Produt                ProductQuantity  FactCount

---------------------------------------------

A1Cycle                100                   1

B1Cycle                 200                   1

Total                     300                   2

My question is : How can I make sure when somebody browses ProductQuantity against Product he sees correct output

Product ProductQuantity   FactCount

-----------------------------------

A1Cycle              100              7

B1Cycle               200              7

Total                   300              14

I tried writing a scope statement

SCOPE ( Measures.ProductQuantity);

this = IIF(Product.Product.currentmember IS Product.Product.levels(0).item(0),

Measures.ProductQuantity,

Measures.ProductQuantity / Measures.FactCount);

END SCOPE;

After doing this I start getting correct output on ROWS at least (since it divides 700/700 = 100 and 1400/700=200, so right output).

However Issue arises with Total/ALL member. It still shows 2100 (and not 300).

Product ProductQuantity   FactCount

-----------------------------------

A1Cycle              100              7

B1Cycle               200              7

Total                   2100              14

If i change my scope statement to

SCOPE ( Measures.ProductQuantity);

this = IIF(Product.Product.currentmember IS Product.Product.levels(0).item(0),

Measures.ProductQuantity / Measures.FactCount,

Measures.ProductQuantity / Measures.FactCount);

END SCOPE;

The All member shows me a total of (700 + 1400)/(7 + 7 ) = 2100/14 = 150 (and not 300)

Product ProductQuantity   FactCount

-----------------------------------

A1Cycle              100              7

B1Cycle               200              7

Total                   150              14

I think correct output will only come if it first divides and then aggregates ie (700/7 + 1400/7 ) = 100 + 200 = 300

Any suggestions will be highly appreciated.

Sunday, September 29, 2013 10:07 PM

### All replies

• Hi

I think, this is a best case of Grain issue and it is even simpler to handle at ETL Load than at Cube.

E.g:

1. Using Row_Number function on Product i.e. A1Cycle, you can get 1,2,3 etc

2. Using CASE statement Set the ProductQuantity on Row_Number 1 and make 0

3. In SSAS, this will solve your issues automatically

RowNumber  Product      ProductQuantity    ProductCompnent     ProductComponentPrice

1          A1Cycle      100                Tyre                10
2          A1Cycle      100                Tube                5

RowNumber  Product      ProductQuantity    ProductCompnent     ProductComponentPrice

1          A1Cycle      100                Tyre                10
2          A1Cycle      0               Tube                   5

Prav

Sunday, September 29, 2013 11:00 PM
• I'm not sure that will give the desired results, since when the user filters on  Tube they will get 0 for the Product quantity.  I would recommend two Fact tables in the data source view:

Product ProductCompnent ProductComponentPrice
A1Cycle Tyre   10
A1Cycle Tube   5
A1Cycle Seat   20
A1Cycle Chain   30
A1Cycle Handle   10
A1Cycle Bell   50
A1Cycle Break   10
B1Cycle Tyre   10
B1Cycle Tube   5
B1Cycle Seat   20
B1Cycle Chain   30
B1Cycle Handle   10
B1Cycle Bell   50
B1Cycle Break   10

Product ProductQuantity
A1Cycle 100
B1Cycle 200

Based on your example I wonder if the Product Component isn't actually an attribute of the Product, rather than something defined in the Fact tables.  If that is the case I would normalize the data further like this:

DIMENSION TABLE
Product ProductCompnent ProductComponentCode
A1Cycle Tyre   A1_Tyre
A1Cycle Tube   A1_Tube
A1Cycle Seat   A1_Seat
A1Cycle Chain   A1_Chain
A1Cycle Handle   A1_Handle
A1Cycle Bell   A1_Bell
A1Cycle Break   A1_Break
B1Cycle Tyre   B1_Tyre
...
B1Cycle Break   B1_Break

TWO FACT TABLES
ProductCompnent ProductComponentPrice
Tyre   10
Tube   5
Seat   20
Chain   30
Handle   10
Bell   50
Break   10

Product ProductQuantity
A1Cycle 100
B1Cycle 200

Martina White

Monday, September 30, 2013 12:12 AM