Essbase DATAEXPORT（转）

DATAEXPORT

Writes data to a text file, binary file, or as direct input to a relational file using ODBC.

Syntax

For a text output file:
DATAEXPORT "File" "delimiter" "fileName" "missingChar"

For a binary output file:
DATAEXPORT "Binfile" "fileName"

For direct export to a relational database using ODBC:
DATAEXPORT "DSN" "dsnName" "tableName" "userName" "password"

"File" "Binfile" "DSN"	Required keyword for the type of output file. Specify the appropriate keyword, then use the associated syntax.
"delimiter"	Required for "File" exports The character that separates fields; for example, "," Do not use with "Binfile" or "DSN" exports
"fileName"	Required for "File" and "Binfile" exports Full path name for the export file. Do not use with "DSN" exports.
"missingChar"	Optional for output type "File" A text string to represent missing data values. Maximum length: 128 characters. "NULL" to skip the field, resulting in consecutive delimiters (such as ,,). Default value: #MI Do not use with "Binfile" or "DSN" exports, or in combination with the SET DATAEXPORTRELATIONALFILE command.
"dsnName"	Required for output type "DSN" The DSN name used to communicate with the SQL database. A substitution variable can be used.   Do not use with output type "File" or "Binfile".
"tableName"	Required for "DSN" exports The table name where the exported data is to be inserted.   Do not use with "File" or "Binfile" exports.
"userName"	Required for "DSN" exports The user name that is used when communicating with the database. A substitution variable can be used.   Do not use with "File" or "Binfile" exports
"password"	Required for "DSN" exports The password that is used when communicating with the database. A substitution variable can be used.   Do not use with "File" or "Binfile" exports

Description

The DATAEXPORT calculation command writes data into a text or binary output file, or connects directly to an existing relational database wherein the selected exported data is inserted.

Whereas both the MaxL export data statement and the ESSCMD EXPORT command can export all, level 0, or input data from the entire database as text data, the DATAEXPORT calculation command also enables you to:

• Use FIX…ENDFIX or EXCLUDE…ENDEXCLUDE calculations to select a slice of the database and use a DATAEXPORTCOND command to select data based on data values.
• Use parameters to qualify the type and destination of the export data.
• Use options provided by the SET DATAEXPORTOPTIONS command to refine export content, format, or process.
• Use the SET DATAIMPORTIGNORETIMESTAMP command to manage the import requirement for a matching outline timestamp.
• Use the DEXPSQLROWSIZE configuration setting to specify the number of rows to be collected in batches for insertion to an SQL database using DATAXPORT "DSN".

Using Report Writer to create an "export" file also provides extensive flexibility in selecting and formatting the data; however, using DATAEXPORT outputs the data more quickly. For information about using Report Writer to export data, see the Hyperion Essbase - System 9 Database Administrator's Guide.

Notes

• In general, specify SET commands within the calculation script to specify various options, and then use FIX…ENDFIX to refine data to be exported, including the DATAEXPORT command within the FIX…ENDFIX command set. Without FIX…ENDFIX, the entire database is exported.
• Some file management systems do not support text files larger than 2 GB. If the exported data exceeds 2 GB, Essbase creates multiple export files, as needed. An underscore and number is appended to the file names of the additional files, starting with _1. For example, if fileName is outfile.txt and three files are created, the resulting file names are outfile.txt, outfile_1.txt, and outfile_2.txt.
• If outputting a file, and:
  • If fileName does not include a path, the file is written in the application directory.
  • If fileName includes a path, Essbase interprets the path in context to the server. Export files cannot be written to a client.
• The export process does not begin if users are logged into the database. After the export process begins, the database is in read-only mode. Users can read the data but they cannot change it. After the export process is finished, Essbase returns the database to read-write mode and users can make changes to the data.
• Use the DATAIMPORTBIN command to import a previously exported binary export file.

Examples

Text Output File Examples

Example 1

SET DATAEXPORTOPTIONS 
  { 
  DataExportLevel "LEVEL0"; 
  };
DATAEXPORTCOND ("Sales">=1000);
FIX ("100-10","New York","Actual","Sales");
DATAEXPORT "File" "," "b:\exports\jan.txt" "#MI";
ENDFIX;

Specifies a level 0 data export level, limits output to data only with 1000 or greater Sales, fixes the data slice, then exports to a text file located at b:\exports\jan.txt, using comma (,) delimiters and specifying #MI for missing data values.

Example 2

SET DATAEXPORTOPTIONS 
  { 
  DataExportLevel "LEVEL0";
  DataExportRelationalFile ON; 
  };
DATAEXPORTCOND ("Sales">=1000);
FIX ("100-10","New York","Actual","Sales");
DATAEXPORT "File" "," "b:\exports\jan.txt";
ENDFIX;

Specifies the same export content as Example 1. However, the output file is formatted for input to a relational database. Notice the  missingChar parameter is intentionally excluded.

Binary Examples

Export

SET DATAEXPORTOPTIONS 
  { 
  DataExportLevel "ALL";
  };
FIX ("New York");
DATAEXPORT "BinFile" "b:\backup\newyork.bin";
ENDFIX;

Exports all New York blocks. Binary exports can be fixed only on sparse dimensions. Essbase uses the same bitmap compression technique to create the file as is used by Essbase Kernel.

Import

SET DATAIMPORTIGNORETIMESTAMP OFF;
DATAIMPORTBIN "b:\backup\newyork.bin"

Imports the previously exported file. The timestamp must match. The data is imported to the database on which the calculation script is executed. Because only data was exported, to recreate a database, after using DATAIMPORT to read in the data, you must recalculate the data.

Direct Input to Relational Database Example

SET DATAEXPORTOPTIONS 
  { 
  DataExportLevel "ALL"; 
  };