Copying Data Subsets and Exporting Data to Other Programs

In This Section:

Process for Creating a Database Subset

Exporting Data to be Input to Other Programs

Process for Creating a Database Subset

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).

  1. On Essbase Server 2, create an application and database to contain the database subset.

    See Create an Application and Database.

  2. 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.

    See Copy the Outline File from the Source Database.

  3. 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.

  4. Load the output file into the new database that you have created.

    See Load the Output File into the New Database.

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

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.

  To create the application and database, see “Creating Applications” and “Creating Databases” in the Oracle Essbase Administration Services Online Help.

Ensure that the new, empty database is not running.

  To stop a database, see “Stopping Databases” in the Oracle Essbase Administration Services Online Help.

Copy the Outline File from the Source Database

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:

    Tool

    Topic

    Location

    Administration Services

    Copying Outlines

    Oracle Essbase Administration Services Online Help

    MaxL

    create database

    Oracle Essbase Technical Reference

    ESSCMD

    COPYDB

    Oracle Essbase Technical Reference

  • If you can not connect, use the operating system to copy the outline file.

    1. Use the operating system to copy the source outline file; for example, copy basic.otl to westmkts.otl.

    2. Give the copied outline exactly the same name as the new database.

    3. 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.

      Note:

      Ensure that the new outline file overwrites the existing, empty outline file, which Essbase created automatically when you created the new application and database.

    4. 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 Containing the Required Data Subset

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.

  To create an output file for a subset of a database, use a tool:

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:

  1. Select the source database. For example, select West Westmkts.

    See “Navigating and Selecting Objects” in the Oracle Essbase Administration Services Online Help.

    • If you can connect to the Essbase Server 1 database from the Essbase Server 2 computer, you can select the source database from Essbase Server 2.

    • If you cannot connect, use a different computer from the Essbase Server 2 computer to select the source database.

  2. Create a report.

    See “Creating Scripts” in the Oracle Essbase Administration Services Online Help.

  3. 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 Measures
    • Use TABDELIMIT to place tab stops between data, instead of spaces, to ensure that no member names or data values are truncated.

    • Use QUOTEMBRNAMES to place quotation marks (" ") around member names that contain blank spaces. Essbase then recognizes the member names when it loads the data.

  4. Execute the report script.

    See “Executing Report Scripts” in the Oracle Essbase Administration Services Online Help.

  5. 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

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:

  1. Select the new database. For example, select Westmkts.

  2. Start the data load using the text file you have just created, for example, westout.

    Note:

    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.

Exporting Data to be Input to Other Programs

  To export data from a database to a format that can be read by programs other than Essbase, use a tool:

Tool

Topic

Location

Spreadsheet Add-in Query Designer

Applying Queries

Oracle Essbase Spreadsheet Add-in User's Guide

Smart View

“Working with Query Designer”

Oracle Hyperion Smart View for Office User's Guide

Calculation scripts

Exporting Data Using the DATAEXPORT Command

Oracle Essbase Database Administrator's Guide

Report scripts

Exporting Text Data Using Report Scripts

Oracle Essbase Database Administrator's Guide

Note:

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.

Exporting Text Data Using Report Scripts

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
    !

Resulting report:

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
     !

Resulting report:

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.

Exporting Text Data Using Calculation Scripts

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.