23 Oracle Hyperion Essbase
This chapter includes the following sections:
Introduction
Oracle Data Integrator Adapter for Oracle's Hyperion Essbase enables you to connect and integrate Essbase with virtually any source or target using Oracle Data Integrator. The adapter provides a set of Oracle Data Integrator Knowledge Modules (KMs) for loading and extracting metadata and data and calculating data in Essbase applications.
Integration Process
You can use Oracle Data Integrator Adapter for Essbase to perform these data integration tasks on an Essbase application:
-
Load metadata and data
-
Extract metadata and data
Using the adapter to load or extract metadata or data involves the following tasks:
-
Setting up an environment: defining data servers and schemas.
-
Reverse-engineering an Essbase application using the Reverse-engineering Knowledge Module (RKM)
-
Extracting metadata and data using Load Knowledge Modules (LKM).
-
Integrating the metadata and data into the Essbase application using the Integration Knowledge Modules (IKM).
Knowledge Modules
Oracle Data Integrator provides the Knowledge Modules (KM) listed in Table 23-1 for handling Hyperion Essbase data. These KMs use Hyperion Essbase specific features. It is also possible to use the generic SQL KMs with the Hyperion Essbase database.
Table 23-1 Hyperion Essbase Knowledge Modules
Knowledge Module | Description |
---|---|
RKM Hyperion Essbase |
Reverse-engineers Essbase applications and creates data models to use as targets or sources in Oracle Data Integrator mappings |
IKM SQL to Hyperion Essbase (DATA) |
Integrates data into Essbase applications. |
IKM SQL to Hyperion Essbase (METADATA) |
Integrates metadata into Essbase applications |
LKM Hyperion Essbase DATA to SQL |
Loads data from an Essbase application to any SQL compliant database used as a staging area. |
LKM Hyperion Essbase METADATA to SQL |
Loads metadata from an Essbase application to any SQL compliant database used as a staging area. |
Installation and Configuration
Make sure you have read the information in this section before you start using the Oracle Data Integrator Adapter for Essbase:
System Requirements and Certifications
Before performing any installation you should read the system requirements and certification documentation to ensure that your environment meets the minimum installation requirements for the products you are installing.
The list of supported platforms and versions is available on Oracle Technical Network (OTN):
http://www.oracle.com/technetwork/middleware/data-integrator/documentation/index.html.
Technology Specific Requirements
There are no technology-specifc requirements for using the Oracle Data Integrator Adapter for Essbase.
Setting up Hyperion Essbase Adapter
The following sections explain how to set up Hyperion Essbase Adapter for ODI Studio and ODI standalone agent.
Setting up Adapter for ODI Studio
Exit from ODI Studio, before starting the setup process.
-
In Oracle Hyperion Essbase directory, locate ess_japi.jar and ess_es_server.jar.
-
If ess_japi.jar and ess_es_server.jar are not directly accessible by ODI, copy them to a location that allows ODI access.
-
Modify <ODI_HOME>/odi/studio/bin/odi.conf file to include ess_japi.jar and ess_es_server.jar.
For example:
AddJavaLibFile /server/lib/ess_japi.jar
AddJavaLibFile /server/lib/ess_es_server.jar
Setting up Adapter for ODI Standalone Agent
Stop ODI Agent before starting the setup process.
-
In Oracle Hyperion Essbase directory, locate ess_japi.jar and ess_es_server.jar.
-
Copy them into <DOMAIN_HOME>/lib directory.
For more information, see Configuring the Domain for the Standalone Collocated Agent in Installing and Configuring Oracle Data Integrator.
Setting up the Topology
Setting up the Topology consists of:
Creating an Hyperion Essbase Data Server
Create a data server for the Hyperion Essbase technology using the standard procedure, as described in Creating a Data Server of Administering Oracle Data Integrator. This section details only the fields required or specific for defining a Hyperion Essbase data server:
-
In the Definition tab:
-
Name: Enter a name for the data server definition.
-
Server (Data Server): Enter the Essbase server name.
Note:
If the Essbase server is running on a port other than the default port (1423), then provide the Essbase server details in this format,
<Essbase Server hostname>:<port>
. -
-
Under Connection, enter a user name and password for connecting to the Essbase server.
Note:
The Test button does not work for an Essbase data server connection. This button works only for relational technologies that have a JDBC Driver.
Creating an Hyperion Essbase Physical Schema
Create a Hyperion Essbase physical schema using the standard procedure, as described in Creating a Physical Schema of Administering Oracle Data Integrator.
Under Application (Catalog) and Application (Work Catalog), specify an Essbase application and under Database (Schema) and Database (Work Schema), specify an Essbase database associated with the application you selected.
Create for this physical schema a logical schema using the standard procedure, as described in Creating a Logical Schema of Administering Oracle Data Integrator and associate it in a given context.
Creating and Reverse-Engineering an Essbase Model
This section contains the following topics:
Create an Essbase Model
Create an Essbase Model using the standard procedure, as described in Creating a Model of Developing Integration Projects with Oracle Data Integrator.
Reverse-engineer an Essbase Model
Reverse-engineering an Essbase application creates an Oracle Data Integrator model that includes a datastore for each dimension in the application and a datastore for data.
To perform a Customized Reverse-Engineering on Hyperion Essbase with a RKM, use the usual procedure, as described in Reverse-engineering a Model of Developing Integration Projects with Oracle Data Integrator. This section details only the fields specific to the Hyperion Essbase technology.
-
In the Reverse tab of the Essbase Model, select the RKM Hyperion Essbase.
-
Set the KM options as indicated in Table 23-2.
Table 23-2 RKM Hyperion Essbase Options
Option Possible Values Description MULTIPLE_DATA_COLUMNS
-
No (Default)
-
Yes
If this option is set to
No
, then the datastore created for the data extract / load model contains one column for each of the standard dimensions and a single data column.If this option is set toYes
, then the datastore created for the data extract / load model contains one column for each of the standard dimensions excluding the dimension specified by the DATA_COLUMN_DIMENSION option and as many data columns as specified by the comma separated list for the DATA_COLUMN_MEMBERS option.DATA_COLUMN_DIMENSION
Account
This option is only applicable if MULTIPLE_DATA_COLUMNS is set to
Yes
.Specify the data column dimension name. For example, data columns are spread across the dimension Account or Time, and so on.
DATA_COLUMN_MEMBERS
Account
This option is only applicable if MULTIPLE_DATA_COLUMNS is set to
Yes
.Separate the required data column members with, (Comma).
For example, if the data column dimension is set to Account and members are set to
Sales,COGS
then the datastore for data extract/load contains one column for each of the dimension except the data column dimension and one column for each of the data column member specified in the comma separated value. For example. Assuming that the dimensions in the Essbase application are Account, Scenario, Product, Market, and Year and the data column dimension is specified as Account and Data Column Members asSales, COGS
, the datastore will have the following columns:-
Scenario (String)
-
Product (String)
-
Market (String)Year (String)
-
Sales (Numeric)
-
COGS (Numeric)
EXTRACT_ATTRIBUTE_MEMBERS
-
No (Default)
-
Yes
If this option is set to
No
, then the datastore created for the data extract / load model contains one column for each of the standard dimensions and a single data column. Attribute dimensions are not included.If this option is set to
Yes
, then the data model contains these columns.-
One column is created for each of the standard dimensions
-
One or more Data column(s) are created depending upon the value of the MULTIPLE_DATA_COLUMN option
-
One column is created for each of the associated attribute dimension
-
The RKM connects to the application (which is determined by the logical schema and the context) and imports some or all of these datastores, according to the dimensions in the application.
Designing a Mapping
After reverse-engineering an Essbase application as a model, you can use the datastores in this model in these ways:
-
Targets of mappings for loading data and metadata into the application
-
Sources of mappings for extracting metadata and data from the application.
The KM choice for a mapping determines the abilities and performance of this mapping. The recommendations in this section help in the selection of the KM for different situations concerning Hyperion Essbase.
This section contains the following topics:
Loading Metadata
Oracle Data Integrator provides the IKM SQL to Hyperion Essbase (METADATA) for loading metadata into an Essbase application.
Metadata consists of dimension members. You must load members, or metadata, before you load data values for the members.
You can load members only to dimensions that exist in Essbase. You must use a separate mapping for each dimension that you load. You can chain mappings to load metadata into several dimensions at once.
-
Note:
The metadata datastore can also be modified by adding or deleting columns to match the dimension build rule that will be used to perform the metadata load. For example, the default datastore would have columns for ParentName and ChildName, if the rule is a generational dimension build rule, you can modify the metadata datastore to match the columns within your generational dimension build rule. The loadMarkets mapping within the samples is an example of performing a metadata load using a generational dimension build rule.
Table 23-3 lists the options of the IKM SQL to Hyperion Essbase (METADATA). These options define how the adapter loads metadata into an Essbase application.
Table 23-3 IKM SQL to Hyperion Essbase (METADATA) Options
Option | Values | Description |
---|---|---|
|
Blank (Default) |
Specify the rules file for loading or building metadata. If the rules file is present on the Essbase server, then, only specify the file name, otherwise, specify the fully qualified file name with respect to the Oracle Data Integrator Agent. |
|
, (Default) |
(Optional) Specify a rule separator in the rules file. These are the valid values:
|
|
|
Restructure database after loading metadata in the Essbasecube. These are the valid values:
Note: This option is applicable for the Essbase Release 9.3 and later. For the Essbase releases prior to 9.3, this option is ignored. |
|
Blank (Default) |
Enable this option to execute a MAXL script before loading metadata to the Essbase cube. Specify a fully qualified path name (without blank spaces) for the MAXL script file. Note: To successfully execute this option, the Essbase client must be installed and configured on the machine where the Oracle Data Integrator Agent is running. |
|
Blank (Default) |
Enable this option to execute a MAXL script after loading metadata to the Essbase cube. Specify a fully qualified path name (without blank spaces) for the MAXL script file. Note: To successfully execute this option, the Essbase client must be installed and configured on the machine where the Oracle Data Integrator Agent is running. |
|
|
This option is only applicable if you are enabling the PRE_LOAD_MAXL_SCRIPT option. If you set the ABORT_ON_PRE_MAXL_ERROR option to Yes, then the load process is aborted on encountering any error while executing the pre-MAXL script. |
|
|
If this option is set to Yes, during the IKM process, logging is done to the file specified in the LOG_FILE_NAME option. |
|
<?=java.lang.System.getProperty ("java.io.tmpdir")?>/Extract_<% =snpRef.getFrom()%>.log (Default) |
Specify a file name to log events of the IKM process. |
|
<?=java.lang.System.getProperty ("java.io.tmpdir")?>/Extract_<% =snpRef.getFrom()%>.log (Default) |
Specify a file name to log the error records of the IKM process. |
Loading Data
Oracle Data Integrator provides the IKM SQL to Hyperion Essbase (DATA) for loading data into an Essbase application.
You can load data into selected dimension members that are already created in Essbase. For a successful data load, all the standard dimension members are required and they should be valid members. You must set up the Essbase application before you can load data into it.
You can also create a custom target to match a load rule.
Before loading data, ensure that the members (metadata) exist in the Essbase dimension. The data load fails for records that have missing members and this information is logged (if logging is enabled) as an error record and the data load process will continue until the maximum error threshold is reached.
Note:
The data datastore can also be modified by adding or delete columns to match the data load rule that will be used to perform the data load.
Table 23-4 lists the options of the IKM SQL to Hyperion Essbase (DATA). These options define how the adapter loads and consolidates data in an Essbase application.
Table 23-4 IKM SQL to Hyperion Essbase (DATA)
Option | Values | Description |
---|---|---|
|
Blank (Default) |
(Optional) Specify a rules file to enhance the performance of data loading. Specify a fully qualified file name if the rules file is not present on the Essbase server. If the rules file option is not specified, then the API-based data load is used. However, you cannot specify the API. |
|
, (Default) |
(Optional) Specify a rule separator in the rules file. These are the valid values:
|
|
Integer |
When performing multiple data loads in parallel, many mappings can be set to use the same GROUP_ID. This GROUP _ID is used to manage parallel loads allowing the data load to be committed when the final mapping for the GROUP_ID is complete. For more information on loading to parallel ASO cubes, refer to the Essbase Database Administrators guide. |
|
1–1000000 |
Multiple data load buffers can exist on an aggregate storage database. To save time, you can load data into multiple data load buffers at the same time. Although only one data load commit operation on a database can be active at any time, you can commit multiple data load buffers in the same commit operation, which is faster than committing buffers individually. For more information on loading to parallel ASO cubes, refer to the Essbase Database Administrators guide. |
|
0-100 |
When performing an incremental data load, Essbase uses the aggregate storage cache for sorting data. You can control how much of the cache a data load buffer can use by specifying the percentage (between 0 and 100% inclusive). By default, the resource usage of a data load buffer is set to 100, and the total resource usage of all data load buffers created on a database cannot exceed 100. For example, if a buffer of 90 exists, you cannot create another buffer of a size greater than 10. A value of 0 indicates to Essbase to use a self-determined, default load buffer size. |
|
|
Enable this option to clear data from the Essbase cube before loading data into it. These are the valid values:
Note: For ASO applications, the Upper Blocks and Non-Input Blocks options will not be applicable. |
|
Blank (Default) |
(Optional) Specify the calculation script that you want to run after loading data in the Essbase cube. Provide a fully qualified file name if the calculation script is not present on the Essbase server. |
|
|
This option is only applicable if you have specified a calculation script in the CALCULATION_SCRIPT option. If you set the RUN_CALC_SCRIPT_ONLY option to Yes, then only the calculation script is executed without loading the data into the target Essbase cube. |
|
Blank (Default) |
Enable this option to execute a MAXL script before loading data to the Essbase cube. Specify a fully qualified path name (without blank spaces) for the MAXL script file. Note: Essbase client must be installed and configured on the machine where the Oracle Data Integrator Agent is running. |
|
Blank (Default) |
Enable this option to execute a MAXL script after loading data to the Essbase cube. Specify a fully qualified path name (without blank spaces) for the MAXL script file. Note: Essbase client must be installed and configured on the machine where the Oracle Data Integrator Agent is running. |
|
|
This option is only applicable if you are enabling the PRE_LOAD_MAXL_SCRIPT option. If you set the ABORT_ON_PRE_MAXL_ERROR option to Yes, then the load process is aborted on encountering any error while executing pre-MAXL script. |
|
1 (Default) |
Enable this option to set the maximum number of errors to be ignored before stopping a data load. The value that you specify here is the threshold limit for error records encountered during a data load process. If the threshold limit is reached, then the data load process is aborted. For example, the default value 1 means that the data load process stops on encountering a single error record. If value 5 is specified, then data load process stops on encountering the fifth error record. If value 0 (== infinity) is specified, then the data load process continues even after error records are encountered. |
|
1000 (Default) |
Commit Interval is the chunk size of records that are loaded in the Essbase cube in a complete batch. Enable this option to set the Commit Interval for the records in the Essbase cube. Changing the Commit Interval can increase performance of data load based on design of the Essbase database. |
|
|
If this option is set to Yes, during the IKM process, logging is done to the file specified in the LOG_FILENAME option. |
|
<?=java.lang.System.getProperty("java.io.tmpdir")?/<%=snpRef.getTargetTable("RES_NAME")%>.log (Default) |
Specify a file name to log events of the IKM process. |
|
|
If this option is set to Yes, during the IKM process, details of error records are logged to the file specified in the ERROR_LOG_FILENAME option. |
|
<?=java.lang.System.getProperty(java.io.tmpdir")?>/<%=snpRef.getTargetTable("RES_NAME")%>.err |
Specify a file name to log error record details of the IKM process. |
|
|
If this option is set to Yes, then the header row containing the column names are logged to the error records file. |
|
, (Default) |
Specify the column delimiter to be used for the error records file. |
|
\r\n (Default) |
Specify the row delimiter to be used for the error records file. |
|
' (Default) |
Specify the text delimiter to be used for the column data in the error records file. For example, if the text delimiter is set as ' " ' (double quote), then all the columns in the error records file will be delimited by double quotes. |
Extracting Data
This section includes the following topics:
Data Extraction Methods for Essbase
The Oracle Data Integrator Adapter for Essbase supports querying and scripting for data extraction. To extract data, as a general process, create an extraction query and provide the extraction query to the adapter. Before the adapter parses the output of the extraction query and populates the staging area, a column validation is done. The adapter executes the extraction query based on the results of the metadata output query during the validation. The adapter does the actual parsing of the output query only when the results of the column validation are successful.
After the extraction is complete, validate the results—make sure that the extraction query has extracted data for all the output columns.
You can extract data with these Essbase-supported query and scripts:
Data Extraction Using Report Scripts
Data can be extracted by parsing the reports generated by report scripts. The report scripts can exist on the client computer as well as server, where Oracle Data Integrator is running on the client computer and Essbase is running on the server. The column validation is not performed when extracting data using report scripts. So, the output columns of a report script is directly mapped to the corresponding connected column in the source model. However, before you begin data extract using report scripts, you must complete these tasks:
-
Suppress all formatting in the report script. Include this line as the first line in the report script—{ROWREPEAT SUPHEADING SUPFORMAT SUPBRACKETS SUPFEED SUPCOMMAS NOINDENTGEN TABDELIMIT DECIMAL 15}.
-
The number of columns produced by a report script must be greater than or equal to the connected columns from the source model.
-
The column delimiter value must be set in the LKM option.
Data Extraction Using MDX Queries
An MDX query is an XML-based data-extraction mechanism. You can specify the MDX query to extract data from an Essbase application. However, before you begin data extract using MDX queries, you must complete these tasks:
-
The names of the dimension columns must match with the dimensions in the Essbase cube.
-
For Type 1 data extraction, all the names of data columns must be valid members of a single standard dimension.
-
For Type 1 data extraction, it is recommended that the data dimension exists in the lower level axis, that is, axis (0) of columns. If it is not specified in the lowest level axis then the memory consumption would be high.
-
If columns are connected with the associated attribute dimension from the source model, then, the same attribute dimension must be selected in the MDX query.
-
The script of the MDX query can be present on the client computer or the server.
Data Extraction Using Calculation Scripts
Calculation scripts provide a faster option to extract data from an Essbase application. However, before you extract data using the calculation scripts, take note of these restrictions:
-
Data extraction using calculation scripts is supported ONLY for BSO applications.
-
Data extraction using calculation scripts is supported ONLY for the Essbase Release 9.3 and later.
-
Set the DataExportDimHeader option to ON.
-
(If used) Match the DataExportColHeader setting to the data column dimension (in case of multiple data columns extraction).
-
The Oracle Data Integrator Agent, which is used to extract data, must be running on the same machine as the Essbase server.
-
When accessing calculation scripts present on the client computer, a fully qualified path to the file must be provided, for example, C:\Essbase_Samples\Calc_Scripts \calcall.csc, where as, to access calculation scripts present on the server, only the file name is sufficient.
Extracting Essbase Data
Oracle Data Integrator provides the LKM Hyperion Essbase DATA to SQL for extracting data from an Essbase application.
You can extract data for selected dimension members that exist in Essbase. You must set up the Essbase application before you can extract data from it.
Table 23-5 provides the options of the LKM Hyperion Essbase Data to SQL. These options define how Oracle Data Integrator Adapter for Essbase extracts data.
Table 23-5 LKM Hyperion Essbase DATA to SQL Options
Option | Values | Description |
---|---|---|
|
Blank (Default) |
(Optional) Specify the calculation script that you want to run before extracting data from the Essbase cube. |
|
|
Specify an extraction query type—report script, MDX query, or calculation script. Provide a valid extraction query, which fetches all the data to fill the output columns. The first record (first two records in case of calculation script) contains the meta information of the extracted data. |
|
Blank (Default) |
Specify a fully qualified file name of the extraction query. |
|
\t (Default) |
Specify the column delimiter for the extraction query. If no value is specified for this option, then space (" ") is considered as column delimiter. |
|
Blank (Default) |
This option is only applicable if the query type in the EXTRACTION_QUERY_TYPE option is specified as CalcScript. Specify a fully qualified file location where the data is extracted through the calculation script.. |
|
Blank (Default) |
Enable this option to execute a MAXL script before extracting data from the Essbase cube. |
|
Blank (Default) |
Enable this option to execute a MAXL script after extracting data from the Essbase cube. |
|
|
This option is only applicable if the PRE_EXTRACT_MAXL option is enabled. If the ABORT_ON_PRE_MAXL_ERROR option is set to Yes, while executing pre-MAXL script, the load process is aborted on encountering any error. |
|
|
If this option is set to Yes, during the LKM process, logging is done to the file specified in the LOG_FILE_NAME option. |
|
<?=java.lang.System.getProperty ("java.io.tmpdir")?/<% =snpRef.getTargetTable("RES_NAME")%>.log (Default) |
Specify a file name to log events of the LKM process. |
|
1 (Default |
Enable this option to set the maximum number of errors to be ignored before stopping extract. |
|
|
If this option is set to Yes, during the LKM process, details of error records are logged to the file specified in the ERROR_LOG_FILENAME option. |
|
<?=java.lang.System.getProperty(java.io.tmpdir")?>/<%=snpRef.getTargetTable("RES_NAME")%>.err |
Specify a file name to log error record details of the LKM process. |
|
|
If this option is set to Yes, then the header row containing the column names are logged to the error records file. |
|
, (Default) |
Specify the column delimiter to be used for the error records file. |
|
\r\n (Default) |
Specify the row delimiter to be used for the error records file. |
|
' (Default) |
Specify the text delimiter to be used for the column data in the error records file. For example, if the text delimiter is set as ' " ' (double quote), then all the columns in the error records file are delimited by double quotes. |
|
|
Set this option to No, in order to retain temporary objects (tables, files, and scripts) after integration. This option is useful for debugging. |
Extracting Members from Metadata
Oracle Data Integrator provides the LKM Hyperion Essbase METADATA to SQL for extracting members from a dimension in an Essbase application.
To extract members from selected dimensions in an Essbase application, you must set up the Essbase application and load metadata into it before you can extract members from a dimension.Before extracting members from a dimension, ensure that the dimension exists in the Essbase database. No records are extracted if the top member does not exist in the dimension.
Table 23-6 lists the options of the LKM Hyperion Essbase METADATA to SQL. These options define how Oracle Data Integrator Adapter for Oracle's Hyperion Essbase extracts dimension members.
Table 23-6 LKM Hyperion Essbase METADATA to SQL
Option | Values | Description |
---|---|---|
|
IDescendants, (Default) |
Enable this option to select members from the dimension hierarchy for extraction. You can specify these selection criteria:
|
|
Blank (Default) |
Enable this option to provide the member name for applying the specified filter criteria. If no member is specified, then the filter criteria is applied on the root dimension member.If the MEMBER_FILTER_CRITERIA value is MEMBER_ONLY or UDA, then the MEMBER_FILTER_VALUE option is mandatory and cannot be an empty string. |
|
|
If this option is set to Yes, during the LKM process, logging is done to the file specified by the LOG_FILE_NAME option. |
|
<?=java.lang.System.getProperty(java.io.tmpdir")?>/Extract_<%=snpRef.getFrom()%>.log |
Specify a file name to log events of the LKM process. |
|
1 (Default) |
Enable this option to set the maximum number of errors to be ignored before stopping extract. |
|
|
If this option is set to Yes, during the LKM process, details of error records are logged to the file specified in the ERROR_LOG_FILENAME option. |
|
<?=java.lang.System.getProperty(java.io.tmpdir")?>/Extract_<%=snpRef.getFrom()%>.err |
Specify a file name to log error record details of the LKM process. |
|
|
If this option is set to Yes, then the header row containing the column names are logged to the error records file. |
|
, (Default) |
Specify the column delimiter to be used for the error records file. |
|
\r\n (Default) |
Specify the row delimiter to be used for the error records file. |
|
|
Specify the text delimiter to be used for the data column in the error records file. For example, if the text delimiter is set as ' " ' (double quote), then all the columns in the error records file are delimited by double quotes. |
|
|
Set this option to No, in order to retain temporary objects (tables, files, and scripts) after integration. This option is useful for debugging. |