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!

ASO Dimension Deletion Limit

Did you know there is a maximum number of times you can add and delete dimensions in an ASO cube? Neither did I until recently. I have a Essbase Studio model the was deleting the dimensions from my cube before rebuilding them. This was necessary because I have a number of shared rollups that can move around and the best way to deal with this is to rebuild the dimension.I t was working fine until one day it gave me error messages about not being able to add a particular dimension. I removed it from my build and the next dimension popped up with the error. After investigation, I found the error “1060190 Cannot Add Dimension – too many dimensions have been created and deleted”. It turns out the maximum number of dimensions you can have is 255 (or add and delete). Each dimension added is tagged with a dimension ID that increments. Once you reach that limit you are in trouble.

As a work-around, one solution is to create a “dummy” model. Using Studio, create an application from the same Essbase Model as your regular app but instead of deleting members first, select to delete and restore database. This will give you a fresh copy of the outline. Then stop your production application and copy the outline from your dummy cube over the production one. Since it was built from the same model, we can assume the dimensionality and members are the same. Starting the app should activate the new outline and you should be set to go. A couple of things to realize

1. There is no MaxL function to copy the outline, I do this through the file system in my batch file using copy commands

2. You should always backup the original outline and data prior to doing this. Remember even if you did the deploy directly into the production app, if members are dropped you lose the data. Safety first!

3. As always Test, Test, Test. Don’t take my word on it, make sure you don’t blow up your production application by listening to me or anyone else. Two particular areas to check out are Security and drill-through reports (in addition to data integrity)

4. While you should be able to get around this issue by not specifying delete all members, and just doing an incremental rebuild on the specific dimensions, I encountered problems doing it, thus I went the delete members first route.

5. Associated with #4 *nix systems have some issues with the deploy command causing issues.

 

A second solution would be to export the data (now that you can in 11) delete and rebuild the outline then import the data and re-agg.

Someone asked me if this is also the case for BSO cubes and I can’t say that I know. I’ve never encountered the issue on a BSO cube, but then again, I have not used the Studio Delete members first as much on a BSO cube.