This discussion is archived
4 Replies Latest reply: Sep 24, 2013 10:43 PM by Joern RSS

Cube-Maintenance for only some partitions

Joern Newbie
Currently Being Moderated

Hello,

I have a cube which covers sales data from a few years. Most of this data is static, but some are not.

The cube has a time dimension (Levels Year,Quarter,Month and Day) and is partitioned by Year.

 

So lets say for example the cubes has data from the years 2010 until today, the data for years 2010 and 2011 is static, but for the years 2012 and 2013 insert, updates and deletes (!!!) are possible in the basic fact table. Until know I first clear all leaves and aggregates and the load and aggregate all the data again. Is it possible in some way only to build the years 2012 and 2013 and so having a faster over all build time?

 

I use AWM 11.2.0.3B with a 11.2.0.3 database.

  • 1. Re: Cube-Maintenance for only some partitions
    Nasar Journeyer
    Currently Being Moderated

    You can have only latest 2012, 2013 records in your source table/view (that is mapped to CUBE) and then do a cube load

    DBMS_CUBE.BUILD  ....with      METHOD = 'S'

     

    See old posts on this topic.

     

    https://forums.oracle.com/thread/2154852

         

    https://forums.oracle.com/thread/2493029?tstart=0

     

    Documentation for DBMS_CUBE.BUILD is at:

    http://docs.oracle.com/cd/E11882_01/appdev.112/e16760/d_cube.htm#CHDFCJBF

     

     

     

    Another way is to provide the WHERE condition in the DBMS_CUBE.BUILD call.

    The following script will load the three cubes for the TIME periods provided, and only aggregate data for these three periods also. It will not touch (or  re-aggregate) data in other periods.             

     



    DECLARE

      jobid VARCHAR2(100);

    BEGIN

      SELECT DBMS_SCHEDULER.GENERATE_JOB_NAME('OLAP_CUBE_JOB$_') INTO jobid FROM DUAL;

      DBMS_SCHEDULER.CREATE_JOB(

        jobid, 'plsql_block',

        'begin

          dbms_cube_log.enable(DBMS_CUBE_LOG.TYPE_OPERATIONS,DBMS_CUBE_LOG.TARGET_TABLE,DBMS_CUBE_LOG.LEVEL_LOW);

          dbms_cube_log.enable(DBMS_CUBE_LOG.TYPE_REJECTED_RECORDS,DBMS_CUBE_LOG.TARGET_TABLE,DBMS_CUBE_LOG.LEVEL_LOW);

          dbms_cube_log.set_parameter(DBMS_CUBE_LOG.TYPE_REJECTED_RECORDS,1,1000);

          dbms_cube.build(

           ''BAWOLAP1.BNSGL_BAL_CB   USING (FOR "TIME"  WHERE "TIME"."DIM_KEY" IN (''''Period 3 - 2013-01-01'''', ''''Period 1 - 2012-11-01'''', ''''Period 2 - 2012-12-01'''') BUILD  (LOAD, SOLVE)),

             BAWOLAP1.BNSGL_BAL_MISC USING (FOR "TIME"  WHERE "TIME"."DIM_KEY" IN (''''Period 3 - 2013-01-01'''', ''''Period 1 - 2012-11-01'''', ''''Period 2 - 2012-12-01'''') BUILD (LOAD, SOLVE)),

             BAWOLAP1.BNSGL_BAL_SC   USING (FOR "TIME"  WHERE "TIME"."DIM_KEY" IN (''''Period 3 - 2013-01-01'''', ''''Period 1 - 2012-11-01'''', ''''Period 2 - 2012-12-01'''') BUILD (LOAD, SOLVE)) '',

           ''SSSSSSS'',

           atomic_refresh=>true,

           add_dimensions=>true,

           refresh_after_errors=>true,

           parallelism=>8);

         end;',

      0, null, null, null, 'DEFAULT_JOB_CLASS', true, true, 'OLAP CUBE Job');

    END;

  • 2. Re: Cube-Maintenance for only some partitions
    Joern Newbie
    Currently Being Moderated

    I know these documents and have tried many things. The problem is that I must first clear the non static partitions. What happened is the following:

     

    1. I build the whole cube with partitions 2011,2012 and 2013.

    2 Then I map a view to the cube that covers only the partitions 2012 and 2013.

    3 Now I do a "execute DBMS_CUBE.BUILD('CUBE_EXAMPLE USING (CLEAR VALUES, LOAD NO SYNCH, SOLVE PARALLEL)','S',false,16,false,true,false);"

     

    The result is that all partitions are cleared and 2012 and 2013 are rebuilded. But I don't want to clear 2011.

     

     

    (When I work with the "where" condition, the whole build takes far too long to complete. It is much faster to completly rebuild the cube)

  • 3. Re: Cube-Maintenance for only some partitions
    Nasar Journeyer
    Currently Being Moderated

    You can try a separate DBMS_CUBE.BUILD call with CLEAR only.  Use WHERE condition to clear data for 2012 and 2013 partitions.

     

    Then in the second DBMS_CUBE.BUILD call,  just load/aggregate 2012,2013 data without any CLEAR.

  • 4. Re: Cube-Maintenance for only some partitions
    Joern Newbie
    Currently Being Moderated

    Thnaks very much for your answer Nasar. But has I wrote in my previous post the "where" condition is extremly slow. In that case it would be better to completly rebuidl the whole cube.

     

    But I have now found the solution.

     

    1. I build the whole cube

    2. I map a view to the cube that covers only the years I want to load.

    3. Before each load I drop the partitions (in my example 2012 and 2013) of the cube with this CUBE-DML command:

         CLEAR ALL FROM <CUBE>(partition <PARTITION>)

    4. For the load I use this command

         DBMS_CUBE.BUILD('CUBE_EXAMPLE USING (LOAD NO SYNCH, SOLVE PARALLEL)','S',false,16,false,true,false);"

     

    This is very fast and works fine.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points