Copying Data Subsets and Exporting Data to Other Programs

You can move data between Hyperion Essbase OLAP Server 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 Hyperion Essbase database from the Hyperion Essbase 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 to Personal Essbase
• Using Report Scripts for Data Exporting

Copying a Database Subset to Personal Essbase

This version of Hyperion Essbase includes the ability to install both the Hyperion Essbase server and client on a Windows NT workstation or a Windows 95 or Windows 98 machine. This is a one-port license and has its own license number. For information about installing and configuring Personal Essbase on a machine, see the Hyperion Essbase Installation Guide.

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

Figure 41-1: Hyperion Essbase Server and Personal Essbase Interaction

Summary of Steps

To copy a database subset to Personal Essbase, complete these steps. The following sections provide detailed information on completing each step.

1. On your Personal Essbase server, create a new application and database to contain the database subset.
2. Copy the outline file (for example, source_dbname.OTL) from your source database to the new database on Personal Essbase.

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

3. Create an output file (for example, an ASCII text file) containing the required data subset.
4. Load the output file into the new database that you have created.

   See Introducing Data Loading for more information about loading data.

If required, you can repeat steps 3 and 4 to create an output file from the database on your Personal Essbase server and load the data back into the main Hyperion Essbase database on a different machine.

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.

Creating a New Application and Database

Create a new application and database on your Personal Essbase server. You will copy the required subset of data into this new database. You can give this application and database any name. In this example, you copy a subset of data for the West market, and name the application WEST.APP, and the database WESTMKTS.DB.

1. In Application Manager, choose File > New > Application to open the Create New Application dialog box. Type the new application name in the Application name text box. For example, type West.
2. Click OK.
3. In Application Manager, choose File > New > Database to open the Create New Database dialog box. Type the new database name in the Database name text box. For example, type Westmkts.
4. Click OK.

   Hyperion Essbase creates the new application and database.

   Figure 41-2: New West Application and Westmkts Database

   

Ensure that the new, empty database is not running. In Application Manager, select the new database and choose Application > Start/Stop from the main menu. A message box tells you if the database is running. If necessary, click Yes in the Stop database message box to stop the database.

Figure 41-3: Stop Database Dialog Box

Copying the Outline File from Your Source Database

Copy the outline file (.OTL) of your 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 Hyperion Essbase database from your Personal Essbase machine.

• If you can connect, use File > Save As in Application Manager to copy the outline. See Copying the Outline File Using Application Manager.
• If you cannot connect (for example, if your Personal Essbase machine is a laptop machine that has no network connection), use your operating system to copy the outline file. For more information, see Copying the Outline File Using the Operating System.

You can also use create database in MaxL or the COPYDB command in ESSCMD to perform this task. For more information, see the online Technical Reference in the DOCS directory.

Copying the Outline File Using Application Manager

Complete these steps if you can connect to the source Hyperion Essbase database from your Personal Essbase machine.

1. Connect to the source server.
2. In Application Manager, open the source outline. For example, open Sample Basic.

   Figure 41-4: Sample Basic Outline

   

3. Choose File > Save As from the Outline Editor menu to display the Save Server Object dialog box.

   Figure 41-5: Save Server Object Dialog Box

   

4. In the Server, Application, and Database list boxes, select the new application and database that you have just created on the Personal Essbase server.
5. Click OK in the Save Server Object dialog box to copy the outline file. At the prompt, click Yes to replace the existing WESTMKTS.OTL file. The existing outline file is an empty file, which Hyperion Essbase created automatically when you created the new application and database.
   • Hyperion Essbase might prompt you to unlock and overwrite the file. If necessary, click Yes at the prompts.
   • Hyperion Essbase might warn you that the outline file did not originate from the latest version on the server. If necessary, click Yes to continue.

Copying the Outline File Using the Operating System

Complete these steps if you cannot connect to the source Hyperion Essbase database from your Personal Essbase machine.

1. 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.
2. Save the outline file in the\ARBORPATH\APP\appname\dbname directory on your Personal Essbase machine, where ARBORPATH is the directory in which your installed Hyperion 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 C:\ESSBASE\APP\WEST\WESTMKTS\WESTMKTS.OTL on your Personal Essbase machine. 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 Hyperion Essbase created automatically when you created the new application and database.

3. Before using the new outline file, you need to stop and restart the new database. Select the new database in Application Manager. For example, select Westmkts.
4. Choose Application > Start/Stop from the main menu to stop the new database, and choose Application > Start/Stop again to restart the database.

