Oracle® Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator 11g Release 1 (11.1.1) Part Number E12644-07 |
|
|
PDF · Mobi · ePub |
This chapter describes how to work with Oracle Hyperion Financial Management in Oracle Data Integrator.
This chapter includes the following sections:
Oracle Data Integrator Adapter for Hyperion Financial Management enables you to connect and integrate Hyperion Financial Management with any database through Oracle Data Integrator. The adapter provides a set of Oracle Data Integrator Knowledge Modules (KMs) for loading and extracting metadata and data and consolidating data in Financial Management applications.
You can use Oracle Data Integrator Adapter for Hyperion Financial Management to perform these data integration tasks on a Financial Management application:
Load metadata and data
Extract data
Consolidate data
Enumerate members of member lists
Using the adapter to load or extract data involves these tasks:
Setting up an environment: defining data servers and schemas
Reverse-engineering a Financial Management application using the Reverse-engineering Knowledge Module (RKM)
See Section 21.4, "Creating and Reverse-Engineering a Financial Management Model".
Loading metadata and data using Integration Knowledge Modules (IKM)
Extracting data and members using Load Knowledge Modules (LKM)
Oracle Data Integrator provides the Knowledge Modules (KM) listed in Table 21-1 for handling Hyperion Financial Management data. These KMs use Hyperion Financial Management specific features. It is also possible to use the generic SQL KMs with the Financial Management database. See Chapter 4, "Generic SQL" for more information.
Table 21-1 Hyperion Financial Management Knowledge Modules
Knowledge Module | Description |
---|---|
RKM Hyperion Financial Management |
Reverse-engineers Financial Management applications and creates data models to use as targets or sources in Oracle Data Integrator interfaces. |
IKM SQL to Hyperion Financial Management Data |
Integrates data into Financial Management applications. |
IKM SQL to Hyperion Financial Management Dimension |
Integrates metadata into Financial Management applications. |
LKM Hyperion Financial Management Data to SQL |
Loads data from a Financial Management application to any SQL compliant database used as a staging area. This knowledge module will not work if you change the column names of the HFMData data store reverse engineered by the RKM Hyperion Financial Management knowledge module. |
LKM Hyperion Financial Management Members To SQL |
Loads member lists from a Financial Management application to any SQL compliant database used as a staging area. |
Make sure you have read the information in this section before you start using the Oracle Data Integrator Adapter for Financial Management:
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/technology/products/oracle-data-integrator/index.html
.
There are no technology-specifc requirements for using the Oracle Data Integrator Adapter for Financial Management.
This section lists the requirements for connecting to Hyperion Financial Management application.
Use the default 32-bit HFM driver for 32-bit Windows platforms.
For 64-bit Windows platforms, the 64-bit version of HFM driver must be used. To use the 64-bit HFM driver:
Exit ODI Studio, if applicable.
Locate the existing HFM driver file and rename it from HFMDriver.dll
to HFMDriver32.dll
.
In the same directory, make a copy of one of the following driver files depending on the Financial Management release:
The default 64-bit HFM driver file (HFMDriver64.dll
) and rename the copy to HFMDriver.dll
For HFM 11.1.2 release, use HFMDriver64_11.1.2.dll
For HFM 11.1.1.2 relase, use HFMDriver64_11.1.1.2.dll
Name this copy HFMDriver.dll
.
Restart ODI Studio.
Setting up the Topology consists of:
Create a data server for the Hyperion Financial Management technology using the standard procedure, as described in "Creating a Data Server" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator. This section details only the fields required or specific for defining a Hyperion Financial Management data server:
In the Definition tab:
Name: Enter a name for the data server definition.
Cluster (Data Server): Enter the Financial Management cluster name.
Under Connection, enter a user name and password for connecting to the Financial Management server.
Note:
The Test button does not work for a Hyperion Financial Management data server connection; it works only for relational technologies that have a JDBC driver.
Create a Hyperion Financial Management physical schema using the standard procedure, as described in "Creating a Physical Schema" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.
Under Application (Catalog), specify a FinancialManagement application.
Create for this physical schema a logical schema using the standard procedure, as described in "Creating a Logical Schema" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator and associate it in a given context.
This section contains the following topics:
Create an Financial Management Model using the standard procedure, as described in "Creating a Model" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.
Reverse-engineering a Financial Management application creates an Oracle Data Integrator model that includes a data store for each dimension in the application, a data store for data, an optional data store for data with multiple periods, and an EnumMemberList data store.
To perform a Customized Reverse-Engineering on Hyperion Financial Management with a RKM, use the usual procedure, as described in "Reverse-engineering a Model" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator. This section details only the fields specific to the Hyperion Financial Management technology.
In the Reverse tab of the Financial Management Model, select the RKM Hyperion Financial Management.
Set the KM options as follows:
CREATE_HFMDATA_MULTIPLEPERIODS: Set to Yes
to create an additional data store for data with multiple periods. The number of periods for that model is specified by the MULTIPERIOD_COUNT option.
Default is No
.
MULTIPERIOD_COUNT: Specifies the number of periods for the HFMData_MultiplePeriod table.
The RKM connects to the application (which is determined by the logical schema and the context) and imports some or all of these data stores, according to the dimensions in the application:
HFMData: For loading and extracting data
HFMData_MultiplePeriods: For data with the number of periods specified by the option MULTIPERIODS_COUNT
Note:
This data store is imported only if the CREATE_HFMDATA_MULTIPLEPERIODS option is set to Yes in the model definition.
Account: For loading the Account dimension.
Entity: For loading the Entity dimension.
Scenario: For loading the Scenario dimension.
Currency: For loading the currency dimension.
Custom1-4: For loading the Custom1-4 dimensions.
EnumMembersList-For extracting a members list.
See Section 21.6, "Data Store Tables" for more information about these tables.
After reverse-engineering a Financial Management application as a model, you can use the data stores in this model in these ways:
As targets of interfaces for loading data and metadata into the application The following figure shows the flow of an interface targeting Financial Management.
As sources of interfaces for extracting data and member lists from the application The following figure shows the flow of an interface with a Financial Management source.
The KM choice for an interface determines the abilities and performance of this interface. The recommendations in this section help in the selection of the KM for different situations concerning Hyperion Financial Management.
This section contains the following topics:
Oracle Data Integrator provides the IKM SQL to Hyperion Financial Management Dimension for loading metadata into a Finanacial Management application.
Metadata comprises dimension members. You must load members, or metadata, before you load data values for the members.
You can load members only to existing Financial Management dimensions. You must use a separate interface for each dimension that you load. You can chain interfaces to load metadata into several dimensions at once.
The IKM SQL to Hyperion Financial Management Dimension supports the following options for defining how the adapter loads metadata into a Financial Management application:
REPLACE_MODE: If set to Yes
, metadata is replaced in the application (Replace); if set to No, metadata is overwritten in the application (Merge). Valid values: Yes or No
(default).
CLEAR_ALL_METADATA_BEFORE_LOADING: If set to Yes
, all metadata is cleared before loading. Valid values: Yes
or No
(default).
Caution:
If you set this option to Yes, you lose any active data or journals in the application.
LOG_ENABLED: If set to Yes
, logging is done during the load process to the file specified by theLOG_FILE_NAME option. Valid values: Yes
or No
(default).
LOG_FILE_NAME: The name of the file where logs are saved; default: Java temp folder/dimension.log
Oracle Data Integrator provides the IKM SQL to Hyperion Financial Management Data for loading data into a Financial Management application.
You can load data into selected dimension members that are already created in Financial Management. You must set up the Financial Management application before you can load data into it.
Before loading data, ensure that the members (metadata) exist in the Financial Management relational database. A data load fails if the members do not exist.
Note:
Use the HFMData or HFMData_MultiplePeriods data stores from a Hyperion Financial Management model as the target data store of your integration interface.
The IKM SQL to Hyperion Financial Management Data supports the following options for defining how the adapter loads and consolidates data in a Financial Management application:
IMPORT_MODE: Determines how data in the application cells is handled during data load. Valid values are:
Merge
(default): For each unique point of view that exists in the load data and in the application, the load data overwrites the data in the application. For each unique point of view that is in the load data but not in the application, the load data is loaded into the application.
Replace
: For each unique point of view in the load data, the system clearscorresponding values from the application, and then the data is loaded.
Note:
Unless the connected user has full access rights to all specified cells, no data is changed.
Replace by Security
: For each unique point of view in the load data to which the user has full access rights, the system clears corresponding values from the application, and then the data is loaded. Cells to which the user lacks full access are ignored.
Accumulate
:For each unique point of view that exists in the load data and in the application, the value from the load data is added to the value in the application.
ACCUMULATE_WITHIN_FILE: If set to Yes
, multiple values for the same cells in the load data are added before they are loaded into the application. Valid values: Yes
or No
(default).
FILE_CONTAINS_SHARE_DATA: Set to Yes
if the load file contains ownership data, such as shares owned. Valid values: Yes
or No
(default).
Caution:
If ownership data is included in the file and this option is set to No, an error occurs when you load the file.
CONSOLIDATE_AFTER_LOAD: If set to Yes
, data is consolidated after being loaded. Valid values: Yes or No (default).
CONSOLIDATE_ONLY: If set to Yes
, data is consolidated but not loaded. Valid values: Yes
and No
.
CONSOLIDATE_PARAMETERS: Specifies the parameters for consolidation as comma-separated values in this order: Scenario (required), Year, Period, Parent.Entity, and Type; default: an empty string.
Valid Type parameter settings:
"I" = Consolidate
"D" = Consolidate All with Data
"A" = Consolidate All
"C" = Calculate Contribution
"F"= Force Calculate Contribution
Example: Actual,1999,2,EastRegion.EastSales,A
LOG_ENABLED: If set to Yes
, logging is done during the load process to the file specified by the LOG_FILE_NAME option. Valid values: Yes
or No
(default)
LOG_FILE_NAME: The name of the file where logs are saved; default: Java temp folder/HFMData.logor HFMData_MultiplePeriod.log.
You can extract data for selected dimension members that exist in Financial Management. You must set up the Financial Management application before you can extract data from it.
Before extracting data, ensure that the members (metadata) exist in the Financial Management relational database; no records are extracted for members that do not exist (including the driver member and the members specified in the point of view.)
This section includes the following topics:
Oracle Data Integrator provides the LKM Hyperion Financial Management Data to SQL for extracting data from an Essbase application.
Use as a source the source data store (HFMData) from a Hyperion Financial Management model.
LKM Hyperion Financial Management Data to SQL supports the following options for defining how Oracle Data Integrator Adapter for Hyperion Financial Management extracts data:
SCENARIO_FILTER: The Scenario dimension members for which you are exporting data.
You can specify comma-delimited Scenario members or one scenario. If you do not specify scenarios, the system exports data for all scenarios.
YEAR_FILTER: The Year dimension members for which you are exporting data
You can specify comma-delimited years or one year. If you do not specify years, the system exports data for all years.
PERIOD_FILTER: The set of Period dimension members for which you are exporting data.
Specify a range of members using the ~ character between start and end period numbers; for example, 1~12. If you do not specify periods, the system exports data for only the first period.
ENTITY_FILTER: The Entity dimension members for which you are exporting data
You can specify comma-delimited entities or one entity. To specify the parent and child, separate them with a period; for example, I.Connecticut
. If you do not specify entities, the system exports data for all entities.
ACCOUNT_FILTER: The Account dimension members for which you are exporting data.
You can specify comma-delimited accounts or one account. If you do not specify accounts, the system exports data for all accounts.
VIEW_FILTER: The View dimension member for which you are exporting data Possible values: Periodic, YTD, or <Scenario_View> (default)
LOG_ENABLED: If set to Yes
, logging is done during the extract process to the file specified inLOG_FILE_NAME
LOG_FILE_NAME: The name of the file where logs are saved
DELETE_TEMPORARY_OBJECTS: If set to Yes
(default), tables, files, and scripts are deleted after integration.
Tip:
Temporary objects can be useful for resolving issues.
Oracle Data Integrator provides the LKM Hyperion Financial Management Members to SQL for extracting members from a dimension in an Essbase application.
You can extract members fromselected member lists and dimensions in a Financial Management application. You must set up the Financial Management application and load member lists into it before you can extract members from a member list for a dimension.
Before extracting members from a member list for a dimension, ensure that the member list and dimension exist in the Financial Management relational database. No records are extracted if the top member does not exist in the dimension.
Use as a source the source data store (EnumMembersList) from a Hyperion Financial Management model.
The LKM Hyperion Financial Management Members to SQL supports the following options for defining how Oracle Data Integrator Adapter for Hyperion Financial Management extracts members of member lists:
DIMENSION_NAME: The name of the dimension for which you are creating a member list; required.
MEMBER_LIST_NAME: A label for the member list; required.
TOP_MEMBER: The top member of the member list.
LOG_ENABLED: If set to Yes
, logging is done during the extract process to the file specified by the LOG_FILE_NAME option. Valid values: Yes
and No
(default) .
LOG_FILE_NAME: The name of the file where logs are saved.
DELETE_TEMPORARY_OBJECTS: If set to Yes
(default), tables, files, and scripts are deleted after integration.
Tip:
Temporary objects can be useful for resolving issues.
The IKM SQL to Hyperion Financial Management loads columns in tables to create data stores. The following tables describe the columns in each data store:
Note:
In the following tables, the column types are String unless the column descriptions specify otherwise.
For Table 21-2 note that if custom dimensions have aliases, the aliases (rather than CustomN) are displayed as column names.
Table 21-2 HFMData
Column | Description |
---|---|
Scenario |
A Scenario dimension member; example: |
Year |
A Year dimension member; example: |
Entity |
An Entity dimension member, in |
Account |
An Account dimension member; example: |
Value |
A Value dimension member; example: |
ICP |
An Intercompany Partner dimension member; example: |
Custom1 |
A Custom1 dimension member; example: |
Custom2 |
A Custom2 dimension member |
Custom3 |
A Custom3 dimension member |
Custom4 |
A Custom4 dimension member |
Period |
A Period dimension member |
Data Value |
The value associated with the intersection. This value is passed as a Double. |
Description |
A description of the data value |
For Table 21-3 note that if custom dimensions have aliases, the aliases (rather than CustomN) are displayed as column names.
Table 21-3 HFMData_MultiplePeriods
Column | Description |
---|---|
Scenario |
A Scenario dimension member; example: |
Year |
A Year dimension member; example: |
Entity |
An Entity dimension member, in |
Account |
An Account dimension member; example: |
Value |
A Value dimension member; example: |
ICP |
An Intercompany Partner dimension member; example: |
Custom1 |
A Custom1 dimension member; example: |
Custom2 |
A Custom2 dimension member |
Custom3 |
A Custom3 dimension member |
Custom4 |
A Custom4 dimension member |
Period1..n |
For every data value being loaded, a period must be specified. The number of periods to be loaded for each intersection is specified when the Hyperion Financial Management model is reversed. A period column is created for each specified period. |
Data Value1..n |
Data values to be loaded. The number of periods to be loaded for each intersection is specified when the Hyperion Financial Management model is reversed. A data value column is created for each specified period. This value is passed as a Double. |
Description1..n |
A description for each data value |
Table 21-4 Account
Column | Description |
---|---|
Member |
An account table; required |
Description |
A description for the account; required |
Parent Member |
The parent account member |
Account Type |
Required; Valid account types:
|
Is Calculated |
Whether the account is calculated. Valid values: |
Is Consolidated |
Whether the account is consolidated into a parent account Valid values: Y if the account is consolidated into a parent, or N (default) if it is not. |
Is ICP |
Whether intercompany transactions are allowed for this account. Valid values:
If you specify Y or R, enter the name of the ICP TopMember. If you do not enter the top member, the default, [ICP TOP], is used. |
Plug Account |
The name of the account used for identifying discrepancies in intercompany transactions; required if intercompany transactions are allowed for this account. |
Custom 1...4 TopMember |
The top member in the hierarchy of a Custom dimension that is valid for the account. The specified member, including all of its parents and descendants, is valid for the account. All other members of the Custom dimension are not valid for the account. These columns required if intercompany transactions are allowed for this account. |
Number of Decimal Places |
The number of digits to display to the right of the decimal point for the account values; required. Specify an integer from 0 (default) to 9. |
Use Line Items |
Whether the account can have line items.Valid values: Y if the account uses line items, or N (default) if it does not. |
Aggr Custom 1...4 |
Whether aggregation is enabled for intersections of the account and the Customdimensions. This column is used for special totals, not summing. Valid values: Y (default) if the account is allowed to aggregate with Custom dimensions, or N if it is not . |
User Defined 1...3 |
Optional custom text for the account |
XBRL Tag |
Optional XBRL tag for the account |
Security Class |
The name of the security class that defines users who can access the account data. Default: DEFAULT security class. |
ICP Top Member |
The top member of the ICP group assigned to the account |
Enable Data Audit |
Whether data auditing is enabled for the account. Valid values: Y (default) to enable auditing, or N to disable auditing |
Description 2...10 |
Optional additional descriptions for the account |
Table 21-5 Entity
Column | Description |
---|---|
Member |
An entity label; required |
Description |
A description for the entity; required |
Parent Member |
The parent entity member |
Default Currency |
The default currency for the entity; required. |
Allow Adj |
Valid values: Y if journal postings are permitted, or N (default) if journal entries are not permitted. |
Is ICP |
Valid values: Y if the entity is an intercompany entity, or N (default) if it is not. Note: An intercompany entity is displayed in the POV in the ICP dimensions under [ICP Entities]. |
Allow Adj From Child |
Valid values: Y if journal postings from children of this parent entity are permitted, or N (default) if they are not. |
Security Class |
The name of the security class that defines users who can access the entity's data. Default: |
User Defined 1...3 |
Optional custom text for the entity |
Holding Company |
The holding company for the entity. Valid values: Any valid entity or blank (default). |
Description 2...10 |
Optional additional descriptions for the entity |
Table 21-6 Scenario
Column | Description |
---|---|
Member |
A scenario label; required |
Description |
A description for the scenario; required |
Parent Member |
The parent Scenario member |
Default Frequency |
Period types for which data input is valid for the scenario; required. |
Default View |
Whether the view is YTD or Periodic; required. |
Zero View Non Adj |
Whether the view is YTD or Periodic when missing, nonadjusted data values exist; required. |
Zero View Adj |
Whether the view is YTD or Periodic when missing, adjusted data values exist; required. |
Consol YTD |
The view for consolidations; required Valid values: Y for YTD, or N for Periodic |
Support PM |
Whether Process Management command is enabled in Data Explorer; required. Valid values: Y to enable Process Management, or N to disable Process Management |
Security Class |
The name of the security class that defines users who can access the scenario data. Default: |
Maximum Review Level |
The maximum process management review level for the scenario. Enter an integer from 1 to 10. |
Use Line Items |
Valid values: Y if the scenario can accept line items, or N (default) if it cannot. |
Enable Data Audit |
Valid values: Y to enable auditing, or N (default) to disable auditing. |
Def Freq For IC Trans |
The default frequency for intercompany transactions. Enter a string that identifies a valid frequency for the application. The default value is an empty string, representing no default frequency. |
User Defined 1...3 |
Optional custom text for the scenario |
Description 2...10 |
Optional additional descriptions for the scenario |
Table 21-7 Currency
Column | Description |
---|---|
Member |
A currency label; required |
Description |
A description for the currency; required |
Scale |
The unit in which amounts are displayed and stored for the currency, which identifies where the decimal point is placed; required Must be one of the following valid integer values:
|
Translation Operator |
Whether conversions for the currency are calculated by multiplying or dividing the translation rate. Valid values: |
Description 2...10 |
Optional additional descriptions for the currency |
Table 21-8 Custom1-4
Column | Description |
---|---|
Member |
The label of a custom dimension member; required |
Description |
A description for the custom dimension member; required |
Parent Member |
The parent custom member; required |
Is Calculated |
Whether the base-level custom account is calculated.If a base-level custom account is calculated, you cannot manually enter values.Valid values: Y if the account is calculated, N if it is not calculated. |
Switch Sign |
Whether the sign is changed (Debit/Credit) for FLOW accounts using the following rules:
Valid values: |
Switch Type |
The account type change for FLOW accounts, following these rules:
Valid values: |
Security Class |
The name of the security class that defines users who can access the custom dimension member data. Default: |
User Defined 1...3 |
Optional custom text for the custom dimension member |
Aggr Weight |
The aggregation weight for the custom dimensions; passed as Double Default: 1 |
Description 2...10 |
Optional additional descriptions for the custom dimension member |