By TopDown Team
January 18th, 2011
In planning for an EPM project, companies need to consider many different factors, the first being the tool selection process. Now many companies know they must go through a tool selection process when undergoing a project addressing a business problem as important as EPM. This often involves a careful review of what tools are out there for an implementation, considering their strengths and weaknesses, determining how the tools fit within their existing enterprise architecture, corporate standards, and so on. What usually gets overlooked, however, is how to leverage existing technologies support these new EPM tools.
Sure, this post starts out much like the previous one, why, because it is true. How, you ask, can I utilize a relational database in my solution? Most of these tools require a database backend and can be hosted on your existing database, but what else can your database do for you? I previously provided “a quick list of just a few of the ways to leverage a relational database” and I plan to further break out a few of those points with some added twists over the next few posts. Today, we’ll look at one, and I’ll add another:
The first two examples of the ways your relational database can help you in EPM:
- Provide a common storage and access point for metadata, KPIs and data –
This could be reworded to “act as a central hub”, creating a single point of truth:
Many companies use a variety of technologies as a hub for providing data to their operational systems. Many EPM suites can benefit by leveraging a relational database in the same way. A central hub can be created by storing data and metadata in the relational database. It can now easily be leveraged by building targeted views upon tables containing the data and/or metadata. Tools such as EIS in the Oracle Hyperion EPM suite can utilize these views to create focused multi-dimensional reporting cubes with little effort from the users. The targeted metadata views, for example, may provide subsets of a geographical hierarchy focused on individual regions for each cube. Utilizing these views, the database can also filter the data so that only the appropriate records are loaded. Once these views and EIS routines are built, any updates to metadata can be seamlessly pushed to the regions by managing only one set of hierarchies. The views are designed to take care of the rest.
- Provide a valuable tool comparing metadata from external sources, identifying changes and building updates to metadata tools or tables.
Metadata is an ever changing set of members, properties, attributes and hierarchies all of which help to define the way companies operate manage and report on their business. Often this critical data is managed in different places, sometimes in a consolidated tool, such as Oracle Hyperion’s MDM solutions, but often it is a combination of sets of reporting hierarchies managed in a spreadsheet and sets located in the operational systems. In order to manage these disparate sets of metadata for an EPM solution, they need to be brought together into one central repository. Once this is done, changes to the metadata done outside of this repository must be identified and applied.
Relational tables provide a great place to store the metadata for easy comparisons. Creating a table and loading it with an initial set of data from one of the outside sources provides an initial basis for comparison to subsequent loads. Each change is easily identified through simple queries. These queries can also be written to not only identify changes. Often, these queries can be written in ways that provide output which can be directly applied to the central repository. An example would be creating a purposed built, comma delimited file used by the Oracle Hyperion DRM tool’s “Automator” to apply changes to properties, add new members, and move them within the hierarchy, as a few examples.
In summary, take stock of all of your assets and select the best ones for your solution, but don’t overlook one of your most valuable existing assets, your relational database. It can be put to use in a variety of ways that compliment the EPM suite. Many of these ways may improve the long-term support of your solution. Be creative and look for ways to leverage it in your architecture to replace some often time-consuming manual efforts.