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.
//ESS_LOCALE English_UnitedStates.Latin1@Binary
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
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…
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";
DATAEXPORT "File" "," "/Oracle/Batchscripts/Allocations/Data/AllocData.txt";
You'll have to change the file path to unix format, above is an example assuming that you have a folder called Oracle under /
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!
You won't be able to control unless you change the outline order. Or else you might have to look at some OS level scripting/load to an RDBMS and generate file from there.
Thanks for the reply Celvin, i dont want to change the order of outline as it is optimized. Could you please explain about OS level scripting?
Thanks again!
what would the best way to Automate the DataExport to run daily night and then flushing out the old DataExport files.
what do you mean by flushing out old files, DATAEXPORTOVERWRITEFILE ON; should overwrite the existing file. Are you getting multiple files while exporting?
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!
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.
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.
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