You can use the following calculation commands to select and format a text import file: DATAEXPORT, DATAEXPORTCOND, SET DATAEXPORTOPTIONS, FIX...ENDFIX, and EXCLUDE...ENDEXCLUDE. For general information about creating and running a calculation script, see Developing Calculation Scripts for Block Storage Databases.
Calculation script-based data export works with stored and dynamically calculated members only and provides fewer formatting options than report scripts. However, calculation script-based data exports provide decimal- and precision-based formatting options and can be faster than report scripts. The DATAEXPORT calculation command also enables export directly to relational databases, eliminating the usual intermediate import step.
The following calculation script example produces a text file that contains a subset of the database.
SET DATAEXPORTOPTIONS { DATAEXPORTLEVEL "ALL"; DATAEXPORTCOLFORMAT ON; DATAEXPORTCOLHEADER Scenario; }; FIX ("100-10","New York","Actual","Qtr1"); DATAEXPORT "File" "," "C:\exports\actual.txt" "NULL"; ENDFIX;
These commands specify inclusion of all levels of data and indicate that data is to be repeated in columns, with the Scenario dimension set as the dense dimension column header for the output. The FIX command defines the data slice, and then the data is exported to a text file at C:\exports\actual.txt. Commas are used as delimiters, and missing data values are indicated by consecutive delimiters. Running this script against Sample.Basic generates the following data:
"Actual" "100-10","New York","Sales","Qtr1",1998 "100-10","New York","COGS","Qtr1",799 "100-10","New York","Margin","Qtr1",1199 "100-10","New York","Marketing","Qtr1",278 "100-10","New York","Payroll","Qtr1",153 "100-10","New York","Misc","Qtr1",2 "100-10","New York","Total Expenses","Qtr1",433 "100-10","New York","Profit","Qtr1",766 "100-10","New York","Opening Inventory","Qtr1",2101 "100-10","New York","Additions","Qtr1",2005 "100-10","New York","Ending Inventory","Qtr1",2108 "100-10","New York","Margin %","Qtr1",60.01001001001001 "100-10","New York","Profit %","Qtr1",38.33833833833834 "100-10","New York","Profit per Ounce","Qtr1",63.83333333333334
For information about DATAEXPORT calculation commands, see the Oracle Essbase Technical Reference.