Data Warehouse Center Administration Guide

Chapter 9. Refreshing a OLAP Server database

You can use warehouse programs to automatically refresh a OLAP Server database. The Data Warehouse Center can help you schedule three types of operations with an OLAP Server:

To use the Data Warehouse Center to load data into the OLAP Server database:

  1. Using the Essbase Administration Manager, create the OLAP Server application and database. Make note of the application name, the database name, the user ID, and the password. You will need this information as input to a warehouse program.
  2. Using the Essbase Administration Manager, define the outline for the database.
  3. Define the data that you want to extract from the operational sources for OLAP Server to load into the Essbase database. You can use this data to update measures (for example, using the Essbase IMPORT command) and dimensions (for example, using the BuildDimension command).
  4. Define a step that extracts data from the operational data sources and builds the data as defined in step 3.
  5. Promote the step to test mode and run it at least once.
  6. Using the Essbase Administration Manager, write and test the load rules that will load the data sources into the Essbase database. Save the load rules into the database or as files on the warehouse agent site.

    You can also define calculation scripts to run after the data is loaded. Save the calculation scripts in files on the warehouse agent site.

    For information about defining load rules and calculation scripts, see the OLAP Server Database Administrator's Guide.

  7. Define a step that uses one of the warehouse programs for Hyperion Essbase, such as DB2 OLAP: Load data from flat file with load rules (ESSDATA2). Use the Process Model window to specify that the step that extracts data is to start this step.
  8. Promote the step to test mode and run it at least once.
  9. Define a schedule for the step that extracts data, and promote the step to production mode.
Figure 14 shows the data flow between the Data Warehouse Center and OLAP Server.

Figure 14. Data flow between the Data Warehouse Center and OLAP Server


Figure db2db348 not displayed.

For more information about completing this procedure, see the Data Warehouse Center online help and OLAP Server: Using OLAP Server.

Defining values for the OLAP Server: Default calc (ESSCALC1) warehouse program

Use the OLAP Server: Default calc (ESSCALC1) warehouse program to call the default calc script that is associated with the target database. The OLAP Server can be a OLAP Server or an Essbase server.

When you select a step that uses the OLAP Server: Default calc (ESSCALC1) warehouse program, do not link the step to a source or a target.

This warehouse program is available for the following operating systems:

The Column Mapping page is not available for this step.

To define values for a step that runs the warehouse program:

  1. Open the step notebook.
  2. Specify general information about the warehouse program. For more information, see Providing general information about a step.
  3. On the Parameters page, in the OLAP Server system name field, type the name of the OLAP server that you want to use.
  4. In the OLAP Server application name field, type the name of the OLAP server application to use.
  5. In the OLAP Server database name field, type the name of the OLAP server database that has the default calc script to call.
  6. In the OLAP Server user ID field, type the user ID to use to connect to the OLAP server database.
  7. In the OLAP Server password field, type the password for the user ID.
  8. In the Verify OLAP Server password field, type the password again.
  9. On the Processing Options page, provide information about how your step processes. For more information, see Defining processing options.
  10. Click OK to save your changes and close the step notebook.

Defining values for the OLAP Server: Calc with calc rules (ESSCALC2) warehouse program

Use the OLAP Server: Calc with calc rules (ESSCALC2) warehouse program to apply the specified calc script to an OLAP server database. The OLAP server can be a OLAP Server or an Essbase server.

When you select a step that uses this warehouse program, do not link the step to a source or a target.

This warehouse program is available for the following operating systems:

The Column Mapping page is not available for this step.

To define values for a step that runs this warehouse program:

  1. Open the step notebook.
  2. Specify general information about the warehouse program. For more information, see Providing general information about a step.
  3. On the Parameters page, in the OLAP Server system name field, type the name of the OLAP Server to use.
  4. In the OLAP Server application name field, type the name of the OLAP server application to use.
  5. In the OLAP Server database name field, type the name of the OLAP server database that has the calc script to call.
  6. In the OLAP Server user ID field, type the user ID to use to connect to the OLAP server database.
  7. In the OLAP Server password field, type the password for the user ID.
  8. In the Verify OLAP Server password field, type the password again.
  9. In the Calc script file name field, type the name of the calc script file to use. If the calc script location flag indicates that the file is on an OLAP server system, the file name must follow the convention for specifying file names on the client or the server. For example, if you are using a file on an Essbase server, you must specify the file name without an extension.

    You must also use the Essbase Application Manager to build the calc script file. To do this, you must store the calc script on the server that uses the Essbase Application Manager.

    If the calc script file location flag in your program indicates that the file is on an agent site, you must specify a fully qualified file name using the file system of the agent site.

  10. From the Calc script file location radio button group, select the location of the calc script file (indicated by the location flag).
  11. On the Processing Options page, provide information about how your step processes. For more information, see Defining processing options.
  12. Click OK to save your changes and close the step notebook.

Defining values for the OLAP Server: Free text data load (ESSDATA1) warehouse program

