1 2 Previous Next 20 Replies Latest reply: May 25, 2011 10:17 AM by DavidGreenfield RSS

    Count of Weeks where a certain measure is > 0

    772199
      Hi,
      I'm trying to create a calculated measure which would give me count of weeks where a certain measure say Sales is greater than 0. I have a hierarchy YEAR->QUARTER->MONTH->WEEK in time dimension DIMTIME. And I have used the expression COUNT(MYCUBE_ONHANDASSETS GT 0) to get the count. But this expression gets count of weeks from all the years instead of selected time periods. Lets say I have the measure MYCUBE_SALES greater than 0 at 2 weeks in Jan 2010, then the count should be 2 at level Jan 2010 in the hierarchy. But it shows 50 which is total weeks in year 2010 where MYCUBE_SALES is greater than 0.

      Can anyone help me solving the issue ?

      Thanks,
      RK
        • 1. Re: Count of Weeks where a certain measure is > 0
          Brijesh Gaur
          You can use it like this :

          shw statlen (lmt(lmt dimtime to MYCUBE_ONHANDASSETS gt 0) keep dimtime_levelrel eq 'WEEK') or
          shw statlen (lmt(lmt dimtime to dimtime_levelrel eq 'WEEK') keep MYCUBE_ONHANDASSETS gt 0)

          Thanks,
          Brijesh
          • 2. Re: Count of Weeks where a certain measure is > 0
            DavidGreenfield
            A standard way to crack this 'distinct count' problem is the following.

            (1) Define a new cube, COUNT_CUBE say, that has the same dimensionality as the base measure.

            (2) Define a numeric measure, SALES_COUNT say, in the new cube.

            (3) Map SALES_COUNT to a query that has the value "1" in any fact row for which sales is greater than zero and is null elsewhere. For example you can load it from a view defined as

            SELECT
            CASE WHEN sales > 0 THEN 1 ELSE NULL END sales_count,
            time,
            ... other dims
            FROM fact
            WHERE sales > 0

            The CASE expression is redundant in this example because of the WHERE clause, but would be important if you added a second measure, costs say, so that your condition becomes WHERE sales > 0 OR costs > 0. You could also load from OLAP DML if your base measure is calculated in the AW.

            (4) Define the aggregation rules COUNT_CUBE to be MAX over all non TIME dimensions in the cube and SUM over the TIME dimension. Note that SUM should be the final aggregation.

            Now load and aggregate your cube and you should be done.
            • 3. Re: Count of Weeks where a certain measure is > 0
              772199
              Hi David,
              Thank you for the reply. Unfortunately the fact which needs to be checked is a cube measure and is not in the fact table(for simplicity I mentioned it as Sales). The name of the measure is OnHandAssets and it is in a cube called MYDWCUBE_AVG with aggregation operator Average on Time dimension and Sum on Product dimension.

              I created a view on the MYDWCUBE_AVG cube view MYDWCUBE_AVG_VIEW defined as below

              SELECT ( CASE WHEN ONHANDASSETS > 0 THEN 1 ELSE 0 END) AS WEEKSCNT, DIMTIME AS TIMEKEY, SKU AS SKUKEY FROM MYDWCUBE_AVG_VIEW


              And created a new cube COUNT_CUBE with a measure based on the above view. But there is a problem in mapping the Dimensions since TIMEKEY and SKUKEY don't match the keys in time and product dimension tables. I think there is no way I can generate the actual Timekey from the cube view because the cube view has an entry for each and every member of time dimension hierarchy and same is the case with SkuKey



              --
              RK
              • 4. Re: Count of Weeks where a certain measure is > 0
                772199
                Hi Brijesh,
                Thank you for the reply. I could use the expression SHOW STATLEN (LIMIT(LIMIT(DIMTIME to DIMTIME_LEVELREL eq 'WEEK') KEEP MYCUBE_ONHANDASSETS gt 0)) successfully in OLAP Worksheet. I need to create a calculated measure which would give me the count of weeks. Can you tell how I can use the above command in a calculated measure in Analytic Workspace Manager ?

                --
                RK
                • 5. Re: Count of Weeks where a certain measure is > 0
                  DavidGreenfield
                  You should be able to copy the data from your MYDWCUBE_AVG cube to COUNT_CUBE using OLAP DML.

                  First limit the dimensions to the appropriate leaf values, then assign. The ACROSS clause should list the partition template (if MYDWCUBE_AVG is partitioned) or the composite if it is not. I am approximating below.
                  LIMIT TIME TO TIME_LEVELREL 'MONTH'
                  LIMIT PRODUCT TO PRODUCT_LEVELREL 'SKU'
                  ...other leaf level conditions
                  LIMIT COUNT_CUBE_MEASURE_DIM TO 'ONHANDASSETSCOUNT'
                  LIMIT MYDWCUBE_AVG_MEASURE_DIM TO 'ONHANDASSETS'
                  COUNT_CUBE_STORED = MYDWCUBE_AVG_STORED ACROSS MYDWCUBE_AVG_PRT_TEMPLATE
                  If you are in 11.2.0.2 then you can also use DBMS_CUBE to copy the data without needing to worry about the physical object details.
                  begin DBMS_CUBE.BUILD(q'! COUNT_CUBE USING 
                    (
                       FOR
                          "TIME" LEVELS ("TIME"."MONTH"),
                          "PRODUCT" LEVELS ("PRODUCT"."SKU"),
                           ... other leaf level conditions ...
                       BUILD 
                       (
                         SET COUNT_CUBE.OnHandAssetsCount = MYDWCUBE_AVG.OnHandAssets
                       )
                    )!'); 
                  end;
                  /
                  • 6. Re: Count of Weeks where a certain measure is > 0
                    772199
                    I could successfully build the Count cube DEMANTRADW_CNT using the script but there is no data to see in the measure. I didn't map the Measures and Dimensions in the Analytic Workspace Manager. I'm new to Oracle OLAP and the it's DML. Below is the exact script which I used

                    begin
                    DBMS_CUBE.BUILD(q'! DEMANTRADW_CNT USING
                    (
                    FOR
                    "DEMTIME" LEVELS ("DEMTIME"."WEEK"),
                    "SKU" LEVELS ("SKU"."MERCHGROUP")
                    BUILD
                    (
                    SET CUBE_DEV.DEMANTRADW_CNT.WEEKSCOUNT = DEMANTRADW_AVG.ONHANDASSETS
                    )
                    )!');
                    end;
                    • 7. Re: Count of Weeks where a certain measure is > 0
                      DavidGreenfield
                      To see if there is any data in a cube you can type the following into 'OLAP Worksheet' (from Tools menu in AWM).
                      show obj(numvals 'DEMANTRADW_CNT_STORED')
                      If it returns 0.0, then there is no data in your cube and we will need to find out why.

                      If it returns something non zero, then there are leaf values in your cube and you just need to aggregate them.
                      exec DBMS_CUBE.BUILD('DEMANTRADW_CNT USING (SOLVE)')
                      Note that you can combine load and aggregation as follows.
                      begin
                        DBMS_CUBE.BUILD(q'! DEMANTRADW_CNT USING
                        (
                          FOR
                           "DEMTIME" LEVELS ("DEMTIME"."WEEK"),
                           "SKU" LEVELS ("SKU"."MERCHGROUP")
                          BUILD
                          (
                            SET CUBE_DEV.DEMANTRADW_CNT.WEEKSCOUNT = DEMANTRADW_AVG.ONHANDASSETS
                          ),
                          SOLVE
                         )!');
                      end;
                      • 8. Re: Count of Weeks where a certain measure is > 0
                        772199
                        Hi David, thank you for the prompt reply. Now I can see data in the DEMANTRADW_CNT.WEEKSCOUNT. I'm looking for count of weeks where DEMANTRADW_AVG.ONHANDASSETS is > 0. The measure DEMANTRADW_CNT.WEEKSCOUNT shows very big values.

                        We are executing SET CUBE_DEV.DEMANTRADW_CNT.WEEKSCOUNT = DEMANTRADW_AVG.ONHANDASSETS . I think there is a problem here.
                        • 9. Re: Count of Weeks where a certain measure is > 0
                          DavidGreenfield
                          You are quite right. We should be setting count equal to 1 when the base measure is greater than zero. Something like

                          SET CUBE_DEV.DEMANTRADW_CNT.WEEKSCOUNT = CASE WHEN DEMANTRADW_AVG.ONHANDASSETS > 0 THEN 1 ELSE NULL END
                          • 10. Re: Count of Weeks where a certain measure is > 0
                            772199
                            I changed the script to
                            begin
                            DBMS_CUBE.BUILD(q'! DEMANTRADW_CNT USING
                            (
                            FOR
                            "DEMTIME" LEVELS ("DEMTIME"."WEEK"),
                            "SKU" LEVELS ("SKU"."MERCHGROUP")
                            BUILD
                            (
                            SET CUBE_DEV.DEMANTRADW_CNT.WEEKSCOUNT = (CASE WHEN DEMANTRADW_AVG.ONHANDASSETS > 0 THEN 1 ELSE 0 END)
                            )
                            )!');
                            end;

                            but it seems there is a syntax error in it. Below is the error message

                            Error report:
                            ORA-37162: OLAP error
                            XOQ-00703: error executing OLAP DML command "(DEMANTRADW_CNT_STORED=IF CUBE_DEV.DEMANTRADW!DEMANTRADW_AVG_ONHANDASSETS GT 0 THEN 1
                            ELSE 0
                            ACROSS CUBE_DEV.DEMANTRADW!DEMANTRADW_AVG_COMPOSITE : ORA-33142: The format of the ACROSS command is incorrect.
                            ORA-33774: ELSE is not a command.
                            ORA-35188: The word 1 must be followed by an expression.
                            )"
                            • 11. Re: Count of Weeks where a certain measure is > 0
                              DavidGreenfield
                              I was able to reproduce this, too. The problem is that there are new lines in the generated IF statement. I will enter a formal bug for this, but in the meantime it is a drag.

                              As a workaround you can define a calculated measure, ONHANDASSETS_CT say, in the DEMANTRADW_AVG cube with definition
                              CASE WHEN DEMANTRADW_AVG.ONHANDASSETS > 0 THEN 1 ELSE 0 END
                              To do this, select "OLAP Expression Syntax" from the "Calculation Type" pull down menu when you define the calculated measure. You can then assign the calculated measure.
                              DBMS_CUBE.BUILD(q'! DEMANTRADW_CNT USING
                              (
                              FOR
                              "DEMTIME" LEVELS ("DEMTIME"."WEEK"),
                              "SKU" LEVELS ("SKU"."MERCHGROUP")
                              BUILD
                              (
                              SET CUBE_DEV.DEMANTRADW_CNT.WEEKSCOUNT = DEMANTRADW_AVG.ONHANDASSETS_CT
                              )
                              )!');
                              end;
                              • 12. Re: Count of Weeks where a certain measure is > 0
                                772199
                                Hi David,
                                That solved the problem. Thank you very much for the continuous support. I want to learn OLAP DML. Can you suggest any material or book to get started ?

                                --
                                RK
                                • 13. Re: Count of Weeks where a certain measure is > 0
                                  DavidGreenfield
                                  I learned OLAP DML by just reading the official "Oracle OLAP DML Reference" guide, which is part of the standard Oracle documentation set. I know of two Oracle OLAP text books, but I haven't read either of them.

                                  <li> Oracle Essbase & Oracle OLAP: The Guide to Oracle's Multidimensional Solution (Osborne Oracle Press Series)

                                  The OLAP part of this was written by some long time OLAP DML experts, Dan Vlamis and Chris Claterbos, and is based on features in 11g.

                                  <li>The Multidimensional Data Modeling Toolkit: Making Your Business Intelligence Application Smart with Oracle OLAP

                                  This was written by another long time expert, John Paredes, but my understanding is that it is focused on Oracle 10g, so it may be less useful given all the changes that went into 11g. People who have read these books should feel free to disagree.
                                  • 14. Re: Count of Weeks where a certain measure is > 0
                                    772199
                                    I heard about John Paredes. I will first go through the Oracle documentation. Thank you for the support.

                                    --
                                    RK
                                    1 2 Previous Next