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


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   


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   


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

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   



63.108.38.204

Have you heard of HyperPipe?

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

Seems like I saw a blog post about this recently. ;)

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

173.167.245.6

:)

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

Jason was kind enough to listen to my ranting and do something to make me shut up.

I haven't yet had the chance to try it but it is on my list of things to do.

Regards,

Cameron Lackpour

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


159.63.144.227

Re: :)

No score for this post
January 8 2013, 1:16 PM 

> CL:
> I haven't yet had the chance to try it but it is on my list of things to do.

You're talking about shutting up, right? =)

-- Joe

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

173.167.245.6

Zing!

No score for this post
January 8 2013, 4:04 PM 

:)

Regards,

Cameron Lackpour

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


4.20.98.115

Perl Module

No score for this post
January 8 2013, 1:35 PM 

Tim - I know you used to dabble in Perl (you used to inspire me with your perl posts from many years ago). Have you tried just executing the MDX from the perl module? The return results come back in that nice (to me at least) DBI type structure.

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

137.254.4.4

Re: Perl Module

No score for this post
January 8 2013, 3:43 PM 

I haven't used the Perl module in several years (nobody wants to have to support it). It's good to know they at least got that part right!

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


199.248.185.22

using sed

No score for this post
January 8 2013, 1:36 PM 

You can use sed command to eliminate the headers an unwanted output

sed -e "/OK\/INFO/d" -e "1,/^+------------------------/d" -e "/^$/d" yourfile > newfile

This will give you everything after +-----

It even takes care of this OK/INFO - 1241150 - MDX Query execution completed.

You download sed.exe from http://unxutils.sourceforge.net/

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

137.254.4.4

But...

No score for this post
January 8 2013, 3:48 PM 

that's not going to put the member from the row header in your column. Also, I'm not sure why they decided to put parenthesis around the member names on the rows -- you'd have to get around that as well. I know it can be done -- heck I could do it in one line in Perl, but the point is that the output is a mess and they don't give you the ability to format it as nicely as Report Scripts.

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

108.5.149.202

Couldn't you write a simple load Rule ... OR ... OR ...

No score for this post
January 13 2013, 11:50 PM 

This is a very simple query with only one axis statement (the first in the file) so couldn't you write a simple rule to pick the axis out as a header and then delete the )( and read the commas as delimiters?


OR...

Even if not it sure seems like a super simple perl or awk or SED - even if it had multiple Axis statements.


OR... could you write a constant for your axis (select on 'x') and then add the single column member to the rows:

SELECT
{'x'}

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



OR EVEN BETTER IF YOU HAVE MULTIPLE AXes:

SELECT
{'x'}

