By TopDown Team
December 14th, 2010
Many companies are now adding an EPM platform to their enterprise architecture by implementing components of the Oracle Hyperion Suite. These components are often selected to address specific needs not met by their current applications. Unfortunately, these companies often overlook one of their most valuable assets when building out the EPM solution, the relational database (RDBMS).
Most enterprises already have a relational database in their overall system architecture either as an integral part of an existing application, often as a data warehouse or even just for reporting. This relational database can be leveraged as a valuable supporting tool for implementing many aspects of the EPM suite. In some cases, it is leveraged as the source for a tool, such as EIS and Essbase Studio, both of which can produce a multi-dimensional, Essbase cube from a star schema built within the RDBMS. In other cases, maybe you need to do some simple transformations of the metadata to bridge the differences between the source and target systems, such as adding prefixes, or transforming date or numeric formats. Though this is usually done for data using an ETL tool such as Informatica, especially for large data sets, for smaller data sets and especially metadata, a relational database can provide a very fast and flexible option. Simple views placed on a table can filter and prep the metadata or data specifically for each target system.
For a robust metadata tool, Oracle Hyperion DRM, which is discussed further in other postings in this blog, is an excellent option within the EPM suite. This tool has been designed specifically for managing metadata and it is a great solution for many organizations, but for those who do not own a metadata management tool, their RDBMS can be leveraged to provide many of their features. One of those, previously mentioned, is the ability to transform properties for metadata using views. Another advantage a modern RDBMS can provide is the ability to retrieve a parent-child, or recursive hierarchy, which many reporting hierarchies are, as well as to provide some analysis of its members. This is necessary for loading some multi-dimensional databases as well as reporting tools.
A quick list of just a few of the ways to leverage a relational database:
- Provide a common storage and access point for metadata, KPIs and data
- Using views, one can isolate target systems, such as EIS, from changes made to backend systems.
- Map inbound data, for example actuals from a legacy system, to the metadata used for reporting. This may range from simply providing a prefix to a complex mapping process.
- Filtering data and metadata to provide subsets specific to the target application’s purpose and toolset. For example, creating separate Essbase cubes by splitting a reporting hierarchy into geographic regions.
- Perform extremely complex calculations for KPIs which may otherwise cause drastic performance issues within other applications
In summary, a relational database can be leveraged in many ways in support of an EPM solution. It has vast capabilities that can be brought to bear as a support tool. It provides simple ways to organize and view data as well as a robust programming language that can be used for large and complex calculations. It doesn’t have to grow into a large data warehouse or targeted data marts. It can be as simple as a few tables and views and still provide a considerable impact. How much it is used would be based on the organizations need and, therefore, a decision for the solutions architect. So remember an old friend, and leverage your relational database!