Copying Data Subsets and Exporting Data to Other Programs Skip Navigation
Essbase® Analytic Services Database Administrator's Guide | Update Contents | Previous | Next | Print | ? |
Information Map

Copying Data Subsets and Exporting Data to Other Programs


You can move data between Analytic Services databases or to another program by extracting an output file of the data you want to move. For example, you can copy a subset of an Analytic Services database from an Analytic Server to Personal Essbase.

In order to meet the import format specifications of most other programs, use the Report Writer to create a text file.

This chapter contains information about the following topics:

Copying a Database Subset

You can install both the Analytic Server and client on a Windows NT or Windows 2000 workstation using Personal Essbase. Personal Essbase is a one-port license and has its own license number. For information about installing and configuring Personal Essbase on a computer, see the Essbase Analytic Services Installation Guide.

Once you have installed Personal Essbase, you can copy the outline file (dbname.otl) and a data subset from the Analytic Server and load them into Personal Essbase. The Personal Essbase server does not communicate with the Analytic Server.

Figure 207: Analytic Services and Personal Essbase Interaction

Note: Do not create more than one application and two databases on the Personal Essbase server.

Process for Creating a Database Subset

This section describes the process for copying a database subset to Personal Essbase.

  1. On the Personal Essbase server, create a new application and database to contain the database subset.

    See Creating a New Application and Database.

  2. Copy the outline file (for example, source_dbname.otl) from the source database to the new database on Personal Essbase.

    You may need to rename the outline file to match the name of the Personal Essbase database (for example, target_dbname.otl), overwriting the existing target database outline file.

    See Copying the Outline File from the Source Database.

  3. Create an output file (for example, an plain text file) containing the required data subset.

    See Creating an Output File Containing the Required Data Subset.

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

    For instructions, see Loading the Output File Into the New Database.

If required, you can repeat steps 3 and 4 to create an output file from the database on the Personal Essbase server and load the data back into the main Analytic Services 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 a Personal Essbase server and the West Westmkts database.

Creating a New Application and Database

Create a new application and database on the Personal Essbase server. 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 Essbase Administration Services Online Help.

Ensure that the new, empty database is not running.

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

Copying 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 to wesmkts.otl on Personal Essbase.

How you copy the outline file depends on whether you can connect to the source Analytic Services database from the Personal Essbase computer.

You now have a copy of the database outline on the Personal Essbase server.

Creating 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 using the Report Writer, see "Executing Report Scripts" in the Essbase Administration Services Online Help.

To create an output file using the Retrieval Wizard, see the Essbase Spreadsheet Add-in User's Guide.

The following example shows how to create a subset of the Westmkts database.

To create a text file that contains the required data subset, follow these steps:

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

    See "Navigating and Selecting Objects" in the Essbase Administration Services Online Help.

  2. Create a new report.

    See "Creating Scripts" in the Essbase Administration Services Online Help.

  3. Write a report script that selects the required data subset. For fundamental information on 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:

    Figure 208: Sample Basic Report Script

    {TABDELIMT}
    <QUOTEMBRNAMES
    Actual
    <IDESC West
    <IDESC Measures
    

  4. Execute the report script.

    See "Executing Report Scripts" in the 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 needs to be in the \ARBORPATH\App\appname\dbname directory on the Personal Essbase server, where ARBORPATH is the directory in which you installed Analytic Services, and appname and dbname are the new application and database directories that you have created.

    If you are using the Personal Essbase computer, you can save the output file directly into the \ARBORPATH\app\appname\dbname directory, for example, c:\essbase\app\west\westmkts\westout.txt.

    If you are not using the Personal Essbase computer, save the output file anywhere on the current computer. By default, Analytic Services saves the file on the Analytic Services 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 the Personal Essbase server. For example, use a disk to copy the file.

    If you are not using the Personal Essbase computer, remember to download and copy the file from the Analytic Server client directory to the \ARBORPATH\app\appname\dbname directory on the Personal Essbase server. For example, copy the output file to c:\essbase\app\west\westmkts\westout.txt.

You are now ready to load the text file into the new database.

Loading the Output File Into the New Database

Load the output file into the new database on the Personal Essbase computer.

To load a file into a database, see "Performing a Data Load or Dimension Build" in the 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 Creating an Output File Containing the Required Data Subset for instructions for naming an output file.

For detailed information on loading data and any errors that may occur, see Performing and Debugging Data Loads or Dimension Builds.

You can now view the data on the Personal Essbase computer. You might need to recalculate the database subset. Because you are viewing a subset of the database, a percentage of the data values will be #MISSING.

If required, you can copy report scripts and other object files to the Personal Essbase computer to use with the database subset you have created.

Exporting Data Using Report Scripts

You can use report scripts to export Analytic Services data to other programs in text format. Report Writer enables you to create text files that meet the import format specifications of most other programs.

For information about exporting databases using other methods, see Exporting Data.

Before you can import data into some programs, you must separate, or delimit, the data with specific characters.

If you plan to import Analytic Services data into a program that requires special delimiters, use the MASK command.

Note: You cannot export data generated by Dynamic Calc members. Because attributes are Dynamic Calc members, you cannot export data generated by attributes.

When you export data to a program that uses a two-dimensional, fixed-field format, you do not need to 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
    ! 
 

This script produces the following 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    
 


If you want to create a two-dimensional report that contains only bottom-level (level 0) data, use CHILDREN or DIMBOTTOM to select level 0 members.

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
     ! 
 

This script produces the following 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 an additional example of formatting for data export, see "Sample 12 on the Examples of Report Scripts" page in the "Report Writer Commands" section of the Technical Reference.

Importing Data Into Other Databases

Before you import data into some programs, you must delimit the data with specific characters. If you plan to import Analytic Services data into a program that requires special delimiters, use the MASK command.

Exporting Data

To export data from a database, use any of the following methods:


Tool
Topic
Location

Administration Services

Exporting Data

Essbase Administration Services Online Help

MaxL

export data

Technical Reference

ESSCMD

EXPORT

Technical Reference



Note: Export files from databases in Unicode-mode applications are in UTF-8 encoding.

For more information about exporting data, see Export Backups.



Hyperion Solutions Corporation link