Why I created a blog

Its been four years since I first created this blog. It has remained true to Essbase and related information over those years. Hopefully it has answered questions and given you insight over those years. I will continue to provide my observations and comments on the ever changing world of EPM. Don't be surprised if the scope of the blog changes and brings in other Hyperion topics.

Monday, July 6, 2009

Dataexport is great

I’ve had the need to work with the newish dataExport command (I did it in 11.1.1.2) recently and thought I would share some things I’ve found with it and with Sql interface in load rules. I think you will find my musings interesting.

First, I was using data export to export date to a flat file to act as an audit report for some users. It worked like a charm. Some of the things I found are if you specify to export level zero, no matter what you put in your fix statement it will only look at level zero blocks. Using DataExportDynamicCalc on allowed me to export dynamically calculated members as well. For my 20K rows it did not seem to slow the export down, ut I don’t know the impact on a big data set. I could also specify DataExportDimHeader ON to get column names. Using DataExportColHeader "Dense dimension name" I could specify the dense dimension I wanted as the column. It would have been nice if I could put a single member of a sparse dimension there, but I understand why I can't.

Next I needed to back up some of the static information from my cube. Static in that it is user input for my testing and I didn’t have an easy source to reload from. I set up a fix statement and used the binfile option (DATAEXPORT "Binfile" "fileName"). It created a file that I could reload to my db. I can see the usefulness of this on a production database where you need to save off something like budget, clear the database and reload it with source actual and the budget. It’s much easier than the old export and reload and much quicker. In addition, you can be selective of what you export instead of exporting everything.

Finally, I needed to load data populate a Sql database with data from Essbase, modify it and load it back. Yes there are some things that Sql can do better than Essbase. In this case, It was to take two disassociated sets of data and merge them together. It needed to join them on the one common dimension member and basically turn the two 3k row tables into about 1.5 million records that get loaded back into Essbase. I set up the ODBC driver with no problem and exported the data into flat files to see their structure. I then created tables that matched the structures. I will say that there is where I had minor difficulty. If the columns don’t match exactly, the export fails with little information (Just the typical messages in the log that tell you “you screwed up”). I played around with the table figuring out that I miscounted the columns and fixed it and it worked fine. I defined the amount columns as float and found that for #missing values Essbase stuck -2e-15 in the columns that were once #missing in Essbase. A quick stored procedure and I converted them to null.

Oh but wait, how could I run the Stored procedure. I could run an OSQL but the instance I was working on the tools are not working right. I could get into Sql Server, but could not run OSQL or Bulk insert. So thinking swiftly, I thought of load rules. A load rule is just supposed to take Sql commands, so how could I get it to run a stored procedure. I know I can put statements like a union in a Sql statement, So I tried something like:

Getdate()
Exec myStoredprocedure.

I clicked ok/retrieve, entered my id and password, and lo and behold, I got the system date back into my load rule. I checked my tables and the bad characters were converted to nulls. Wow it worked. Who would have thought? I figured I could use the load date to update the alias of a member to set the last time the calculation was run. Another suggest I had was to use rejection criteria to reject the row and load nothing. I used this technique to run another stored procedure that truncated and populated a table from the tables I loaded, so my next step was to create a load rule and bring the data back in. Everything was done without having to resort to anything but MaxL statements.

I’ve since added a custom defined function that will run the SQL statement directly from the calc script. I got this from Touifc Walkim the development manager for Smartview, a very nice guy and CDF guru. Some clients don’t like the idea of CDFs so I have my origina method available when necessary. IF this works, you can get the CDF here



Adding DATAEXPORTENABLEBATCHINSERT TRUE to my config file made the process run faster as it allows the dataexport to use a batch insert method(when the ODBC driver allows it).

As I use Dataexport more, I’ll comment on my findings, but I have to say I’m impressed so far. I have asked for enhancements to the command, and they have been added to the enhancement list. I’m interested to see if or when they are implemented. I was very happy when I was told they were added to the list since it appears that Oracle is open to hear what people recommend. Some of the things I recommended were:
Add an append option to the file export so you could have multiple fix statements write to the same file
Add a debug option to the SQL export to see the SQL statement generated to make it easier to debug when you have problems
Allow aliases to be exported instead of file names.

Let’s see if these get implemented.

15 comments:

Gary Crisci, Oracle Ace said...

Glenn,

Great write up. Could you send me a copy of the CDF?


Thanks,

GlennS said...

Thanks for the comments Gary. I don't know of a way to post the zip file on my blog, I'm looking for a way to make it available to anyone who wants it, until then, I don't plan on mass mailing it to all those who ask for it. It would take up too much time

Alp Burak Beder said...

Glenn,
This is an excellent post. I can't tell you how it would help us having this CDF. We are currently trying to do this with the help of a CDF which triggers and ODI package, but it's just too much work to code and not performing top notch.
Thanks for this again.

Alp Burak Beder said...

Hello Glenn,
Thanks for sharing the CDF but I think it's not up at the moment. The link directs me to the main page of file upload site.
Regards,
Alp

GlennS said...

Alp,
I'm not sure what happened to the upload file. it was working the other day. I'll see what I can do about getting to another place to upload from

Cameron Lackpour said...

As the guy that is hosting (I have an account and offered to do it, no more) the file for Glenn I was intrigued by Alp's comment.

FWIW, I can download the file without trouble. 14 others have done the same.

I will concur that Filedropper's web interface could use a tune up. :)

Regards,

Cameron Lackpour

office 2007 enterprise key said...

I've recently started a blog, the information you provide on this site has helped me tremendously. Thank you for all of your time & work.

Don Church said...

Hi Glenn,

I have also been a big fan of the dataexport calc and have used it extensively. However, I have run into a situation where the export suddenly starts to export my data in different columns for no apparent reason. Have you run into this?

GlennS said...

The only time I ran into it is when I changed the order of dimensions in the outline. the dataexport command follws the outline order (sparse first then dense)

Ariel said...

Glenn, Unfortunatly FileDroper link is not working. It points to not valid zip file. Could you provide new link to CDF function?

GlennS said...

Ariel,
Don't know what to tell you. I just connected to it and it worked fine. perhaps your organization is blocking it

Mario said...

Hello Glenn; I would like to know if you have an experience with DATAEXPORT when the FIX has level 0 members from each dimensión and the result is 1 record; the problema is that the time is 3 or more minutes to write the flat file.
The database is 30GB and has 2 dense and 10 spares diemnsions.

Thanks for your attention, best regards
Mario

GlennS said...

Hi Mario,
IT could be slow if you have DataExportLevel ALL and or DataExportDynamicCalc ON.
I recommend turning All to level0 and On to off. Both All and On are the defaults so if you did not include the statements, you would get those

Dinesh Chand said...

Appreciate this is a fairly old post. Is the RUNSQL CDF still available? Where can I get it - it is no longer accessible via the filedropper link.

Thanks,
Dinesh

GlennS said...

Dinesh,
Send me an email to gschwartzberg@interrel.com and I'll get you a copy