By TopDown Team
April 28th, 2011
This discussion is presented in two parts. Part I covered the components of a sample EPM solution for financial monitoring. Part II discusses the deployment of the sample EPM solution.
The solution components for the EPM solution example include:
- Relational Database
- Oracle Hyperion Planning
- Oracle Hyperion HFM
- Oracle Hyperion DRM
Figure 1 – Schematic of a Typical EPM Deployment
An enterprise maintains the majority of its financial information—and any other required information for reporting—in the operational systems. Using the schematic above, those operational systems can be any combination of Oracle Financials, PeopleSoft, SAP, and JD Edwards, along with any in-house developed systems.
On a predetermined or on-demand basis, you pull extracts from the operational systems and push them to the FDR. From there, a routine strips the metadata (dimensions and attributes) from the files and creates a file that can then be compared against the existing dimension and attribute values currently in the FDR. Differences will range from new values coming in to changes in parent-child relationships. The result of these comparisons is the creation of an automator file which is used to update the information contained in the DRM hierarchies. Individuals responsible for maintaining the correct relationships within DRM will receive alerts about the new members for which they will need to go into each of the dimensions and validate, correct or modify the new entries, ensuring they have been positioned correctly within the appropriate hierarchies.
Upon completion of the validation process, exports of all the appropriate metadata (dimensions and attributes) are then spun out of DRM after which the appropriate FDR processes receive the new information and rebuild the dimension and attribute tables. These new tables then form the backbone for the loads that are used by the reporting layer. The FDR is now in synch with the new fact data that was read into the FDR. The loads and processing of the new data can commence, and then the new data can be loaded into the Essbase cubes, sent to Planning via ODI, or prepped for HFM with a new load file.
This ongoing cycle allows:
- metadata to be in synch with the new fact tables.
- all of the data necessary to rebuild the financial reporting environment to reside in one location.
If any Lock and Send spreadsheets are used to make modifications or incorporate outlying data, then these spreadsheets should also be maintained for historical purposes in the database.
The FDR should become an organization’s sole source for rebuilding data and, if fed properly, will ensure that should anything happen to all or any part of the reporting environment, a rebuild using the data and metadata from the FDR will create a consistent set of information.
The sample FDR design shown below is presented as a twist on a traditional method because it provides an excellent mechanism to easily incorporate new dimensions and attributes without disrupting the existing design.
It is a basic tenet of BI tools that work in conjunction with a relational database to incorporate a Star schema as an integral part of the design. Figure 2 represents a Star schema.
Figure 2 – A Typical Star Schema
In a typical Star, the fact table resides at the center of the design. The fact table consists of records within a table, which for each row in the table contains the dimension keys along with the measure value used to create the desired intersection point within the reporting environments. The key to each dimension contains the value that points to the dimension table and provides the necessary descriptors to give meaning to each row in the fact table. For each dimension member referenced in the fact table, there is a corresponding row in the dimension table. The corresponding row in the dimension table has the attribute values associated for that dimension.
Consider the following Fact table:
|Account ID||Region ID||Office ID||Sales Person ID||Currency ID||Amount|
This row entry represents the revenue booked against all the dimensions contained in the row. Consider now the associated dimension tables:
|Account Dimension||1000-011||Revenue for Product Line A|
|Region Dimension||RG_2110||Quebec, Canada|
|Office_ID||OF_1200||Downtown Quebec Sales Office|
|Sales Person ID||SP_34||Mark Thompson|
|Currency ID||CU_CA||Canadian Dollar|
|Amount||Measure||1250.34 Canadian Dollars|
Consider also that associated with the Office Dimension is a Sales Outlet Type. For this particular OF_1200, the Sales Outlet Type is “Store Front.” The row record in the Office Dimension would look like:
|Office Key||Office Description||Sales Outlet Type|
|OF_1200||Downtown Quebec Sales Office||Store Front|
Once the conceptual FDR has been designed, there are a number of ways to physically deploy the FDR. This case study is going to document only one of the methods. Figure 3 below is the selected method of FDR deployment.
Figure 3 – Physical FDR Deployment
In this deployment, all of the attributes have been added to one physical database table. The structure for that table is built to support DRM maintaining each of the attributes in separate tables. The minimum information for the Attribute Table would be:
- Sequential Key – Computer generated
- DRM Dimension Name (i.e. Asset Type)
- Parent Node Name
- Child Node Name
Each attribute is associated to the appropriate dimension table and a cross-reference record is created that lists at a minimum:
- Dimension ID
- Attribute Sequential ID
- Attribute Child Name
Once these associations have been set up, the Oracle Hyperion tools are free to build the required models using the appropriate dimensions and the associated attribute cross references as appropriate to process the correct fact tables.
Benefits of Using this Design
There are a number of benefits to implementing the FDR using this physical deployment.
- THE FDR can be built and deployed without knowing all of the dimensions and attributes beforehand. This design is built as a framework and foundation.
- DRM provides the framework to maintain all of the hierarchies for both dimensions and attributes.
- Adding new dimensions and attributes can be proceduralized allowing for additions without disruption.
- Adding new dimensions and attributes does not disrupt existing models.
- This framework is easy to maintain and works well with all of the Oracle Hyperion BI tools used to build models.
- New models can easily be built to accommodate new fact tables as required.
Please feel free to publish the above blog in full or in part with attribution according to the Creative Common license.