This discussion is archived
6 Replies Latest reply: Feb 14, 2013 10:31 AM by Nick Clinite RSS

AWM Calculated Measure for Percent of Total Across Multiple Dimensions?

Nick Clinite Newbie
Currently Being Moderated
I noticed that AWM has a Share function that gives me a percent total of a grain from a specific hierarchy's Top of Hierarchy, but is there any way to do that with multiple dimensions? For example, if I had a Share of Dimension X = 55% Where Time = 1/1/2013 and a Share of Dimension Y = 32% Where Time = 1/1/2013, then could I have a Share of both Dimension X and Dimension Y = 16% Where Time = 1/1/2013?
  • 1. Re: AWM Calculated Measure for Percent of Total Across Multiple Dimensions?
    Nasar Journeyer
    Currently Being Moderated
    Can you come up with an example, with couple of rows of data ?
  • 2. Re: AWM Calculated Measure for Percent of Total Across Multiple Dimensions?
    Nick Clinite Newbie
    Currently Being Moderated
    Sure thing!

    Let's say I have a Sales Fact Table with a grain of 1 product per sale with a Quantity measure, a Product dimension with a default hierarchy of All Products -> Product Type -> Product, and a Store dimension with a default hierarchy of All Stores -> Region -> County -> City -> Store.

    Now let's say I have a product that I wanted to find out what percentage of sales were made up by this single product. I could easily do this by using the Share Calculated Measure on the Quantity measure and the Product dimension, and then drill down to individual product.

    Now I want to find the percentage of sales of all products from a specific Store. Just as before, I would use the Share Calculated Measure on the Quantity measure and the Store dimension, and then drill down to the specific store.

    But what if you wanted to find out the percentage of sales of a specific product at a specific store (out of all sales from all stores)?

    Here is how the data would look via SQL:

    SELECT SUM(quantity)
    FROM sale_fact;

    SUM(quantity)
    --------------------
    2875

    SELECT SUM(quantity)
    FROM sale_fact, product_dim
    WHERE sale_fact.product_key = product_dim.product_key
    AND product_id = 'CX-867054';

    SUM(quantity)
    --------------------
    345 [12% of all products sold]

    SELECT SUM(quantity)
    FROM sale_fact, store_dim
    WHERE sale_fact.store_key = store_dim.store_key
    AND store_id = 'NY_ALBA_013';

    SUM(quantity)
    --------------------
    977 [34% of all products sold]

    SELECT SUM(quantity)
    FROM sale_fact, store_dim, product_dim
    WHERE sale_fact.store_key = store_dim.store_key
    AND sale_fact.product_key = product_dim.product_key
    AND store_id = 'NY_ALBA_013'
    AND product_id = 'CX-867054';

    SUM(quantity)
    --------------------
    88 [3.06% of all products sold]
  • 3. Re: AWM Calculated Measure for Percent of Total Across Multiple Dimensions?
    Nasar Journeyer
    Currently Being Moderated
    You can try to create a calculated measure with this OLAP Expression Syntax:

    SHARE(SALES.QUANTITY OF HIERARCHY PRODUCT.PRODUCTHIER TOP OF HIERARCHY STORE.STOREHIER TOP)

    Then pick any leaf level PRODUCT and STORE and see if you get the correct number for this measure.

    This assumes that your cube name is "SALES" and stored measure is "QUANTITY"
    Dimension names are PRODUCT and STORE, with hierarachy ids PRODUCTHIER and STOREHIER
    And there is one top node in each hierarchy.

    If this does not work, then post again. Hopefully someone else will provide another solution to your problem.

    .
  • 4. Re: AWM Calculated Measure for Percent of Total Across Multiple Dimensions?
    Shankar S. Journeyer
    Currently Being Moderated
    I had used an alternate solution which is a bit more cumbersome using native OLAP_DML formulae/expression.
    The above expression using OLAP Expression Syntax is much better (if it works).
    I was not aware of this OLAP expression syntax when i needed to create a kpi for similar requirement.

    We had Qty measure and Share along dimension like "Qty - Share at Year level", "Qty - Share of Total Customer".
    We needed to get "Qty - Share at Total Customer, Year".

    Cube= SALESCUBE
    Base Meas = QTY

    Step 1) Create Measure which represents "Qty - Total Customer, Year" which will work in any reporting context ...
    Note: For time=day1/2/3/ within same year and customer=cust1/2/3/... or Total Customer, the expression result will be constant (result fixed for any dimension members/status along the 2 dimensions - TIME and CUST). Denominator Value changes each year since we have constrainted time dimension to the ancestor of current cell at YR level. If we choose anscestor at TOP level TOTTIME say then the value is fixed for all time dimension members/values.

    olap dml expression: QUAL(SALESCUBE_QTY, CUSTOMER limit(CUSTOMER to CUSTOMER_LEVELREL 'TCUST'), TIME limit(limit(TIME to ANCESTORS USING TIME_PARENTREL TIME(TIME TIME)) KEEP TIME_LEVELREL eq 'YR'))

    in awxml - this becomes:
    ETMeasureColumnName="QTY_TCUST_YR"
    Name="QTY_TCUST_YR"
    MeasureExpression="OLAP_DML_EXPRESSION('QUAL(SALESCUBE_QTY, CUSTOMER limit(CUSTOMER to CUSTOMER_LEVELREL ''TCUST''), TIME limit(limit(TIME to ANCESTORS USING TIME_PARENTREL TIME(TIME TIME)) KEEP TIME_LEVELREL eq ''YR''))', NUMBER)">
    <Classification
    Value="AwmDescriptionType=OLAP_DML_CALC"/>
    ...
    <Description
    Type="LongDescription"
    Language="AMERICAN"
    Value="Qty - Total Customer, Year">


    Step 2) Define the share measure explicitly since we have already calculated the denominator needed for composite share.
    Check for division by 0 error before performing the share calculation explicitly as Numerator= Qty (for current cell/reporting context) and Denominator = Qty - Total Customer, Year (from Step 1)

    olap dml expression: if SALESCUBE_QTY_TCUST_YR ne 0 then SALESCUBE_QTY / SALESCUBE_QTY_TCUST_YR else na

    in awxml - this becomes:
    ETMeasureColumnName="QTY_SHARE_TCUST_YR"
    Name="QTY_SHARE_TCUST_YR"
    MeasureExpression="OLAP_DML_EXPRESSION(&apos;if SALESCUBE_QTY_TCUST_YR ne 0 then SALESCUBE_QTY / SALESCUBE_QTY_TCUST_YR else na&apos;, NUMBER)">
    <Classification
    Value="AwmDescriptionType=OLAP_DML_CALC"/>
    ...
    <Description
    Type="LongDescription"
    Language="AMERICAN"
    Value="Qty - Share of Total Customer, Year">
    </Description>

    Report needs to use measure QTY_SHARE_TCUST_YR (Qty - Share of Total Customer, Year) defined in Step 2. It may be useful to expose/display intermediate measure QTY_TCUST_YR also so as to make the basis of calculation very clear to the user.

    ***********
    Nick,

    If you customize above soln. to use Total Time, Total Prod and Total Organization (along 3 dimensions)... I am sure the fact table has a time dimension which should also be factored in in your calc/defn.
    If you have missed out the join to Time dimension from fact in your queries, in olap reporting terms, its similar to Time Dimension selection of Time level = TTIME Total Time (1 node at TOP).


    You need:
    Step 1) Qty - Total Time, Total Product and Total Org
    Step 2) Qty - Share of Total Time, Total Product and Total Org


    Then the example you gave should be covered via QTY and QTY_SHARE_TTIME_TPROD_TORG (Step 2)
    SUM(quantity)
    -------------------------
    2875
    should be QTY, QTY_SHARE_TTIME_TPROD_TORG (=100%) at Time level = TTIME, Product level = TPROD, Org level = TORG


    SUM(quantity)
    -------------------------
    345 [12% of all products sold]
    should be QTY, QTY_SHARE_TTIME_TPROD_TORG (=12% hopefully) at Time level = TTIME, Product level = PROD with report filter on product = 'CX-867054', Org level = TORG


    SUM(quantity)
    -------------------------
    977 [34% of all products sold]
    should be QTY, QTY_SHARE_TTIME_TPROD_TORG (=34% hopefully) at Time level = TTIME, Product level = TPROD, Org level = STORE with report filter on store = 'NY_ALBA_013'


    SUM(quantity)
    -------------------------
    88 [3.06% of all products sold]
    should be QTY, QTY_SHARE_TTIME_TPROD_TORG (=3.06% hopefully) at Time level = TTIME, Product level = PROD with report filter on product = 'CX-867054', Org level = STORE with report filter on store = 'NY_ALBA_013'



    HTH
    Shankar
  • 5. Re: AWM Calculated Measure for Percent of Total Across Multiple Dimensions?
    Shankar S. Journeyer
    Currently Being Moderated
    TIME --> PROD --> ORG --> Qty --> Qty - Prod Share --> Qty - Org Share --> Qty - Prod Org Share --> Qty - Prod Org Time Share
    Day1 --> prod1 --> store1 --> 10 --> 22% --> 33% --> 8% --> 2%
    Day1 --> prod2 --> store1 --> 15 --> 33% --> 38% --> 13% --> 4%
    Day1 --> prod3 --> store1 --> 20 --> 44% --> 40% --> 17% --> 5%
    Day1 --> prod1 --> store2 --> 20 --> 27% --> 67% --> 17% --> 5%
    Day1 --> prod2 --> store2 --> 25 --> 33% --> 63% --> 21% --> 6%
    Day1 --> prod3 --> store2 --> 30 --> 40% --> 60% --> 25% --> 7%
    Day2 --> prod1 --> store1 --> 40 --> 30% --> 44% --> 13% --> 10%
    Day2 --> prod2 --> store1 --> 45 --> 33% --> 45% --> 15% --> 11%
    Day2 --> prod3 --> store1 --> 50 --> 37% --> 45% --> 17% --> 12%
    Day2 --> prod1 --> store2 --> 50 --> 30% --> 56% --> 17% --> 12%
    Day2 --> prod2 --> store2 --> 55 --> 33% --> 55% --> 18% --> 13%
    Day2 --> prod3 --> store2 --> 60 --> 36% --> 55% --> 20% --> 14%
  • 6. Re: AWM Calculated Measure for Percent of Total Across Multiple Dimensions?
    Nick Clinite Newbie
    Currently Being Moderated
    Thanks for the assistance.

    Using SHARE(SALES.QUANTITY OF HIERARCHY PRODUCT.PRODUCTHIER TOP OF HIERARCHY STORE.STOREHIER TOP) appears to give me some funny results, with a leaf from Dimension 1 returning 0.02, yet a leaf from both Dimension 1 and 2 returns 0.05, essentially saying that 2% of the Quantity contains 5% of the Quantity.

    Nasar wrote:
    You can try to create a calculated measure with this OLAP Expression Syntax:

    SHARE(SALES.QUANTITY OF HIERARCHY PRODUCT.PRODUCTHIER TOP OF HIERARCHY STORE.STOREHIER TOP)

    Then pick any leaf level PRODUCT and STORE and see if you get the correct number for this measure.

    This assumes that your cube name is "SALES" and stored measure is "QUANTITY"
    Dimension names are PRODUCT and STORE, with hierarachy ids PRODUCTHIER and STOREHIER
    And there is one top node in each hierarchy.

    If this does not work, then post again. Hopefully someone else will provide another solution to your problem.

    .

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points