In my last post, I introduced you to Oracle’s new Enterprise Resource Planning Integrator (ERPi). In this post, I explore ERPi in more detail.
ERPi Key Benefits
- Directly link into the general ledger to retrieve data on an ad hoc or scheduled basis
- Drill through from EPM application, SmartView or Financial Reports to the transactional level detail
- Ability to integrate with FDM to leverage the data integration capabilities of FDM
- Ability to source metadata from the general ledger to build the dimensions and hierarchies of your target EPM applications
- Ability to write back budget data from Hyperion Planning or Essbase to the general ledger
Drill Through With ERPi & FDM
For anyone that has been using FDM since the 9.3.1 days, drill-through (formerly “drill back”) is likely a known concept. In HFM, Planning, SmartView or Financial Reports, anyinput level cell loaded by FDM provides drill through to the detail in FDM.
This action brings you to the FDM “Landing Page” which displays all of the mapped records that make up the balance. The landing page also allows you to view common application events – such as last time data was loaded – to help determine if additional workflow events may be needed.
On the FDM drill screen, you can view each of the source records that make up the balance from which you have drilled in the target EPM application. When using ERPi, a new option is available: “Open Source System”
When integrating with Oracle eBusiness Suite, this menu item will bring you right to the Account Analysis page in eBS. There is no intermediate staging database with replicated data, this is your live G/L system. From this page, all of the native ERP functionality takes over.
You can drill into an account balance and see the transactional detail that comprises that balance. This is live general ledger data and all of the functionality available to you in eBS (or PeopleSoft) is able to be accessed.
Peeling Back the Onion
So at this point, you’re thoroughly impressed, right? But you’re asking yourself some questions:
- How does this all work?
- When do I use ERPi and when do I use FDM?
- Can I automate this?
Great questions and I’m glad you asked!
ERPi is technically a stand along product. It is part of the Oracle EPM Workspace. It leverages Oracle Data Integrator (ODI) to perform the following steps:
- One of the real value adds of the ERPi product is that a great majority of the complex, technical components (aka, ODI scenarios) are pre-written by Oracle and packaged as part of the product. This saves a tremendous amount of development time and minimizes the technical expertise needed to support this application once it is live.
- Transform* to the target EPM dimensionality
- Load** to the target EPM application
If you didn’t previously know what the acronym ETL meant, you do now.
During the Extraction stage, ODI reaches into the ERP system and extracts data into the ERPi application. This process is executed through the web interface of ERPi; however, FDM can be layered into the solution and use its
Once the Extraction is complete, the next step in the process is to transform the data from the ERP dimensions to the EPM dimensions. You may have noticed the asterisks next to Transformation & Load. Transformation in less technical terms is sometimes called mapping or translation. I avoid translation because many confuse it with currency translation. So for the sake of this post, let’s refer to transformation as mapping from now on.
Technically, ERPi can (and does) perform mapping. It has mapping functionality similar to FDM with:
- Explicit (1 to 1; Ex: Company 100 is US)
- Between (continuous range of members that map to a single target; Ex: Account 1000-1999 is Cash)
- Like (wildcard maps used to match patterns in the source system to reduce maintenance; Ex: All cost centers beginning with 65 are finance)
While ERPi has fairly powerful mapping capabilities, FDM provides the additional cross dimensionality/conditional mapping, logic groups and intersection validation to further enhance the data transformation process.
On the Load stage of the ETL process, ERPi will load mapped data into the target EPM applications (ex: HFM, Essbase). As with the Transformation stage, FDM offers additional benefits that ERPi stand alone does not yet have available. These include:
- Data Protection
- Automatic calculation of the target application following a load
- Post load validation (aka Check) report to ensure that data is properly mapped
Finally, as with most things EPM, yes ERPi can be automated to perform the entire ETL process. This can be accomplished with JAVA web services. Again, FDM can factor into this conversation. Since FDM and its source adaptor can initialize the ERPi process, The FDM batch loader can be used to provide lights out automation of the joint ERPi/FDM ETL process. The FDM batch loader, in its simplest form, requires almost no custom coding whereas developing a JAVA web service will.
Putting Humpty Dumpty Back Together Again…
So I’ve thrown a lot of information at you. You’ve learned that ERPi can do the entire end-to-end ETL process but the additional functionality that FDM offers provides a strong case for using ERPi with FDM.
So what’s the right solution? The honest answer is, it depends on your requirements, timeline & budget. Quite often, ERPi with FDM will be the right answer but there are situations where ERPi stand alone may be required. As with most things, there are few absolutes.
If you are considering ERPi and have questions, feel free to post here or contact me to arrange a time to discuss further. I’m happy to share my implementation experiences with you.