Want to know The Truth About CPM?

24 October 2012

Stupid Programming Tricks #13 -- Exporting in EAS so you can import

The export problem

Oh, I cannot believe that I didn't know this one. 

Can

Not

Believe

It

Have you ever wanted to do an export in EAS but realized that you didn't have file rights to the Essbase server?  Most locked down environments are this way, right?  And so what do you do?  You find someone (anyone) who has file rights to the Essbase app folder and he moves it to a location you can access.  The thing is, if that was a big export, and it usually is, it takes time to move it to a semi-open location and of course when you load via EAS, you will load first to EAS, and then EAS loads that data to Essbase.  At least three copies of the data file(s) occur.  Ugh.

Wouldn’t it be great if there was a way to export that Essbase data to a location you could access directly?  Wouldn’t it?  Why yes it would.

The export Stupid Trick

I have to give credit to Dave Anderson for showing me this technique.  This is now the second time he has shown me a Stupid Trick.  Sigh.

Application level

So easy, even Cameron can do it.  

When you export, put in the application name into the Export to file (shown here in 11.1.1.3 ‘cause that’s what’s on my VM, but trust me, it’ll work in the latest release as well), then a backslash, and then the file name.
See the “Sample\”?  How oh how oh how did I not try this in like, oh, the last 18 years?  How?  Don’t answer.

Do you see the file?  This is on a data load – I simply navigated to the application folder.
Arrrrgh!  

And Yeeeeeaaaaaahhhhhh!  

All at the same time.  The jubilation outweighs the frustration, thankfully.

Database level

So if I can export to the application level, surely I can export directly to the database folder itself, right?  And so it was.

 

Note that I have now typed in “Sample\Basic\” in front of the file name.  Could it, would it, does it work?  Yes indeedy.

 
Multiple file export
Can this be expanded to multiple files?  Oh, yes, it most absolutely can.


 It’s difficult to see, but I typed: “Sample\Basic\Tst1.txt,Sample\Basic\Tst2.txt”


And here they are, in their parallel export glory:

 

It's that easy.

So do you feel dumb or smug?

I have an email list of fellow consultants, former and current customers, and various people who are too polite to tell me to go away – this is my Stupid Tricks list.  When Dave Anderson showed me how this worked, I had to share with everyone.  Some people (Opal, Natalie, S√©bastien) thought this was way cool and others (Glenn, Rich, Josie – hmm, all colleagues from a particularly difficult project, I think I now know who was the weak sister) couldn’t believe I didn’t know this.  I couldn’t believe I didn’t know this trick.  But now everyone does, I hope.  

I have banged my head against the wall with exactly the problem this Stupid Trick solves, oh, a zillion times during my Essbase life and this would have been so nice to know.  At least I learnt it before I retired.  :)

11 comments:

Anonymous said...

Brilliant! I was so used to picking it off the server 15+yrs that I never thought of that when I was blocked the occasional time. How the heck did I miss that one? :) More stupid tricks please!

tisco said...

Hello Cameron,

Good post as always. I tested it on my Essbase server 11.1.1.3 on windows and it works as you say but when the essbase server is on unix the text file gets saved as Sample\Basic\test.txt or Sample\test.txt or whatever path you indicate...

Would you know why this happens?

Best regards

tisco said...

Hello Cameron,

Forget my previous comment, my lack of knowledge of Unix...

if instead of putting the backlash you put / it works perfectly.

Thanks for the trick.
Best regards

Brian said...

Another useful trick related to this I have used in the past is for transferring data between apps. Simply put the app/dbname of the destination in the export path. For example you could export from Sample Basic to Sampeast\East\transferdate.txt and then load the data directly into Sampeast:East. For those working on UNIX servers keep in mind the UNIX is case sensitive and that since this is a filesystem write, it will matter.

Brian

DiabloPaulo said...

Hi,

OMG Sometimes I feel like I know nothing when I read around the blogs etc. However, you have gave me great encouragement that maybe I do know "stuff"!!

I've always done it this way, and to be honest I didn't even know if you didn't where the files actually went.....so I guess I learned they go to the EAS sevrer somewhere..... lol

THANKS for making me feel just the little bit smarter.....

Oh - I bought your book, can I have a refund? lol

Cameron Lackpour said...

Paul,

I am always happy when my ignorance makes other people look good by comparison.

:)

Regards,

Cameron

Nat said...

excellent! Could i do a query here about Export?
Here goes: where could i find a log indicating if the export was successfully or not?

thank you!

Cameron Lackpour said...

Nat,

Take a look in the application log. Here's an example of Sample.Basic being exported:

[Fri Nov 23 07:44:00 2012]Local/Sample///8196/Info(1013210)
User [hypadmin@Native Directory] set active on database [Basic]

[Fri Nov 23 07:44:00 2012]Local/Sample/Basic/hypadmin@Native Directory/7840/Info(1013091)
Received Command [ParExport] from user [hypadmin@Native Directory]

[Fri Nov 23 07:44:00 2012]Local/Sample/Basic/hypadmin@Native Directory/7840/Info(1019020)
Writing Free Space Information For Database [Basic]

[Fri Nov 23 07:44:00 2012]Local/Sample/Basic/hypadmin@Native Directory/7840/Info(1005029)
Parallel export enabled: Number of export threads [1]

[Fri Nov 23 07:44:00 2012]Local/Sample/Basic/hypadmin@Native Directory/8276/Info(1005031)
Parallel export completed for this export thread. Blocks Exported: [1]. Elapsed time: [0.02].

[Fri Nov 23 07:44:00 2012]Local/Sample/Basic/hypadmin@Native Directory/7840/Info(1005002)
Ascii Backup Completed. Total blocks: [1]. Elapsed time: [0.022].

[Fri Nov 23 07:44:00 2012]Local/Sample/Basic/hypadmin@Native Directory/7840/Info(1013270)
Database export completed ['Sample'.'Basic']


Regards,

Cameron Lackpour

Philip Hulsebosch said...

Hello,
there is extension to it... sometimes EAS can see different essbase servers, while they are on different domains and data can not travel between them.
Export the file and give it the extension .rep. Then you can copy a rather large report file (aka. data export) from one Essbase server to the other via EAS.
Regards,
Philip H.

Mei Liu said...

You are my hero! Just used this trick.
Thank you and looking forward to seeing you again at KSCOPE!

Best,

Mei Liu said...

You are my hero! Just used this!
Looking forward to see you again at KSCOPE this year!

Best,
Mei