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"Note that DATAEXPORT to binary files is not supported across Essbase releases or between 32-bit and 64-bit operating systems.
For direct export to a relational database using ODBC:
DATAEXPORT "DSN" "dsnName" "tableName" "userName" "password"
| Parameter | Description |
|---|---|
"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"
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 Name of the table where the exported data is to be inserted. The table must exist, and table and column names cannot contain spaces. 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. |
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.
If outputting a file, and fileName:
Does not include a path, the file is written in the application directory.
Includes a path, Essbase interprets the path in context to the server. Export files cannot be written to a client.
When using DATAEXPORT "DSN" to export data for direct insertion to a relational database:
You can use the DATAEXPORTENABLEBATCHINSERT configuration setting to enable the batch insert method, which is faster than the default row-insert method. With batch insert, Essbase determines the batch size, but you can use the DEXPSQLROWSIZE configuration setting to specify the number of rows (from 2 to 1000) to be batch inserted. Essbase inserts the rows when the specified batch size is reached.
The table to which the data is to be written must exist prior to data export.
Table and column names cannot contain spaces.
Note: | 64-bit Essbase does not support using the DATAEXPORT batch-insert method to export data directly into a SQL data source. |
For information on configuring ODBC DSNs, refer to “Configuring Data Sources” in the Oracle Essbase SQL Interface Guide:
Windows—Follow the instructions in “Configuring Data Sources on Windows”.
UNIX—Follow steps 1 and 2 in “Configuring Data Sources on UNIX”. Ignore steps 3 and 4 and, instead, set the ODBCINI environment variable to the location of the odbc.ini file.
Note: | Anytime you make changes to odbc.ini, you must restart Essbase. |
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.
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.
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 Oracle Essbase Database Administrator's Guide.
Example
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.
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.
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.
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";
};
FIX("100-10","New York","Actual","Sales");
DATAEXPORT "DSN" "cur_sale" "newyork" "admin" "password";
ENDFIX;Inserts the selected records directly to the table named newyork. By default, Essbase inserts exported data row-by-row. If the DATAEXPORTENABLEBATCHINSERT configuration setting is set to TRUE in essbase.cfg, records are batch inserted. To control the number of rows that are batch inserted at a time, use the DEXPSQLROWSIZE configuration setting in conjunction with DATAEXPORTENABLEBATCHINSERT set to TRUE.
See Also