Why I created a blog

Its been many 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.


Friday, June 11, 2010

ASO Outline Compaction

The other day Tim Tow on his blog talked about opening outlines and commented about how slow ASO outlines created in EIS or Essbase Studio were to open. 
http://timtows-hyperion-blog.blogspot.com/2010/06/essbase-outline-performance-testing.html

I've seen that and have a number of large outlines that don't seem to compact. I have one very similar to the zzzz application Tim talks about. sitting at about 122 meg. I tried to compact it and brought it down to 120 Meg. Still big and still slow to open. This is where OTN came to my rescue. There is a extension to Esscmd (yes Esscmd not MaxL) that calls the outline API. This is used by Oracle internal Quality Engeneering to test the API. On the page they have a PDF that explains how to compact an ASO outline. I tried it and my outline went down to 8K and opened in a few seconds. Simply amazing results.

I've sent this post to Tim and hopefully he will test his instance and will report on the results.

I guess I should tell you where to get this godsend. You can download it from  http://www.oracle.com/technetwork/middleware/bi-foundation/esscmdq-sampleapps-093105.html?ssSourceSiteId=otnjp

 
While not a supported application ,it does have versions for all of the platforms and for versions from 6 to 11.

18 comments:

Unknown said...

Glenn, I tried to get the utility from the link you posted but got error. Your link works, but then the links within that page don't work for me.

GlennS said...

I too just tied the links and they are broken. I'm not sure who to contact to get them fixed. Maybe they read my blog and decided that it was an embarassment to say this works

GlennS said...

While the link was broken before , it seems to be working corectly now

Anonymous said...

The link works but I oped a dozed zip files and they are all missing a file so you cn't execute.

On AIX I get,
Could not load program ESSCMDQ:
Dependent module /essapp/hyperion/products/Essbase/EssbaseServer/bin/libessapinuS.a could not be loaded.
The module has an invalid magic number.

GlennS said...

I used it on Windows and found I had to create a bat file that brought in locale information. I also needed to run it from a directory Hyperion/common/EssbaseRTC/9.5.0.0
which I believe stands for Run time client verion 9.5 (9.5 = 11X)

Tey Brady said...

Glenn,
Thanks for sharing this! I talked to you a little about this at the Dallas Oracle User Group. I finally had a chance to try it and it sped up both the time it takes to open/edit outlines and build the outlines by around 90%. Waiting 10 - 15 seconds for an outline to open is not as painful as waiting between two and three minutes. Dimensions builds taking one minute instead of 10 is also nice.

Thanks again!

jithendra said...

great info Glenn. you can download the utility link from the below link
http://www.oracle.com/technetwork/middleware/bi-foundation/esscmdq-sampleapps-093105.html

trei said...

just tried it with an outline of 741 MB size. Unfortunately afterwards the size was 766 MB...
In an other try I reduced the outline size from 19 MB to 16 MB...

Anonymous said...

Glenn,

Great post. Replying to an old post, Sorry for that.

We are encountering a similar outline behavior at my client and yes, we do a lot of data loads specifically at month-ends during that time we require to run the ESSCMDQ utility to compact the outline.

Is there any way to find out little in advance that the data loads or deployments could fails and we need to execute the utility to fix the issue ?

Any help appreciated.

Thanks.

AndyH said...

Thanks Glenn .. just ran it on a 447 MB ASO outline and it reduces to 21 MB ... FANTASTIC

Russ S. said...

Hey Glenn. I'm trying to use this but i'm getting a "name too long" error when providing the fully qualified outline path and filename. Do you know what the character limit is? Just wondering if this is truly the error or could something else be going on. At present the full path is 109 characters. Thx.

GlennS said...

Hi Russ,
does your path have spaces in it? that could be the issue. Frankly, I always connect to the server and do the update in place. I've never had an issue with it not working correctly. Can you paste in your commands?

Russ S. said...

no spaces - just lots of folders... Here are the commands minus the login. I made a special Essbase cube with a short name as a test "AP1/DB1". It errors out on the openotl command. I've tried it with this script and typing directly into the command line.

select "AP1" "DB1";
Openotl "2" 1 "AP1" "DB1" "/ess001/oracle/Middleware/user_projects/epmess1/EssbaseServer/essbaseserver1/app/AP1/DB1/DB1.otl" "y" "y" 1;
Writeotl 1 "2" 1 "AP1" "DB1" "/ess001/oracle/Middleware/user_projects/epmess1/EssbaseServer/essbaseserver1/app/AP1/DB1/DB1.otl";
Restructotl 1;
Closeotl 1;
Unlockobj 1 "AP1" "DB1" "/ess001/oracle/Middleware/user_projects/epmess1/EssbaseServer/essbaseserver1/app/AP1/DB1/DB1.otl";

GlennS said...

on the openoutline and writeoutline since you are specifying a 2 which designates it is a server outline, you don't need a path to it just the outline name.
Here is the ful syntax you need. replae app with ap1 and DB with DB1 (in your example) Of cours the login info as well.

Login "host" "user" "password" ;
Select "app" "db" ;
Openotl "2" 1 "app" "db" "db" "y" "y" 1 ;
Writeotl 1 "2" 1 "app" "db" "db" ;
Restructotl 1 ;
CloseOtl 1 ;
Unlockobj 1 "app" "db" "db" ;
LogOut ;
Command Reference

Unknown said...

How do you start Esscmdq in Linux? Is there a shell script?

Russ S. said...

Just to close the loop Glenn - I found out there was an error in the outline that does not stop EPMA from deploying but stops EAS and Esscmd from saving the outline (error message OTLAPI_ERR_NOTVERIFIED: Outline has errors (when saving to the server)). Once I corrected the error the maxl file worked great. Thanks for your assistance in getting me further down the path.

GlennS said...

For either Linux or Windows, you have to create your own script if you want to automate it. I typically put Esscmdq in the same place that Esscmd exists and clone the startEsscmd pointing it to the EsscmdQ executable

GlennS said...

Thanks Russ for the followup. Good to know and post for people to see