Many of us may have used SET DATAEXPORTOPTIONS. But I noticed that there are some glitches in that command usage that is not documented explicitly. It can cause a “LOT” of trouble with respect to performance. I spent few hours on this and thought will share it with you all to save some time.
However, this is an existing script that was already set up for one of my clients. I had to review and fix the performance issues. It is a simple data export MAXL to export data out for recovery readiness.
When investigating, I found out that the batch file that fires the MAXL are having 3 SET DATAEXPORTOPTIONS commands one after the other. The MAXL was exporting “All” ESSBASE data to a file as well as “LEVEL0” of the ESSBASE data to a table and finally it was also exporting “ALL” budget only ESSBASE data to a file . They were doing 3 different exports from the same ESSBASE database using a MAXL file which was executed from a batch file.
SAMPLE LOOK at a portion on the MAXL:
Observations:
1) Having 3 different DATAEXPORTOPTIONS (Refer above picture) command in the same MAXL is actually accepting only the first DATAEXPORTOPTIONS.
– How did I find out? Trial and Error exports and comparing file sizes / data rows in the table.
2) The 2nd DATAEXPORT command (Refer above picture) instead of exporting just LEVEL0 data to the table, It was exporting “ALL” data.
Essbase to Table export is basically slow as you know and on top of it this was an overhead.
– How did I find out? You would think having a “};” at the end of DATAEXPORTOPTIONS indicates it is only the setting for the DATAEXPORT command that follows. But it is not the same when used within same MAXL script in my trial and error observations.
Solution – I ended up creating different MAXLs for each of the different DATAEXPORT option to resolve the performance issue as well as to perform the export of the needed DATAExportLevel only.
After I worked on the solution, I found a glitch in the existing code:
3) If you noticed the existing script in the above picture – the second DATAEXPORTOPTIONS has DataExportLevel “LEVEL0”.
Now, if you check ESSBASE technical reference guide for the syntax – There is no syntax that says we have to use quotes around LEVEL0.
The usage of “LEVEL0” with quotes is a simple honest mistake But it ended up taking ALL for DATAEXPORTLEVEL option instead of just LEVEL0 as ALL is the default and “LEVEL0” is unknown.
Syntax:
DataExportLevel ALL | LEVEL0 | INPUT;
This exporting “LEVEL0” added to the performance bottle neck widely.
But again – I could have put it as LEVEL0 back without quotes – to keep all the 3 DATAEXPORTOPTIONS in the same MAXL file and tested for the performance again.
Honestly, I didn’t have any more time to perform more testing and rewrite of the scripts. Instead I ended up writing my own rules of thumb with respect to DATAEXPORTOPTIONS in ESSBASE.Here they are:
I conclude that the below points should improve performance around DATAEXPORT setting
1) Follow the syntax right for DATAEXPORT and SET DATAEXPORTOPTIONS especially with respect to LEVEL0.
2) Use one DATAEXPORTOPTIONS for one type of DATAEXPORT in one MAXL.
