none
SSAS Custom additive behavior

    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.

    Thanks in advance




    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