Data Export Calc Script vs Report Script

Data Export Calc Script vs Report Script

Joined: April 6th, 2011, 3:09 pm

January 3rd, 2013, 8:51 pm #1

Hello,
I am researching on what is the best method to extract data from Essbase into a text file. This file will be needed to load into an outside FDM system. I am looked at DATAEXPORT calc scripts but it does not appear that it has many options for formatting or supressing zero's or missing data. Basically this export would be run on a monthly basis and only for that current month and year and always using the ACTUAL scenario so these could be global headings, I don't necessarily need them in each row. Also, i would only have one column of data since I don't need all 12 months either. Does this seem more like something report scripts can handle? We are on Essbase 9.3. Thank you.
Quote
Like
Share

Joachim
Joachim

January 4th, 2013, 8:35 am #2

My experience is that DATAEXPORT is faster and more robust, although it doesn't have the formatting options that Report scripts do. Report scripts can be a bit unstable if the output is huge or you are reporting many dependant dynamically calc'd sparse members for example, but used in the right way they are just great. If you don't know the report script however, I am not sure that you would want to invest the time in learning the archaic syntax. Go as far as you can with DATAEXPORT because it is simple enough to be worth learning anyway. Then there is ODI if you have the time..

Quote
Share

Joined: November 26th, 2001, 10:15 pm

January 4th, 2013, 11:52 am #3

Hello,
I am researching on what is the best method to extract data from Essbase into a text file. This file will be needed to load into an outside FDM system. I am looked at DATAEXPORT calc scripts but it does not appear that it has many options for formatting or supressing zero's or missing data. Basically this export would be run on a monthly basis and only for that current month and year and always using the ACTUAL scenario so these could be global headings, I don't necessarily need them in each row. Also, i would only have one column of data since I don't need all 12 months either. Does this seem more like something report scripts can handle? We are on Essbase 9.3. Thank you.
They work against BSO Essbase and are (I think) the default language for Financial Reports and Smart View.

I *think* ODI also uses MDX to go against Essbase under the covers although I would not swear to that.

Have you thought of looking at Star Integration Server? They have a very nice (and fast) and easy interface for extracting data.

Lastly, do you have a nightly backup process that includes level zero exports? If you did that in columnar format, you could suck the data into SQL (or grep it if your regex skills were good -- actually, I really like that approach more) and then query out what you need.

Lots of options, and I would do some quick benchmarking to see which was the fastest/least painful.

Regards,

Cameron Lackpour
Quote
Like
Share

Joined: April 6th, 2011, 3:09 pm

January 4th, 2013, 7:02 pm #4

Hi Cameron,
Thank you for your response. I have been reading through the technical documentation regarding MDX, my question is with using MDX against a BSO cube, will I run into any performance issues with dimension order of sparse and dense dimensions. I know that report scripts can be very finicky about the order in which you pull data. Does MDX spare me some of those issues? Also, I am currently running some MDX queries through the MaXL Shell, how do I get the output into a file? Many thanks!
Quote
Like
Share

Joined: November 26th, 2001, 10:15 pm

January 4th, 2013, 9:40 pm #5

Re outputting to a file, see this thread:
http://www.network54.com/Forum/58296/th ... MDX+Output

Re performance -- well, this weekend (oh, joy) I will be playing with a (for me) very large BSO database and will be writing MDX scripts against it for benchmarking purposes. I would think that the same things that make a report script slow, e.g., cycling through a lot of blocks, would be the same. However, MDX queries do take a different path than report scripts and there are some keywords not available in report scripts, notably NONEMPTYMEMBER that I believe outperforms {SUPMISSINGROWS}.

I'll let you know if I can see a difference. The database is freaking (again, for me) huge -- 87 .PAG files. I'm sure there are larger but is amusing to see a BSO database that big.

Regards,

Cameron Lackpour
Quote
Like
Share

Joined: November 26th, 2001, 10:15 pm

January 6th, 2013, 11:01 pm #6

MDX is *twice* as fast as a report script. At least in my testing and database.

