Export Data

Click here for aggregate storage version

Using this statement, you can export all data, level-0 data, or input-level data, which does not include calculated values.

Essbase writes data export files to the cube directory, unless an alternate path is specified using FILEGOVPATH configuration. To use Report Writer, export the data using a report file. Export data files cannot be written to the client computer.

Minimum permission required: Database Access.

Syntax

Description of the illustration export.gif

You can export data from a database in the following ways using export data.

Keywords

export database <dbs-name> all data...

Export all data in the specified cube to the application/cube directory.

Note:

Exporting data does not clear the data from the database.

export database <dbs-name> level0 data...

Export level-0 data blocks only (blocks containing only level-0 sparse member combinations. Note that these blocks may contain data for upper level dense dimension members.) A level-0 block is created for sparse member combinations when all of the members of the sparse combination are at the bottom of dimension branches.

Note:

Exporting data does not clear the data from the cube.

export database <dbs-name> input data...

Export only blocks of data where the block contains at least one data value that was loaded (imported), rather than created as the result of a calculation.

export database <dbs-name> ... data in columns

Export data in columns, to facilitate loading the exported data into a relational database. In each row, the columnar format displays a member name from every dimension. Names can be repeated from row to row.

Columnar format provides a structure to the exported data, so that it can be used for further data processing by applications other than Essbase tools. In non-columnar format, sparse members identifying a data block are included only once for the block. Because the export file in non-columnar format is smaller than in columnar format, reloading a file in non-columnar format is faster.

export database <dbs-name> ... data anonymous

Export data in anonymized format. Anonymization removes the risk of sensitive data disclosure, and can be used in case sample data needs to be provided for technical support. Essbase replaces real data values with incremental values beginning with 0, increasing by 1 for each value in the block.

export database <dbs-name> ...using...report_file...

Run a stored report script, exporting a subset of the database.

Notes

This statement requires the database to be started.
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. For example, if the block storage database is very small, with only 100 data blocks, Essbase will use only 100 threads, even if you specify a higher number. This approach results in a more even distribution of data blocks between export threads.

Note:

In specifying the number of export files, it is important to consider the number of available CPU cores and I/O bandwidth on the computer on which Essbase Server runs. Specifying too large a number can result in poor performance.

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.

The naming convention for additional export files is as follows: _1, _2, etc. are appended to the additional file names. If the specified output file name contains a period, the numbers are appended before the period. Otherwise, they are appended at the end of the file name.

For example, if the given file name is exportfile.txt, the next additional file is exportfile_1.txt.
To export data in column format, use the optional "in columns" grammar.
During a data export, the export process allows users to connect and perform read-only operations.
When MaxL exports data from a Unicode-mode application, the export file is encoded in UTF-8. You cannot use UTF-8-encoded export files from a Unicode-mode application to import data to a non-Unicode-mode application.
MaxL cannot export databases with names containing hyphens (-).

Example

export database Sample.Basic data to server data_file 'myfilesamp.txt'

Exports data from the Sample Basic cube to Sample/Basic/myfilesamp.txt, or to whichever Essbase Server directory the administrator has specified for FILEGOVPATH.