ON COLUMNS,
{ CROSSjoin( [Time].leaves, CrossJoin([Ledger].Leaves,CrossJoin([Business Unit].Leaves,CrossJoin([Account].FirstChild.Leaves,[Product].Leaves)))}

on rows


That would give you the format you wanted with all dimensions on the row specification

Not sure about the 'x' but you probably have a fixed dimension somewhere you are not specifying.

Finally doing it this way you will probably hit the issue GlennS mentions where you exceed the limit (forgot what it was) on number of cells in the query. You might have to break it up.

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

174.227.193.176

Re: Couldn't you write a simple load Rule ... OR ... OR ...

No score for this post
January 14 2013, 9:28 AM 

The point isn't to read it into Essbase but rather send it to another system. For those purposes, report script is still the best method as much as it pains me to say it.

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


24.106.220.67

Re: The results are in and MDX wins

No score for this post
January 14 2013, 7:24 AM 


I found something not too long ago that *really* is a game changer. It was so good, in fact, that it caused us to add MDX support to Dodeca within 48 hours of learning of this (undocumented) command.

I passed the information along to Cameron so he could test the results on the tests in this thread and, based on his draft blog entry that I just read, I think he concurs that it is a game changer. I am guessing he will post his blog entry today showing the undocumented syntax, the results and the implications. As for me, I am enjoying a couple of extra days here at the beach in Wilmington, NC (which means I will probably have time to actually post a couple of blog posts of my own!).

Tim

Tim Tow
Oracle ACE Director
Applied OLAP, Inc

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

159.53.78.140

I don't know about you guys but

No score for this post
January 14 2013, 2:27 PM 

I have been refreshing camerons blog all day...this is just a big tease.

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


24.106.220.67

Re: Mission Accomplished!

No score for this post
January 14 2013, 3:40 PM 


My post was intended to be a teaser for Cameron's blog post. The difference in results, however, is very dramatic (so, naturally, we needed this big build up..)

Tim

Tim Tow
Oracle ACE Director
Applied OLAP, Inc

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

71.185.1.134

It's here, and it's amazing (the technique, not the blog post)

No score for this post
January 14 2013, 7:13 PM 

That's just the usual collection of nonsense. :)

NONEMPTYBLOCK is...amazing. Amazing. And *fast*. Read the post to find out all about it.

Thanks to a number of people on getting the word on this out as I certainly didn't do it alone:
Tim Tow
Bryan Bain
John Booth
Dan Pressman

Regards,

Cameron Lackpour

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

71.185.1.134

Would it help if I posted the link? Why yes it would

No score for this post
January 14 2013, 7:17 PM 

http://camerons-blog-for-essbase-hackers.blogspot.com/2013/01/the-fastest-way-to-export-targeted-data.html

Regards,

Cameron Lackpour

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

159.53.78.140

Undocumented

No score for this post
January 15 2013, 10:28 AM 

Why are these features undocumented?

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


24.106.220.67

Re: Undocumented

No score for this post
January 15 2013, 2:01 PM 


I don't know yet why they are undocumented, but it could be a number of things. Most likely it is a functionality that Oracle does not want to officially support as the usefulness applies only to certain cases. Alternatively, they may have envisioned it only for use with Hyperion Planning. If I learn of an official position from Oracle, I will post it here.

Tim

Tim Tow
Oracle ACE Director
Applied OLAP, Inc

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

199.248.185.22

Re: Would it help if I posted the link? Why yes it would

No score for this post
January 15 2013, 1:35 PM 

Wowww!!! (I think I should add more w's). That's a killer, Let me figure out if there is a way with sed or awk to do a formatting and get in a report script formatted way.

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


24.106.220.67

Re: summary

No score for this post
January 15 2013, 1:58 PM 

Here is an excerpt from the email Cameron sent me with his test runs (with the numbers rounded to the nearest second):

Report script ----------------- 1270 seconds
MDX with NON EMPTY ---------- 639 seconds
MDX with NONEMPTYBLOCKS ------ 2 seconds

This could definitely be a game changer in some circumstances. For us, it was such a big deal that we immediately went to work to add MDX reporting functionality to Dodeca. Our investment in a modular architecture under Dodeca really paid off as we were able to add two major MDX related features to our server and to our client in less than 2 days.

Tim

Tim Tow
Oracle ACE Director
Applied OLAP, Inc


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

159.53.46.140

Version specific?

No score for this post
January 16 2013, 7:34 AM 

I am trying to use it on 9.3.1 and it is not working. Using a very basic MDX query NONEMPTYBLOCK brings back no results, whereas NON EMPTY brings back the expected value. I am not versed in the ways of planning, just traditional essbase, so maybe it is a lack of understanding on my side.

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


24.106.220.67

Re: Version specific?

No score for this post
January 16 2013, 8:47 AM 


It may very well be version specific. As it is undocumented, it is hard to tell when it actually appeared in the product unless we test every version. I was using 11.1.2.1.102 when I found it; I believe Cameron was working on 11.1.2.2.

Tim

Tim Tow
Oracle ACE Director
Applied OLAP, Inc

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

159.53.46.140

Figured it out

No score for this post
January 16 2013, 10:32 AM 

The cube I was querying had a dimension that is full dynamic. That is probably why it was ntot working. I shifted things around and it worked. Slightly different results (not data values but rows retruned). I am sure these are explainable if I could figure out what it was doing. I assume nonemptyblock probably means do not even check any blocks that are empty (and in my case dynamic) - may account for the variations in data returned "bug"

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

159.53.46.140

ok last time I bother you guys.

No score for this post
January 16 2013, 10:35 AM 

when i use non empty and nonemptyblock together it produced the same number of records and the same results...duh....so as long as I am not querying dynamic members I am good....and it is faster..

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


161.107.18.136

bugs

No score for this post
January 16 2013, 9:11 AM 

As it's undocumented, it's possible there are some bugs. Known:

***

13037253 -- MDX. When using NONEMPTYBLOCK in an MDX query with a dynamic
sparse member on the slicer axis, the query results are incorrect. (fixed in 11.1.2.2)

***

This one is strange to me. At first glance it seems to have nothing to do with NONEMPTYBLOCK but read the solution:
-Cause
The cause of this issue is unknown and an unpublished bug has been raised:
BUG 7461239 - DATA COPY COMMAND CAUSES ESSBASE APPLICATION TO CRASH WITH EXCEPTION LOG FILE

-Solution
This problem is fixed in Hyperion Essbase version 9.3.1.2.09 or higher.

One workaround is to prevent the execution of Hyperion Essbase queries containing the NONEMPTYBLOCK keyword. Similarly, disable the "Suppress Missing Blocks" option in Hyperion Planning web forms.

A second workaround is to prevent the execution of Hyperion Essbase calc scripts or business rules containing the "CLEARBLOCK UPPER" statement. If these type of calculations are required, then they can be executed immediately after re-starting the Hyperion Essbase database.

***


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

173.161.250.177

Bugs as a fact of life

No score for this post
January 16 2013, 10:33 AM 

Interesting that PD (I think) couldn't get it to work on his release of 9.3.1 yet the bug references 9.3.1.2.09. I guess an easy test would be to see if your instance of Planning (if you have one) has the Suppress Missing Blocks box or not. If it does, your related release of Essbase likely has the keyword. Although who knows, maybe it's there and somehow not available through MDX queries? All a big bunch of unknowns. For sure it works in 11.1.2.2. I'll give it a try in 11.1.1.3 tonight and report back if it works.

Btw, I contacted the person I know at the EPM documentation team and given the response, I suspect we will see the command documented soon-ish.

Regards,

Cameron Lackpour

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

159.53.46.140

yeah about that....

No score for this post
January 16 2013, 10:37 AM 

...PD got it to work... :)

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

173.161.250.177

I just want to know about your all-dynamic BSO database

No score for this post
January 16 2013, 10:55 AM 

As in: why?

Regards,

Cameron Lackpour

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

159.53.46.140

Just one dimension is all dyanmic

No score for this post
January 16 2013, 11:04 AM 

My query was pulling total for that dim and was bringing nothing back. I changed it to pull level 0 of that dim and presto - results.

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


148.87.67.204

Re: Bugs as a fact of life

No score for this post
January 16 2013, 11:25 AM 

I searched the internal knowledge base and the defect from 9 was the only thing I found. I also searched the internal chat room and found two unanswered questions -- one asking why there was no documentation on it and the other mentioning a customer not getting back the expected results (similar to what PD was seeing it sounds like). Both messages were from 2010.

I'm curious what they're doing differently to make it sooo much faster.

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


24.106.220.67

Re: why is it faster?

No score for this post
January 16 2013, 2:52 PM 


I don't know for sure, but I would suspect the internal algorithm looks at the disk for the appropriate blocks and pulls those. I believe that report scripts/straight non empty mdx allocate space for the data for each valid combination of members, and then returns only the rows that actually have data.

Tim Tow
Oracle ACE Director
Applied OLAP, Inc

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


76.126.160.225

A question

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

You said:

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


Are you referring to the MDX function?

e.g.

select { Descendants(Year, 99, LEAVES) } on columns from sample.basic

or something else? If so, it seems to work AFAIK. See screenshot:

https://dl.dropbox.com/u/38910931/screenie.png





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

24.45.30.60

Re: A question

No score for this post
July 14 2014, 5:37 PM 

Cameron,

I thought this was your work --> http://calc-essbase.blogspot.com/2013/10/export-data-essbase.html, unless somehow it's linked to your original blog.

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

99.66.118.193

Re: A question

No score for this post
July 14 2014, 5:46 PM 

Same blog contains material lifted from all over the place (including the Oracle docs).

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

108.2.198.177

Yes, I wrote it, he stole it

No score for this post
July 14 2014, 8:19 PM 

Yes, I might have written that first:
http://camerons-blog-for-essbase-hackers.blogspot.com/2013/01/the-fastest-way-to-export-targeted-data.html

Note my January 2013 date versus his October 2013 date. Which comes first and is there any other idiot that writes like me? January and hopefully not.

I have to say that I have seen my work appropriated in abstracts for Kscope (John Booth and I did EPM in the cloud in 2010, in 2011 someone submitted an identical abstract), blogs (per this one and others), and even presentations (think content).

The point behind all of my evangelizing is to share information, and sometimes I even get asked and receive credit, so I can't complain too much about people finding what I write valuable. At the same time this stuff can take a *long* time to conceive, test, and write. I don't regurgitate the ReadMe in funnier prose -- maybe I should. So when I don't get credit for it, as in this particular post, yes, it is annoying.

But I can live with it. :)

Thank you for pointing out that someone stole my words. A pox upon his house.

Regards,

Cameron Lackpour


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

24.1.183.234

DMCA Take Down?

No score for this post
July 14 2014, 8:47 PM 

I suppose one could use a DMCA take down if one were really not happy about the blatant plagarism....

Kind Regards,

John
Kind Regards,

John A. Booth
http://www.metavero.com

 
Scoring disabled. You must be logged in to score posts.Respond to this message   
Current Topic - Data Export Calc Script vs Report Script
  << 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.