You can move data between 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 Essbase database from OLAP 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:
This version of Essbase includes the ability to install both the Essbase server and client on a Windows NT or Windows 2000 workstation. 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 Essbase Installation Guide.
Once you have installed Personal Essbase, you can copy the outline file (dbname.OTL) and a data subset from the Essbase server and load them into Personal Essbase. The Personal Essbase server does not communicate with the OLAP Server.
Figure 491: Essbase Server and Personal Essbase Interaction
Note: Do not create more than one application and two databases on the Personal Essbase server.
To copy a database subset to Personal Essbase, complete these steps. The following sections provide detailed information on completing each step.
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.
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 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.
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.
To create the application and database:
Essbase creates the new application and 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 493: Stop Database Dialog Box
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 Essbase database from your Personal Essbase machine.
You can use the create database statement to perform this task. For more information, see the Technical Reference in the docs directory. |
|
You can use the COPYDB command in ESSCMD to perform this task. See the Technical Reference in the docs directory for information about this command. See Automating the Production Environment for information about ESSCMD. |
Complete these steps if you can connect to the source Essbase database from your Personal Essbase machine.
Figure 494: Sample Basic Outline
Figure 495: Save Server Object Dialog Box
The existing outline file is an empty file, which Essbase created automatically when you created the new application and database.
Complete these steps if you cannot connect to the source Essbase database from your Personal Essbase machine.
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 Essbase created automatically when you created the new application and database.
Before using the new outline file, you need to stop and restart the new database.
You now have a copy of the database outline on your Personal Essbase server.
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.
Note: You can also use the Spreadsheet Client Retrieval Wizard to create a spreadsheet file of the data subset. For more information on using the Retrieval Wizard, see the Essbase Spreadsheet Add-in User's Guides.
To create an ASCII text file that contains the required data subset:
Figure 496: Westmkts Database Selected in Application Manager
For example, the following report script selects the Actual, Measures data for the West market from Sample Basic:
Figure 497: Report Editor With Sample Basic Report Script
Figure 498: Report Output Options Dialog Box
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 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, Essbase saves the file on your Essbase client machine, and not on the 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.
Essbase displays the report in a window and sends it to the file you specified in the Report Output Options dialog box.
If you are not using your Personal Essbase machine, remember to download and copy the file from the 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.
Load the output file into the new database on your Personal Essbase machine.
Figure 500: Westmkts Database Selected in Application Manager
The Data Load dialog is box displayed.
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 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.
You can use report scripts to export 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 Essbase data into a program that requires special delimiters, use the MASK command. For the syntax and usage of Report Writer commands, see the Technical Reference in the docs directory.
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 example produces the following report:
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 example produces the following report:
See Examples of Report Scripts, for another example of formatting for data export.
Before you import data into some programs, you must delimit the data with specific characters. If you plan to import Essbase data into a program that requires special delimiters, use the MASK command. For the syntax and usage of Report Writer commands, see the Technical Reference in the docs directory.
You can use MaxL or ESSCMD to export data at the command line or from a script. For instructions, see Using Export to Back Up Data, or see the entries for the MaxL export data statement or the ESSCMD EXPORT command in the Technical Reference in the docs directory.
You can use Application Manager to export data.
Use this procedure to export data with Application Manager:
For more information, see the Application Manager online help.
![]() © 2002 Hyperion Solutions Corporation. All rights reserved. http://www.hyperion.com |