Integrating Oracle® Hyperion Financial Management, Fusion Edition 11.1.2 Data with Oracle® Essbase Analytics Link for Hyperion Financial Management

<Do not delete this text because it is a placeholder for the generated list of "main" topics when run in a browser>

Purpose

This tutorial covers integrating Financial Management data with Essbase Analytics Link for Financial Management.

Time to Complete

Approximately 1 hour.

Overview

The current integration process of Financial Management and Essbase data is a long and expensive manual process that consists of the following steps:
1. Extract data from Financial Management.
2. Build an Essbase outline.
3. Load Essbase with Financial Management data.
4. When Financial Management data changes, repeat steps 1 through 3.

Essbase Analytics Link for Financial Management provides near-real-time data synchronization between Financial Management and Essbase, enabling Essbase users to view live Financial Management data without having to extract and load tasks. The integration can be performed in real time, where data changes in Financial Management are immediately represented in Essbase, or it can be performed on demand, where you control when the synchronization takes place.

It enables accelerated financial reporting for a financial close and delivers preintegrated reporting to Financial Management from Essbase and Oracle Business Intelligence (BI).

Scenario

Your company wants to speed up its closing process by analyzing Financial Management data in real time. The existing integration between Financial Management and Essbase is a time-consuming effort involving extracting and loading data manually. Your manager asked you to use Analytics Link to integrate real-time data between Financial Management and Essbase.

Prerequisites

Before starting this tutorial, you should:

.

Have created the Analytics Link Servers node by enabling the Analytics Link Administration Services Console plug-in

.

Have added the following Analytics Link components to the Analytics Link Servers node:

a) Analytics Link Application Server

b) Financial Management server and application

c) Essbase server, database, and application

d) Data Synchronization Server

e) Data Store

.

Have installed and configured Oracle Hyperion Smart View for Office, Fusion Edition 11.1.2 with Essbase server

Creating an Analytics Link Bridge

In this part of the tutorial, you create an Analytics Link bridge for a Financial Management application, an Essbase outline, an Analytics Link Data Synchronization Server, and an Analytics Link repository. The bridge enables you to build a whole or partial replica of the Financial Management application metadata in an Essbase outline.

When you create a bridge, the Analytics Link application is automatically created. The application is a representation of the Financial Management application within the Data Synchronization Server. The Data Synchronization Server is an aggregation engine that builds its own database based on the metadata and data of the Financial Management application.

Note: The Data Synchronization Server does not perform consolidations. Consolidations must be performed in Financial Management.

.

Navigate to Programs > Start > Oracle EPM Products > Essbase > Essbase Administration Services > Start Administration Services Console.

The Oracle Essbase Administration Services Login dialog box is displayed.

 

.

Perform the following actions:

a) In the Administration Server list, select a server.

b) In the Username field, enter a user name.

c) In the Password field, enter a password.

d) Click OK.

The Oracle Essbase Administration Services console is displayed.


.

Select the Enterprise View tab.

Enterprise View is displayed.

 

.

Expand the Analytics Link Servers node.

The Analytics Link Application Server is displayed.

 

.

Expand the Analytics Link Application Server.

The Financial Management Servers, Essbase Servers, Data Synchronization Servers, Data Stores, and Bridges are displayed.

 

.

Right-click Bridges, and select Add Bridge.

The Add Bridge dialog box is displayed.

 

.

Enter the following information:

a) In the Name field, enter a name for the bridge.

b) In the Type list, select Mirror.

c) Select a Financial Management server and application.

d) Select an Essbase server, application, and database.

e) Select a Data Synchronization Server and Data Store.

You are now ready to set the data synchronization interval between Financial Management and Data Synchronization Server.

Note: If you set the data synchronization to occur automatically, only data, not metadata is synchronized.

 

.

Clear the HFM Clusters Synch Maximum Delay check box, and in the Data Synchronization Interval field, enter a data synchronization interval (in seconds).

Click OK.

Note: If you choose not to clear the HFM Clusters Synch Maximum Delay check box, you can set the data synchronization interval in the Financial Management application settings.

 

.

Confirm that the bridge was successfully created.

 

.

Under Bridges, double-click the newly created bridge.

The bridge is displayed in a console.

Note: The Essbase database and application have still not been created.

 

.

On the Summary tab, click Rebuild Bridge.

 

.

Click Yes.

The Analytics Link application and the Essbase database and application are created from the Financial Management data and metadata.

 

.

Review the message and confirm that the Essbase database was successfully created.

 

