Essbase® Analytic Services Database Administrator's Guide | | Update Contents | Previous | Next | Print | ? | |
Information Map | |
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:
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.
This section describes the process for copying a database subset to Personal Essbase.
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 Creating an Output File Containing the Required Data Subset.
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.
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.
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.
Tool |
Topic |
Location |
---|---|---|
basic.otl
to westmkts.otl
.\
ARBORPATH
\App\
appname
\
dbname
directory on the Personal Essbase computer, where ARBORPATH is the directory in which you installed Analytic Services, 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 the Personal Essbase computer. 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 Analytic Services created automatically when you created the new application and database.
See "Starting Databases" and "Stopping Databases" in the Essbase Administration Services Online Help.
You now have a copy of the database outline on the Personal Essbase server.
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:
See "Navigating and Selecting Objects" in the Essbase Administration Services Online Help.
See "Creating Scripts" in the Essbase Administration Services Online Help.
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
See "Executing Report Scripts" in the Essbase Administration Services Online Help.
.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.
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:
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.
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:
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:
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.
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.
To export data from a database, use any of the following methods:
Tool |
Topic |
Location |
---|---|---|
Note: Export files from databases in Unicode-mode applications are in UTF-8 encoding.
For more information about exporting data, see Export Backups.
![]() |