13 Replies Latest reply: Aug 29, 2012 11:49 AM by 938723 RSS

    Methods available for storing Custom Measures

    938723
      Just a general question.

      Using 11g. If I have a complex DML program, which calculates values using existing stored measures within the cube and would like to store the end results of the values calculated during the run of my program. What methods are available?

      Should my program be run as part of DBMS_CUBE package, should it be part of a maintenance script? I appreciate any info on this.
        • 1. Re: Methods available for storing Custom Measures
          DavidGreenfield
          You can copy data from a calculated measure to a stored measure using the DBMS_CUBE.BUILD procedure. A simple example would be the following
          exec dbms_cube.build('UNITS_CUBE USING (SET UNITS_CUBE.STORED_MEASURE = UNITS_CUBE.CALC_MEASURE'));
          You can limit the scope of the copy by using the FOR ... BUILD syntax. The syntax is documented in the latest revision of the 11.2 documentation: http://docs.oracle.com/cd/E11882_01/appdev.112/e23448/d_cube.htm#CHDJBHEH . You can also see an earlier blog post by Stuart Bunby: http://oracleolap.blogspot.co.uk/2010/10/cell-level-write-back-via-plsql.html .

          This method takes care of translating between logical objects (e.g. the measure name STORED_MEASURE within the cube named UNITS_CUBE) and underlying AW objects (e.g. the VARIABLE named UNITS_CUBE_STORED and any associated partition template). It will also perform the assignment in parallel if the cube is partitioned and you specify parallelism > 1. You could also just write to the variable directly using OLAP DML if you prefer.

          Whatever you decide to do, you must be careful about the looping for the assignment. If you get this wrong, then the assignment could take a very long time. There was an example in the forum a few months back: Re: using dbms_cube.build for write-back.
          • 2. Re: Methods available for storing Custom Measures
            938723
            Thank you David.

            Regarding looping and the example that you posted. If the value to update was unique for each day i.e. using your example, the allocation factor was different for each day or week (perhaps a list). How would you have handled this efficiently?
            • 3. Re: Methods available for storing Custom Measures
              DavidGreenfield
              The problem in the other forum post was that the loop was happening in PL/SQL, not the DBMS_CUBE.BUILD procedure. If the "allocation factor" varies, then it can be stored in a dimension attribute (or measure if it is multi-dimensional).

              A more subtle looping issue is whether the loop within the DBMS_CUBE.BUILD procedure is 'dense' or 'sparse'. Suppose, for example, that you have a 3-dimensional cube where each dimension has 100 members. This cube has 1,000,000 logical cells, but you may want to assign values to only 1000 of these cells. You need to make sure that you loop over the 1000 cells (sparse) and not over the 1,000,000 cells (dense). The SET method in DBMS_CUBE.BUILD will try to loop sparsely, but it depends on the complexity of the source expression/measure. If it is too complex, such as a call to an OLAP DML PROGRAM, then it will loop densely. You can control this in two ways:

              (1) By explicitly limiting the loop using the FOR ... BUILD syntax.

              (2) By setting the $LOOP_DENSE and $LOOP_VAR properties on the calculated measure. See the SET_PROPERTY OLAP DML command program for (somewhat limited) details.
              http://docs.oracle.com/cd/E11882_01/olap.112/e12197/dml_commands_2053.htm#CCHIEIFE

              You can use these in conjunction.
              • 4. Re: Methods available for storing Custom Measures
                938723
                There are basically two way to compute the aggregate.

                1. Using a custom SQL Aggregate function and using my existing Cube Views to generate the data from.
                2. Using a DML Program

                In both cases I will have data in the form

                Date, ComplicatedComputedValue

                Sorry if I keep asking the same thing. But if I need to update ever value over my Date Dimension based on the set of values say from 1,2 how would I accomplish this? I don't quite understand the FOR ... BUILD syntax.

                In the theme of the example you posted, allocation_factor would have to be an array/list where each entry was a Date and you would be updating the Cube in the matching Date cell. So yes the allocation factor here varies, but the allocation factor itself is a complicated calculation.

                Edited by: user7853353 on Aug 14, 2012 10:20 AM
                • 5. Re: Methods available for storing Custom Measures
                  938723
                  Can

                  exec dbms_cube.build('UNITS_CUBE USING (SET UNITS_CUBE.STORED_MEASURE = UNITS_CUBE.CALC_MEASURE'));

                  Be restricted to a specific level?
                  • 6. Re: Methods available for storing Custom Measures
                    wendress
                    It would probably be useful to understand what the calculation needs to do, but in general you can embed this as David describes or you can do it directly using OLAP DML. Working off David's example:

                    exec dbms_cube.build('UNITS_CUBE USING (SET UNITS_CUBE.STORED_MEASURE = UNITS_CUBE.CALC_MEASURE'));

                    The direct OLAP DML method would look something like this:

                    SET units_cube_store_measure = units_cube_calc_measure

                    Or, let's say I want to assign data to sales_cube.sales with the expression sales_cubes.sales = sales_cube.quantity_sold * price_cube.unit_price. This might like like:

                    SET sales_cube_sales = sales_cube_quantity_sold * price_cube_unit_price

                    Note that these expressions refer to the OLAP DML objects (that is, the physical objects) rather than the API level objects.

                    If you want to constrain the values being set, use the LIMIT command. E.g.,

                    LIMIT time TO time_levelrel 'MONTH'
                    LIMIT product TO product_levelrel 'ITEM'
                    LIMIT geography TO geography_levelrel 'CUSTOMER'
                    SET sales_cube_sales = sales_cube_quantity_sold * price_cube_unit_price

                    David also notes that you need to pay attention to looping. The above example will "loop dense", that is loop over the base dimensions. Since data is usually sparse, that's not going to be as efficient as it can be. So, introduce the ACROSS command and loop over the composite dimension of the sales_cube. Assuming that the cube is partitioned (almost all cubes should be partitioned), the program now looks like this:

                    LIMIT time TO time_levelrel 'MONTH'
                    LIMIT product TO product_levelrel 'ITEM'
                    LIMIT geography TO geography_levelrel 'CUSTOMER'
                    SET sales_cube_sales = sales_cube_quantity_sold * price_cube_unit_price ACROSS sales_cube_prt_template

                    The ACROSS command will cause the SET command to loop only values in the sales_cube where data exists (for any store measure).

                    Finally, don't forget to save your work with UPDATE and COMMIT commands. E.g.,

                    LIMIT time TO time_levelrel 'MONTH'
                    LIMIT product TO product_levelrel 'ITEM'
                    LIMIT geography TO geography_levelrel 'CUSTOMER'
                    SET sales_cube_sales = sales_cube_quantity_sold * price_cube_unit_price ACROSS sales_cube_prt_template
                    UPDATE
                    COMMIT

                    David also mentions that dbms_cube.build will run jobs in parallel (assuming the cube is partitioned). That's really nice. You can parallelize this in OLAP DML. See http://oracleolap.blogspot.com/2010/03/parallel-execution-of-olap-dml.html for an example.

                    So, which method should you use? It depends on what you are trying to do. If it's easy to do with dbms_cube.build, I would probably do that. But, if you need more power and/or control, the OLAP DML method might be best.
                    • 7. Re: Methods available for storing Custom Measures
                      938723
                      Thank you for all the responses. Would it be possible to provide an example of using $LOOP_DENSE and $LOOP_VAR?
                      • 8. Re: Methods available for storing Custom Measures
                        Nasar-Oracle
                        http://docs.oracle.com/cd/E11882_01/olap.112/e17122/dml_properties013.htm

                        http://docs.oracle.com/cd/E11882_01/olap.112/e17122/dml_properties014.htm
                        .
                        • 9. Re: Methods available for storing Custom Measures
                          938723
                          I've seen this. But when trying to apply I received lockfn and other errors. Could someone post a simple example?
                          • 10. Re: Methods available for storing Custom Measures
                            DavidGreenfield
                            You will get a lockdfn error if you use CONSIDER on a locked object. This is why the SET_PROPERTY program was added.
                            CALL SET_PROPERTY(property_name, logical_cube, meas_name, property_value)
                            E.g.
                            CALL SET_PROPERTY('$LOOP_VAR', 'UNITS_CUBE', 'MY_CALC_MEASURE', 'UNITS_CUBE_STORED')
                            • 11. Re: Methods available for storing Custom Measures
                              938723
                              David, this is very impressive. The query performance went up by nearly 30 folds. Thank you!

                              Can you explain the difference between LOOP_DENSE and LOOP_VAR in the sense of when LOOP_VAR should be used and LOOP_DENSE should not.

                              Based on documentation:

                              The $LOOP_VAR property specifies that when a OLAP_TABLE SQL function with the LOOP OPTIMIZED clause is executed, the formula on which it is assigned is looped in the same manner as the variable or QDR specified in the property.

                              The $LOOP_DENSE property is used to determine how to loop the formula on which it is assigned when a OLAP_TABLE SQL function with the LOOP OPTIMIZED clause is executed. It specifies that Oracle OLAP loops densely over the formula (that is, that it loop over every tuple of the formula—even those member cells that do not have values).

                              This is definitely a feature worth discussing on the OLAP blog.
                              • 12. Re: Methods available for storing Custom Measures
                                DavidGreenfield
                                $LOOP_VAR is the name of a VARIABLE that acts as the basis for the loop. As a simple example if your DML formula is
                                SALES * 100
                                then $LOOP_VAR should be 'SALES' because "SALES * 100" has a value if and only if SALES has a value.

                                $LOOP_DENSE allows you to specify that one or more of the dimensions of the $LOOP_VAR should be looped densely even if they are not dense in the variable itself. This is useful for time series calculations that can have values even when the base variable has none. The simplest example is a DML formula that means 'year to date of SALES'. (I won't give any actual OLAP DML for this because there are many formulations and it would only obscure the issue.) Now suppose your base variable, SALES, has a value for '1 Jan 2012' but for no other day in 2012. If you simply set $LOOP_VAR to be 'SALES' then 'year to date of SALES' would be NULL for all the other days in 2012. This is clear wrong from a business perspective. The solution is to also set '$LOOP_DENSE' to be 'TIME', where TIME is the time dimension. The formula will now return values for all days in 2012. You can think of this in SQL terms as a partitioned outer join between the $LOOP_VAR 'fact table' and the $LOOP_DENSE 'dimension table'.
                                SELECT ...
                                FROM 
                                  sales_fact 
                                    PARTITION BY (...other dims..) 
                                    RIGHT OUTER JOIN time_dim
                                    ON ...condition...
                                • 13. Re: Methods available for storing Custom Measures
                                  938723
                                  Thank you for the in depth explanation. This explains a lot.