Specifies options for data export operations.
SET DATAEXPORTOPTIONS
{
DataExportLevel ALL | LEVEL0 | INPUT;
DataExportDynamicCalc ON | OFF;
DataExportDecimal n;
DataExportPrecision n;
DataExportColFormat ON | OFF;
DataExportColHeader dimensionName;
DataExportDimHeader ON | OFF;
DataExportRelationalFile ON | OFF;
DataExportOverwriteFile ON | OFF;
DataExportDryRun ON | OFF;
};
Each SET DATAEXPORTOPTIONS command specifies a set of option values that are in place until the next SET DATAEXPORTOPTIONS command is encountered. At that time, option values are reset to default, and newly specified option values are set.
The option list must start with a left brace ({) and end with a right brace followed by a semicolon (};). Each option ends with a semicolon (;). The options can be listed in any order. When an option is not specified, the default value is assumed.
The options are described here in three categories:
DataExportLevel ALL | LEVEL0 | INPUT
All (Default) All data, including consolidation and calculation results. Level 0 Data from level 0 data blocks only (blocks containing only level 0 sparse member combinations). Input Input blocks only (blocks containing data from a previous data load or spreadsheet Lock & Send). This option excludes dynamically calculated data. See also the DataExportDynamicCalc option.
Description
Specifies the amount of data to export.
DataExportDynamicCalc ON | OFF
ON (Default) Dynamically calculated values are included in the export. OFF No dynamically calculated values are included in the report. Description
Specifies whether a text data export excludes dynamically calculated data.
Notes:
- Text data exports only. If DataExportDynamicCalc ON is eencountered with a binary export (DATAEXPORT BINFILE …) it is ignored. No dynamically calculated data is exported.
- The DataExportDynamicCalc option does not apply to attribute values.
- If DataExportLevel INPUT is also specified and the FIX statement range includes sparse Dynamic Calc members, the FIX statement is ignored.
DataExportPrecision n
n Optional, default 16.
A value that specifies the number of positions in exported numeric data. If n < 0, 16-position precision is used.Description
Specifies that the DATAEXPORT calculation command will output numeric data with emphasis on precision (accuracy). Depending on the size of a data value and number of decimal positions, some numeric fields may be written in exponential format; for example, 678123e+008. You may consider using DataExportPrecision for export files intended as backup or when data ranges from very large to very small values. The output files typically are smaller and data values more accurate. For output data to be read by people or some external programs, you may consider specifying the DataExportDecimal option instead.
Notes:
Example
- By default, Essbase supports 16 positions for numeric data, including decimal positions.
- The DataExportDecimal option has precedence over the DataExportPrecision option.
SET DATAEXPORTOPTIONS { DataExportPrecision 6; DataExportLevel "ALL"; DataExportColHeader "Measures"; DataExportDynamicCalc ON; }; DATAEXPORT "File" "," "output1.out";Initial Data Load Values
"Sales" "COGS" "Margin" "Marketing" "Payroll" "Misc" "Total Expenses" "Profit" "Opening Inventory" "Additions" "Ending Inventory" "Margin %" "Profit %" "100-10" "New York" "Jan" "Actual" 678123456.0 271123456.0 407123456.0 941234567890123456.0 51123456.0 0 145123456.0 262123456.0 2101123456.0 644123456.0 2067123456.0 60123456.029 38123456.6430 "Feb" "Actual" 645123 258123 3871234 9012345 5112345 112345678 14212345 24512345 2067123456 61912345 20411234 601234 37123456.98 "Mar" "Actual" 675 270 405 94 51 1 146 259 2041 742 2108 60 38.37037037037037 "Qtr1" "Actual" 1998 799 1199 278 153 2 433 766 2101 2005 2108 60.01001001001001 38.33833833833834Exported Data Format
"Sales","COGS","Margin","Marketing","Payroll","Misc","Total Expenses","Profit","Opening Inventory","Additions","Ending Inventory","Margin %","Profit %","Profit per Ounce","100-10","New York"
"Jan","Actual",6.78123e+008,2.71123e+008,4.07e+008,9.41235e+017,5.11235e+007,0,9.41235e+017,-9.41235e+017,2.10112e+009,6.44123e+008,2.06712e+009,60.0186,-1.388e+011,-7.84362e+016
"Feb","Actual",645123,258123,387000,9.01235e+006,5.11235e+006,1.12346e+008,1.2647e+008,-1.26083e+008,2.06712e+009,6.19123e+007,2.04112e+007,59.9886,-19544.1,-1.05069e+007
"Mar","Actual",675,270,405,94,51,1,146,259,2041,742,2108,60,38.3704,21.5833
DataExportDecimal n
n A value between 0 and 16.
If no value is provided, the number of decimal positions of the data to be exported is used, up to 16 positions, or a value determined by the DataExportPrecision option if that is specified.Description
Specifies that the DATAEXPORT calculation command will output numeric data with emphasis on legibility; output data is in straight text format. Regardless of the number of decimal positions in the data, the specified number is output. It is possible the data can lose accuracy, particularly if the data ranges from very large values to very small values, above and below the decimal point.
Notes:
- By default, Essbase supports 16 positions for numeric data, including decimal positions.
- If both the DataExportDecimal option and the DataExportPrecision option are specified, the DataExportPrecision option is ignored.
Example
SET DATAEXPORTOPTIONS
{
DataExportDecimal 4;
DataExportLevel "ALL";
DataExportColHeader "Measures";
DataExportDynamicCalc ON;
};
DATAEXPORT "File" "," "output1.out";Initial Data Load Values
"Sales" "COGS" "Margin" "Marketing" "Payroll" "Misc" "Total Expenses" "Profit" "Opening Inventory" "Additions" "Ending Inventory" "Margin %" "Profit %" "100-10" "New York" "Jan" "Actual" 678123456.0 271123456.0 407123456.0 941234567890123456.0 51123456.0 0 145123456.0 262123456.0 2101123456.0 644123456.0 2067123456.0 60123456.029 38123456.6430 "Feb" "Actual" 645123 258123 3871234 9012345 5112345 112345678 14212345 24512345 2067123456 61912345 20411234 601234 37123456.98 "Mar" "Actual" 675 270 405 94 51 1 146 259 2041 742 2108 60 38.37037037037037 "Qtr1" "Actual" 1998 799 1199 278 153 2 433 766 2101 2005 2108 60.01001001001001 38.33833833833834Exported Data Format
"Sales","COGS","Margin","Marketing","Payroll","Misc","Total Expenses","Profit","Opening Inventory","Additions","Ending Inventory","Margin %","Profit %","Profit per Ounce" "100-10","New York" "Jan","Actual",678123456.0000,271123456.0000,407000000.0000,941234567890123520.0000,51123456.0000,0.0000,941234567941246980.0000,-941234567534246910.0000,2101123456.0000,644123456.0000,2067123456.0000,60.0186,-138799883591.4395,-78436213961187248.0000 "Feb","Actual",645123.0000,258123.0000,387000.0000,9012345.0000,5112345.0000,112345678.0000,126470368.0000,-126083368.0000,2067123456.0000,61912345.0000,20411234.0000,59.9886,-19544.0820,-10506947.3333 "Mar","Actual",675.0000,270.0000,405.0000,94.0000,51.0000,1.0000,146.0000,259.0000,2041.0000,742.0000,2108.0000,60.0000,38.3704,21.5833
DataExportColFormat ON | OFF
ON The data is output in columnar format. OFF Default. The data is output in non-columnar format. Description
Specifies if data is output in columnar format. Columnar format displays a member name from every dimension; names can be repeated from row to row, enabling use by applications other than Essbase tools. In non-columnar format, sparse members identifying a data block are included only once for the block. Non-columnar export files are smaller, enabling faster loading to an Essbase database.
Notes
- Do not use the DataExportColFormat option in combination with the DataExportRelationalFile option which already assumes columnar format for files destined as input files to relational databases.
Example
SET DATAEXPORTOPTIONS { DATAEXPORTCOLFORMAT ON; }; FIX("100-10", Sales, COGS, Jan, Feb, Mar, Actual, Budget) DATAEXPORT "File" "," "d:\temp\test2.txt" ; ENDFIX;
DataExportColHeader dimensionName
Description
Specifies the name of the dense dimension that is the column header (the focus) around which other data is referenced in the export file. Use the DataExportColHeader option only when you export data to a text file. For example, if from Sample Basic the Year dimension is specified, the output data starts with data associated with the first member of the Year dimension: Year. After all data for Year is output, it continues with the second member: Qtr1, and so on.
Notes
- MaxL, ESSCMD, and Essbase exports do not provide a similar capability. With these methods, Essbase determines the focal point of the output data.
- Exporting through Report Writer enables you to specify the header in the report script.
Example
SET DATAEXPORTOPTIONS {DATAEXPORTCOLHEADER Scenario;};Specifies Scenario as the page header in the export file. The Scenario dimension contains three members: Scenario, Actual, and Budget. All Scenario data is shown first, followed by all Actual data, then all Budget data.
DataExportDimHeader ON | OFF
ON The header record is included. OFF Default. The header record is not included. Description
Use the DataExportDimHeader option to insert the optional header record at the beginning of the export data file. The header record contains all dimension names in the order as they are used in the file. SSpecifyingthis command always writes the data in "column format".
Example
SET DATAEXPORTOPTIONS { DATAEXPORTLEVEL "ALL"; DATAEXPORTDIMHEADER ON; };
FIX("100-10", "New York", "Actual") DATAEXPORT "File" "," "E:\temp\2222.txt" ; ENDFIX;Specifying the DataExporttDimHeader ON option while exporting Sample Basic writes the data in column format, with common members repeated in each row. The data begins with a dimension header, as shown in the first two rows of the example file below:"Product","Market","Year","Scenario","Measures" "Sales","COGS","Marketing","Payroll","Misc","Opening Inventory","Additions","Ending Inventory" "100-10","New York","Jan","Actual",678,271,94,51,0,2101,644,2067 "100-10","New York","Feb","Actual",645,258,90,51,1,2067,619,2041 "100-10","New York","Mar","Actual",675,270,94,51,1,2041,742,2108 "100-10","New York","Apr","Actual",712,284,99,53,0,2108,854,2250 "100-10","New York","May","Actual",756,302,105,53,1,2250,982,2476 "100-10","New York","Jun","Actual",890,356,124,53,0,2476,1068,2654 "100-10","New York","Jul","Actual",912,364,127,51,0,2654,875,2617 "100-10","New York","Aug","Actual",910,364,127,51,0,2617,873,2580 "100-10","New York","Sep","Actual",790,316,110,51,1,2580,758,2548 "100-10","New York","Oct","Actual",650,260,91,51,1,2548,682,2580 "100-10","New York","Nov","Actual",623,249,87,51,0,2580,685,2642 "100-10","New York","Dec","Actual",699,279,97,51,1,2642,671,2614
DataExportRelationalFile ON | OFF
ON The output text export file is formatted for import to a relational database.
- Data is in column format; sparse member names are repeated. (The DataExportColFormat option is ignored.)
- The first record in the export file is data; no column heading or dimension header is included, even if specified. (The DataExportColHeader and DataExportDimHeader options are ignored.)
- Missing and invalid data is skipped, resulting in consecutive delimiters (commas) in the output. The optional "missing_char" parameter for DATAEXPORT is ignored
OFF Default. The data is not explicitly formatted for use as input to a relational database. Description
Using the DataExportRelationalFile option with DATAEXPORT enables you to format the text export file such that it can be used directly as an input file for a relational database.
Example
SET DATAEXPORTOPTIONS { DataExportLevel "ALL"; DataExportRelationalFile ON;
}; FIX (Jan) DATAEXPORT "File" "," c:\monthly\jan.txt ENDFIX;
DataExportOverwriteFile ON | OFF
ON The existing file with the same name and location is replaced. OFF Default. If a file with the same name and location already exists, no file is output. Description
Manages whether an existing file with the same name and location is replaced.
DataExportDryRun ON | OFF
ON DATAEXPORT and associated commands are run, without exporting data. OFF Default. Data is exported. Description
Enables running the calculation script data export commands to see information about the coded export, without exporting the data. When the DataExportDryRun option value is ON, the following information is written to the output file specified in the DATAEXPORT command:
- Summary of data export settings
- Info, Warning, and Error messages
- Exact number of blocks to be exported
- Estimated time, excluding I/O time.
Notes
- The DataExportDryRun option does not work with exports to relational databases.
- If you modify the script for reuse for the actual export, besides removing the DataExportDryRun option from the script you may want to change the name of the export file.
Example
SET DATAEXPORTOPTIONS { DataExportLevel "ALL"; DataExportColHeader "Measures"; DataExportColFormat ON; DataExportDimHeader ON; DataExportDynamicCalc OFF; DataExportDecimal 0; DataExportDryRun ON;
DataExportOverwriteFile ON; }; FIX("Qtr1") DATAEXPORT "File" "," "E:\temp\log.txt" ; ENDFIX;Creates the file"E:\temp\log.txt"containing the following information:<EXPORT_OPTIONS> <DELIMITER> , </DELIMITER> <MISSING_VALUE> #Mi </MISSING_VALUE> <EXPORT_LEVEL> ALL </EXPORT_LEVEL> <DYNAMIC_CALC_EXPORT> OFF </DYNAMIC_CALC_EXPORT> <COLUMN HEADER> Measures </COLUMN HEADER> <COLUMN_FORMAT> ON </COLUMN_FORMAT> <DIMENSION_HEADER_WRITE> ON </DIMENSION_HEADER_WRITE> <FILE_OVERWRITE> ON </FILE_OVERWRITE> <DECIMAL_POINT> 0N </DECIMAL_POINT> <PRECISION POINT> 16 </PRECISION_POINT> <RELATIONAL_EXPORT> OFF </RELATIONAL_EXPORT> </EXPORT_OPTIONS> <MESSAGE> <INFO> DataExport Warning: FIX statement contains Dynamic Calc member [Qtr1]. No Dynamic Calc members are exported with the DataExportDynamicCalc option set to OFF. </INFO> <INFO> Data Export Completed. Total blocks: [332]. Elapsed time: [3.846] secs. </INFO> </MESSAGE