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,

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

    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.

    dbms_cube.import_xml( to_clob( q'!
    *<Cube Name="BNSGL_ACTV">*
    *</Metadata> !'));*

    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="">*


    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.



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