Use the OLAP Server: Free text data load warehouse program to load data from a comma-delimited flat file into a multidimensional OLAP Server database using free-form data loading. The OLAP server can be a OLAP Server or an Essbase server.

The OLAP Server: Free text data load warehouse program uses the file that is selected as a source for the step in the process. The step must have only one source file selected. The data in the source file must match the OLAP server outline values exactly. The selected source file must be on the agent site.

This warehouse program is available for the following operating systems:

The Column Mapping page is not available for this step.

To define values for a step that runs this warehouse program:

  1. Open the step notebook.
  2. Specify general information about the warehouse program. For more information, see Providing general information about a step.
  3. On the Parameters page, in the OLAP Server system name field, type the name of the OLAP server to use.
  4. In the OLAP Server application name field, type the name of the OLAP server application to use.
  5. In the OLAP Server database name field, type the name of the OLAP server database that has the default calc script to call.
  6. In the OLAP Server user ID field, type the user ID to use to connect to the OLAP server database.
  7. In the OLAP Server password field, type the password for the user ID.
  8. In the Verify OLAP Server password field, type the password again.
  9. On the Processing Options page, provide information about how your step processes. For more information, see Defining processing options.
  10. Click OK to save your changes and close the step notebook.

Defining values for the OLAP Server: Load data from file with load rules (ESSDATA2) warehouse program

Use the OLAP Server: Load data from file with load rules (ESSDATA2) warehouse program to load data from a flat file into a multidimensional OLAP Server database using load rules. The OLAP server can be a OLAP Server or an Essbase server.

The warehouse program uses the selected file as a source for the step. The step must have only one source file selected. The source file name must follow the conventions for specifying file names on the client or server of your OLAP server system.

If the file location flag in your program indicates that the file is on an agent site, you must specify a fully qualified file name in the Load rule file name field on the Parameters page of the Step notebook.

If the file location flag indicates that the source file is on the OLAP server, you must specify the file name without an extension in the Load rule file name field on the Parameters page of the Step notebook.

This warehouse program is available for the following operating systems:

The Column Mapping page is not available for this step.

To define values for a step that runs this warehouse program:

  1. Open the step notebook.
  2. Specify general information about the warehouse program. For more information, see Providing general information about a step.
  3. On the Parameters page, in the OLAP Server system name field, type the name of the OLAP server to use.
  4. In the OLAP Server application name field, type the name of the OLAP server application to use.
  5. In the OLAP Server database name field, type the name of the OLAP server database that has the default calc script to call.
  6. In the OLAP Server user ID field, type the user ID to use to connect to the OLAP server database.
  7. In the OLAP Server password field, type the password for the user ID.
  8. In the Verify OLAP Server password field, type the password again.
  9. In the Load rules file name field, type the name of the load rules file that you want the program to use. If the file is on the OLAP server, the file name cannot have an extension. If the file is located on the agent site, you must specify the fully qualified file name.
  10. In the Load rules file location flag area, click the location of the rules file.
  11. In the Source file location flag area, click the location of the source file.
  12. In the OLAP Server utility abort flag area, specify whether to stop the OLAP server utility if there is an error.
  13. On the Processing Options page, provide information about how your step processes. For more information, see Defining processing options.
  14. Click OK to save your changes and close the step notebook.

Defining values for an OLAP Server: Load data from SQL table with load rules (ESSDATA3) warehouse program

Use the OLAP Server: Load data from SQL table with load rules (ESSDATA3) warehouse program to load data from an SQL source into a multidimensional OLAP Server database using load rules. The OLAP server can be a OLAP Server or an Essbase server.

The OLAP Server: Load data from an SQL table with load rules warehouse program uses the user ID and password defined for the source database for the step in the Steps notebook.

When you select a step that uses this warehouse program, link a source table to the step, but do not link the step to a target.

This warehouse program is available for the following operating systems:

The Column Mapping page is not available for this step.

To define values for a step that uses this warehouse program:

  1. Open the step notebook.
  2. Specify general information about the warehouse program. For more information, see Providing general information about a step.
  3. On the Parameters page, in the OLAP Server system name field, type the name of the OLAP server to use.
  4. In the OLAP Server application name field, type the name of the OLAP server application to use.
  5. In the OLAP Server database name field, type the name of the OLAP server database that has the default calc script to call.
  6. In the OLAP Server user ID field, type the user ID to use to connect to the OLAP server database.
  7. In the OLAP Server password field, type the password for the user ID.
  8. In the Verify OLAP Server password field, type the password again.
  9. In the Load rules file name field, type the name of the load rules file that you want the program to use. If the file is on the OLAP Server, the file name cannot have an extension. If the file is located on the agent site, you must specify the fully qualified file name.
  10. In the Load rules file location flag area, click the location of the rules file.
  11. In the OLAP Server utility abort flag area, specify whether to stop the OLAP server utility if there is an error.
  12. On the Processing Options page, provide information about how your step processes. For more information, see Defining processing options.
  13. Click OK to save your changes and close the step notebook.

