This blog post is a follow up to . Specifically, it will talk about how to use Oracle’s ESSCMDQ utility, which was their recommendation on how to fix the following error:
- Download the utility from Oracle:
http://www.oracle.com/technetwork/middleware/bi-foundation/esscmdq-sampleapps-093105.html - Copy what is in the zipped file to the Arborpath on the Essbase server. For version 11.x, it is located at Z:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseClient\bin where the Z drive would be your specific drive.
- Double-click on the ESSCMDQ utility to start it up.
- The first thing you want to do is login to the server. Since my issue was on the same server, I will be using localhost for my example.
login “localhost” “szumbrum” “password123”;
Hit enter to record the command.
- Select your application and database that you want to compress:
select “Sample” “Basic”;
Hit enter to record the command.
- Open the outline:
openotl “2” “1” “Sample” “Basic” “Basic” “y” “y” 1;
“2” = location of the outline file. 1 for local/client, 2 for remote/server
“1” = type of object. 1 is for outline.
“Sample” = application
“Basic” = database
“Basic” = name of the outline (same as the database name)
“y” = lock file?
“y” = keep transactions?
1 = number for the outline handle. Note that you can use any number 0-4. - Write the outline to the file server:
writeotl 1 “2” 1 “Sample” “Basic” “Basic”;
1 = outline handle
“2” = location of the outline handle
1 = ??? couldn’t find notes on this
“Sample” = application
“Basic” = database
“Basic” = name of the outline (same as the database name) - Restructure the outline:
restructotl 1;
1 = all data
2 = no data
3 = low data
4 = in data - Close the outline:
closeotl 1;
1 = outline handle
- Unlock the outline for use:
unlockobj 1 “Sample” “Basic” “Basic” ;
1 = outline handle
“Sample” = application“Basic” = database“Basic” = name of the outline (same as the database name) - Logout:
logout
- You have now compressed your ASO outline!
G’day,
Just a quick question (on what is a staggeringly painful problem – I’m not as nice as you to suggest the jury is out on this being a bug!)
Does compressing the outline like this actually fix the problem? It didn’t work for us the first time, at the moment we are still duplicating the app and renaming it (as well as dealing with all the required changes to subvariables and user provisioning).
And if it does fix the problem, have you had luck in integrating it into an overnight/weekly process?
Cheers
Pete
Hi Pete,
Yes, this fixed the problem…although I will say that it did not work the first time we tried. Once I got a good rhythm going, I *did* put the process into place whereby when the script was run to deploy the application from EPMA, the outline would be compacted automatically. This worked for the few months I was onsite and have not heard anything going wrong with the process.
Give it another try…possibly right after deployment. Don’t give up! 🙂
scz
Hi Sarah,
I found another solution to this that works quite well. We completely clear our database each month and reload from source, so we don’t care much about persisting the data from one cycle to the next. We issue a ‘alter database App1.Db1 reset all;’ which empties the outline (and data) and allows us to deploy from EPMA successfully, every time! We schedule this script to run just prior to the EPMA deployments and it has worked like a charm. It’s also a solution that doesn’t require any additional installs or complicated work-arounds.
Regards,
Wayne