An EPM Solution Using Oracle Hyperion Tools – Part II

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.

SOLUTION COMPONENTS

The solution components for the EPM solution example include:

• Relational Database
• Essbase
• Oracle Hyperion Planning
• Oracle Hyperion HFM
• Oracle Hyperion DRM

Solution Deployment

In order to best understand the proposed solution, consider the Figure 1 below.

 

 

 

 

 

 

 

 

 

 

Figure 1 – Schematic of a Typical EPM Deployment

Solution Description

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.

FDR Design

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

Star’s Components

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
1000-011	RG_2110	OF_1200	SP_34	CU_CA	1250.34

 

This row entry represents the revenue booked against all the dimensions contained in the row. Consider now the associated dimension tables:

 

Dimension Name	Value	Description
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

FDR Deployment

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.

 

---