Essbase DataExport and Formatting 12

This entry was posted in Customization DATAEXPORT Essbase on by
I think you all know that there are 3 options in Essbase to extract DATA,
  • Columnar Data Export (Run from EAS, you can decide whether to use columnar or not)
  • Report Scripts
  • DATAEXPORT command (introduced from 9 version)
Previously I was always using Report Scripts for any DATAEXPORT. (Its gives you options to arrange the dimensions into Rows and Columns), however I moved to DATAEXPORT command and fell in love with that 😉

It is fast (compared to Report scripts)…It took less than 10 mins to dump the entire database where as Report scripts were running for more than an hour and still didn’t yield any result.

The only challenge with the DATAEXPORT command is that you don’t have options for formatting, but if you know little scripting you can get away with the issue of formatting.

So here is a small script to create a format for import into HFM.

HFM people (in my previous project) would like to have all the members arranged first and then the data, which is not easy to achieve using an ordinary DATAEXPORT command.

I’m using Sample Basic to demonstrate the scenario. I’ve created a calculation script to export all Sales Data.

SET DATAEXPORTOPTIONS

{

DataExportLevel “ALL”;
DataExportColHeader “Measures”;
DataExportColFormat ON;
DATAEXPORTOVERWRITEFILE ON;
};

FIX(@IDESCENDANTS (Year),Sales)
DATAEXPORT “File” “|” “allexport.txt” “#”;
ENDFIX
Output of this calc script is as given below.

“Sales”

“100-10″|”New York”|”Jan”|”Actual”|678

“100-10″|”New York”|”Feb”|”Actual”|645
“100-10″|”New York”|”Mar”|”Actual”|675
“100-10″|”New York”|”Qtr1″|”Actual”|1998


But then this is not in the required format…..so I had to format it and report scripts was not an option.

So I created a script to format it…..and this comes a lot in handy. Let’s say that you’ve to send all Budget and Sales data to an external system..Same calc script can be modified to extract Sales and Budget value. Add a second fix on Budget and extract it to a different file. We can use the OS script to combine both Sales and Budget into a single file

REM # Edit files to make it as FDM format.

REM # this command is used to do a replace 

REM # for example if the file string is

REM # “100-10″|”New York”|”Jan”|”Actual”|678
REM # After command
REM # “100-10″|”New York”|”Jan”|”Actual”|”Sales”|678
 “C:UsersckattookaranDocumentsMy DataToolsunixbinsed.exe” -e “1d” -e “s/|[0-9].*$/|”Sales”&/g” C:OracleMiddlewareuser_projectsepmsystem1EssbaseServeressbaseserver1appallexport.txt >C:OracleMiddlewareuser_projectsepmsystem1EssbaseServeressbaseserver1appSales.txt

 “C:UsersckattookaranDocumentsMy DataToolsunixbinsed.exe” -e “1d” -e “s/|[0-9].*$/|”Budget”&/g” C:OracleMiddlewareuser_projectsepmsystem1EssbaseServeressbaseserver1appallbudget.txt >C:OracleMiddlewareuser_projectsepmsystem1EssbaseServeressbaseserver1appBudget.txt


REM # Combine Sales and Budget to a common file (Sales file will be the master file now.

“C:UsersckattookaranDocumentsMy DataToolsunixbincat.exe” C:OracleMiddlewareuser_projectsepmsystem1EssbaseServeressbaseserver1appBudget.txt >> C:OracleMiddlewareuser_projectsepmsystem1EssbaseServeressbaseserver1appSales.txt

REM # Rename files
MOVE C:OracleMiddlewareuser_projectsepmsystem1EssbaseServeressbaseserver1appSales.txt C:OracleMiddlewareuser_projectsepmsystem1EssbaseServeressbaseserver1appalldata.txt
REM # Delete temp files
DEL /Q C:OracleMiddlewareuser_projectsepmsystem1EssbaseServeressbaseserver1appBudget.txt

Result

 “100-10″|”New York”|”Jan”|”Actual”|Sales|678

“100-10″|”New York”|”Feb”|”Actual”|Sales|645

“100-10″|”New York”|”Mar”|”Actual”|Sales|675
“100-10″|”New York”|”Jan”|”Actual”|Budget|1000
“100-10″|”New York”|”Feb”|”Actual”|Budget|1500
“100-10″|”New York”|”Mar”|”Actual”|Budget|675
“100-10″|”New York”|”Qtr1″|”Actual”|Budget|1998
“100-10″|”New York”|”Apr”|”Actual”|Budget|712
“100-10″|”New York”|”May”|”Actual”|Budget|756
“100-10″|”New York”|”Jun”|”Actual”|Budget|890
“100-10″|”New York”|”Qtr2″|”Actual”|Budget|2358
“100-10″|”New York”|”Jul”|”Actual”|Budget|912
“100-10″|”New York”|”Aug”|”Actual”|Budget|910
“100-10″|”New York”|”Sep”|”Actual”|Budget|790
“100-10″|”New York”|”Qtr3″|”Actual”|Budget|2612
“100-10″|”New York”|”Oct”|”Actual”|Budget|650
“100-10″|”New York”|”Nov”|”Actual”|Budget|623
“100-10″|”New York”|”Dec”|”Actual”|Budget|699
“100-10″|”New York”|”Qtr4″|”Actual”|Budget|1972
“100-10″|”New York”|”Year”|”Actual”|Budget|8940


Hope it helps. You can download sed.exe, cat.exe and the dll file. Keep all the files under same folder.

About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

12 thoughts on “Essbase DataExport and Formatting”

  • Reply ↓
    Toto019

    Thanks you saved me a lot of time. I just wanted to do it to backup part of my Essbase cube and your code do the job.
    By the way I don't get the dataexport can't do it natively…

  • Reply ↓
    sam

    Hi,

    So we migrated from Windows 11.1.2.1 to Oracle On Demand which uses Linux Servers for Essbase. So how do we change the export directory to Linux format? Our current code is:

    DATAEXPORT "File" "," "F:BatchscriptsAllocationsDataAllocData.txt";

  • Reply ↓
    Rajesh G

    Hi Celvin,
    I am using DATAEXPORT command on BSO cube, currently data is exporting in below format to a textfile::
    //"Year", "Company", "Scenario", "Analysis", "Period", "Accounts" //
    But, my requirement is to export in other/below format::
    //"Year", "Analysis", "Company", "Scenario", "Accounts", "Period"//

    Please help.
    Thanks in Advance!

  • Reply ↓
    ROD HERNANDEZ

    Hello, is it possible to export data selecting the alias of the Year dimensión using the DATAEXPORT command in Calculation Manager (Planning 11.1.2.3)? Remaining metadata output is ok as the member name (ID) is required. Thanks in advance!

    • Reply ↓
      Celvin Kattookaran Post author

      There is no option with DATAEXPORT. You can try using the function @CalcMgrMDXExport. you’ll have to write an MDX query and there are options to use aliases.

      • Reply ↓
        Sravs

        Hi Celvin,

        Are there any examples available for a MDX Query to do a Export ASO Cube data. Specially when each dimension export is at different hierarchy like one dimension at level zero , other at a particular member.

  • Reply ↓
    Ashok Ballamudi

    Hi,

    I am exporting three individual data files by using single calc script.Now I want to export three data files into single data file.

    If i give same name to three data files in the script, Will it override the data for previous data.

    Please help me on this issue.

    Thanks,
    Ashok B