Thursday, December 16, 2010

Fragmentation in ASO Cubes

It seems Everything old is new again. For years we have been defragmenting BSO cubes by exporting the data, clearing the cube and then re-importing the data. When ASO came along, I thought “Great since there are no blocks I won’t get fragmentation and don’t have to worry it any more” . Turns out I was wrong.
I was at a client and needed to make a copy of their 50 gig  production cube  onto their test system to do some work with it. I exported the data, copied the outline over to test and re-imported the data. I looked at the file system during the load to see how far along and was surprise when the cube stopped loading the .Dat file was only 9 gig. I ran the materialization of aggregations using a saved aggregation from query tracking and the cube grew to 25 gig. About half of the size of the production cube.
There are a couple of things to know.
  1. During the year, the client deletes some data out of the cube by deleting the members (old years)
  2. The cube contains no slices
  3. The cube gets an incremental update multiple times a month by clearing out the current Month/Year and reloading the data
  4. The cube has been in production for about a year and the outline gets new members added on a regular basis.
Since this worked so well on test, we took the original exports and cleared the production cube and its size is now comparable to the test cube (we actually added additional aggregations which makes it a bit larger).  OK lesson learned, the client will now be adding a process to periodically rebuild their ASO cube. I did not do a compress of the outline, but this is something else they can add to their process. Who would have thought!
at

8 comments:

  1. srxDecember 17, 2010 at 12:15 AM

    Glenn what about about the ESSCMDQ utility - is its use restricted to structure fragmentation or may it be used for data fagmentation as well?

    http://www.oracle.com/technetwork/middleware/bi-foundation/esscmdq-sampleapps-093105.html

    Login "svr" "admin" "password";
    Select "app" "db";
    Openotl "2" 1 "app" "db" "db" "y" "y" 0;
    Writeotl 1 "2" 1 "app" "db" "db";
    Restructotl 1;
    CloseOtl 1;
    Unlockobj 1 "app" "db" "db";
    LogOut;

    ReplyDelete
  2. GlennSDecember 17, 2010 at 12:51 AM

    I've not checked it for data restructure. I have used the utility for outline compaction. I wrote about it a few months ago

    ReplyDelete
  3. AnonymousJanuary 6, 2011 at 9:47 PM

    Hi Glenn:

    Thanks for the post!

    I am curious how does your client incrementally update multiple times a month by clearing out the current Month/Year and reloading the data without using slice in an ASO cube? I thought slice is the way to do it?

    Thanks
    Sherry

    ReplyDelete
  4. GlennSJanuary 7, 2011 at 6:48 AM

    Sherry,
    I have them using a MDX statement inside of MAxL to do a physical clear of the data. It is slower than doing a logical clear, but avoids some of the issues of a logical clear.

    ReplyDelete
  5. Gyanesh TiwariSeptember 21, 2011 at 1:14 PM

    Hi Glenn,

    Can throw some light on how to determine whether a ASO cube is fragmented and needs a defrag?

    Regards,
    Gyanesh

    ReplyDelete
  6. GlennSSeptember 21, 2011 at 1:33 PM

    It this client's case, they use a MaxL statement to clear data from their cube (not slices) and reload data multiple times a month. We think this is causing the fragmentation (but I can't prove it) is is also possible that merging slices could cause the issue.

    As for outline fragmentation, changes to the outline itself cause that and ASO outlines continue to grow with every update.

    ReplyDelete
  7. AnonymousMarch 6, 2012 at 6:41 AM

    How much did the fragmentation impact retrieval performance?

    ReplyDelete
  8. er77October 29, 2014 at 7:05 AM

    Glenn
    Is it this issue available now ?
    No new suggestions ?

    ReplyDelete