Export Data (Aggregate Storage)

Click here for non-aggregate storage version

Export level-0 data, which does not include calculated values, from an aggregate storage database. Export data files are written to Essbase Server in the ARBORPATH/app directory, unless an absolute path is specified. To use Report Writer, export the data using a report file. Export data files cannot be written to the client computer.

Minimum permission required: Read.

Syntax

Syntax diagram for export database (aggregate storage).DBS-NAMEFILE-NAME

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

KeywordDescription

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 /home/exportfile.txt, the next additional file is /home/exportfile_1.txt. If the file name is /home/exportfile, the next additional file is /home/exportfile_1.

Example

Example 1

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

export database ASOsamp.Sample data to data_file 'exportfile.exp';

Example 2

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

export database ASOsamp.Sample using report_file 'C:\\inst2\\Essbase\\EssbaseServer\\app\\ASOsamp\\Sample\\Bottom.rep' to data_file 'c:\\temp\\Bottom.rpt';

Example 3

The following example is a UNIX version of Example 2.

export database ASOsamp.Sample using report_file "'$ARBORPATH/app/ASOsamp/Sample/Bottom.rep'" to data_file 'Bottom.rpt';

Sample Report Script and Output

For examples 2 and 3, assume that Bottom.rep is the following report script file based on ASOsamp.Sample:

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