Exporting Data Using the DATAEXPORT Command

The DATAEXPORT command enables calculation scripts to export data in binary or text, or directly to a relational database. A set of data-export-related calculation commands qualify what data to export and provide various output and formatting options.

Note:

DATAEXPORT to binary files is not supported across Essbase releases or between 32-bit and 64-bit operating systems.

The following command sequence shows the typical calculation script structure for exporting data:

SET DATAEXPORTOPTIONS
  {
    DATAEXPORTLEVEL parameters;
    DATAEXPORTDYNAMICCALC ON | OFF;
    DATAEXPORTNONEXISTINGBLOCKS ON | OFF;
    DATAEXPORTDECIMAL n;
    DATAEXPORTPRECISION n;
    DATAEXPORTCOLFORMAT ON | OFF;
    DATAEXPORTCOLHEADER dimensionName;
    DATAEXPORTDIMHEADER ON | OFF;
    DATAEXPORTRELATIONALFILE ON | OFF;
    DATAEXPORTOVERWRITEFILE ON | OFF;
    DATAEXPORTDRYRUN ON | OFF;
   };
DATAEXPORTCOND parameters;
FIX 
  (fixMembers)
  DATAEXPORT parameters;
ENDFIX;

The following tables list the SET DATAEXPORTOPTIONS commands, which are all optional:

Table 86. SET DATAEXPORTOPTIONS Commands: Content Options

CommandCalculation
DATAEXPORTLEVEL

Specify a data value: ALL, LEVEL0, or INPUT

In specifying the data value for the DataExportLevel option, use these guidelines:

  • The values are case-insensitive. For example, you can specify LEVEL0 or level0.

  • Enclosing the value in quotation marks is optional. For example, you can specify LEVEL0 or “LEVEL0”.

  • If the value is not specified, Essbase uses the default value of ALL.

  • If the value is incorrectly expressed (for example, LEVEL 0 or LEVEL2), Essbase uses the default value of ALL.

DATAEXPORTDYNAMICCALCControl export of dynamically calculated values
DATAEXPORTNONEXISTINGBLOCKSSpecify whether to export data from all potential data blocks or only from existing data blocks
DATAEXPORTDECIMALSpecify the number of decimal positions in the exported values
DATAEXPORTPRECISIONSpecify the total number of positions in the exported values

Table 87. SET DATAEXPORTOPTIONS Commands: Output Format Options

CommandCalculation
DATAEXPORTCOLFORMATSpecify columnar or noncolumnar format
DATAEXPORTCOLHEADERSpecify a dense dimension for the column header
DATAEXPORTDIMHEADERInclude a header record that lists all dimension names in the same order as the data in the file
DATAEXPORTRELATIONALFILEFormat the text export file for importing the data into a relational database

Table 88. SET DATAEXPORTOPTIONS Commands: Processing Options

CommandCalculation
DATAEXPORTOVERWRITEFILESpecify whether an existing file with the same name and location is replaced
DATAEXPORTDRYRUNEnable validating the set of calculation commands and viewing export statistics—including a time estimate—without having to perform the entire export process

  To develop a calculation script that exports a subset of data:

  1. Specify the SET DATAEXPORTOPTIONS command to define options for export content (see Table 86, SET DATAEXPORTOPTIONS Commands: Content Options), format (see Table 87, SET DATAEXPORTOPTIONS Commands: Output Format Options), and process (see Table 88, SET DATAEXPORTOPTIONS Commands: Processing Options).

  2. Use a DATAEXPORTCOND command to select data based on data values.

  3. Use FIX...ENDFIX or EXCLUDE...ENDEXCLUDE calculation commands to select a slice of the database to be exported.

  4. Within the FIX...ENDFIX or EXCLUDE...ENDEXCLUDE group, include the DATAEXPORT command.

    If you are using the DATAEXPORT command to insert the exported data directly into a relational database, see Exporting Data into a Relational Database.

  5. Use the DATAIMPORTBIN calculation command to import a previously exported binary export file.

    Note:

    DATAIMPORTBIN is not supported across Essbase releases or between 32-bit and 64-bit operating systems.

    The SET DATAIMPORTIGNORETIMESTAMP calculation command enables you to manage the import requirement for a matching outline timestamp.

Other export methods include using ESSCMD, MaxL, and Administration Services Console for database backup. Report Writer can be used to select and format a database subset, creating an output text file (see Exporting Text Data Using Report Scripts).