Financial Data Quality Management Enterprise Edition (FDMEE) is Oracle’s strategic product for integrating ERP systems with Oracle’s Hyperion EPM applications. It provides seamless integration of both master data and data between major ERP systems such as E-Business Suite, PeopleSoft GL, SAP and Hyperion EPM applications. See the architecture diagram below:
FDMEE is embedded in the Oracle Enterprise Performance Management System architecture and is accessed through Oracle Hyperion Enterprise Performance Management Workspace. Like Workspace, FDMEE uses Shared Services to authenticate users. Another key component of FDMEE is Oracle Data Integrator. FDMEE sits on top of Oracle Data Integrator and orchestrates the movement of metadata and data into Hyperion EPM applications. Apart from data transformation/data transfer, another key feature of FDMEE is its ability to drill back to the source ERP system. Drill back can be performed from EPM products such as Planning, HFM, Smart View, Hyperion Financial Reports and FDMEE Data Load Workbench.
Now that you know the purpose and components of FDMEE, I want to describe my top 6 tips when building an FDMEE integration that sources from E-Business suite.
Tip #1 – Designing your Master Data Integration
The mapping of the segments between the source and the target application is performed here. It may be a one to one mapping or alternatively, multiple segments from your chart of accounts can be mapped to a single dimension. These mappings depend on the reporting needs of the target application. Below are examples of one to one and multiple segment mapping options in FDMEE.
EBS Segment mappings are used to derive the target members for each Essbase dimension based on the source value. Hierarchies must be defined in the source system for integration using FDMEE. At a minimum, at least one top parent must be defined for each segment mapped from EBS to the target dimension.
From an architecture perspective, the base hierarchies can be maintained on the source system since the General Ledger is usually considered the system of record. This is an easy task that can be performed in EBS by setting up a top parent (aka. T Parent) by defining their range of minimum and maximum, where ‘0’ would usually represent the minimum value and a value like ‘ZZZZZZZ’ would represent the maximum range value of a segment.
Tip #2 – Data Integration
Common errors that occur during the data load are due to
a. The database user used to configure the source system (in ODI) does not have the necessary privilege
The easiest approach would be to use the APPS user while establishing your database connection with your source ERP system and Oracle Data Integrator (ODI). Since, the APPS schema has access to the complete Oracle E-Business Suite data model, it is analogous to the SYSTEM schema, which has access to the entire database. Hence IT administrators tend not to provide the APPS user credential for this reason.
The other option would be to create a database user who has the necessary grants and privileges to the tables, views, and packages as outlined in the administrator guide by source system. Additionally, if you do not plan to use FDMEE to write back to your ERP system, read only privilege is sufficient.
b. Data Load Mapping is not properly defined
One of the most common errors encountered during the data load is “Data rows with unmapped dimensions exist for Period ‘OCT-07’”. After defining the data load rule, choose the Data Load Mapping and for each dimension, click on the “Like” tab which shows the field “Apply to Rule” is blank. This must be populated with the name of the data load rule and applied to the mapping. This can be validated using Data Load WorkBench.
Tip #3 – Performance Management Architect Applications
To configure Performance Management Architect with FDMEE, the interface data source must be configured as part of the FDMEE database schema. Make sure that you select the option to create tables that create the EPMA Interface Tables in the FDMEE database schema. This only applies to applications that are deployed using EPM Architect.
After defining the EPMA Interface Data Source, it must be referenced in your EPMA FDMEE System Settings as shown below.
A system generated EPMA import profile is automatically created when the FDMEE Metadata rule is executed.
Tip #4 – Apply Latest Patches
It is highly recommended to install the latest release of the FDMEE patch set. As of this writing, that is release (.520). Information regarding the most recent patch set updates as well as the link to download the patch set updates can be found on My Oracle Support (https://support.oracle.com/).
Tip #5 – ERPi Drillback
SmartView and HFR currently does not allow FDMEE drill through to the EBS General Ledger source system. This is a known product defect in FDMEE 11.1.2.520 PSU. To get around this defect, create a drill through region directly in EAS or using the MaxL Client. Choose the year, all of the periods for that year, and the scenario. Then use the following Essbase drill through xml content:
The above xml content can be stored in an xml file and later imported to EAS or referenced in the MaxL Script while creating the Essbase drill through definition.
Essbase drill through definitions
The Essbase drill through definitions can be created directly in EAS or using MaxL. Before defining the Essbase drill through region definitions, the existing drill through definition created via ERPi must be dropped and recreated. The scripts to create the drill through definitions using MaxL are provided in the section below in their order of execution.
After launching the MaxL Client:
This section can be easily automated using a batch Script.
Tip #6 – Performance Tuning
One of the key steps in performance tuning is to monitor the performance of processes using the FDMEE Process Log files, with the log level set to 4 or 5. A Log Level of 4 provides summary information of the time taken for each dimension and each map. In some cases this information may not be enough to evaluate the indexing required. In that case Log Level 5 can be used. This provides a complete SQL statement for each mapping execution. Your DBA can help you analyze the information from the logs and create the necessary indexes. In general, writing to the system log slows performance slightly; Be sure to reset the log level back to 4 after performance tuning of the FDMEE database.
For further information, refer to the Oracle FDMEE Tuning Guideline:
http://www.oracle.com/technetwork/middleware/bi-foundation/fdmee-tuning-1112x-2349440.pdf
Thanks for reading! I hope you found these tips useful. As always, you can drop me a line at manooj.thomas@perficient.com to discuss FDMEE or any other application in the Hyperion suite.