.

Select the Data Synchronization tab.

Click Start Periodic Update Agent.

Based on the update interval specified (in seconds), Analytics Link automatically synchronizes the Data Synchronization Server with any changes in the Financial Management data.

Note: After starting the periodic update, you cannot manage the Analytics Link application: that is, you cannot create, delete, resume, or disable the application.

 

Customizing Essbase Outlines

When Analytics Link creates an Essbase outline with Financial Management data, all Financial Management dimensions and their members are represented in the outline. Analytics Link enables you to customize the Essbase outline so that you can include the required dimensions and members.

Renaming Financial Management Dimensions in Essbase Outline

When Analytics Link creates an Essbase outline with Financial Management data, Financial Management dimension names in Essbase are prefixed with “ESS" (such as ESSYear and ESSScenario).

You can rename the Financial Management dimensions in the Essbase outline.

.

In the Oracle Essbase Administration Services console, double-click a bridge to open it.

The bridge console is displayed.

 

.

Select the Essbase tab.

The Financial Management dimensions and Essbase dimension names are displayed.

Notice that the Essbase dimensions corresponding to the Financial Management dimensions are prefixed with ESS.


.

In the ESSDim/HFMember/Complicated column, double-click a dimension, and enter a new name.

 

.

Double-click another dimension and enter a new name.

 

.

Change the dimension names for other dimensions, and click Save.

 

.

Click Delete.

 

.

Click Yes to remove the existing database.

 

.

Click Create.

 

.

Confirm that a new database based on the updated bridge definition is created.

 

Renaming Financial Management Member Names in Essbase Outline

You use member mapping to rename the Financial Management member names in the Essbase outline. For example, you can display the YTD and QTD members in Financial Management as YearToDate and QtrToDate, respectively, in the Essbase outline. Before you can map member names, you must create a mapping table in the Analytics Link repository that is associated with the bridge, and you must enter the mapping data. In Analytics Link, the mapping is performed using an SQL statement.

.

In Oracle SQL Developer, navigate to the Analytics Link repository.

 

.

Expand the Analytics Repository, right-click Tables, and select New Table.

The Create Table page is displayed.


.

Perform the following actions:

a) In the Schema list, select a schema. (EALDS is selected in this tutorial.)

b) In the Name field, enter a name for the table. (In this tutorial, a table named "ViewMapping" is created for the View dimension.)

c) Click Add Column.

The Create Table page displays two columns.


.

Perform the following actions:

a) In Column 1, enter HFM.

b) In Column 2, enter ESS.

c) Click OK.

The completed table is displayed.


.

Select the Data tab.

The HFM and ESS columns are displayed.


.

Perform the following tasks:

a) Click Insert Row to insert rows for the HFM and ESS columns.

b) Name the rows as follows:

HFM ESS
YTD YearToDate
QTD QuarterToDate
MTD MonthToData

c) Click Commit Changes to commit the mapping data to the data store.


.

The mapping table is created.

Repeat steps 2 to 6 to create mapping tables for other dimension members that you want to rename.


.

Navigate to the Essbase tab of the bridge console and under Metadata Extraction Rules, perform the following actions:

a) Select the dimension with the changed member names. (In this tutorial, it is the View dimension.)

b) In the View row, double-click the field in the Mapping SQL column.

The Edit SQL page is displayed.


.

Enter a SQL statement to select the data from the mapping table. (In this tutorial, the SQL statement is SELECT * FROM ViewMapping.)

Click OK.

Repeat steps 8 and 9 to enter SQL statements for other mapping tables that you created.


.

Click Save.


.

Click Synchronize Mapping.


.

Click Delete.


.

Click Yes.

Confirm that the Essbase database was successfully deleted.


.

Click Create.

Confirm that the Essbase database was successfully created.


Selecting Dimension Members

When Analytics Link creates an Essbase outline with Financial Management data, all members in the Financial Management dimensions are represented in the outline. You can modify the dimension definition to include select dimension members and hierarchies. For example, you may want to select the 2009 and 2010 members for the Year dimension and the Actual and Budget members for the Scenario budget.

.

In the Oracle Essbase Administration Services console, double-click to open a bridge.

The bridge console is displayed.

 

.

On the Essbase tab, under Metadata Extraction Rules, select the dimension that you want to modify. (In this tutorial, it is Year.)


.

In the List of HFM Members column, double-click the field for the selected dimension.

The List of Members window is displayed.


.

