You can move data between Essbase databases or to other programs by extracting an output file of the data that you want to move. To meet the import format specifications of most other programs, create a text file using a report script or a calculation script.
This section describes the process for copying a database subset from one Essbase Server (Essbase Server 1) to another (Essbase Server 2).
On Essbase Server 2, create an application and database to contain the database subset.
Copy the outline file (for example, source_dbname.otl) from the source database on Essbase Server 1 to the new database on Essbase Server 2.
You may need to rename the outline file to match the name of the database Essbase Server 2 (for example, target_dbname.otl), overwriting the existing target database outline file.
Create an output file (for example, a plain text file) containing the required data subset.
See Create an Output File Containing the Required Data Subset.
Load the output file into the new database that you have created.
If required, repeat steps 3 and 4 to create an output file from the database on Essbase Server 2 and load the data back into the main Essbase database on a different computer.
The example in the following sections is based on the Sample.Basic database. The data subset in the example is the Actual, Measures data for the West market. The example copies the data subset to Essbase Server 2 and the West Westmkts database.
Create an application and database on Essbase Server 2. You will copy the required subset of data into this new database. You can give this application and database any name.
Copy the outline file (.otl) of the source database to the new database that you have created. In this example, you copy the basic.otl outline file from the Sample.Basic database and rename it wesmkts.otl on Essbase Server 2.
How you copy the outline file depends on whether you can connect to the source Essbase database from the Essbase Server 2 computer.
If you can connect, use any of the following methods to copy the outline:
If you can not connect, use the operating system to copy the outline file.
Use the operating system to copy the source outline file; for example, copy basic.otl to westmkts.otl.
Give the copied outline exactly the same name as the new database.
Save the outline file in the ARBORPATH/app/appname/dbname directory on the Essbase Server 2 computer, where ARBORPATH is the directory in which you installed Essbase, and appname and dbname are the new application and database that you have created.
For example, copy basic.otl to a disk, renaming it to westmkts.otl. Then copy westmkts.otl from the disk to the following directory on Essbase Server 2:
Oracle/Middleware/EPMSystem11R1/products/Essbase/EssbaseServer/app/west/westmkts/westmkts.otl
It is safe to overwrite the existing, empty westmkts.otl file.
Stop and restart the new database.
See “Starting Databases” and “Stopping Databases” in the Oracle Essbase Administration Services Online Help.
You now have a copy of the database outline on Essbase Server 2.
Create an output file that contains the required data subset. The output file can be a text file or a spreadsheet file. Use either of the following methods to create a data subset.
The following example uses Report Writer to create a subset of the Westmkts database. You can also use a report script to export a subset of data.
To create a text file that contains the required data subset:
Select the source database. For example, select West Westmkts.
See “Navigating and Selecting Objects” in the Oracle Essbase Administration Services Online Help.
See “Creating Scripts” in the Oracle Essbase Administration Services Online Help.
Write a report script that selects the required data subset. For information about writing report scripts, see Understanding Report Script Basics.
For example, the following report script selects the Actual, Measures data for the West market from Sample.Basic:
{TABDELIMT} QUOTEMBRNAMES Actual <IDESC West <IDESC MeasuresSee “Executing Report Scripts” in the Oracle Essbase Administration Services Online Help.
Save the report script with a .txt extension; for example, westout.txt.
To load the data, the output file must be in the ARBORPATH/app/appname/dbname directory on Essbase Server 2, where appname and dbname are the new application and database directories that you have created.
If you are using the Essbase Server 2 computer, you can save the output file directly into the ARBORPATH/app/appname/dbname directory; for example:
Oracle/Middleware/EPMSystem11R1/products/Essbase/EssbaseServer/app/west/westmkts/westout.txt
If you are not using the Essbase Server 2 computer, save the output file anywhere on the current computer. By default, Essbase saves the file on the Essbase client computer and not on the server. When you run the report, use the operating system to copy the file to the ARBORPATH/app/appname/dbname directory on Essbase Server 2. For example, use a disk to copy the file.
If you are not using the Essbase Server 2 computer, download and copy the file from the Essbase client directory to the ARBORPATH/app/appname/dbname directory on Essbase Server 2. For example, copy the output file to:
Oracle/Middleware/EPMSystem11R1/products/Essbase/EssbaseServer/app/west/westmkts/westout.txt
You are now ready to load the text file into the new database.
Load the output file into the new database on Essbase Server 2.
To load a file into a database, see “Performing a Data Load or Dimension Build” in the Oracle Essbase Administration Services Online Help.
The following example illustrates how to load data into the Westmkts database:
Start the data load using the text file you have just created, for example, westout.
If westout is not displayed, check that you gave it a .txt extension and placed it in the ARBORPATH/app/West/Westmkts directory. See Create an Output File Containing the Required Data Subset.
See Performing and Debugging Data Loads or Dimension Builds.
You can now view the data on the Essbase Server 2 computer. You might need to recalculate the database subset. Because you are viewing a subset of the database, a percentage of data values will be #MISSING.
If required, you can copy report scripts and other artifact files to the Essbase Server 2 computer to use with the database subset that you have created.
To export data from a database to move data between Essbase databases or to a format that can be read by programs other than Essbase, use a tool:
Tool | Topic | Location |
|---|---|---|
Smart View | “Working with Query Designer” | Oracle Smart View for Office User's Guide |
| MaxL | export data | Oracle Essbase Technical Reference |
Calculation scripts | Oracle Essbase Database Administrator's Guide | |
Report scripts | Oracle Essbase Database Administrator's Guide |
When you use MaxL, calculation scripts, or Administration Services Console to export text data, and the end fields of the last record have no values, those fields will not contain the missing value marker (such as #MI). To improve overall processing time, the fields are left blank in the last output record. |
Export files from databases in Unicode-mode applications are in UTF-8 encoding. If Essbase finds insufficient disk space for the export file, no data is exported.
If you plan to import Essbase data into a program that requires special delimiters, use the Report Writer MASK command or the delimiter parameter of the DATAEXPORT calculation command to specify the delimiter.
For more information about exporting data for backup purposes, see the Oracle Hyperion Enterprise Performance Management System Backup and Recovery Guide.
You can use report scripts or calculation scripts to export Essbase data in text format. Both tools enable you to create text files that meet the import format specifications of most other programs.
You can export data from an Essbase database using the export data MaxL statement. Data can be export serially or in parallel. If the data for a thread exceeds 2 GB, Essbase may divide the export data into multiple files with numbers appended to the file names.
Block storage databases: You can export all data, level-0 data, or input-level data, which does not include calculated values.
To export data in parallel, specify a comma-separated list of export files, up to a maximum of 1024 file names. The number of file names determines the number of export threads. The number of available block-address ranges limits the number of export threads that Essbase actually uses. Essbase divides the number of actual data blocks by the specified number of file names (export threads). If there are fewer actual data blocks than the specified number of export threads, the number of export threads that are created is based on the number of actual data blocks. This approach results in a more even distribution of data blocks between export threads.
Note:
In specifying the number of export files, it is important to consider the number of available CPU cores and I/O bandwidth on the computer on which Essbase Server runs. Specifying too large a number can result in poor performance.
Aggregate storage databases: You can export only level-0 data, which does not include calculated values. (Level-0 data is the same as input data in aggregate storage databases.) You cannot perform upper-level data export or columnar export on an aggregate storage database.
To export data in parallel, specify a comma-separated list of export files, from 1 to 8 file names. The number of threads Essbase uses typically depends on the number of file names you specify. However, on a very small aggregate storage database with a small number of data blocks, it is possible that only a single file will be created (in effect, performing serial export), even though parallel export to multiple files is requested.
Export files are stored in the ARBORPATH/app directory on the server unless an absolute path is specified.
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.
Report Writer provides extensive flexibility in selecting output data and formatting it to meet the needs of other programs. Report scripts work with all member types (for example: stored members, Dynamic Calc members, attributes, label-only members, shared members, and aliases.) However, report scripts generally are slower because they use query-based data extraction, which probes data blocks that are not materialized in the database. See “Executing Report Scripts” in the Oracle Essbase Administration Services Online Help and Executing Report Scripts. Report script-based export provides more flexibility in formatting the data and is ideal for generating professional-looking reports.
When you export data to a program that uses a two-dimensional, fixed-field format, you need not specify page or column dimensions. To create a two-dimensional report, you can specify every dimension as a row dimension. Use the ROWREPEAT command to add the name of each member specified to each row (rather than the default, nested style). The following script example and report illustrate this situation for a five-dimensional database:
<ROW (Year, Measures, Product, Market, Scenario)
{ROWREPEAT}
<ICHILDREN Year
Sales
<ICHILDREN "400"
East
Budget
!Qtr1 Sales 400-10 East Budget 900 Qtr1 Sales 400-20 East Budget 1,100 Qtr1 Sales 400-30 East Budget 800 Qtr1 Sales 400 East Budget 2,800 Qtr2 Sales 400-10 East Budget 1,100 Qtr2 Sales 400-20 East Budget 1,200 Qtr2 Sales 400-30 East Budget 900 Qtr2 Sales 400 East Budget 3,200 Qtr3 Sales 400-10 East Budget 1,200 Qtr3 Sales 400-20 East Budget 1,100 Qtr3 Sales 400-30 East Budget 900 Qtr3 Sales 400 East Budget 3,200 Qtr4 Sales 400-10 East Budget 1,000 Qtr4 Sales 400-20 East Budget 1,200 Qtr4 Sales 400-30 East Budget 600 Qtr4 Sales 400 East Budget 2,800 Year Sales 400-10 East Budget 4,200 Year Sales 400-20 East Budget 4,600 Year Sales 400-30 East Budget 3,200 Year Sales 400 East Budget 12,000
To create a two-dimensional report that contains only bottom-level (level 0) data, use CHILDREN or DIMBOTTOM to select level 0 members.
To list only level 0 data for specific members, use the CHILDREN command with the level 1 member as a parameter above the data that you want to print.
To list all level 0 data for the dimension to which a given member belongs, use the DIMBOTTOM command with any member in the dimension that contains the data that you want to print.
For example, the following script uses the CHILDREN command to select the children of Qtr1, which is a level 1 member, and the DIMBOTTOM command to select all level 0 data in the Product dimension.
<ROW (Year, Measures, Product, Market, Scenario)
{ROWREPEAT}
{DECIMAL 2}
<CHILDREN Qtr1
Sales
<DIMBOTTOM Product
East
Budget
!Jan Sales 100-10 East Budget 1,600.00 Jan Sales 100-20 East Budget 400.00 Jan Sales 100-30 East Budget 200.00 Jan Sales 200-10 East Budget 300.00 Jan Sales 200-20 East Budget 200.00 Jan Sales 200-30 East Budget #MISSING Jan Sales 200-40 East Budget 700.00 Jan Sales 300-10 East Budget #MISSING Jan Sales 300-20 East Budget 400.00 Jan Sales 300-30 East Budget 300.00 Jan Sales 400-10 East Budget 300.00 Jan Sales 400-20 East Budget 400.00 Jan Sales 400-30 East Budget 200.00 Feb Sales 100-10 East Budget 1,400.00 Feb Sales 100-20 East Budget 300.00 Feb Sales 100-30 East Budget 300.00 Feb Sales 200-10 East Budget 400.00 Feb Sales 200-20 East Budget 200.00 Feb Sales 200-30 East Budget #MISSING Feb Sales 200-40 East Budget 700.00 Feb Sales 300-10 East Budget #MISSING Feb Sales 300-20 East Budget 400.00 Feb Sales 300-30 East Budget 300.00 Feb Sales 400-10 East Budget 300.00 Feb Sales 400-20 East Budget 300.00 Feb Sales 400-30 East Budget 300.00 Mar Sales 100-10 East Budget 1,600.00 Mar Sales 100-20 East Budget 300.00 Mar Sales 100-30 East Budget 400.00 Mar Sales 200-10 East Budget 400.00 Mar Sales 200-20 East Budget 200.00 Mar Sales 200-30 East Budget #MISSING Mar Sales 200-40 East Budget 600.00 Mar Sales 300-10 East Budget #MISSING Mar Sales 300-20 East Budget 400.00 Mar Sales 300-30 East Budget 300.00 Mar Sales 400-10 East Budget 300.00 Mar Sales 400-20 East Budget 400.00 Mar Sales 400-30 East Budget 300.00
For another example of formatting for data export, see “Sample 12 on the Examples of Report Scripts” page in the “Report Writer Commands” section of the Oracle Essbase Technical Reference.