2 Replies Latest reply: Oct 8, 2012 6:13 AM by Joan Puig RSS

    Changing cost-based precompute % programatically

    Joan Puig
      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-Oracle
          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
            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