Overview-
Creating a dimensional model to represent GL data is relatively straightforward. Basically the goal is to capture 3 fact areas: Actual Balances, Budget Balances, and Journal Entries. Typically the model will consist of 2 fact tables: GL_Balances and GL_Journal_Entries. With this design Actual and Budget measures are both part of the GL_Balances fact. They may be represented on separate rows and differentiated by a foreign key to a dimension that indicates “Actual” or “Budget”, or they may be pivoted and placed right next to one another. The latter scenario is often preferable because it makes it very easy to represent Actual-to-Budget comparisons in reports. Journal Entry data must be in a separate fact table because the granularity is lower than that of the GL_Balances fact. However, both fact tables will share several dimensions:
GL Period
Set of Books
Account Type (Revenue, Expense, Asset, Equity, Liability)
Chart of Accounts
This technical brief will focus on how to design and build the Chart of Accounts dimension(s).
Design-
The Chart of Accounts (COA) is a concept that is common to most accounting systems. It is a list of all the accounts that are tracked by the accounting system. The COA is often represented as a hierarchy but may or may not actually implement a strict one-to-many relationship from top to bottom. This is an important point because identifying this characteristic is critical to developing the appropriate design. If the COA is represented as a strict hierarchy, then a single COA dimension table can be used. If not, then it may be necessary to create a dimension table for each level in the COA.
Implementation-
In Oracle EBS, the COA is identified in the transaction records by a field called CODE_COMBINATION_ID. This field maps back to the table GL_CODE_COMBINATIONS, which holds the hierarchy in fields called Segment1, Segment2, Segment3, etc. The business meaning of the fields can be found by querying the some of the FND tables:
SELECT application_column_name,
segment_name
FROM fnd_id_flex_segments fs, fnd_id_flexs f
WHERE fs.id_flex_code = f.id_flex_code
AND f.application_table_name = 'GL_CODE_COMBINATIONS';
The results of this query will look something like this. This information is helpful to determine appropriate table and/or columns names for the COA dimension table(s).
SEGMENT1 Company
SEGMENT2 Commercial Unit
SEGMENT3 Branch
SEGMENT4 Department
SEGMENT5 Account
Getting the data for the dimension also requires queries against the FND tables. The approach will be different depending on which design is chosen. If the COA can be implemented as a single dimension table then following query is a good start.
Approach 1 – Query to Generate the COA Hierarchy for a single COA Dimension:
SELECT gcc.segment1 company_cd,
fvc.description company_desc,
gcc.segment2 commercial_unit_cd,
fvcu.description commercial_unit_desc,
gcc.segment3 branch_cd,
fvb.description branch_desc,
gcc.segment4 department_cd,
fvd.description department_desc,
gcc.segment5 account_cd,
fva.description account_desc
FROM gl_code_combinations gcc,
fnd_flex_values_vl fvc,
fnd_flex_values_vl fvcu,
fnd_flex_values_vl fvb,
fnd_flex_values_vl fvd,
fnd_flex_values_vl fva
WHERE fvc.flex_value_set_id =
(SELECT flex_value_set_id
FROM fnd_flex_value_sets
WHERE flex_value_set_name = 'XX_COMPANY')
AND fvc.flex_value_meaning = gcc.segment1
AND fvcu.flex_value_set_id =
(SELECT flex_value_set_id
FROM fnd_flex_value_sets
WHERE flex_value_set_name = 'XX_COMMERCIAL_UNIT')
AND fvcu.flex_value_meaning = gcc.segment2
AND fvb.flex_value_set_id = (SELECT flex_value_set_id
FROM fnd_flex_value_sets
WHERE flex_value_set_name = 'XX_BRANCH')
AND fvb.flex_value_meaning = gcc.segment3
AND fvd.flex_value_set_id =
(SELECT flex_value_set_id
FROM fnd_flex_value_sets
WHERE flex_value_set_name = 'XX_DEPARTMENT')
AND fvd.flex_value_meaning = gcc.segment4
AND fva.flex_value_set_id =
(SELECT flex_value_set_id
FROM fnd_flex_value_sets
WHERE flex_value_set_name = 'XX_ACCOUNT')
AND fva.flex_value_meaning = gcc.segment5
ORDER BY 1, 3, 5, 7, 9
If the design calls for separate dimension tables for each level within the COA then one approach is to build a View that will act as the single source for each dimension table. The code below displays how this is done.
Approach 2 – DDL for a view that will act as the data source for each COA-related dimension table:
CREATE OR REPLACE VIEW "GL_COA_V" ("COA_FLEX_VALUE_ID",
"COA_FLEX_VALUE_MEANING",
"COA_DESCRIPTION",
"COA_FLEX_VALUE_SET_NAME"
)
AS
SELECT DISTINCT fvt.flex_value_id coa_flex_value_id,
fvt.flex_value_meaning coa_flex_value_meaning,
fvt.description coa_description,
fvs.flex_value_set_name coa_flex_value_set_name
FROM fnd_flex_values fv,
fnd_flex_values_tl fvt,
fnd_flex_value_sets fvs
WHERE fv.flex_value_id = fvt.flex_value_id
AND fvs.flex_value_set_id = fv.flex_value_set_id
AND fvs.flex_value_set_name IN
('GL_SRS_ACCOUNT_TYPE',
'XX_COMPANY',
'XX_COMMERCIAL_UNIT',
'XX_BRANCH',
'XX_DEPARTMENT',
'XX_ACCOUNT'
)
AND fvt.source_lang = 'US'
ORDER BY 1;
Approach 2 – Query against the view to generate the data for a dimension table representing an individual COA segment:
SELECT coa_flex_value_meaning branch_cd,
coa_description branch_name
FROM gl_coa_v
WHERE coa_flex_value_set_name = 'XX_BRANCH';
A query, similar to the one above, would be used to generate the dimension table for each segment of the COA. The only change necessary would be the column name and the filter in the WHERE clause.
Conclusion-
The Chart of Accounts is a fundamental accounting concept and the heart of a GL data mart. It is the glue that ties GL Balances to Journal Entries and it enables drill-down capabilities that are so crucial to financial analysis. Implementing the COA dimension is not difficult, but determining where to find the data within EBS may be challenging for someone that is experienced with the data model. The approaches detailed above should be a good starting point for anyone who is beginning to design a finance or GL data mart.
