Skip Headers
Oracle® Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator
11g Release 1 (11.1.1)

Part Number E12644-07
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

21 Oracle Hyperion Financial Management

This chapter describes how to work with Oracle Hyperion Financial Management in Oracle Data Integrator.

This chapter includes the following sections:

21.1 Introduction

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.

21.1.1 Integration Process

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:

21.1.2 Knowledge Modules

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.


21.2 Installation and Configuration

Make sure you have read the information in this section before you start using the Oracle Data Integrator Adapter for Financial Management:

21.2.1 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/technology/products/oracle-data-integrator/index.html.

21.2.2 Technology Specific Requirements

There are no technology-specifc requirements for using the Oracle Data Integrator Adapter for Financial Management.

21.2.3 Connectivity Requirements

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:

  1. Exit ODI Studio, if applicable.

  2. Locate the existing HFM driver file and rename it from HFMDriver.dll to HFMDriver32.dll.

  3. 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

  4. Name this copy HFMDriver.dll.

  5. Restart ODI Studio.

21.3 Setting up the Topology

Setting up the Topology consists of:

  1. Creating an Hyperion Financial Management Data Server

  2. Creating an Hyperion Financial Management Physical Schema

21.3.1 Creating an Hyperion Financial Management Data Server

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:

  1. In the Definition tab:

    • Name: Enter a name for the data server definition.

    • Cluster (Data Server): Enter the Financial Management cluster name.

  2. 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.

21.3.2 Creating an Hyperion Financial Management Physical Schema

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.

21.4 Creating and Reverse-Engineering a Financial Management Model

This section contains the following topics:

21.4.1 Create an Financial Management Model

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.

21.4.2 Reverse-Engineer an Financial Management Model

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.

  1. In the Reverse tab of the Financial Management Model, select the RKM Hyperion Financial Management.

  2. 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.

21.5 Designing an Interface

After reverse-engineering a Financial Management application as a model, you can use the data stores in this model in these ways:

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:

21.5.1 Loading Metadata

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

21.5.2 Loading Data

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.

21.5.3 Extracting Data

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:

21.5.3.1 Extracting Financial Management Data

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.

21.5.3.2 Extracting Members from Member Lists

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.

21.6 Data Store Tables

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: Actual

Year

A Year dimension member; example: 2000

Entity

An Entity dimension member, in parent.child format. For example: United States.NewYork to specify member NewYork as a child of member United States.

Account

An Account dimension member; example: Sales

Value

A Value dimension member; example: USD

ICP

An Intercompany Partner dimension member; example: [ICP Entities]

Custom1

A Custom1 dimension member; example: AllCustomers

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: Actual

Year

A Year dimension member; example: 2000

Entity

An Entity dimension member, in parent.child format. For example: United States.NewYork to specify member NewYork as a child of member United States.

Account

An Account dimension member; example: Sales

Value

A Value dimension member; example: USD

ICP

An Intercompany Partner dimension member; example: [ICP Entities]

Custom1

A Custom1 dimension member; example: AllCustomers

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:

  • ASSET

  • LIABILITY

  • REVENUE

  • EXPENSE

  • FLOW

  • BALANCE

  • BALANCERECURRING

  • CURRENCYRATE

  • GROUPLABEL

  • DYNAMIC

Is Calculated

Whether the account is calculated.

Valid values: Y if the account is calculated, or N (default) if it is not calculated and manual input is enabled

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:

  • Y if ICP transactions, including self-ICP transactions, are allowed

  • N (default) if ICP transactions are not allowed

  • R if ICP transactions are allowed but the account is restricted from having ICP transactions with itself

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: DEFAULT security class.

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: DEFAULT security class.

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:

  • Blank = None

  • 0 = Units

  • 1 = Tens

  • 2 = Hundreds

  • 3 = Thousands

  • 4 = Ten Thousands

  • 5 = Hundred Thousands

  • 6 = Millions

  • 7 = Ten Millions

  • 8 = Hundred Millions

  • 9 = Billions

Translation Operator

Whether conversions for the currency are calculated by multiplying or dividing the translation rate.

Valid values: D to divide (default) or M to multiply

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:

  • ASSET to LIABILITY

  • LIABILITY to ASSET

  • EXPENSE to REVENUE

  • REVENUE to EXPENSE

  • BALANCE to FLOW

  • FLOW to BALANCE

Valid values: Y if the account type is switched, or N if it is not switched

Switch Type

The account type change for FLOW accounts, following these rules:

  • ASSET to EXPENSE

  • EXPENSE to ASSET

  • LIABILITY to REVENUE

  • REVENUE to LIABILITY

  • BALANCE to FLOW

  • FLOW to BALANCE

Valid values: Y if the account type is switched, or N if it is not switched

Security Class

The name of the security class that defines users who can access the custom dimension member data. Default: DEFAULT security class.

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


Table 21-9 EnumMembersList

Column Description

Member

The members of the member list