When getting started with Oracle EPM, its important to do an analysis of how your organization is going to maintain the systems after development is complete. As your business changes and grows over time; products, accounts, entities, employees need to be added to your metadata structures in order to capture and report the data accurately.
When determining a metadata update strategy its important to understand the facts, the scale of your environment, and which products you have and need to update.
In this post, I’m going to go through a wide variety of metadata maintenance solutions at a high level, and then debate some pros, cons, and considerations of each. I also will note which EPM systems can be updated by the tool or process. I’m going to start with more advanced Enterprise-solutions, and then follow up with metadata maintenance options that are more simplified and application-specific.
First, here is a diagram of the tools covered today, and the EPM applications that can received metadata updates. Note, this does not include the new HFM metadata update tool that is in the development pipeline.
Data Relationship Management
It’s no secret that DRM is my favorite product; but only because of its lack of limitations. DRM can update metadata across the entire EPM suite: HFM Classic & EPMA, Planning Classic & EPMA, Essbase Classic & EPMA, ARM, FDM/FDMEE, HPCM, and also outside of Oracle Hyperion products, like GL systems, transactional systems, HR systems, homegrown systems, etc.
DRM is completely configurable to have custom downstream property placeholders for any system, and then exports of your hierarchy trees and those properties are sent in any format (or directly to a database) to be loaded into your applications. The thought here is that it is also scalable; as you add Essbase or Planning applications to your EPM environment, the hierarchies can be leveraged from DRM.
Most of the time, however, another integration tool has to be used in conjunction to load the DRM metadata into the application (except for PBCS and EPMA 18.104.22.168+):
- HFM Classic: Export .app file from DRM and load through Workspace using Administer > Load Tasks > Load Metadata
- HFM EPMA: Direct integration in 22.214.171.124, but otherwise have to upload to interface tables or export an .ADS file format from DRM and load into EPMA
- Planning (On Prem): Use Outline Load Utility or ODI to load file to application
- PBCS: Use the File Transfer Utility (EPMCopy) to transfer a file from a DRM Outbox to the PBCS server, and then schedule the dimension load in the Simplified Interface
- Essbase: Use Load Rules or ODI to load file to application
Valid For: All EPM, HFM EPMA & Classic, On-Prem Planning (EPMA & Classic), Essbase, HPCM, PBCS
Other Features: DRM is compatible with PBCS and has a roadmap to integrate to all upcoming Oracle Cloud EPM applications; Provides audit trail capabilities for metadata changes; DRG module for master data workflow process; simple installation and migration
Other Considerations: If there are other systems outside of EPM that leverage the same hierarchies, they can be integrated as well, including EBS, SAP, PeopleSoft, etc.
There are 3rd party adapters coming available for PeopleSoft and Salesforce, or a consultant can build the integrations to the databases. Even custom “home grown” systems can be integrated with DRM.
One caveat of DRM is that although it is a “one stop shop” and an Enterprise solution, it can be a little pricey on the licensing and implementation side. However, clients that implement it save so much time doing metadata updates and reconciliations, that they don’t know how they lived without it. You get what you pay for 🙂
EPMA is a utility that lives within Workspace that allows a graphical view of hierarchies and trees, properties, and application settings.
EPMA has a “Shared Library” that allows hierarchies to be shared and managed across applications; which encourages consistency. “Local” dimensions are application specific, and can opt out of the “Shared Library”, if desired. To update metadata, right click the application and “Deploy” it within the Application Manager. If there are any issues with the metadata, the validation won’t allow the application to deploy.
This is a great solution if you do not require much auditability into the metadata maintenance process, and have applications that can all be converted to EPMA.
Valid For: HFM (not Classic), On-Prem Planning, Essbase, HPCM
Other Features: Synchronizes Data between Applications (Ver. 126.96.36.199), Direct Integration from DRM (Ver. 188.8.131.52)
Considerations: Not supported on Linux or Exalytics, and no plans to do so. Also, please note that at this time there is no development on the EPMA product, nor is it on the roadmap to move to the cloud.
Much like legacy ERPi, FDMEE comes with system adapters that allows you to pull metadata and data directly into FDMEE (with the assistance of Oracle Data Integrator) to be transformed and loaded to your downstream EPM applications, without the use of a flat file.
Metadata rules are created to pull data from the source system, apply any metadata mapping considerations, and load the metadata into the EPM application. One thing to consider, is that a validation needs to be performed beforehand to ensure than no metadata coming from the source GL has special characters that are not supported by the downstream system. If you need a reference on what characters are not supported, see my post on metadata limitations here.
Metadata rules will usually prefix or suffix members (like adding an “A” to an account segment), and also define which hierarchies to integrate and any statistical accounts.
Valid For: Planning, HFM, and HPCM
Loading data to Target applications, including:
- Planning (Classic and EPMA)
- HFM (Classic and EPMA) and Tax Module
- Custom downstream file formats
Data Mapping: FDMEE also maps data from one system to a target system utilizing mappings – mapping rules are applied in order from most generic to most specific: like, between, in, and explicit.
Write Back: FDMEE can be configured to “write back” data from EPM into a source system.
Drill Through: FDMEE can be configured to drill through FDMEE into a source transactional system to view specific entry detail.
Other Considerations: FDMEE was an acquisition (formerly Upstream) and has been transformed to a one-stop metadata and data shop. The change from FDM to FMDEE happened in version 184.108.40.206, which added tons of functionality. Due to the fact that we are on version 220.127.116.11, there are still some serious bugs and lots of patches to the software.
Unfortunately, there are not many options for supported integrations (Version 18.104.22.168):
- Fusion GL and EBS
- GL Segments are mapped to dimensions
- PeopleSoft Enterprise Financial Management
- Chartfields are mapped to dimensions
*Human Resource Systems are not supported.
ODI is often seen as a facilitator for other utilities on this list. For instance, DRM can export a .app file for HFM, or a columnar file for Planning, and ODI can pick up the file and load it with the proper ODI Knowledge Module (KM).
Oracle Data Integrator (ODI), integrates nearly any Oracle products, not just EPM. In fact, EPM application is just a mere sliver in what ODI can do.
Additionally, FDMEE actually leverages ODI functionality under-the-covers, but provides an easier interface for the integrations.
Valid For: HFM, Planning (On-Prem), Essbase, HPCM, Other
Other Features: Import, Map and Export metadata and data from a long list of systems utilizing the correct Adapter/KMs; ODI also can be scheduled with the ODI Automator, relieving the need for any other batch or Enterprise scheduler.
Other Considerations: ODI is not a “user friendly” tool, like Excel, or graphical like DRM. It is a very IT centric tool, and may often times need an administrator or IT contact to maintain.
Also at this time, the ODI KM for HFM is not available for the 22.214.171.124 release of HFM. There will be a new Planning adapter in release 12c, and the Essbase KMs are going to remain unchanged. If you want more information, see the “Statement of Direction” for ODI straight from Oracle.
HFM Desktop Client
The HFM client is the “fat client” that is located on the HFM server and is used for managing metadata for Classic HFM applications and creating application profiles. The “fat client” is not very flashy, hasn’t been updated in several versions, and is very clunky to use; however it allows the user to submit metadata directly to the HFM application.
Valid For: HFM Classic Only
Other Features: Creating the HFM .per (profile) file
Other Considerations: The HFM Desktop Client is “going away” in future releases of HFM, potentially starting with the next EPM release (126.96.36.199 or 12).
Outline Load Utility
The Outline Load Utility was previously a back-end-only utility to load flat files in specified format to Hyperion Planning (On-Prem). This allowed administrators to update dimensionality off of files from other systems (such as DRM) and also able to schedule the loads.
In version 11.2.3, some of the functionality of Outline Load Utility was moved to Workspace. Users are able to Import and Export Metadata, and Import and Export Data.
Valid For: Planning Classic (On-Prem)
Other Features: Importing and Exporting Data, Exporting Metadata, automatable with .bat files and ODI, Windows Scheduler, or other Enterprise schedulers.
Other Considerations: The Outline Load Utility has been modified for PBCS use also; although there are some issues with formatting when exporting from the Outline Load Utility to a .csv file (aliases with commas expand out extra columns, exporting formulas goes to many lines, etc).
The funcitonality within Planning to go to Administer > Import and Export > Import Metadata from File and Export Metadata from file are both utilizing the same Outline Load Utility functionality, but through workspace. This is available in both On-Prem and PBCS, but is a manual process and cannot be automated.
Starting in version 188.8.131.52 metadata can be manipulated and sent to the application via SmartView. By connecting to the application in the SmartView Connections Panel, arrow through the folders to Dimensions.
Add members by defining the name, parent member, and any other required properties of the dimension and press “Submit Data” in the SmartView ribbon – and voila. Metadata management with Excel!
Valid For: Planning Classic (On-Prem and PBCS)
Other Features: Retrieving and submitting data / Ad-Hoc Reporting Tool
Other Considerations: Although this gives an educated administrator some lee way to make dimension changes on the fly; its important to set security appropriately so untrained users don’t change properties or update the hierarchy incorrectly (or delete members!)
Essbase Load Rules
You may be familiar with loading data via an Essbase Load Rule, but you can also use the Load Rule functionality to build a dimension. Define the parent/child relationship in the Build rule, and build a hierarchy from a flat file.
Valid For: Essbase Only
Other Features: Load rules can be used for loading data in conjunction with building dimensions.
Other Considerations: Essbase Build Load Rules and Data Load Rules can be automated via MaxL scripting and a batch scheduler to run at a certain time/frequency. The dimensions can be built from DRM export files, or other parent/child formatted output from other systems.
Although Planning’s back-end is technically an Essbase cube, any changes made in EAS will get over-written when Planning is refreshed. All Planning metadata changes must occur via the Dimension editor in Workspace, Outline Load Utility, or EPMA.
EPM Automate is a utility downloadable from PBCS Workspace to assist with the transfer of files from a On-Premise server to the cloud inbox, and then run a metadata load rule. The caveat of this is that a file needs to be generated by another source with the metadata information necessary for PBCS (manual, DRM, ODI, etc).
EPM Automate uses batch scripting to load metadata and data through the Simplified Interface on jobs (something an Admin can easily do). The batch script allows the jobs to be run in sequence for all maintenance to the application, such as:
- Load Metadata
- Refresh Application
- Load Data
- Run Business Rule
- Extract Metadata
- Extract Data
Valid For: PBCS Only
Other Features: Importing and Exporting Data, Exporting Metadata, automatable with .bat files and in-cloud scheduling
Other Considerations: The EPM Automate feature uses the Simplified Interface, which is the direction Oracle is going with all Cloud applications. What does this mean? Essentially, Workspace is going away. The sooner you can adopt the Simplified Interface as an organization, the easier the transition is going to be when that happens.
Dimension Editor – Planning Classic (On-Prem and PBCS)
Dimension Editor in workspace is the most manual way of loading metadata. If you have more than one or two members to add, this can be a time consuming experience. If this is the case, it may be more effective to export the hierarchy using Workspace > Administration > Outline Load Utility > Extract Metadata for the dimension, and then manually editing the .csv file output and then loading it back in.
If given administrative permissions, to go Administer > Extract Tasks > Extract Metadata to extract the file. Open the file in a text editor, and make appropriate changes. It is imperative that the “columns” for each dimension are filled in appropriately. There are 2 sections per dimension, Members and Hierarchy. Members defines the member and the appropriate properties, where Hierarchy defines the parent/child format. Make updates to both areas. The file format should be .app.
In HFM, go to Administer > Load Tasks > Load Metadata to validate and then load the file.
Essbase – EAS
For Essbase, to manually edit the outline of the cube, you must go into EAS and add members manually. Right click in the hierarchy to add the member, and define any properties or UDAs.