30 Exporting Data
You can export data from a Essbase database to move data between cubes, or for backup and migration purposes.
Topics:
To export whole cubes from the Essbase web interface to a workbook format, see Export a Cube to an Application Workbook.
See also Insert and Export Data with MDX.
Exporting Data Using MaxL
You can export data from an Essbase database using the export data MaxL statement. Data can be export serially or in parallel. If the data for a thread exceeds 2 GB, Essbase may divide the export data into multiple files with numbers appended to the file names.
-
Block storage databases: You can export all data, level-0 data, or input-level data, which does not include calculated values.
To export data in parallel, specify a comma-separated list of export files, up to a maximum of 1024 file names. The number of file names determines the number of export threads. The number of available block-address ranges limits the number of export threads that Essbase actually uses. Essbase divides the number of actual data blocks by the specified number of file names (export threads). If there are fewer actual data blocks than the specified number of export threads, the number of export threads that are created is based on the number of actual data blocks. This approach results in a more even distribution of data blocks between export threads.
-
Aggregate storage databases: You can export only level-0 data, which does not include calculated values. (Level-0 data is the same as input data in aggregate storage databases.) You cannot perform upper-level data export or columnar export on an aggregate storage database.
To export data in parallel, specify a comma-separated list of export files, from 1 to 8 file names. The number of threads Essbase uses typically depends on the number of file names you specify. However, on a very small aggregate storage database with a small number of data blocks, it is possible that only a single file will be created (in effect, performing serial export), even though parallel export to multiple files is requested.
Export files are stored in the database directory in the file catalog; for example, Files > applications > Sample > Basic.
Exporting Text Data Using Calculation Scripts
You can use the following calculation commands to select and format a text import file:
- DATAEXPORT
- DATAEXPORTCOND
- SET DATAEXPORTOPTIONS
- FIX...ENDFIX
- EXCLUDE...ENDEXCLUDE
Calculation script-based data export works with stored and dynamically calculated members only and provides fewer formatting options than report scripts. However, calculation script-based data exports provide decimal- and precision-based formatting options and can be faster than report scripts. The DATAEXPORT calculation command also enables export directly to relational databases, eliminating the usual intermediate import step.
The following calculation script example produces a text file that contains a subset of the database.
SET DATAEXPORTOPTIONS
{ DATAEXPORTLEVEL "ALL";
DATAEXPORTCOLFORMAT ON;
DATAEXPORTCOLHEADER Scenario;
};
FIX ("100-10","New York","Actual","Qtr1");
DATAEXPORT "File" "," "actual.txt" "NULL";
ENDFIX;These commands specify inclusion of all levels of data and indicate that data is to be repeated in columns, with the Scenario dimension set as the dense dimension column header for the output. The FIX command defines the data slice, and then the data is exported to a text file actual.txt in the database directory. Commas are used as delimiters, and missing data values are indicated by consecutive delimiters. Running this script against Sample.Basic generates the following data:
"Actual"
"100-10","New York","Sales","Qtr1",1998
"100-10","New York","COGS","Qtr1",799
"100-10","New York","Margin","Qtr1",1199
"100-10","New York","Marketing","Qtr1",278
"100-10","New York","Payroll","Qtr1",153
"100-10","New York","Misc","Qtr1",2
"100-10","New York","Total Expenses","Qtr1",433
"100-10","New York","Profit","Qtr1",766
"100-10","New York","Opening Inventory","Qtr1",2101
"100-10","New York","Additions","Qtr1",2005
"100-10","New York","Ending Inventory","Qtr1",2108
"100-10","New York","Margin %","Qtr1",60.01001001001001
"100-10","New York","Profit %","Qtr1",38.33833833833834
"100-10","New York","Profit per Ounce","Qtr1",63.83333333333334