7 Replies Latest reply: Sep 29, 2011 6:39 AM by CBessios RSS

    CUBE_DFLT_PARTITION_LEVEL in 11g?

    713387
      We had a Oracle provided variable CUBE_DFLT_PARTITION_LEVEL as belows in 10g which seems to be missing in 11g. Any advise as to what the corresponding variable is in 11g . We use the same to clear individual partiitions. Please advise.

      in 10g:

      CUBE_DFLT_PARTITION_LEVEL

      DEFINE CUBE_DFLT_PARTITION_LEVEL RELATION ALL_LEVELS <ALL_CUBES>
        • 1. Re: CUBE_DFLT_PARTITION_LEVEL in 11g?
          DavidGreenfield
          You can clear an individual partition in release 11.2.0.2 using DBMS_CUBE.BUILD. (Sadly this method doesn't work in 11.2.0.1 or 11.1.) The PL/SQL below will clear the CY2007 partition of SALES_CUBE, which is partitioned by the CALENDAR_YEAR level of the CALENDAR hierarchy in TIME. You can adjust the syntax to fit your own case.
          BEGIN 
            DBMS_CUBE.BUILD(q'!
             "SALES_CUBE" USING
             (
               FOR "TIME" 
               WHERE "TIME"."DIM_KEY" IS DESCENDANT OF 'CY2007' WITHIN "TIME"."CALENDAR"
               BUILD(CLEAR)
             )!'
            ); 
          END;
          /
          You can modify this example to manipulate a partition in other ways. For example, if you want to load and aggregate CY2008 you can say the following.
          BEGIN 
            DBMS_CUBE.BUILD(q'!
             "SALES_CUBE" USING
             (
               FOR "TIME" 
               WHERE "TIME"."DIM_KEY" IS DESCENDANT OF 'CY2008' WITHIN "TIME"."CALENDAR"
               BUILD(LOAD, SOLVE)
             )!'
            ); 
          END;
          /
          If you cannot move to 11.2.0.2 (and I would recommend that you do), then you will need to write OLAP DML to clear the variables yourself.
          • 2. Re: CUBE_DFLT_PARTITION_LEVEL in 11g?
            Shankar S.-Oracle
            Nice feature.

            David,
            Can you please explain the syntax used as argument to DBMS_CUBE.BUILD?
            Is it some way to provide awxml instructions via clear text?
            Is passing input via q!'<...>!' a short form of providing xml based pseudo-code?

            Bit confused as we can pass the pseudo-code/instructions as clear text also as below:
            DBMS_CUBE.BUILD('GLOBAL.PRODUCT USING (CLEAR) GLOBAL.TIME USING (CLEAR) ...')

            Thanks
            Shankar
            • 3. Re: CUBE_DFLT_PARTITION_LEVEL in 11g?
              DavidGreenfield
              The q'!...!' in my example is just a standard (and apparently little known) Oracle SQL notation for string literals. It has nothing to do with XML or OLAP. I used it here because the build script contains single quotes (around the value CY2007, for example). See the SQL Reference section on "String Literals". e.g. http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/fundamentals.htm#i38404 for details. Here is a quote from the documentation

              >
              Doubling the quotation marks within a complicated literal, particularly one that represents a SQL statement, can be tricky. You can also use the following notation to define your own delimiter characters for the literal. You choose a character that is not present in the string, and then do not need to escape other single quotation marks inside the literal:

              -- q'!...!' notation allows the of use single quotes
              -- inside the literal
              string_var := q'!I'm a string, you're a string.!';

              You can use delimiters [, {, <, and (, pair them with ], }, >, and ), pass a string literal representing a SQL statement to a subprogram, without doubling the quotation marks around 'INVALID' as follows:

              func_call(q'[select index_name from user_indexes where status =
                         'INVALID']');
              >

              You can certainly do the same thing without the q notation as long as you escape the single quotes.
              BEGIN DBMS_CUBE.BUILD('
                 "SALES_CUBE" USING
                 (
                   FOR "TIME" 
                   WHERE "TIME"."DIM_KEY" IS DESCENDANT OF ''CY2007'' WITHIN "TIME"."CALENDAR"
                   BUILD(CLEAR)
                 )' ); 
              END;
              /
              • 4. Re: CUBE_DFLT_PARTITION_LEVEL in 11g?
                842519
                Sorry wrong post

                Edited by: 839516 on 07-Jun-2011 14:56
                • 5. Re: CUBE_DFLT_PARTITION_LEVEL in 11g?
                  CBessios
                  David,

                  the syntax:

                  WHERE "TIME"."DIM_KEY" IS DESCENDANT OF 'CY2007' WITHIN "TIME"."CALENDAR"

                  and especially the "IS DESCENDANT OF ... WITHIN (HIER)"

                  where is documented? Is it OLAP expression syntax or OLAP DML?

                  Thank you very much.

                  Christos
                  • 6. Re: CUBE_DFLT_PARTITION_LEVEL in 11g?
                    DavidGreenfield
                    It should be documented in the "Oracle OLAP Expression Syntax Reference", but I see that it was missed. For reference, here is a formal syntax declaration.
                    <relationship_condition>::=
                      <expression> IS [NOT] <relationship> [OR SELF] OF <expression> WITHIN <hierarchy>
                    
                    <relationship>::= 
                      PARENT | CHILD | ANCESTOR | ROOT_ANCESTOR | DESCENDANT | LEAF_DESCENDANT | RELATIVE
                    Most of this should be self evident. 'RELATIVE' means any related node in the hierarchy, either ancestor or descendant.
                    • 7. Re: CUBE_DFLT_PARTITION_LEVEL in 11g?
                      CBessios
                      That's why I couldn't find it in the docs. Thank you very much David, very helpful.

                      Christos