By TopDown Team
August 11th, 2011
One of the biggest reasons to utilize data marts within an EPM implementation is the ability to speed development by reusing data and metadata across multiple systems. Additionally, future systems can leverage the database development effort for the initial system.
- Metadata often provides a great basis to leverage or build upon.
- The dataset is usually the same with possibly dimensions removed or consolidated.
- Data may be summed to a higher level.
- Tools creating reporting cubes may provide drilling capabilities to the detailed records stored in the database.
This blog will describe a development effort in progress currently realizing these savings. The ongoing development effort in this example exists in one of TopDown’s current projects with a large retailer/manufacturer that is implementing Oracle Hyperion EPM products along with a new SAP implementation. The initial effort concentrated on developing HFM (Oracle Hyperion Financial Management) and several reporting cubes representing existing reporting structures along with the new structures being put in place during the SAP implementation.
First, we exported the HFM application utilizing Extended Analytics, a web-based exporting capability that comes with HFM and is used to export data and metadata to a star schema within a database. From there, we reused the metadata, utilizing views, to make adjustments to the hierarchies as well as splitting or combining them depending on the downstream application’s requirements—in this case, several Essbase reporting cubes. In addition, for the historical hierarchies, we created tables to provide a method for mapping the new SAP accounts into the historical account structure. This allowed the parts of the company that are not yet leveraging SAP to continue seeing the Income Statement as it was in the familiar legacy system until it is brought forward to the new standard.
Once the initial effort was well underway, we could leverage components of it again for the planning system build. Each planning app consisted of a different subset of one or more of the hierarchies as well as a hierarchy that consolidated two hierarchies into one. These efforts were again accomplished by utilizing views built upon the HFM extracted hierarchies. In addition, we loaded the actual data pulled from HFM into the planning apps, leveraging the new planning hierarchies to filter the data appropriately. Any changes made within HFM flowed through to both the Essbase reporting cubes and planning apps without any additional effort. Planning does require more effort when building the hierarchy views as it is less forgiving than Essbase when it comes to defining properties such as ones for shared members and having no duplicates in member names and aliases.
Another great time savings the project is realizing comes from feeding data from Planning apps and Essbase cubes back into the data set within the database. By leveraging export scripts, you can write planning data and updates made to a dimension within an Essbase cube back to the database, which is then moved into the base tables feeding the reporting cubes. Utilizing this method has provided a quick turnaround on feeding planning data quickly during development to help in resolving any metadata issues and data analysis—a real asset in preparing the new planning environment. Planning data can even be fed from the legacy system through the FDM (Financial Data Quality Management) tool into HFM, through an EA export and into the same tables used for Actual data, allowing the data to utilize all of the previous development effort throughout all of the reporting cubes and planning apps. This allowed the project team to increase the speed of development capabilities when combining a data mart into the system flow.
In summary, data marts provide a great opportunity to speed the development efforts by building upon each of the previous stages. It makes it possible for the whole project team to work more productively as changes flow better throughout the system even while additional development continues in all phases of the project.
- Transformations can be made within the views specific to each of the applications and cubes.
- Special needs for each of the tools can be accommodated.
- Multiple data types can be fed into a central table feeding all of the applications.
- Reporting cubes can be built summarizing the data, while still being able to drill to the lowest level of detail stored in the database.
Bottom line: Data marts are a valuable asset to any EPM development effort.
Please feel free to publish the above blog in full or in part with attribution according to the Creative Common license.