This discussion is archived
2 Replies Latest reply: Oct 8, 2012 4:13 AM by Joan Puig RSS

Changing cost-based precompute % programatically

Joan Puig Newbie
Currently Being Moderated
Hi Experts,

I'm trying to do some tests on how the OLAP cubes scale in size. I have a fact table with 7 days of data ( 20.87M rows) and two dimensions: time and location.

My plan is to get statistics from 7 and 14 days and 2 different aggregation levels by:

1.- Build the cube with precompute 20% (on both top and bottom)
2.- Check LOB sizes both in USER_SEGMENTS and the AW$XXX_AW table
3.- Change precompute to 40%
4.- Cube Complete refresh
5.- (2)
6.- Add 7 more days of data to the fact table
7.- (4)
8.- (2)
9.- Change precompute to 20%
11.- (4)
12.- (2)

The main problem is that this process will take a very long time, so I'd like to leave it running overnight (and not having to change the aggregation level manually using the AWM). To do this I need a way to change the cost-based aggregation precompute level within a SQL or PL/SQL script. Is this possible?

Thank you,

Joan
  • 1. Re: Changing cost-based precompute % programatically
    Nasar Journeyer
    Currently Being Moderated
    Joan,

    For all these kind of things, just save your AW to an xml file and then take the appropriate tags and run the DBMS_CUBE.IMPORT_XML command.

    I did that on my AW and I was able to come up with the following code, which worked fine.

    begin
    dbms_cube.import_xml( to_clob( q'!
    *<Metadata>*
    *<Cube Name="BNSGL_ACTV">*
    *<Organization>*
    *<AWCubeOrganization*
    PrecomputePercent="35"
    PrecomputePercentTop="0"
    PartitionLevel="TIME.TIMEHIER.FISCAL_PERIOD"
    AW="BNSOLAP">
    *</AWCubeOrganization>*
    </Organization>     
    *</Cube>*
    *</Metadata> !'));*
    end;


    In some cases, you may have to provide the Version and Minimum database version with the <Metadata> tag, which you can also get from your xml file.
    For example:
    *<Metadata Version="1.3" MinimumDatabaseVersion="11.2.0.2">*

    .

    Edited by: Nasar Ali-Khan on Oct 5, 2012 7:43 AM
  • 2. Re: Changing cost-based precompute % programatically
    Joan Puig Newbie
    Currently Being Moderated
    Thank you, Nassar. It worked like a charm.

    For any skeptical people reading this: dbms_cube.import_xml only modifies the tags you include. The rest of the Analytic Workspace remains untouched.

    Joan

Legend

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