Optimizing Report Scripts

Yes, who uses the report scripts now, but sometimes, that is the best option; here I am not going in the debate that which is better Report Scripts, Calc Exports or MDX. This post will only concentrate on what is the best you can do with the report scripts.

I myself faced a challenge in which report script was taking a couple hours to export the data, we have to bring it down to the least, here are few observations which I would like to share with all and hope this will save our fellows' time. Following them I was able to cut down the time up to 1/100X:

Here are few stages where we can optimize reports:

1. Pre Report Creation Optimization,

2. Formatting Optimization,

3. Member Selection and Grouping Optimization,

4. Report Specific Optimization,

Before from the time report is extracted by report extractor and displayed by report viewer, we can do be some updates to the application, this is pre report creations optimization and this will be common for all the reports, here is the list:

1. Update Buffer Size: Both Retrieve buffer and Sort buffer,

2. Use cfg setting for NUMERICPRECISION to restrict the decimal places,

3. Increase the size of application cache (e.g. alter application <app> set cache_size <size>MB),

Formatting Optimization, as the name suggest these take care of the formatting section, how data will be displayed on completion of  the script, few things which you can keep in mind are:

1.  You may want to keep the report script symmetric by adding <SYM,

2.  Use SUPALL to suppress almost everything,

3.  Don't use Totaling Column, Rows etc. if its really not necessary,

5.  Use <SUPSHARE to suppress share members,

6.  Avoid using row formatting commands when you are using <ORDERBY in a report,

7.  Limit your decimals up to 2 or 3, 

8.  Keep the missing text as small as possible,

Member Selection and Grouping Optimization: Report script is all about the way you select the intersection which you want to retrieve, few of the ground rules which you can follow are: 

1.  For large dimensions use the following as much as you can:

·         Leaves() MDX function

·         NonEmptySubset() MDX function

·         MDX optimization properties: NONEMPTYMEMBER and NONEMPTYTUPLE

·         Leaves Report Writer Command

·         Generation or level specification in Descendants and Idescendants Report Writer commands (when used within Link command)

2.  Creating the report script in the same order as Report Extractor extracts data,

3.  Select Members yourself wherever possible, instead of letting a member selector function taking it out from the outline,  

4. Grouping dense dimensions in columns and sparse dimensions in rows 

5. When it comes to ASO cubes, in <Row keep the dimension name in ascending order as per the number of members you are going to retrieve, Ex: if number of members you are exporting (Actual number  of the members in a dimension doesn't matter, its the number of member which you are trying to export from that dimension) are: D1 10 out of 100, D2 150 out of 200, D3 50 out of 1000, D4 100 out of 3000 then your row export should be like:

<ROW (D1,D3,D2,D4)

Reports specific:

Every report is different, it calls for its own optimization, you need to look for the pain point and see the possible work around, and here I am going to take one example, which is actually very common.

I had one ASO cube in which we had a typical formula for member NumberOfDays  and formula was like IIF([Period].CurrentMember IS([Period].[Jan]) OR  IS([Period].[Mar]),...),31.. etc.

This formula was used in 30 Other Stats Members, Ex Sales Daily Avg: [Sales]/[NumberOfDays] etc. It was taking ages to export Stats data even for a small combination. The reason behind that was when Essbase was trying to export these members it inturn going NumberOf Days Member calculation. As it’s an ASO cube even level 0 stored members with formula are calculated at run time. In my case they were just 30 odd members I have replaced the formula directly in the Stats members, Ex:

Sales Daily Avg: [Sales]/[NumberOfDays] 

Is turned into:

Sales Daily Avg: [Sales]/(IIF([Period].CurrentMember IS([Period].[Jan]) OR  IS([Period].[Mar]),...),31...)

This has improved the performance 2000X, for one profit center it was taking 2006 secs to extract earlier, it came down to 2.18 secs.

All the best guys.

Cheers..!!!

Rahul S.