In this short technical article, I will outline the data structures of the General Ledger module in Oracle eBusiness Suite that are relevant in building the Financial data mart. If you are currently engaged in building a data warehouse with Oracle eBusiness suite as the primary source of data, you may find this useful, particularly, if you are performing ETL tasks.

Refer to part 3 of this series for the broad design. Here are the sources in Oracle eBusiness suite for the different dimension and fact tables:

Dimensions:

Facts and Document Dimensions:

Source Data Structures:

The following diagram outlines the source table relationships for the facts above:

The Budget tables’ relationships are given below:

More detailed diagrams can be downloaded from Oracle Metalink.

The descriptions for the chart of account segments are stored in Flexfields within Oracle. The following query, for example, can be used to get the descriptions:

select gcc.segment1 company
, gcc.segment2 account
, gcc.segment3 cost_center
...
, fvc.description company_desc
, fva.description account_desc
, fvcc.description cost_center_desc
...
from gl_code_combinations gcc
,fnd_flex_values_vl fvc
,fnd_flex_values_vl fva
,fnd_flex_values_vl fvcc
where fvc.flex_value_set_id = (select flex_value_set_id from fnd_flex_value_sets where flex_value_set_name like '<YOUR_COMPANY_GL_AFF_COMPANY')
and fvc.flex_value_meaning = gcc.segment1
and fva.flex_value_set_id = (select flex_value_set_id from fnd_flex_value_sets where flex_value_set_name like ' YOUR_COMPANY_GL_AFF_ACCOUNT')
and fva.flex_value_meaning = gcc.segment2
and fvcc.flex_value_set_id = (select flex_value_set_id from fnd_flex_value_sets where flex_value_set_name like ' YOUR_COMPANY _GL_AFF_COST_CENTER')
and fvcc.flex_value_meaning = gcc.segment3
order by 1,2,3,...

ETL Tip:

Loading Fact GL Balances may be tricky if you are not aware that the source table stores balances for all types of amounts - budgets, actual amounts, encumbrances and statistical amounts. Also, it stores the translated amounts if the translation activity has been performed at the close of the fiscal month. As a result of this activity, you may have adjustments posted to certain accounts where you will have actual adjustment amounts posted in the translated currency even though the corresponding local currency amounts are zero.  If you are translating amounts in the Fact GL Balances table on the fly using the reporting tool, be aware of this issue. Although translations can easily be done dynamically, I have found it unwise to do so because of these issues and the complex business rules that often are applied to translations. It is safer to store actual translated values in the fact table itself.

This is the end of this series. I will write about building Sales Data Marts in the next series. Hope you enjoyed these - I am happy to answer any questions you may have or elaborate on anything I have stated in these posts.

Also read Part 1, Part 2 and Part 3 of this series.