Enter the members for the Year dimension, and click OK.

The list of members is displayed in the List of HFM Members column.


.


.

Repeat Steps 2 to 4 to select members for other dimensions, and click Save to update the bridge definition.


.

Click Delete.


.

Click Yes.


.

Click Create to create a new database with the updated bridge definition.


.

Confirm that the database was created successfully.

The selected dimension members are displayed.


.


Exracting Data and Metadata to Relational Databases

Analytics Link enables you to extract Financial Management data and metadata directly from the Analytics Link Data Synchronization Server into a relational database.

When extracting data, you can choose to append to, or drop data from, a previous extraction. You can also create extraction templates that are useful when performing the same extraction multiple times. Extraction templates are stored in the Analytics Link repository.

You can extract metadata only after the data is synchronized. Tables in the bridge data source are dropped and re-created each time you extract metadata.

Note: Oracle recommends that you create a separate database user if you plan to extract data and metadata.

.

In the Oracle Essbase Administration Services console, double-click to open a bridge.

The bridge console is displayed.

 

.

Select the Extract tab.


.

Perform the following actions:

a) In the Template Name field, enter a name for a template, and click Create.

b) Under Extraction Templates, in the Type column, specify a type for each Financial Management dimension.

c) Click Save Template.


.

Click the Summary tab, and confirm that the status of Analytics Link Data is "on."

Note: You cannot extract data if the status of the Analytics Link Data is "off." In that case, select the Data Synchronization tab, and under Analytics Link Data, click Resume.


.

On the Extract tab, perform the following tasks:

a) Under Extract Data, enter a table name where data will be extracted.

b) Optional: Select Drop if you want to clear data from a previous extraction; otherwise, data rows are appended.

c) Click Extract Data.

Confirm that data extract was successful.


.

Navigate to your bridge data store (relational database) and confirm that the table was created.


.

On the Extract tab, under Extract Metadata, perform the following actions:

a) Choose Parent-Child, Fixed-Level, or both hierarchy options.

b) Click Extract Metadata.

Confirm that the metadata extract was successful.


.

Navigate to your bridge data store (relational database), and confirm that the metadata tables are created.


Integrating Real-Time Financial Management Data with Essbase

When you extract Financial Management data and metadata to a relational database, you are extracting static data. If you make any changes to the data after data extraction, you must extract the data again.

By linking a Financial Management application to an Essbase application, Analytics Link enables you to integrate real-time Financial Management data with Essbase. Your changes to the Financial Management data are displayed in real time in Essbase, enabling you to analyze data in Essbase in real time.

Note: When you created the bridge, the TOTCONSOL Financial Management application was linked to the EAL Demo Essbase application.

.

On your desktop, perform the following actions:

a) Double-click the Workspace icon.

b) Enter your user name and password.

c) Click Log on to log on to Workspace.

 

.

Select Navigate > Applications > Consolidation > TOTCONSOL (name of the application).

The TOTCONSOL application is displayed.


.

On the Data Grids tab, select and open a data grid.


.

Select the following point of view (POV):

Dimension Value
Scenario Actual
Year 2010
Period June
View Periodic
Entity E01_101_1100.E01_101_1130 (North America-East Sales.PA)
Value <Entity Currency>
Account Defined in Rows
ICP [ICP None]
Custom1 [None]
Custom2 [None]
Custom3 [None]
Custom4 [None]

 

.

For June, enter the following amounts, and click Submit.

Account Amount
Travel Expense 25,890
Meals Expense 5,525
Office Supplies Expense 2,800


.

In Smart View, connect to the TotCons-EAL Demo Essbase application.


.

Enter a user name and password, and click Connect.


.

Click Ad Hoc Analysis, and on the POV bar, set the POV as follows:

Scenario Actual
Year 2010
Period June
View Periodic
Entity East Sales.PA
Value FM Entity Currency
Account Defined in Rows
ICP None FM ICP None
Custom1 No Segment
Custom2 Default Data Load
Custom3 Scenario@ Scenario Rate
Custom4 Custom4.FM None


.

Click Refresh.


.

The spreadsheet is updated with the data entered in Financial Management.


.

Navigate to the Financial Management application, enter the following amounts, and click Submit.

Account Amount
Postage and Freight 2512
Telephone Expenses 3258
Cellular Charges 1587


.

Navigate to Smart View, and click Refresh.

The spreadsheet is updated in Essbase in real-time.


Summary

In this tutorial, you learned how to:

Resources

About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Your Privacy Rights