Essbase - Export Data (Post 1 of 3)

There are a couple ways to export data from an Essbase cube, the old-school way and now in version 9.3, there is the calculation function "DATAEXPORT". Let's discuss in detail:

Export Data (the capability that has been around all along):

* For Block Storage Applications, you can export all data, level-0 data or input-level data. The 'all data' option is nice, but that assumes your hierarchies don't change or level-0 member doesn't get remapped to a new Level-1 member. So, if you hierarchies don't change and it takes too long to aggregate (CALCALL) your cube, then the 'all data' option is the way to go. If your hierarchies change often, then you need your level-0 option (this is the option I prefer to use most of the time as the hierarchies will change at some point in time and its easier to reload based on level-0 then all data. I rarely use the input-level data, as my input data is usually my level-0 data.

* You can export the data into column format or non column format. This option works well depending on what you are trying to accomplish. Column format files will be larger in size than non column format files, but you can use a load rule to load that data into another cube. Say you have to make some sort of data manipulation to get that data into your new cube, it would be advantageous to use a load rule and perform your data manipulation. If you want to simply update your hierarchies, then you can use the non-column format option (which is not pretty), but the file size is smaller. Smaller files size will mean faster export time and faster import time, but you can't you use a load rule for any data manipulation.

* You can export to multiple files at one time, like this:


/*--------------------------------------------------------------------------*/
/* EXPORT DATABASE TO 8 FILES */
/*--------------------------------------------------------------------------*/
export database Sample.Basic level0 data in columns
to data_file 'C:\Temp\ExportData1.0.Txt',
'C:\Temp\ExportData2.0.Txt',
'C:\Temp\ExportData3.0.Txt',
'C:\Temp\ExportData4.0.Txt',
'C:\Temp\ExportData5.0.Txt',
'C:\Temp\ExportData6.0.Txt',
'C:\Temp\ExportData7.0.Txt',
'C:\Temp\ExportData8.0.Txt';

* When you run the level-0 export, you will get upper level data on all dense dimensions, don't be alarmed, its just how the functionality works, even if the member properties are set as dynamic calcs

* The export function will NOT export any values for Dynamic Calc members, only Stored members (unless its a dense dimension)

* In version 9 (not sure which release), but users are able to retrieve on the database during the export process. Previous versions used to lock the database from any Read-Only action.