Why I created a blog

Its been four years since I first created this blog. It has remained true to Essbase and related information over those years. Hopefully it has answered questions and given you insight over those years. I will continue to provide my observations and comments on the ever changing world of EPM. Don't be surprised if the scope of the blog changes and brings in other Hyperion topics.

Thursday, December 9, 2010

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.

12 comments:

srx said...

Great and scaring news Glenn! Did you share your finding with Oracle Support?

GlennS said...

A support request was issued (to see if there was any other work arounds) but they just confirmed why the issue occured. Sounds like the counter was defined as a small int.

srx said...

But why a bloody counter?

basintx said...

Glenn, We just heard from Oracle support that you should be able to compress the outline with ESSCMDQ, and that should reset all of the dimension counters. They also said that this should not break any links you have to a Studio drill-thru report. We are testing this now and I'll let you know if this works. Brian

Shalom said...

Confirmed solution using ESSCMDQ.

You can download the file from

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

Oracle support was a bit surprised about this issue and I directed them to this blog.

My .otl file size went from 90MB to 8MB after restructuring it with ESSCMDQ.

Anonymous said...

Glen,

We are encountering this on System 9 and System 11. Compacting the outline did nothing. Recreating the cube worked. Why can't Oracle team update this in the dbag manuals as limits in ASO.

Jullin

GlennS said...

Just compacting the outline from EAS or MaxL won't help. You have to use the EsscmdQ utitity mentioned to get it to work

Anonymous said...

This is awesome. Would try it and see and update on file size reduction.

Anonymous said...

This is very powerful. It reduced my outline size from 260M to 6.9M
It was incredicly fast too to accomplish. Thanks again Glen.

Dan said...

Hi Gary,

Thanks for this - recently hit the same thing after a few weeks. An alternative to solving this is to do a maxl statement:
alter database xx.xx reset all;

This resets the outline too and cures the limit (and keeps the outline small).

I load all data from SQL but if you want to keep data an export followed by an import can be an alternative solution to copying the otl from another application, though it doesn't cope with members removed that have existing data (no data gets imported). ESSCMDQ is great though and one worth remembering whenever I see a huge ASO outline!

GlennS said...

Reset all clears the outline so I would assume it clears the counters as well, but that is a dangerous thing to do The EsscmdQ shrinks the outline file without disturbing the data

Anonymous said...

Glenn Great information. Thank you for sharing..


This counter can be reset by using ESSCMDQ which can be download from the following location (From the previous post)

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

Here is the steps to do

. /opt/oracle/Middleware/user_projects/epmsystem4/EssbaseServer/essbaseserver1/bin/setEssbaseEnv.sh
./ESSCMDQ

Login "servername" "username" "password" ;
Select "appname" "dbname" ;
Openotl 3 1 "Null" "Null" "/Oracle/Middleware/.../appname/dbname/dbname.otl" "y" "y" 0 ;
Writeotl 0 "3" 1 "Null" "Null" ""/Oracle/Middleware/.../appname/dbname.otl" ;
CloseOtl 0 ;
Logout ;