Like this:

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.

Advertisement