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
6.- Add 7 more days of data to the fact table
9.- Change precompute to 20%
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?
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'!
*<AWCubeOrganization* PrecomputePercent="35" PrecomputePercentTop="0" PartitionLevel="TIME.TIMEHIER.FISCAL_PERIOD" AW="BNSOLAP">
*</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.
*<Metadata Version="1.3" MinimumDatabaseVersion="184.108.40.206">*
Edited by: Nasar Ali-Khan on Oct 5, 2012 7:43 AM