You now have a copy of the database outline on your 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 an ASCII text file, or a spreadsheet file. You can use the Report Writer to create an ASCII text file of the data subset. For example, use <IDESCENDANTS to select a data subset.

1. In Application Manager, select the source database. For example, select West Westmkts.

   Figure 41-6: Westmkts Database Selected in Application Manager

   

   • If you can connect to your main Hyperion Essbase database from your Personal Essbase machine, you can select the source database from your Personal Essbase machine.
   • If you cannot connect, use a different machine from your Personal Essbase machine to select the source database.
2. Click the Report icon, , and then click New to open the Report Editor.
3. Write a report script that selects the required data subset. For information on writing report scripts, see Quick Start to Report Scripts and the online Technical Reference in the DOCS directory.

   For example, the following report script selects the Actual, Measures data for the West market from Sample Basic:

   Figure 41-7: Report Editor With Sample Basic Report Script

   

   • Use TABDELIMIT to place tab stops between data, instead of spaces. This ensures that no member names or data values are truncated.
   • Use QUOTEMBRNAMES to place quotation marks ("") around member names that contain blank spaces. Hyperion Essbase then recognizes the member names when it loads the data.
4. In the Report Editor, choose Report > Output Options to open the Report Output Options dialog box. Check the File and Window check boxes.

   Figure 41-8: Report Output Options Dialog Box

   

5. Give the report output file any name 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 your Personal Essbase server, where ARBORPATH is the directory in which you installed Hyperion Essbase, and appname and dbname are the new application and database directories that you have created.

   If you are using your Personal Essbase machine, you can save the output file directly into the\ARBORPATH\APP\appname\dbname directory. For example, type C:\ESSBASE\APP\WEST\WESTMKTS\WESTOUT.TXT in the File text box.

   If you are not using your Personal Essbase machine, save the output file anywhere on the current machine. By default, Hyperion Essbase saves the file on your Hyperion Essbase client machine, and not on the Hyperion Essbase server. When you run the report, use your operating system to copy the file to the\ARBORPATH\APP\appname\dbname directory on your Personal Essbase server. For example, use a disk to copy the file.

6. Click OK in the Report Output Options dialog box.
7. Save your report script and choose Report > Run from the Report Editor menu to run the report.

   Hyperion Essbase displays the report in a window and sends it to the file you specified in the Report Output Options dialog box.

   Figure 41-9: Sample Basic Report Script Output

   

If you are not using your Personal Essbase machine, remember to download and copy the file from the Hyperion Essbase client directory to the\ARBORPATH\APP\appname\dbname on your 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 your new database.

Loading the Output File Into the New Database

Load the output file into the new database on your Personal Essbase machine.

1. In Application Manager on your Personal Essbase server, select the new database. For example, select Westmkts.

   Figure 41-10: Westmkts Database Selected in Application Manager

   

2. Choose Database > Load Data from the Application Manager main menu.

   The Data Load dialog is box displayed.

3. Click Find to open the Open Server Data File Objects dialog box.
   • In the Objects list box, select the ASCII text file you have just created. For example, select WESTOUT.
   • Click OK.

Figure 41-11: Data Load Dialog Box with WESTOUT Selected

Click OK in the Data Load dialog box to load the text file into the new database. For detailed information on loading data and any errors that may occur, see Introducing Data Loading.

You can now view the data on your Personal Essbase machine. 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 your Personal Essbase machine to use with the database subset you have created.

Using Report Scripts for Data Exporting

You can use report scripts to export Hyperion Essbase 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.

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

If you plan to import Hyperion Essbase data into a program that requires special delimiters, use the MASK command. For the syntax and usage of Report Writer commands, see the online Technical Reference in the DOCS directory.

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

• To list only level 0 data for specific members, use the CHILDREN command with the level 1 member as a parameter above the data you want to print.
• To list only level 0 data for a given member (including a dimension), use the DIMBOTTOM command as a parameter with the dimension name that contains the data 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
     !

This example 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

See Examples of Report Scripts for another example of formatting for data export.

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 Hyperion Essbase data into a program that requires special delimiters, use the MASK command. For the syntax and usage of Report Writer commands, see the online Technical Reference in the DOCS directory.

Copyright © 1991-2000 Hyperion Solutions Corporation. All rights reserved.