Defining values for an OLAP Server: Load data from a file without using load rules (ESSDATA4) warehouse program

Use the OLAP Server: Load data from a file without using load rules (ESSDATA4) program to load data from a flat file into a multidimensional OLAP server database without using load rules. The OLAP server can be a OLAP Server or an Essbase server.

When you select a step that uses this warehouse program, link a source file to the step, but do not link the step to a target table.

This warehouse program is available for the following operating systems:

The Column Mapping page is not available for this step.

To define values for a step that uses this warehouse program:

  1. Open the step notebook.
  2. Specify general information about the warehouse program. For more information, see Providing general information about a step.
  3. On the Parameters page, in the OLAP Server system name field, type the name of the OLAP server to use.
  4. In the OLAP Server application name field, type the name of the OLAP server application to use.
  5. In the OLAP Server database name field, type the name of the OLAP server database that has the default calc script to call.
  6. In the OLAP Server user ID field, type the user ID to use to connect to the OLAP server database.
  7. In the OLAP Server password field, type the password for the user ID.
  8. In the Verify OLAP Server password field, type the password again.
  9. In the Source file location flag area, click the location of the source file.
  10. In the OLAP Server utility abort flag area, specify whether to stop the OLAP server utility if there is an error.
  11. On the Processing Options page, provide information about how your step processes. For more information, see Defining processing options.
  12. Click OK to save your changes and close the step notebook.

Defining values for an OLAP Server: Update outline from file (ESSOTL1) warehouse program

Use the OLAP Server: Update outline (ESSOTL1) warehouse program to update an OLAP server outline from a source flat file using load rules. The OLAP server can be a OLAP Server or Essbase server.

The OLAP Server: Update outline warehouse program uses the selected file as a source for the step. The step must have only one source file selected. The source file name must follow the conventions for your OLAP server for specifying file names on the client or server.

If the file location flag in your warehouse program indicates that the file is on an agent site, you must specify a fully qualified file name in the Load rules file name field on the Parameters page of the Step notebook.

If the file location flag indicates that the source file is on the OLAP server, you must specify the file name without an extension in the Load rules file name field on the Parameters page of the Step notebook.

For more information, see the documentation for your OLAP server.

When you select a step that uses this warehouse program, link a source file to the step, but do not link the step to a target table.

This warehouse program is available for the following operating systems:

The Column Mapping page is not available for this step.

To define values for a step that uses this warehouse program:

  1. Open the step notebook.
  2. Specify general information about the warehouse program. For more information, see Providing general information about a step.
  3. On the Parameters page, in the OLAP Server system name field, type the name of the OLAP server to use.
  4. In the OLAP Server application name field, type the name of the OLAP server application to use.
  5. In the OLAP Server database name field, type the name of the OLAP server database that has the default calc script to call.
  6. In the OLAP Server user ID field, type the user ID to use to connect to the OLAP server database.
  7. In the OLAP Server password field, type the password for the user ID.
  8. In the Verify OLAP Server password field, type the password again.
  9. In the Load rules file name field, type the name of the load rules file that you want the program to use. If the file is on the OLAP server, the file name cannot have an extension. If the file is located on the agent site, you must specify the fully qualified file name.
  10. In the Load rules file location flag area, click the location of the rules file. In the Source file location flag area, click the location of the source file.
  11. On the Processing Options page, provide information about how your step processes. For more information, see Defining processing options.
  12. Click OK to save your changes and close the step notebook.

Defining values for an OLAP Server: Update outline from SQL table (ESSOTL2) program

Use the OLAP Server: Update outline from SQL table (ESSOTL2) warehouse program to update an OLAP server outline from an SQL source using load rules. The OLAP server can be a OLAP Server or an Essbase server.

When you select a step that uses this warehouse program, link a source table to the step, but do not link the step to a target table.

This warehouse program is available for the following operating systems:

The Column Mapping page is not available for this step.

To define values for a step that uses this warehouse program:

  1. Open the step notebook.
  2. Specify general information about the warehouse program. For more information, see Providing general information about a step.
  3. On the Parameters page, in the OLAP Server system name field, type the name of the OLAP server to use.
  4. In the OLAP Server application name field, type the name of the OLAP server application to use.
  5. In the OLAP Server database name field, type the name of the OLAP server database that has the default calc script to call.
  6. In the OLAP Server user ID field, type the user ID to use to connect to the OLAP server database.
  7. In the OLAP Server password field, type the password for the user ID.
  8. In the Verify OLAP Server password field, type the password again.
  9. In the Load rules file name field, type the name of the load rules file that you want the program to use. If the file is on the OLAP server, the file name cannot have an extension. If the file is located on the agent site, you must specify the fully qualified file name.
  10. In the Load rules file location flag area, click the location of the rules file.
  11. On the Processing Options page, provide information about how your step processes. For more information, see Defining processing options.
  12. Click OK to save your changes and close the step notebook.

[ Top of Page | Previous Page | Next Page ]