Do you have multiple applications with similar dimensions? Is the maintenance of multiple metadata ETL processes for these same dimensions driving you crazy? Do you wish there was an easy way to share dimensions between similar applications? If so, EPMA could be the solution you’re looking for! Let’s review an example.

Consider This…

The two applications above can be Essbase, Planning, Hyperion Financial Management (HFM), Hyperion Profitability and Cost Management (HPCM), or any combination of these four application types. In this case, let’s assume we have a Hyperion Planning application containing current financial data and an Essbase cube serves as the historical data archive. What do these applications have in common? Since the Essbase cube is simply an archive, it shares many of the same dimensions with the main application. The only major difference between the applications is the Years dimension. Our main application only has limited historical data and a few forward looking years, while the archive application houses all historical data. As you can see, our applications share FIVE out of six dimensions. Let’s look at how we would build these applications using the classic approach.

Building the Applications – Classic Approach

If we were to build these applications using a common development approach, how would we approach it?

First, we would define our dimensions within a data warehouse or flat file. After we build our dimensions, we would create an ETL process to load the metadata to our application. The ETL Process can be a load rule, the outline load utility, or ODI, depending on the application we are building. As it pertains to the Years dimension, we would build our processes to filter on years such that only the appropriate years are loaded into each respective application.

The Metadata Nightmare

As you can see from the diagram above, we would have had to build an ETL Process for EACH application. If both applications are the same type (Essbase, Planning, HFM, or HPCM), then some ETL programming can be reused. However, if we have a Planning Application built for forecasting the future years, while our archive application is an Essbase Cube (just like in our example), we cannot reuse any ETL processing. Since Essbase and Planning metadata is loaded through different mechanisms, we would need SEPARATE processes to create our outlines. This causes scattered maintenance by requiring us to manage all three layers of our application separately. As dimensions change, we must change our source, EACH of our ETL processes, and any application specific items. To make matters worse, different layers of the application tend to be managed by different administrators, thereby necessitating a constant coordination with multiple people to ensure that the updates happen where required. Because of the amount of maintenance needed on all aspects of the application, the solution does not scale well. As we add more applications, we drastically increase the maintenance complexity even further.

How Much is Too Much?

We just walked through a scenario in which we built and maintained only two applications. We defined our dimensions once as well as developed the applications and an ETL process for each one. So, to maintain two applications, we need to manage five components. This means that every time we need to update our processes, we need to make a change in five different places! What happens when we have five or ten applications?

If we have five applications, we need to maintain at least eleven different components. If you don’t have a hard enough time following the diagram above, imagine what this diagram would look like if we had ten applications!

Can you tell which dimensions are missing from each application by looking at the diagram? We can’t either, and we built the diagram! What happens when we need to change a mapping in our ETL layer? All these applications are similar, yet we need to go into every process and make the change. We end up creating one tangled mess in all of our application components. Is this a nightmare or what? Managing several applications is difficult enough; having to manage a separate processing layer on top of that is just too much.

Classic Build Drawbacks – Summary

• Process Required for EACH Application
• Scattered Maintenance
• Difficult to Manage
• Does Not Scale Well

Can We Build it Better?

Thanks to EPMA, this is not the only way in which we can build our applications. We are not doomed to manage two or three objects for every application that we build. EPMA allows us to cut the maintenance in half by worrying only about the applications and not the ETL layer. How? Let’s take our original example to see how we can use EPMA to achieve these goals.

As you can see from the diagram above, our dimensions are built in the data warehouse and imported into the EPMA Dimension Library. Building dimensions in the data warehouse is one way of defining the dimensions. Dimensions can be built and loaded into EPMA via Interface Tables in the data warehouse, flat files built with the EPMA file generator, or through Oracle DRM. Dimensions are loaded into EPMA through Import Profiles. We can create separate import profiles for every dimension and bring them all in separately, or we can write ONEprofile to bring in all dimensions. The Import Profile can be setup through the EPMA batch client to be run automatically.

Once the dimensions are in the Dimension Library, we can simply drag and drop them into the applications. Shared dimensions can be designated as such so that they automatically get updated in the application when a property changes. In contrast, the Years dimension can be dragged into each application and its properties can be designated as local and unique to that application. We can maintain only the years that we want in each application. Once a shared dimension changes in the dimension library, all changes are instantly pushed to the applications and take effect upon redeployment. The redeploy command can be automated through a batch script as well.

EPMA Benefits

So, why is this method better? We don’t need a separate process for each application. We need only ONE single table for each dimension if we are building our dimensions through a data warehouse. We don’t need to create load rules for Essbase hierarchy builds. We don’t need to fumble around with the Outline load utility for Planning. We build all of our dimensions once, and EPMA handles the push to each application. This reduces multiple maintenance points in the Integration/ETL layer down to one. Now, we don’t need to worry about how many changes we need to make if a mapping changes. We can make the change once and we are good to go!

Another benefit in using EPMA is flexibility. EPMA hierarchies can be defined in a data warehouse, flat files, Oracle DRM, or the shared library. Hierarchies can also be partially defined in several different ways and merged together in the import process. If you would like to create the main hierarchy in DRM and an alternate rollup in a flat file, you can merge both sources together in the import process.

Because of the reduced maintenance and flexibility of EPMA, we now have a solution that is highly scalable. There is no limit to the number of times we can reuse our shared dimensions. We can build 100 applications and only need one process to import them into the shared library. From there, we can drag and drop the dimensions into the applications and choose whether we want to maintain them directly in the application as local dimensions or maintain them from the source and run an import into the application. We don’t need to build 100 load rules and hope that nothing ever changes.

EPMA Benefits – Summary

• Reduced Maintenance – Manage directly in application
• Automation – EPMA Batch Client can automate imports and deploys
• Flexible – Define hierarchy in data warehouse/flat file/DRM/shared library
• No ETL Required – DRM & shared library
• Scalable – Infinite reusability

Integrations

EPMA integrates well with other Oracle products. We saw how it can interact with Essbase, Planning, HFM, and HPCM. A new feature in EPM 11.1.2.2 is the direct integration between DRM and EPMA. Hierarchies can be defined in DRM and pushed directly to the EPMA Dimension Library without any ETL processing required. EPMA also integrates well with Essbase Studio – metadata can be imported into Essbase Studio from EPMA. This means that Essbase applications can be built using EPMA Shared Dimensions with full drill through capabilities. We will cover integrations in more detail in a subsequent article.

Upcoming Topics to be covered in future articles:

• Data Synchronization Capabilities
• Integrations with other Oracle Products
• Converting Classic Planning Applications to EPMA
• EPMA Tips and Tricks

While significant flexibility and gains can be observed by using EPMA, implementations can be complex if not properly designed. At Peloton, we have successfully implemented EPMA for clients in various industries, across a wide variety of business scenarios. To learn more about EPMA and our approach towards implementing it, please contact us.