This Essbase discussion board is provided as a free service and dedicated to all the Essbase professionals out there!
  << Previous Topic | Next Topic >>Return to Index  

Data Export Calc Script vs Report Script

January 3 2013 at 3:51 PM
No score for this post
Anonymous 
from IP address 199.105.75.242

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.

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
AuthorReply
Joachim

77.40.205.226

It depends..

No score for this post
January 4 2013, 3:35 AM 

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..


 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Cameron Lackpour

71.185.1.134

What about MDX queries?

No score for this post
January 4 2013, 6:52 AM 

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

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Anonymous

199.105.75.242

MDX Queries?

No score for this post
January 4 2013, 2:02 PM 

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!

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Cameron Lackpour

173.167.245.6

Dunno now, but will by the end of the weekend

No score for this post
January 4 2013, 4:40 PM 

Re outputting to a file, see this thread:
http://www.network54.com/Forum/58296/thread/1142392476/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

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Cameron Lackpour

71.185.1.134

The results are in and MDX wins

No score for this post
January 6 2013, 6:01 PM 

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/thread/1183660262/1183732035/Suppress+%23Missing+in+MDX+-+Anyone+know+how-

You can find it here as well:
http://docs.oracle.com/cd/E17236_01/epm.1112/esb_tech_ref/mdx_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.

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Tim Faitsch

71.23.187.8

Re: The results are in and MDX wins

No score for this post
January 6 2013, 7:02 PM 

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?

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Anonymous

199.105.75.242

Thanks Cameron!

Score 4.0 (1 person)
January 7 2013, 10:46 AM 

Cameron, thank you for the response, much appreciated. I am planning to explore these options, I'll follow up with my outcome. Thank you.

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
GlennS

63.127.232.196

Re: Thanks Cameron!

No score for this post
January 7 2013, 12:37 PM 

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)

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Cameron Lackpour

173.167.245.6

You did catch the first four words of my recommendation, right?

No score for this post
January 7 2013, 2:45 PM 

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

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
GlennS

63.127.232.196

Re: You did catch the first four words of my recommendation, right?

No score for this post
January 7 2013, 4:54 PM 

Wait you wanted me to actually read everything you said. I'm not sure I could handle that. I just glance over it and shoot from the hip knowing I'll be getting your goat. I have no doubt you do your testing, but just wanted to remind the OP that what works for one instance is not a universal that it will work everywhere with the same success.

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
John A. Booth

63.87.246.250

Be sure to feed the goat

No score for this post
January 7 2013, 5:40 PM 

The goat gets surly when not fed properly.

Regards,

John

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Cameron Lackpour

173.167.245.6

You know you are my older brother who happens to have completely different parents

No score for this post
January 7 2013, 6:19 PM 

And like all older brothers, you just love to torture the younger ones. Like me. Fair enough, as I did it to the one that I do actually share biological parents with. Although he turned out to be way smarter than me. Just like you. Oh well. Instead of a dromedary curve in intelligence our (mythical) family is bactrian in profile if distributed by age. Sigh again.

In any case, I know you do it to get a rise out of me and I also know you can receive in kind. I just thought that I would point out that I learnt your lesson well of always qualifying performance techniques. Twice as fast in one instance at least suggests the OP ought to take a look. He/she/army-of-marauding-Essbase-robots may get a pleasant boost in performance. Or maybe not. OP, are you going to report back to this thread?

Regards,

Cameron Lackpour

P.S. Tim F -- I set the columns to 80 characters (because I am lazy and the db has long member names). Is parsing on 80 character fields so bad? Nothing is as sweet as tab delimited (well, maybe coffee), but fixed length fields can be worked with.

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Tim Faitsch

64.134.70.62

MDX file format

No score for this post
January 7 2013, 7:45 PM 

It's been a few months since I created an extract but I seem to remember it not giving me a comma delimited file and it always included the MDX statement in the output of the file. I remember asking my co-workers and nobody had any bright ideas for eliminating the extra stuff. Maybe the newest version is better? I was using 11.1.1. I'm going to get my 11.1.2.x demo drive going next week so I will be able to test then...

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Tim Faitsch

137.254.4.4

starting to come back to me

No score for this post
January 8 2013, 10:14 AM 

When I run this:
alter session set dml_output alias off;

spool on to 'X:\P2_2010.txt';

SELECT
{[Time].[Period 2 2010]}

ON COLUMNS,
{CrossJoin([Ledger].Leaves,CrossJoin([Business Unit].Leaves,CrossJoin([Account].FirstChild.Leaves,[Product].Leaves)))}
on rows

from APP1.DB1;


I get this in my file:

SELECT
{[Time].[Period 2 2010]}

ON COLUMNS,
{CrossJoin([Ledger].Leaves,CrossJoin([Business Unit].Leaves,CrossJoin([Account].FirstChild.Leaves,[Product].Leaves)))}
on rows

from APP1.DB1


Axis-1 (Period 2 2010)
-------------------------------------- ---------------------
(ACTUALS, BU1, ACCT1, PROD1) 0
...


****

Now what am I supposed to do with all the garbage? I now need to parse the file to get what I need which is a total PITA. They sent us a really cool toy but you end up spending a bunch of time removing the little bits of styrofoam it comes packed in!

At this point I would be really happy if someone would school me (make me look like a dummy, go ahead) and show me some little command that will make the output clean. :-)

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Cameron Lackpour

173.167.245.6

You're right

No score for this post
January 8 2013, 10:30 AM 

Yeah, the headers kind of suck. But you (mostly) know when they stop. I am no regex expert, but I would think it wouldn't be that hard to work around. And you could always do it in a script that looked for "+------" as the beginning of the data and then did whatever is needed to transform the data records to something nicer. Really, you could (do you like how I am generous with your time?) write this utility once and then apply it to the MaxL output again and again.

Hmm, on rereading, that really is a pain, isn't it. But it would still be be worthwhile if time were important and MDX was faster. You could always turn spooling on just before the MDX but it the query itself will still show up in the output. Bummer.

Regards,

Cameron Lackpour

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
GlennS

63.127.232.196

Re: You're right

No score for this post
January 8 2013, 10:36 AM 

If you run it from within Smart View, The MDX parser will put it nicely into the cells for you :) Other than that, it is write your own parser

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Tim Faitsch

137.254.4.4

Re: You're right

No score for this post
January 8 2013, 10:38 AM 

...Or the Oracle developers could make the output capabilities similar in what you'd find in, oh, say, SQL+. Hmm, which software company makes that? I can't remember...

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Cameron Lackpour

173.167.245.6

Ran into NETDELAY errors

No score for this post
January 8 2013, 11:13 AM 

Yup, thought the same thing about running it in SV. But the query took so long (even thought it was faster) I got timeout errors. I just haven't had a chance to go in and tweak the Essbase.cfg settings.

Btw, did you know that SSPROCLIMIT is valid for Smart View? If you read the Essbase docs, all it talks about is the classic add-in. I can assure you that it does make a difference. And no, this is not the setting in APS' Essbase.properties.

Regards,

Cameron Lackpour

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Cameron Lackpour

173.167.245.6

And wouldn't it be awesome if SQL+ could *write* to Essbase

No score for this post
January 8 2013, 11:14 AM 

Why yes it would. Who owns both of those products? Hmm....

Regards,

Cameron Lackpour

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
 
< Previous Page 1 2 3 Next >
  << Previous Topic | Next Topic >>Return to Index  

RSS feed for this forum - http://www.network54.com/Forum/58296?xml=rss. Please email hypess (at) gmail.com, if you have any questions/feedback/issues.