Thanks first to Dan Pressman and Gary Crisci. Dan was on the phone with me working this out, Gary gave out the key command quite a few years ago -- see:
http://www.network54.com/Forum/58296/th ... +know+how-

You can find it here as well:
http://docs.oracle.com/cd/E17236_01/epm ... _axis.html

FWIW, with a BSO database:
1) <LEAVES doesn't work with BSO databases -- this is documented
2) <DESCENDANTS with LEAVES also doesn't work -- this is NOT documented


A dead end: NonEmptySubSets -- Ooooh, this is a fun one. It is not really fit for large number of sparse data sets and the way it determines if a data set is empty or not is defined by data values at default member selections (I think).

The answer is NON EMPTY as in the above two links.

Having said all of that, the Essbase report script (which uses { SUPEMPTYROWS }) took 1272.55 seconds whereas the MDX query took 641.6 seconds. The dimension layout was the same for both queries, with level zero retrieves of two rather large sparse dimensions.

Test your own database, but in my mind there is no reason to stick with report scripts for this kind of extract.

Regards,

Cameron Lackpour

P.S. Talk about learning curve -- it took me, oh, eight minutes to write the report script and about a day to write the MDX query. Good times, good times. Regardless, MDX spanked report scripts.
Quote
Like
Share

Tim Faitsch
Tim Faitsch

January 7th, 2013, 12:02 am #7

The problem with MDX is that the output format is a disaster. Anybody know of an easy way to export a clean file, comma delimited, etc?
Quote
Share

Joined: April 6th, 2011, 3:09 pm

January 7th, 2013, 3:46 pm #8

MDX is *twice* as fast as a report script. At least in my testing and database.

Thanks first to Dan Pressman and Gary Crisci. Dan was on the phone with me working this out, Gary gave out the key command quite a few years ago -- see:
http://www.network54.com/Forum/58296/th ... +know+how-

You can find it here as well:
http://docs.oracle.com/cd/E17236_01/epm ... _axis.html

FWIW, with a BSO database:
1) <LEAVES doesn't work with BSO databases -- this is documented
2) <DESCENDANTS with LEAVES also doesn't work -- this is NOT documented


A dead end: NonEmptySubSets -- Ooooh, this is a fun one. It is not really fit for large number of sparse data sets and the way it determines if a data set is empty or not is defined by data values at default member selections (I think).

The answer is NON EMPTY as in the above two links.

Having said all of that, the Essbase report script (which uses { SUPEMPTYROWS }) took 1272.55 seconds whereas the MDX query took 641.6 seconds. The dimension layout was the same for both queries, with level zero retrieves of two rather large sparse dimensions.

Test your own database, but in my mind there is no reason to stick with report scripts for this kind of extract.

Regards,

Cameron Lackpour

P.S. Talk about learning curve -- it took me, oh, eight minutes to write the report script and about a day to write the MDX query. Good times, good times. Regardless, MDX spanked report scripts.
Cameron, thank you for the response, much appreciated. I am planning to explore these options, I'll follow up with my outcome. Thank you.
Quote
Like
Share

GlennS
GlennS

January 7th, 2013, 5:37 pm #9

I just like to argue with Cameron, While in his case, he found MDX to be much faster, I've had cases where the MDX would not run or Report scripts were much faster than MDX. I had a case where I had to break an MDX statement into three parts for it to return data while the report script did it with no issues. So with everything in programming, you have to test, test, test in your environment with your cubes to find out what works best (and yes cameron, I used non empty in my MDX)
Quote
Share

Joined: November 26th, 2001, 10:15 pm

January 7th, 2013, 7:45 pm #10

MMIC,

I know you enjoy this, but you have to give me credit for (I think maybe you taught me this) always qualifying my statements. Weasel words or words of wisdom, you decide.

>>MDX is *twice* as fast as a report script. At least in my testing and database.
^^^Last seven words.

>>Test your own database, but in my mind there is no reason to stick with report scripts for this kind of extract.
^^^First four words.

:)

As always, I left myself an out (two, actually). MDX was quite a bit faster than a report script, at least in my database.

Regards,

Cameron Lackpour
Quote
Like
Share


Confirmation of reply: