This content has been marked as final.
Show 6 replies

1. Re: AWM Calculated Measure for Percent of Total Across Multiple Dimensions?
NasarOracle Feb 13, 2013 6:17 PM (in response to Nick Clinite)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 Feb 13, 2013 9:47 PM (in response to NasarOracle)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 = 'CX867054';
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 = 'CX867054';
SUM(quantity)

88 [3.06% of all products sold] 
3. Re: AWM Calculated Measure for Percent of Total Across Multiple Dimensions?
NasarOracle Feb 13, 2013 10:53 PM (in response to Nick Clinite)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.Oracle Feb 14, 2013 1:30 PM (in response to NasarOracle)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('if SALESCUBE_QTY_TCUST_YR ne 0 then SALESCUBE_QTY / SALESCUBE_QTY_TCUST_YR else na', 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)
should be QTY, QTY_SHARE_TTIME_TPROD_TORG (=100%) at Time level = TTIME, Product level = TPROD, Org level = TORG

2875
SUM(quantity)
should be QTY, QTY_SHARE_TTIME_TPROD_TORG (=12% hopefully) at Time level = TTIME, Product level = PROD with report filter on product = 'CX867054', Org level = TORG

345 [12% of all products sold]
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'

977 [34% of all products sold]
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 = 'CX867054', Org level = STORE with report filter on store = 'NY_ALBA_013'

88 [3.06% of all products sold]
HTH
Shankar 
5. Re: AWM Calculated Measure for Percent of Total Across Multiple Dimensions?
Shankar S.Oracle Feb 14, 2013 2:04 PM (in response to Shankar S.Oracle)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 Feb 14, 2013 6:31 PM (in response to NasarOracle)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.
.