When you bring EPMA and ETL together, the possibilities are endless…Okay, maybe not endless, but substantial.
People may be getting tired of reading my posts about EPMA, but unfortunately I am not yet tired of writing them. EPMA is a very powerful tool that admittedly had a rocky debut, which debut has caused some to remain reluctant to give it serious consideration. I love the challenge of finding ways to accomplish functional/business requirements by uniquely leveraging the tools in the Oracle EPM stack, and I would like to share one of those with you. Consider the following scenario: you are working on an Oracle EPM implementation and want to use EPMA to manage the metadata for all of the EPM applications. Sounds easy enough so far, right? Now imagine that some of those are Essbase Integration Services applications. Let me save you some time – don’t bother looking for any references to EIS in EPMA, because they don’t exist. EIS expects (requires) a fact table and dimension tables (in a star scheme relationship) in order to build the outline and load the data. So there was the challenge: how can we utilize EPMA to manage the metadata for EIS?
Since we were working from the assumption that EPMA would be used to manage the metadata, the more specific question was how to get the metadata from EPMA into dimension tables/views in a relational database. We initially considered writing custom SQL to query the EPMA repository for the metadata and build dimension tables/views based upon those queries. This option was rejected fairly quickly, for two main reasons. First, as I have described in other posts, the structure of the EPMA repository is complex, and we could only be moderately confident that the queries we wrote were completely correct. After spending some quality time poring over the EPMA repository, we reached a point where we felt we did know how to write the SQL statements that would be required. It was the second objection, however, that ultimately led us to abandon this option. The second concern was the potential for the structure of the EPMA repository to change in subsequent releases, possibly requiring significant changes to the custom SQL. This implementation was on the 11.1.1 release of EPMA, and the project was actually started on 9.3.1, with the upgrade to 11.1.1 occurring midway through the project. Given the substantial changes we had witnessed in EPMA over the course of the implementation, we determined that the risk of substantial changes to the underlying repository in future releases was too high.
The solution we selected may surprise you; I recall being met with a fair amount of skepticism about its feasibility after proposing it. Ultimately, we opted to create EPMA application views for the EIS applications and maintained them just was non-EIS cubes were maintained. The applications were then deployed to Essbase. We were utilizing Data Integration Management (DIM) for our ETL processes (the project began before the discontinuation of DIM was announced), and we took advantage of DIM’s ability to extract metadata from Essbase outlines. We created DIM workflows to extract the metadata from Essbase, re-format/map the property values to fit what EIS expected and write the resulting metadata to dimension tables in the data warehouse, where the fact table was stored. These workflows were incorporated to our overall automated metadata update process and worked extremely well. While our process used DIM, Oracle Data Integrator (ODI) has a similar ability to extract metadata from Essbase. Even with the long-term future pointing towards Essbase Studio – and its built-in ability to source metadata from EPMA – a solution like the one described here could be a viable option for at least the next several years, particularly for organizations that wish to utilize EPMA and to deploy Essbase cubes with drill-through functionality but are not ready to embrace Essbase Studio.