2 Replies Latest reply on Oct 8, 2012 11:13 AM by Joan Puig-Oracle

    Changing cost-based precompute % programatically

    Joan Puig-Oracle
      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.ali-khan at -Oracle

          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-Oracle
            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.