Export Data (Aggregate Storage)

The MaxL export data statement for ASO mode helps you export data from an Essbase aggregate storage database.

Click here for non-aggregate storage version

Use this statement to export level-0 data, which does not include calculated values, from an aggregate storage cube. Export data files are written to the cube directory, unless an alternate path is specified using FILEGOVPATH configuration. To use Report Writer, export the data using a report file.

Minimum permission required: Database Access.

Syntax

Description of the illustration export_data_as.gif

• DBS-NAME

• FILE-NAME

On aggregate storage databases, use export data to export in the following ways:

Keywords

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

Export level-0 input data to a text file. You cannot export aggregates, upper level data, or data from dynamically calculated members.

Note:

Exporting data does not clear the data from the database.

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

This statement performs the same action as export database <dbs-name> level0 data....

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 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.

• Exports on aggregate storage databases are limited as follows:

  • You can export level-0 data only (level-0 data is the same as input data in aggregate storage databases).

  • You cannot perform upper-level data export on an aggregate storage database.

  • You cannot perform 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. This number should generally be equal to the number of processors on the machine that you wish to commit to doing parallel export. 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. In this case, the export file name will be the first file name given as input.

  • During a data export, the export process allows users to connect and perform read-only operations.

  • 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.

Example

Example 1

The following example exports all level 0 data from ASOsamp.Basic to an export file.

export database ASOsamp.Basic data to server data_file 'myfilesamp.txt';

The export location is the cube directory (ASOSamp/Basic/myfilesamp.txt), or whichever Essbase Server directory the administrator has specified for FILEGOVPATH.

Example 2

The following example uses a report script, Bottom.rep, to export a subset of sorted data from ASOsamp.Basic to an output file, Bottom.rpt.

export database ASOsamp.Basic using report_file 'Bottom.rep' to data_file 'Bottom.rpt';

Sample Report Script and Output

For example 2, assume that Bottom.rep is the following report script file based on ASOsamp.Basic:

//Bottom.rep
<Sym
<Column (Measures, Years)
<Row (Geography, Products)
<ICHILDREN Geography
<ICHILDREN Products
<Bottom (3, @DataColumn(1))
    !

The report script produces the following report (Bottom.rpt):

Measures Years Time Transaction Type Payment Type Promotions Age Income Level Stores 

North East       All Merchandise  43,250,241 
                   Products       43,250,241 
                 High End Mercha~ 11,379,402 
South            All Merchandise  32,790,838 
                   Products       32,790,838 
                 High End Mercha~ 8,436,598 
  Geography      All Merchandise  76,041,079 
                   Products       76,041,079 
                 High End Mercha~ 19,816,000