Enterprise Performance Foundation (EPF or FEM) – Data Model Introduction
Posted by Venkatakrishnan J on December 24, 2008
As you might already know, with the acquisition of Hyperion, Oracle’s product stack for financial clients have been strengthened quite a bit due to products like Hyperion Planning and Financial Management. Hyperion Planning and Financial Management are best breed of products in its line of offerings. Having said that, there are quite a few customers who are still on the Oracle’s own products like Enterprise Performance Foundation, Financial Consolidation Hub, Enterprise Planning and Budgeting etc. I was in three different calls last week with 3 customers who were using Enterprise Performance Foundation as their primary reporting data warehouse. Also all these clients were using CPM analytics for doing their reporting in BI EE. One of the major advantages of this reporting data warehouse(its not a true data warehouse per se. But it is tuned more towards reporting) is that it is integrated very tightly with E-Business Suite. It is even tightly integrated with products like Profitability Manager, Financial Consolidation hub etc. And it caters to almost all kinds of reporting. I had covered CPM analytics in my blog entry before here. CPM Analytics is one of those BI Applications which does not have an ETL layer. Rather, it provides a set of reports and dashboards for reporting directly on Enterprise Performance Foundation. One of the frequent issues the end users face with this application is that, the reports and dashboards do not give correct or expected results. This is primarily not because of any issues in CPM analytics but instead because of customizations in the EPF layer. To understand this better, we shall first try to understand how the EPF is structured at a high level from a data model perspective. Also, we shall compare and contrast this data model layer with respect to Hyperion Financial Management later. At a high level, the below diagram would give a basic overview of the EPF data model.
I typically call EPF or FEM as the financial data warehouse. Its fact tables primarily contains all financial data like Customer Loans, Credit Cards, Consumer Credits, Account Balances, Company Profit and Loss etc. Each of these different financial data is identified by means of different dimensions. Enterprise Performance Foundation has these below out of the box dimensions.
2. Financial Element
4. Product and Product Type
5. Cost Center
7. Natural Account
13. Line Item
These are the out of the box dimension that EPF provides. Not everyone use all of the above dimensions. The extent of usage of the above dimensions are driven by the type of company and the source system. Typical source for EPF would be Oracle General Ledger. Apart from the above dimensions, EPF also provides 20 more user customizable dimensions(named as User1…User20). In all the cases that i have seen so far, there is always a necessity to use atleast 3-4 of these user customizable dimensions. This is primarily because not every business requirements are satisfied by the 13 or more out of the box dimensions. In fact, there are cases wherein custom dimensions would have to be used as a replacement for the out of the box dimensions. For example, the Customer dimension provided by EPF might not satisfy all the necessary tracking required by business. In such a case, one would have to use any one these custom dimensions as a replacement for this dimension. This is the primary reason why the reports/dashboards provided by CPM Analytics do not give a true state of the business. Having said that, the repository and the approach to reporting provided by this CPM analytics is excellent and in fact can be leveraged to provide better results.
Now lets go back to the data model and try to understand the various aspects of reporting that EPF provides. One of the excellent flexibilities of EPF is that it can maintain multiple hierarchies for the same dimension. In addition to multiple hierarchies it can also maintain multiple versions of the same hierarchy. And all the hierarchies are maintained in the form of parent-child relationships. For example, let us take a look at the NATURAL ACCOUNT dimension.
As you see, this dimension supports SCD-2 & SCD-1 kind of reporting. The ENABLED_FLAG, LAST_UPDATE_DATE flags provide the flexibility to maintain history of the same account (if necessary). Also, each dimension can itself be further subclassified based on different value sets. The dimension structure is same for all the dimensions. Now, lets take a look at the hierarchy structure (always parent child).
The child_id column above in the hierarchy view is actually the source dimension tables member ids. As you see, this has quite a few good features like SINGLE_DEPTH_FLAG, CHILD_DEPTH_NUM etc which are very crucial for reporting. The SINGLE_DEPTH_FLAG helps in identifying whether the child and parent are immediate parent and child. For example, in cases wherein a member does not have any parent(a root node) then the parent_id and child_id for such members would be the same. Hence, in reporting when we do a connect by this will result in a loop(in 10g and above we have loop identifiers in the CONNECT BY queries). The SINGLE_DEPTH_FLAG helps in identifying such members. Also, the HIERARCHY_OBJ_DEF_ID helps in maintaining different hierarchies for the same dimension. Also, there can be different versions of the same hierarchy. As a next step lets take a look at the Attributes view.
EPF supports 3 type of attributes. They are Varchar, Numeric and Date type attributes. The above pretty self explanatory. Each dimension can have a lot of attributes. For example, Customer would have an Age attribute. And each attribute can take many values. The attributes view above provides all such combinations for reporting. The structures above are very generic and would remain the same across all the dimensions. All the above are maintained through a generic EPF UI (similar to Ebiz screens). EPF has around 20 out of the box fact tables catering to different kinds of financial businesses like Corporate Loans, Banks, Credit Cards etc. Also, each fact table is bifurcated into 2 types. They are are transaction fact tables. And the other is the summarized granular fact tables. This should give us an understanding of the EPF data model. Now, in the future i will be blogging about how CPM analytics uses these above dimensions, attributes and facts to provide comprehensive reporting. But the above should serve as an introduction to anyone who is making a foray into EPF.