In many multi-part series, I will lay out the design for the Enterprise Data Warehouse from an Oracle eBusiness Suite. Obviously, these will be by business areas. I will start with the core financial module – the General Ledger. Let’s try to put a structure to each of these series:

Part 1 – Functional Overview
Part 2 - Key Reporting / Analyses
Part 3 - Target design and considerations
Part 4 – Source Schema, Key queries, ETL maps, business rules
And more as needed for the topic under discussion.

Part 1: A short Overview of Oracle General Ledger:

The General Ledger (GL) is at the heart of the Accounting system of Oracle eBS. The main purpose of GL is to record financial activity of the company and to produce financial and management reports. The GL consolidates financial information from all other transactional modules or subledgers and maintains summary level information. For example, it stores the accounting information for a Receivables invoice or an invoice payable to a vendor. It stores accounting entries for expense for depreciation of an asset or an inventory transfer. It also has accounting entries made within itself using journal entries.

If you want to know more on Oracle General Ledger, or any other Financial module, refer to the latest User Guide (pdf file) or the Oracle E-Business Suite Financials Handbook, a handy reference that I highly recommend to anyone who wants a thorough understanding of the functional concepts of the financial modules within Oracle eBusiness Suite.

The following diagram is a high level overview of how some of the core modules interact with each other within Oracle Financials:
http://intelligentbusiness.files.wordpress.com/2008/06/gl_int1.gif

Source: 11i Financial Functional Foundation

Let us dig a little deeper to understand some of the key concepts in General Ledger that we will need to know before we attempt to design the EDW. 

Set of Books and The three C’s:

The set of books in Oracle GL is a relationship where chart of accounts, fiscal calendar and currency of the organization are defined. More than one set of books are usually defined for an enterprise, each set of books identifying one business unit. For example, you may have operations in USA, Canada, UK and other countries. You would want to set up a different set of books for each country (with its own currency) and a set of books to consolidate financial information from all the other set of books. Set of Books will be one of the conformed dimensions in your EDW. Each of the three C’s are shared with the subledgers:

• The subledgers use the GL Chart of Accounts to record accounting entries for transactions which are then transferred to GL.
• The subledgers open and close their own accounting period but the periods are defined in the fiscal calendar in GL (Fixed Assets is an exception to this rule, as it creates its own calendar. However, as part of Oracle eBS setup, FA period names always match the GL period names). 
• Currency: Financial information can be entered in different currencies (more on this a bit later)
  As we develop the BI analytic requirements, it will be evident that these three Cs are also going to be conformed dimensions within the EDW.

A quick note on security:Oracle GL, and for that matter, all Oracle modules are secured by “Responsibilities”. For GL, each set of books is linked to a responsibility. Responsibilities can be more restrictive to limit access to specific functions within GL. For EDW, however, security needs are different. These needs have to be understood at the data level, not at the process level, as is the case in Oracle eBS.

More on currencies:In Oracle GL (and all modules) a “functional” currency is established for each set of books. Transactions can happen in the functional currency or any other “foreign” currency. For transactions done in foreign currencies, there will be a need for Conversion (foreign currency that is immediately converted to functional currency at the point of transaction), Revaluation (adjustment to liability or asset accounts that may be differently stated at the end of the period due to the fluctuation of the exchange rate between the transactional and functional currencies) and Translation (restating the GL balances for an entire set of books from the functional currency to a foreign currency). We need to understand these as these will have an impact on our ETL design, which I will discuss in a later post in this series.

In my next post I will lay out some of the key reporting requirements that will be the basis for our financial data warehouse design.