6 Replies Latest reply: Feb 14, 2013 12:31 PM by Nick Clinite RSS

    AWM Calculated Measure for Percent of Total Across Multiple Dimensions?

    Nick Clinite
      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-Oracle
          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
            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-Oracle
              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
                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.-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
                    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.

                    .