by Ravi Hoskote
Chart of Accounts Hierarchy
Chart of Accounts Hierarchy (COA) is a structure that is used to classify and group accounts for financial reporting purposes. All transactions can be done at the Account Level (lowest), and higher levels are for roll-ups for reporting purposes.
The diagram below shows a Chart of Accounts Hierarchy of Company X:
- Level 1 is the TOP node.
- Level 2 is Assets and Liabilities
- Level 3 is Fixed Assets, Current Assets, and Loan.
- Level 4 are the Accounts.
Challenge
Within the 10g version of Oracle Business Intelligence (OBIEE 10g), Extract, Transfom and Load (ETL) does not support Ragged and Skipped Hierarchies for Chart of Accounts.
OBIEE 11g does support Ragged and Skipped Hierarchies. You will need to select these options on the Hierarchy in the Business Model and Mapping (BMM) layer of OBIEE Repository Protocol Design (RPD). This will work as long as the underlying reporting table contains data that represents Ragged and Skipped Hierarchies.
The underlying table, W_HIERARCHY_D, contains Chart of Accounts Hierarchies and other Hierarchies within. This table does not have data that represents Ragged and Skipped Hierarchies.
Following are examples of Ragged and Skipped Hierarchies:
Ragged Hierarchies
The number of levels in all the branches of a hierarchy are not equal. All child nodes/lowest nodes are not at the same level, one or more of the nodes are in a higher level.
For example, the Asset Accounts has 4 Levels while Liabilities may have only 3 Levels. The child node Account number 20000 is at Level 4, and all other child nodes in Assets are in Level 5.
Ragged Hierarchies are rarely implemented in the Oracle E-Business Suite financial system and usually all Accounts in a company are at the same bottom level.
Skipped Hierarchies
The number of levels between a parent node and a child node in a branch is more than one. In other words, a few levels are skipped between the parent node and child node.
Example: Summary Account Liabilities is at Level 2, the Account 21000 is at Level 5 and Levels 3 and 4 are skipped.
Existing Business Intelligence Applications Charts of Account Architecture
Oracle Business Applications have two options that allow you to build a Chart of Accounts (COA) Hierarchy:
- Extract using Oracle E-Business Suite Financial Statement Generator.
- Extract using Oracle E-Business Suite Foundation hierarchy tables.
This discussion talks about Extracting from Oracle E-Business Suite Foundation Hierarchy tables.
The above diagram shows the Extract, Transform and Load flow for Foundation Hierarchy. The nodes and their relationships are extracted from the Foundation Hierarchy Table to build and flatten the hierarchy. The Top node is placed at Level 1 and the lower node is placed at Level 20.
All child accounts are at Level 20 and the Skipped and Ragged Hierarches are not tracked. The child nodes are repeated in the dimension columns and filled in through Level 20.
The data loaded into W_HIERARCHY_D, is shown above. The child nodes are repeated until the Hier20_code. All child nodes are repeated from hier5 until hier20 except account 21000 which is repeated from hier3 until hier20.
Solution for Ragged Hierarchy
As mentioned earlier, these are rare scenarios in Oracle E-Bussiness Suite Finance. In general, all Chart of Accounts child accounts are at the lowest level. In Oracle Business Intelligence Applications, all accounts are at Level 20.
The child nodes that are not at the lowest level are the Ragged Hierarchy nodes and there are no transactions on these accounts. Users are only interested in financial transactions so it is not useful to bring Ragged Rierarchy nodes into the Oracle Business Intelligence Data Warehouse.
All financial transactions are on the child accounts in the lowest level. These are important to be captured by Oracle Business Applications as they are currently being brought in and tracked in the Oracle Business Intelligence Data Warehouse.
Bringing in Ragged Hierarchy into the Hierarchy dimension means that the lowest nodes (Level 20) are empty. However, the structure and functionality of the Hierarchy dimension in Business Intelligence Applications won't allow the lowest nodes (Level 20) to be empty. The first and the last levels have to be filled.
Solution for Skipped Hierarchy
The idea is to use the existing Business Intelligence, Extract, Transfom and Load flow to build hierarchies and to later update the hierarchy table with skipped levels using another flow. This is a two step process.
Step 1 – Assign Level Numbers
This is a crucial step where we need to assign and input level numbers to each Chart of Accounts Hierarchy node in the Oracle E-Business Suite Hierarchy. This can be a time consuming process, but only needs to be done once.
The level assigned to each node in the W_GL_SEGMENT_D table is shown above. Notice that I have assigned Level 5 to CASH and SECURED LOAN and Level 4 is skipped.
Step 2 - Update Hierarchies on Hierarchy Dimension
In Step 2 we use the Out-of-the-Box Business Intelligence Application, Extract, Transform, and Load to build hierarchies and use the input from Hierarchy Levels to reassign and create skipped levels in the Hierarchy Dimension.
Result
The data in W_HIERARCHY_D after applying the solution is show above. Please note that NULL values are inserted in HIER4 and CASH and SECURED LOAN are now in HIER5, as assigned in W_GL_SEGMENT_D. Now, the W_GL_SEGMENT_D table has data which represents skipped levels in Charts of Account. This table is used to model Skipped Hierarchies in OBIEE 11g.
 |
Ravi Hoskote is a business Intelligence and data warehouse professional with expertise in implementation and customization of Oracle BI Applications. Ravi has successfully led several Oracle Business Intelligence projects for KPI Partners that include HR Analytics, Procurment & Spend Analytics, Financial Analytics, Sales Analytics, and Project Analytics. Check out Ravi's blog at KPIPartners.com. |
