How to Integrate Financial Analytics with Oracle Essbase for E-Business Suite
Oracle Essbase, with its architecture, provides the capability to load aggregated data, which results in better retrieval times as compared to a similar request on a star schema in the data warehouse. Also, it allows us to perform dynamic aggregation, which helps to load the FSG hierarchies into Oracle Essbase, and build a similar report from OBIEE using the data loaded into the cube. You can leverage these key benefits of Oracle Essbase and integrate GL data to provide the capability of reporting against the Essbase data.
One of the key requirements in Oracle BI Applications Financial Analytics has always been to report general ledger data against multiple accounting segment hierarchies, that is, to perform roll-up reporting against the accounting segment trees. In addition, there has been a requirement to build a financial report in Oracle BI that is exactly similar to the FSG report in Oracle E-Business Suite. The FSG report involves various kinds of arithmetic aggregations in the hierarchy structure and also a drill down on the measures within these financial reports.
Overview
In integrating Financial Analytics with Oracle Essbase for E-Business Suite, we do the following:
-
Load GL Balances and GL Budgets (Actuals and Budgets) data from our data warehouse into the Essbase cube.
-
Load the accounting segment hierarchies (Value Set Hierarchies in EBS terminology) into the cube. Unlike the hierarchies that are loaded in the DW, that are level-based and have a consistent number of levels, the hierarchies that are loaded into the Essbase cube are ragged hierarchies and will be reported as ragged hierarchies from OBIEE.
-
Load FSG row set hierarchies into one of the dimensions in the cube. Such a dimension is referred to as the Financial Reporting dimension from that point.
-
Integrate Essbase with OBIEE as well, and provide the capability of reporting Essbase data from OBIEE. In addition to the Financial Reporting dimension, this integration supports all other key GL dimensions, such as Time, Ledger, Budget, Accounting segment dimensions such as Balancing Segment, Cost Center, Natural Account, GL Segment1 through GL Segment10, Program Segment, and Project Segment. This enables users to build reports that are similar to the ones built from the regular DW GL Star schema.
Key Notes
-
For the financial reporting dimension, only the row set hierarchies have been brought in from EBS, thereby allowing you to perform various arithmetic aggregations among the hierarchy nodes. Base metrics such as Budget Amount, Balance Amount, and Activity Amount are available for reporting. If required, additional metrics such as YTD and PTD metrics can be built as a customization on top of the base metrics that are available out of the box.
-
The base metrics provided out of the box are supported in 4 different currencies – Ledger Currency, Global1 Currency, Global2 Currency, and Global3 Currency. There is no support for document currency in this release.
-
This integration is currently supported only for budgets and actuals data. Encumbrances are not loaded into the Essbase cube.
-
This integration is currently supported only for a DW loaded from a single EBS instance. Integration with a multi-source DW or a multi instance DW is not supported.
-
With this integration, users are expected to retrieve data that is residing in Essbase only through OBIEE and not through other Essbase clients, such as smart view.
-
Ledger security has been implemented for this integration. Data viewed by users is restricted to the ledgers that the users have access to.
Performing the Integration
The following are the prerequisites required for this integration to work:
-
Create an ODBC connection with the name OBIAFA from your Essbase server host, pointing to your Oracle DW target database.
Note:
It is important that you use the same connection name (OBIAFA) as mentioned here.If your Essbase server is on a Windows host, create the ODBC connection using the DataDirect 6.0 Oracle Wire Protocol driver.
-
Perform the following steps to create a physical data server in the ODI repository pointing to your Essbase server, and then to assign it to the appropriate execution context:
-
In your ODI repository, navigate to Topology > Physical Architecture > Technologies > Hyperion Essbase, and create a new physical data server with the name Essbase_BIAPPS11G.
-
Create a new physical schema for the new physical data server. Hardcode the application and the database name to be OBIAFA.
-
In your ODI repository, navigate to Topology > Contexts > Global, and assign the newly created physical server and schema to the appropriate logical schema for the Global Context.
On the Schemas tab, set the logical schema ESSBASE_BIAPPS11G to the new physical schema that you created in the previous step.
-
Executing Oracle Essbase Load Plans
All the mappings that are required to load the Essbase cube are shipped in separate load plans (one per EBS version) and are not integrated with the regular load plan components. Therefore, these load plans must be run after the regular DW load plan execution is completed.
To execute the Essbase load plans:
-
In ODI studio, navigate to Load Plans and Scenarios > Predefined Load Plans > Essbase, and run the load plans in full mode.
Note:
The Oracle Essbase load plans can only be run in full mode, that is, there is no incremental logic implemented for any of the mappings in the load plan. Every time these load plans are run, even the Essbase cube is refreshed in full. -
In the Startup Values window that is displayed when executing these Load Plans, provide values for the following parameters:
-
DATASOURCE_NUM_ID: The data source ID that is configured for your source E-Business Suite instance in the Oracle BI Applications Configuration Manager. All mappings have a filter on the DSN value. Therefore, to ensure that the mappings process the data, it is important that you provide the correct value for this parameter.
-
LANGUAGE_BASE: The language base value configured for this parameter in Oracle BI Apps Configuration Manager.
-
ESSBASE_LOG_LOCATION: Location where all the log files are stored. Log files are useful for debugging purposes.
-
IS_FIN_ESSBASE_ENABLED: Set this parameter value to Y.
-
Building a Financial Report from OBIEE Answers
After the execution of the Essbase Load plans are completed and the data loaded into Essbase, you can build Financial Reports, similar to FSG reports in E-Business Suite, using the Financials – GL Balance (Essbase) subject area. This topic explains the basic steps for building a financial report from OBIEE answers.
-
Drag the necessary columns from the Financials – GL Balance (Essbase) subject area. For the Financial Reporting dimension, drag the Financial Reporting presentation hierarchy.
-
Apply the appropriate filters.
-
Navigate to the Results tab.
Based on the filters that are applied, all the applicable hierarchies are displayed in the report. These hierarchies are FSG row sets that are defined in E-Business Suite.
-
Drill down to any hierarchy or row set of your choice.
-
To see only one hierarchy in a report and save the report for future usage, perform the following steps:
-
To filter out the necessary hierarchy, for example, Balance Sheet hierarchy, use the Selection Steps feature of OBIEE.
-
In the lower pane, expand Selection Steps and edit the Dim – Financial Reporting dimension to start with the hierarchy of your choice.
Only the specified hierarchy is displayed on the screen.
-
Expand all the nodes in the hierarchy, and save the report for future usage.
-
Applying Segment Overrides in the Financial Reports
When an FSG report is built in E-Business Suite, it is common practice to override some segment values for the COA structure. For example, a Balance Sheet report can be built for a company, product, and sub account combination. This topic explains how to apply such filters on reports that are built from OBIEE.
Consider the example of a Balance Sheet report that does not have any segment filters, and assume that you must apply a filter on Company (Segment Value – 01), Product (Segment Value – 000) and Sub account (Segment Value – 0000) segments. Before you apply these filters, you must know the dimensions to which these segments map in Oracle BI Applications, and then apply the filters accordingly. This is because filters are applied on the code columns of the respective dimensions. For example, if Company maps to the Balancing Segment dimension, Sub Account maps to the GL Segment1 dimension, and Product maps to GL Segment2 dimension, the filters are applied on the code columns of these dimensions.
All the segment values are stored as hierarchies in the Essbase cube, and therefore, the filters are applied as fully qualified paths and must strictly adhere to the following format:
[<Segment String>].[<Value Set ID>].[Value Set ID>~<Segment Value>]
The segment strings to be used for the various dimensions are as shown in the following table:
| Dimension | Segment String |
|---|---|
| Balancing Segment | All Balancing Segments |
| Natural Account | All Natural Accounts |
| Cost Center | All Cost Centers |
| GL Segment 1–10 | All Segments |
The following are a few more examples for a better understanding of applying filters:
-
Apply a filter on Cost Center, Account, and Company segments, which map to Cost Center, Natural Account, and Balancing Segment respectively.
-
Apply a filter on Account and Organization segments, which map to Natural Account and GL Segment3 respectively.
Loading Financial Reporting Hierarchies from a CSV File
This section is applicable for E-Business Suite customers who don’t have FSG implementation but still want to leverage the Oracle Essbase integration and build financial reports from OBIEE. It explains how to add financial reporting hierarchies in a CSV file so that the ETL process loads these hierarchies into the cube, similar to how FSG hierarchies areloaded into the cube. The remaining process of building the report in OBIEE is as explained in the previous sections.
You can define the financial reporting hierarchies using the configuration file, file_group_acct_hier.csv. The configuration file or files for this task are available at either of the following locations when you install Oracle BI Applications:
-
Source-independent files: <Oracle Home for BI>\biapps\etl\data_files\src_files\
-
Source-specific files: <Oracle Home for BI>\biapps\etl\data_files\src_files\<source adaptor>
However, the system administrator would have copied these files to another location and configured ODI connections to read from this location. Work with your system administrator to obtain the files. When configuration is complete, a system administrator must copy the configured files back to the location from where ODI reads these files.
Edit the file_group_acct_hier.csv file and define any financial reporting hierarchy you need. The following screenshot provides an example for one such hierarchy.
Notes:
-
While defining the hierarchy, enter all the child nodes under the CHILD_GROUP_ACCT_NUM column, and the parent nodes under PARENT_GROUP_ACCT_NUM column. The values in these columns must be the Group Account codes. The names and descriptions for these group account codes can be entered in Oracle BI Applications Configuration Manager, for the Group Accounts domain. Ensure that the values entered in the CSV file match the values defined in Configuration Manager.
-
The AGGREGATION column indicates the arithmetic operator between the nodes in the hierarchy, and it can be +, -, *, or /.
-
The SEQUENCE column indicates the sequence of child nodes for any parent node in the hierarchy. For example, if Gross Profit is defined as REVENUE-COGS, the sequence is defined as 1 for REVENUE and 2 for COGS. This sequence is important because the nodes are entered in the same order as mentioned in the CSV file into the cube, and it enables Essbase to perform the appropriate arithmetic operation or consolidation in the proper order.
-
The following example explains how to define this SEQUENCE column better. Let us assume that you want to define an expression for a node A as A = (B+C)/(D+E).
In such a case, it is always prudent that you split these operations. Therefore, define two intermediate nodes:
-
X, whose children are B and C with sequence 1 and 2 respectively.
-
Y, whose children are D and E with sequence 1 and 2 respectively.
Then define the node A, with children X and Y with sequence 1 and 2 respectively. This enables Essbase to perform the consolidation in the appropriate order, as mentioned earlier.
Note:
Two sample hierarchies, Sample Income Statement and Sample Balance Sheet, are included in the out-of-the-box file, and can be used as references. -