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

# AWM Calculated Measure for Percent of Total Across Multiple Dimensions?

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?
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?
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)
--------------------

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)
--------------------

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)
--------------------
• ###### 3. Re: AWM Calculated Measure for Percent of Total Across Multiple Dimensions?
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?
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(&apos;QUAL(SALESCUBE_QTY, CUSTOMER limit(CUSTOMER to CUSTOMER_LEVELREL &apos;&apos;TCUST&apos;&apos;), TIME limit(limit(TIME to ANCESTORS USING TIME_PARENTREL TIME(TIME TIME)) KEEP TIME_LEVELREL eq &apos;&apos;YR&apos;&apos;))&apos;, 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)
-------------------------
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)
-------------------------
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)
-------------------------
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?
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?
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