Oracle HFM Basics and comparison to SAP BPC
In this insight, we try to do a side-by-side comparison of the main functionality of Oracle HFM against SAP BPC, 2 of the leading solutions in the Gartner Corporate/Enterprise Performance Management Magic Quadrant. Both solutions are trying to address one of the major functionalities of CPM : financial consolidation and management reporting.
Short History of Hyperion-Oracle
Hyperion started as an independent company IMRS in 1981. Its focus has always been on the EPM-market, initially consolidation.
Some major milestones of the company included :
- 1983: release of MicroControl, an MS-dos management consolidation software, to be maintained by finance users
- 1991: release of Hyperion Enterprise, a Windows-based, management consolidation software
- 1998: merger of Hyperion Software with Arbor (main product: Essbase, OLAP dB) and rebranding to Hyperion Solutions
- 2000: release of Hyperion Financial Management (Oracle HFM), a largely web-based application aimed at management and legal consolidation and reporting
- 2006: acquisition of UpStream (a.k.a. Financial Data Quality Management, ETL focused to load data to Hyperion products)
- 2007: acquisition of Hyperion by Oracle
Oracle’s Performance Management applications
When comparing the features of Oracle HFM and SAP BPC we need to bear in mind the following. SAP BPC offers functionality to plan/budget and consolidate. Oracle HFM, as one of Oracle’s Enterprise Performance Management applications, focuses primarily on financial consolidation only.
Other modules of the Oracle EPM Suite thus have to be considered when comparing all the functionalities of SAP BPC, especially in the areas of Planning, Budgeting & Forecasting. These are e.g. Oracle Hyperion Planning (financial planning & budgeting solution), Oracle Hyperion Financial Data Quality Management (for Extraction-Transformation & Loading, ETL) and Oracle Hyperion Smart View for Office (an Excel add-in). The last 2 are also included with Oracle HFM.
Upload of data
SAP BPC uses an Import Package with transformation and conversion files for the upload of data, from within Microsoft Excel.
Oracle HFM uses Oracle Hyperion Financial Data Quality Management Enterprise Edition (FDM EE): this web-based ETL, part of the suite, can feed data to Oracle HFM, but also to other Oracle products, like Hyperion Planning or Essbase. Various adapters are available to connect to other source databases (SAP (ECC 6.0, SAP BW…), JD Edwards … ).
Both solutions offer the flexibility to read data from files or source systems and map from external to internal ids. FDM EE allows to drill back from Oracle HFM to the source data or system. For SAP BPC, this feature would require the usage of SAP FIM (SAP BusinessObjects Financial Information Management).
Data entry and reporting
For SAP BPC, Input forms and reports are defined and used in Excel, with the EPM-add in. Web-based reports and input forms are also possible, but these are limited to a selection of elements in rows and columns. They are -as a result- rarely used in the field.
In Oracle HFM, this functionality is located, with a certain overlap, in different modules: data forms, data grids, Financial reporting, SmartView.
However, they all tend to be used by customers in parallel.
- Data Forms
Data forms are used to enter data manually. They are web-based. These allow to combine members from various dimensions, basic calculations, formatting ... A right-click on a cell can lead to a Linked Form for additional detail (e.g. inter-company information). Textual comments or uploaded files can be linked to a cell for extra information. Each row and column is defined separately.
Data can be entered periodically or YTD indifferently.
Line Items can be applied on designated accounts. This allows to enter separate amounts and optional text to a single account (e.g. each entity can specify the current account in various banks, with individual amounts and bank names as free text, the total is written to the cash account, the detail remains and can be consulted).
From a cell within a data form, it is possible to request the "Entity Details”. This screen will show the manually entered amount, and the adjustments made by journals. It is also possible to drill to these journals.
Recently "On-demand rules” were introduced. They allow to run only a subset of calculations, to speed up the calculations for the current form. Previously, calculating a form was only possible when running the complete "Calculate" rule for the current entity, period, year and scenario. This feature is comparable to packages in SAP BPC, where script logic can be divided in separate calculations, to be executed at the user’s discretion.
Since Oracle HFM mainly revolves around the P&L and the B/S (Balance Sheet), which normally make up the majority of the accounts dimension, the benefits of this subset of calculations seems limited.
- Data grids
Data grids are an alternative to data forms. They are also web-based. The user can easily drag dimensions to rows and columns for further selection. Contrary to data forms, they allow drill-downs, but no formatting or calculations, and no asymmetric combinations of dimension members. They are very similar to the web-based reports and input-forms from SAP BPC.
- Oracle Hyperion Financial Reporting Studio
This windows client is the interface to create output reports with rows, columns, page-axis, conditional formatting. Basic calculations are possible (+,-,*,/, Averages, Better/Worse variance, If Then…), but not to the extent Excel has (and which SAP BPC can easily re-use).
The dimensions are set either for rows or columns. Each row and column however is defined separately. This facilitates the creation of asymmetric reports. In SAP BPC, elements are selected per dimension, for either columns or rows.
Selecting Actual, Budget and Forecast, 2013, 2014 and 2015 will generate all (3x3=9) possible combinations, which either requires suppressing of certain combinations (6 in this example, e.g. through the "Excluded Members” functionality) or hard-coding.
Fig 1: Asymmetric report
Prompts allow users to select certain members at run-time (e.g. Select Scenarios for Column 1 and 2, 3rd column shows the variance) (comparable to EpmSelectMember in SAP BPC).
Graphs can be created based on the retrieved (possibly hidden) data, although these are not as elaborate as Excel graphs.
In Oracle HFM, basic reports are easier to create.
SAP BPC has a steeper learning curve, but allows (far) more complex reports (e.g. Excluded Members, Dimension Override…).
Once created, reports are accessed through the web, either in the browser (allowing e.g. drill-downs) or as PDF. They can further be combined in "books", for a variety of point-of-views (equivalent to the "Context" in SAP BPC), generating a combined PDF (e.g. P&L, B/S and C/F for the current month, for all European subsidiaries).
Books can be distributed to a folder-location, or through e-mail, through a pre-defined batch process, or requested ad-hoc.
- Oracle Hyperion Smart View for Office
Oracle Hyperion Smart View for Office is an add-in that integrates with Microsoft Excel, Word and PowerPoint. It allows data entry, reporting and analysis.
This module also works on other products, like Oracle Planning or Essbase.
- Data Forms or Grids: you can open Data forms and grids created in Oracle HFM in Excel. They are shown with the same lay-out as in Oracle HFM, and the same POV-choices. Data can be read and written.
- Ad-hoc analysis: allows to show data for a number of dimensions in rows and columns, and drill down within existing dimension hierarchies. Dimensions and members can be selected in rows or columns from scratch, or a grid can be used as starting point. It is possible to add excel-formulas to these queries. With Essbase, it is possible to check "Formula fill" in the Options. When zooming in on a dimension, the fomula will be propagated along the expanding row or columns. This is comparable to LocalMembers in SAP BPC. However, when connected to Oracle HFM this is not (yet?) possible.
- Functions: Using specific functions, data and cell text can be retrieved or written to the database. These functions result in fixed reports.
Main functions are:
- HsGetValue : Retrieves data from a data source (SAP BPC: EPMRetrieveData).
- HsSetValue: Sends values to the data source (SAP BPC: EPMSaveData).
- HsCurrency: Retrieves the entity currency for the selected member (SAP BPC: EPMMemberProperty).
- HsSetText: Sends cell text to the data source (SAP BPC: EPMSaveComment).
In Smart View, there is no equivalent to some of the more powerful EPMadd-in functions like EPMAxisOverride, EPMCopyRange, EPMSuppress…
- Reports: Reports defined in the Reporting Studio (see above) can be opened in Excel, Word and PowerPoint.
In general, although the reporting and data entry capabilities around Oracle HFM are not as flexible and versatile as the EPM addin of SAP BPC, they are quite capable of serving the needs related to legal and management reporting.
The user accesses Oracle HFM through the web-based Workspace. Once in the chosen application, he can use the standard Application Tasks panel to access the module he wants. Alternatively, he can follow one of the Task Lists tailor-made by the administrator, which will guide him step by step.
Process control limits what and when a user can do. The administrator can decide which entities are open to viewing or modifying. Users will promote their data when their data is complete, and certain pre-established criteria are met (validations like a balance with a limited unbalance, filled-in FTE’s …).
The check of the validation only happens after the data has been saved.
Process control allows the controllers to check the closing process and see who’s potentially lagging … Changes in status can also be communicated to the administrator through e-mail. Additionally, the closing process can be split according to parts of dimensions, e.g. interco accounts could be submitted (and therefore locked to the user) before the P&L, the P&L before the B/S, the B/S before kpi’s…
This feature is comparable to the Work Status in SAP BPC.
Oracle HFM has no functionality similar to the Business Process flow SAP BPC has. Business Process Flow guide a user through specific activities (e.g. first fill in data entry form XYZ, then post journals, then run a consolidation), which are also monitored by reviewers. In Oracle HFM, the Task lists will guide the user through the required steps, just like a Business Process flow does, but without the ability for the administrator to track the progress in the tasks, or to enforce the correct order of the steps.
Journals allow to enter data in a debit/credit way. They can write data between entities. They can be auto-reversing in the next period, or recurring.
A journal is associated to a specific period. If an amount gets carried over across different periods (e.g. a movement in the balance sheet, through rules), a user will have to find the period where the journal was originally posted in order to find its details (contrary to SAP BPC, where the journal itself is re-opened every year).
The set-up of the Legal Consolidation starts in the Manage Ownership panel. It stores ownership detail through the following technical accounts:
- Active Consolidation: status of a child into its parent: Consolidate the entity Yes or No to its parent
- [PCON]: Percent consolidation. The percentage of the value of an entity that consolidates to the parent of the entity
- [POWN]: Percent ownership based on the shares of the entity that are owned by other entities
- [DOWN]: Percent of direct ownership
- [PCTRL]: Percent control based on the voting shares of the entity that are owned by other entities
- Method Consolidation: method assigned to the entity, "None” or a selection from the list of available methods (Holding, Global, Proportion, Equity and Disposed)
This is similar to the information stored in SAP BPC.
- Translation and consolidation
SAP BPC has 2 business rules for the set-up (Currency Conversion Rules and Consolidation Rules) (tables where source and target accounts, data sources, calculation… are selected or defined).
Oracle HFM has 2 rules, Translate and Consolidate. These rules are written in VB script.
The scripts are driven by properties of accounts (Income, Expense, Asset, Liability and Equity) and User defined attributes. These attributes, like Properties in SAP BPC, allow to distinguish different members (e.g. which accounts are translated at Historical rate?).
However, unlike SAP BPC, where properties can be defined at will, they are limited to 3 or 4 in Oracle HFM (depending on the dimension). If more are required, it is possible to combine multiple strings in a User defined attribute, and use string manipulations in logic scripts to separate them again (e.g. LEFT(UD3,4)), which is less elegant.
These rules will read data from the technical accounts mentioned above.
Using the User-defined attributes and Rules in the most efficient way will limit the maintenance of the rules to a minimum.
There are no separate dimensions for different consolidations or currencies like in SAP BPC (Groups). Different consolidations are handled through parent.child relations in the Entity dimension. If an entity needs to be consolidated to a specific group, in a specific currency, a parent will be created for that purpose. Multiple entities can roll up to that same parent entity, and an entity can roll up to various parents.
Members from the Value dimension (see below) store the details from the various calculations (translation, elimination, contribution…).
While SAP BPC stores exchange rates in a separate cube, in Oracle HFM these are stored within each application.
Execution of the translation or consolidation can be executed from data forms, data grids, SmartView and Data Process Control, on selected entity, scenario, year and period.
A user can chose for Translation or Consolidation (task gets executed) or the Impacted version (task is only executed if necessary, e.g. change in the data of an entity reporting to the consolidated parent), resulting in faster execution times. "Impacted” packages are not possible in SAP BPC.
Tracking ((approximate) percentage completion, display of entity currently consolidated, user…) and stopping of consolidation is possible.
Oracle HFM and SAP BPC will handle most legal consolidations. SAP BPC has a set-up through tables that is more user-friendly than the script-based approach from Oracle HFM.
Intercompany reports can easily be created to show imbalances in transactions between partners. These can be suppressed from the report if the imbalance is between a certain threshold (in absolute or relative amounts), to be selected at runtime. Similar reports can be created in SAP BPC.
This module is an integral part of Oracle HFM, but stores its data separately, and with additional data. It allows to enter or load intercompany transactions and match these between 2 partners. This can be done by the partners, and doesn’t require involvement from the administrator.
The approved balance can automatically be sent to the corresponding account in the Chart of accounts. This is set at application level. While sending the data increases data integrity, it can block the closing process if parties don’t agree on certain transactions.
Next to a complete detail of the corresponding dimension members (account, time, scenario, entity…(see below) ), the transaction should contain data on transaction currency, transaction amount, either corresponding conversion rate or entity currency amount, and Transaction ID (e.g. invoice number).
The Transaction ID allows to automatically match transactions. Manual match is also possible.
The administrator defines if matching is required before posting and what the tolerances are (either in % or absolute amount). This is done by period and scenario, allowing a custom approach for e.g. monthly, quarterly or yearend closing.
Specific transactions can also be manually matched with tolerance or without a tolerance, depending on a user’s security rights. Users can send email alerts from within the module to request information, prompt for action…
Similar functionality can be achieved through a separate module in SAP BPC.
Oracle HFM dimensions
An Oracle HFM application consists of 8 pre-defined dimensions, and 2 or more custom dimensions.
Each member of any dimension has 3 or 4 User Defined attributes (or properties). These can be used in rules. If more are required, it is possible to combine multiple strings in a Defined attribute, and use string manipulations in logic scripts to separate them again.
The dimensions are to a large extent fixed.
Oracle HFM dimensions:
- Scenario (SAP BPC:Category): Actual, Budget… Peculiarities: Default Frequency allows to have e.g. Monthly input data for Actuals and Quarterly input data for Budget, which is not possible in SAP BPC.
- Period (SAP BPC:Time): Periods define time and frequencies within a year. Contrary to SAP BPC, the creation of an INP period is not possible.
- Year (SAP BPC:Time): simple list of years, used in combination with the Period definition.
- Account (SAP BPC:Account): a number of technical accounts are predefined, mostly linked to consolidation (Active, PCON, PCONTROL…)
Peculiarities: accounts can be flagged as Dynamic, meaning they will be calculated on the fly, whenever shown in reports, data forms… They are mainly used for ratios, allowing for e.g. divisions on parent levels to obtain a %, rather than an aggregation of % of underlying members (SAP BPC: dimension formulas).
A link is created between individual accounts and elements from the User Defined dimensions.
Although all dimensions cross each other, for each individual account we can define what the Top Member for each User Defined dimension is
If flows are defined in User Defined dimension 1, Flow 1 could be the parent flow for Opening, Movement and Closing, Flow 2 could be the parent flow for Opening, Movement, Amortisation and Closing. As account Cash has no Amortisation, we would define Flow 1 as Top Member, and Flow 2 for account Equipment.
This also defines which combinations account- custom members is valid. Invalid combinations will automatically be blocked from input and show up in red in input forms
The Submission Group each account belongs to (see higher "Phased Submissions”) is also specified here, account by account.
Entity (SAP BPC:Entity): An entity represents a legal entity, a region, a business unit…An entity can roll up to multiple parents within the hierarchy. When selecting an entity, one always selects a combination of entity and one of its parents (e.g. entity "Bikes France” rolls up both to entity "Bikes” and to entity "France”). When working with Bikes France, the user has to select Bikes.Bikes France or France.Bikes France (see below).
Value (SAP BPC:Data source): Value members are system-generated and can’t be changed. They contain details on the reporting and consolidation process
: normal input, manual or through load : input through a journal : total of 2 previous value members : translation of previous member to the parent currency. As of this level, the parent.entity combination is required. : input through a journal, to ALL parents sharing the same currency as the selected parent : total of 2 previous value members
- [Parent]: same as previous value member
- [Parent Adjs]: input through a journal, but ONLY to the selected parent
- [Parent Total]: total of 2 previous value members
- [Proportion]: Calculation of Proportional Value
- [Elimination]: calculation of Eliminations (Intercompany amounts, generation of Minorities, elimination of investments in subsidiaries…)
- [Contribution]: total of 2 previous value members
- [Contribution Adjs]: input through a journal, directly affecting the contribution of the entity to its parent
- [Contribution Total]: total of 2 previous value members
The philosophy is similar to the datasource dimension from SAP BPC, which is however customizable.
A second type of Value members is related to currencies. Every currency defined in an application is represented in the value members. Ad hoc, any entity currency value can be translated to any other currency present in the application.
ICP (SAP BPC:Interco): Intercompany partners are defined by flagging the respective Entities as "Interco”.
They automatically roll up to [ICP Entities]. [ICP None] is used for third-party amounts. This requires a detailed entry of third-party and interco amounts.
This is similar to SAP BPC. SAP BPC however also offers the possibility to enter the sum of interco and third-party amounts, and obtain third-party amounts by deducting the interco amounts.
Custom dimensions (SAP BPC:Userdefined): custom dimensions are associated with accounts. They allow to store further detail (flows, products, customers…). They have a hierarchy, with parents aggregated automatically.
There used to be 4 fixed dimensions. They were called "smart”, in the sense that the ability to define the Top parent of a custom dimension for each individual account allows the control of valid combinations (see higher). This also allowed to have e.g. both Flow details only for B/S accounts and at the same time Product detail only for P&L accounts in the same Custom dimension 1. This allows to have virtually more dimensions than the 4 Custom dimensions.
As of version 22.214.171.124, the number of custom dimensions can be customized. Depending on business requirements, underlying database and volume of data and performance, the number of custom dimensions can be increased. In SAP BPC, the creation of Userdefined dimensions is less subject to similar constraints.
As mentioned before, a member can roll up multiple times, to different parents, within the same hierarchy, which is not possible in SAP BPC.
View (SAP BPC: Measure: allows to view and modify the data as periodic or YTD (while in SAP BPC the decision is made upfront if periodic or YTD input is allowed), or view as QTD etc. (in a monthly set-up). This is valid for all input accesses (data forms, data grids, SmartView)
Member lists allow to select a subset of dimension members. This helps in the selection of members in a data form, rows for a report, setting the scope a calculation rule should be limited to…
They are defined at application-level by an administrator.
Two types exist:
- system-defined : typically based on hierarchical properties: e.g. descendants or base members of a certain parent
- user-defined (or rather administrator defined)
Two types of user-defined member lists exist:
- Static: fixed list of selected members (e.g. account Gross Sales, Cost of Sales and Gross Margin)
- Dynamic: based on certain properties of the members (e.g. all Income type accounts under Net Profit). They are dynamic in the sense that at run-time they will read the members and their properties and create the list.
Additionally, they can be driven by the Point of view for the following dimensions: scenario, year, period and entity (e.g. base members of the entity selected by the user).
They are defined in a script language (see example below).
If HS.Dimension = "Entity” Then
If HS.MemberListID = 1 Then
HS.AddEntityToList "UnitedStates”, "Connecticut”
HS.AddEntityToList ”UnitedStates”, ”Massachusetts”
HS.AddEntityToList ”UnitedStates”, ”RhodeIsland”
HS.AddEntityToList ”UnitedStates”, ”Maine”
ElseIf HS.MemberListID = 2 Then
HS.AddEntityToList ”UnitedStates”, ”Connecticut”
ElseIf HS.MemberListID = 3 Then
HS.AddEntityToList ”UnitedStates”, ”California”
Member lists do not exist in SAP BPC, but various menus and functions in the EPM add-in easily allow a selection based on properties, which is more flexible and user-friendly than in Oracle HFM.
Rules define the calculations in an application. They are written in VBA, or a combination of VBA and a graphical presentation.
These are the Rule Types:
- Calculation: Calculation rules are used for calculations that cannot be calculated through the roll-up of the dimension hierarchies. They can be used for the retrieval of opening balances, cash flow… It is one single routine. It is executed when calculating or consolidating data.
- Translation: Translation rules are used to perform calculations from the Entity value members to the
value member when they have different currencies. They are used to calculate currency translation adjustments, deal with historical rates… The Translate() routine is executed when you translate or consolidate data.
- Consolidation: Consolidation rules are used to perform consolidations, most commonly found in statutory applications (elimination of investments, calculation of minority interests…). They depend on the data entered in the Manage Ownership panel.
- Allocation: Allocation rules allocate data from one entity to a list of entities (e.g. to calculate administrative expenses for a list of entities based on the total administrative expenses of another entity). They are not as powerful as the allocation-possibilities in SAP BPC. This is a functionality that is more developed in Oracle Planning.
- Input: Input rules allow input at the Parent entity level. Only the
Value member is supported.
- NoInput: NoInput rules prevent input at the Base entity level.
- Dynamic Calculation: Dynamic rules enable to dynamically calculate accounts, on the fly. Only Base accounts can be dynamically calculated. They are used for ratios, KPI’s… to avoid adding up percentages at parent levels which are meaningless. This is similar to dimension formulas in SAP BPC.
- Transactions: Transactions rules specify the accounts in the application that support intercompany transactions.
- Equity Pickup: Equity pickup rules, used in consolidation, specify the owned entity, owner entity, and percentage of ownership
- OnDemand: On-demand rules were introduced recently. They are used in Data Forms, and enable to run a subset of calculations (Calculate type) to quickly see the results in a data form. All HS functions that can be used in Sub Calculate (but no others) can also be used in OnDemand rules. This is similar to packages with specific calculations in SAP BPC.
In SAP BPC, it is fairly common to delve into the source data, be it in SQL or SAP BW, to e.g. analyse the data, get a detailed view of the records generated by a new script. This is also facilitated by the transparent set-up of the tables. In Oracle HFM this is not so transparent, and as such it is harder, if not impossible to analyse the source data to the average administrator of Oracle HFM.
Both Oracle HFM and SAP BPC are up to their consolidation and reporting tasks. To the user, their user-friendliness is comparable. They each have their strengths and weaknesses regarding set-up and maintenance: reports are easier to set up in Oracle HFM than in SAP BPC, but are less customizable; consolidation and translation rules are easier to set up in SAP BPC than Oracle HFM (tables vs. scripts).
With SAP BPC you get the Planning functionality (like the very powerful allocation script logic) on top. For that you need to turn to an additional product e.g. Hyperion Planning in the Oracle EPM solution set.