|
|
|
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.
| | | |
| - For Aggregate Storage Applications, you can only export level-o data in non-column format. Big difference here is that you can't export all data and you can't export in column format. So, you really can't use this export file unless you plan on putting it back into the same outline structure as it was exported from.
- You can export to multiple files just like you can with a Block Storage model (see above example)
- Prior to Version 9, ASO cubes did not have the capability to perform an export. Report scripts were your only option, ugh.
- Users are able to retrieve on the database during the export process, it does not lock the cube
Report Scripts: - You can export data using a report script. Prior to Version 9.3.1, this was the only means of getting a subset of data out of an Essbase database. Not a big fan of them, but if you are running a version prior to 9.3.1, then you need to use them.
/*--------------------------------------------------------------------------*/
/* EXPORT DATA USING REPORT SCRIPT */
/*--------------------------------------------------------------------------*/
export database ASOsamp.Sample using report_file "'$ARBORPATH/app/ASOsamp/Sample/XptData.rep'" to data_file 'C:\Temp\ReportExport.txt';
DATAEXPORT Calculation function: I have been working with Essbase for a long time and I wish this function came out a long time ago. - Only available in BSO applications as Calc Functions are not supported in ASO.
- You can export data to a text file while specifying the delimiter and file path/name (I have used this often since the release)
- You can export data to a binary file (haven't needed to go down this path yet, but looking forward to testing with it)
- You can export data to a relational database (like SQL Server) using an ODBC connection, be careful with this if you are using SQL Server 2005, had some issues about 1 year ago and the help desk really didn't help...but no issues with SQL Server 2000.
- The best part about this function is that it works like any other calculation function where you can FIX on any subset of data. So, if you only to fix on "Actual", for the Month of January, for a specific product..YOU CAN!!! The performance is very fast (assuming you have your dense/sparse settings set up properly)
- You have the ability to Export data based on certain conditions, like "Sales">500.
- Not only can you export using a Fix statement, you have ability to control the format of the text file (similar to report script functionality). Here are the options (I've used the ones in bold most often):
DataExportLevel ALL | LEVEL0 | INPUT;
DataExportDynamicCalc ON | OFF;
DataExportDecimal n;
DataExportPrecision n;
DataExportColFormat ON | OFF;
DataExportColHeader dimensionName;
DataExportDimHeader ON | OFF;
DataExportRelationalFile ON | OFF;
DataExportOverwriteFile ON | OFF;
DataExportDryRun ON | OFF;
| |
|