The Financial Data Mart – Part 3

Prove the numbers. No one in the enterprise is as concerned as Finance to prove the numbers correct. Disparities and inaccuracies in financial numbers not only present a distorted picture of organizational performance, they also carry the threat of huge regulatory consequences. That is why, even before Finance starts using the data warehouse for analysis, they validate and reconcile the details with the different sub-ledgers to prove that the summaries indeed tie to the details. This task is not trivial and if business rules are not vetted and understood well enough, discrepancies will creep up that will take an inordinate amount of time to resolve. These issues crop up when there are external systems that feed financial information to the General Ledger as summarized journal entries, while retaining the details in those systems. The subledgers in the data warehouse then have to assimilate these details from all the external systems in addition to those in Oracle GL. Such issues are unique for each organization. I will attempt to broadly outline these issues in a later post. For now, let us look at some of the most common reports that are generated from the Financial data mart.

Financial Statement Analysis:

• Trial Balance reports are used to review balances for GL accounts for budgets, actuals and encumbrances for any currency. They are typically run for a date range, a range of accounts
• Balance Sheet detail reports showing balance sheet accounts (assets, liability and equity accounts). Period to period comparisons, month-to-date, quarter-to-date, year-to-date, budget-to-actual comparisons are typical of these reports.
• Cash Flow statements for companies (usually, the first segment of the GL chart of accounts), fiscal periods.
• GL Balances report – this is a detail of all balances in different currencies, for different periods, for different cost centers
• GL detail, or Journal Entry detail reports –to trace the GL balance to all the supporting transactions.
• P & Ls (Profit & Loss Statements) show the revenue, costs, expenses, etc. for different cost centers, fiscal periods. Usually, these are more complicated as further details of the underlying subledgers are usually sought. Revenue analyses by additional dimensions like sales persons are done, some times at the detail of individual billing transactions (or invoices).

Key Financial Ratios: Detail reports are not usually run for Financial ratios and metrics like Current Ratio, Quick Ratio, Debt to Asset, Fixed Asset Turnover, Inventory Turnover (GL), Gross Profit Margin, Net Income, Retained Earnings, Return on Equity, Return on Assets, Asset Turnover, etc. The metrics are calculated for the company and trends over time and comparisons with budgeted numbers are usually of most interest.

Statistical Reports: 

• Headcounts by cost center per company, budget-actual comparisons, and trends over time
• Asset count (e.g. how many laptops do we own?) by cost center, by company, budget-actual comparisons, trended for different time periods or period-period comparisons.

The source of almost all of these metrics is the General Ledger. The source of all detail level GL transactions are Journal Entries. For further analysis, one needs to drill down to the individual Subledger (revenue, cost, expense subledgers). Budget details are also sourced from the GL. Thus, in the Financial data mart, these will be our fact tables:

• General Ledger Balances
• Journal Entry Details 
• Budget details

 Looking at the different analyses above, the key dimensions are apparent: 

• Time (Day, Month, Quarter, Year)
• Fiscal Month (for all aggregate analysis)
• Chart of Accounts
• Set of Books

If you decide to build the subledgers as part of the subject area data marts (e.g. Payables Subledger as part of the payables data mart), then these are dimensions above will need to be used and are the designated conformed dimensions.

A note about Hierarchies:

In Oracle General Ledger, you can define parent-child relationships over the individual segments of the accounting Flexfield. There are no restrictions to the number of levels that you can define. This is an example of the hierarchies for the Company segment of the Accounting Flexfield (actual company name overwritten):

Company 001 can also be defined as the parent of 002 thru 006. All segments can have hierarchies to as many levels as is desired by the business. Hierarchies can also be defined by rollup groups that go across all segments. This is suitable if reporting requirements are known in advance, something that does not happen too often, but is certainly not rare.

Whichever way hierarchies may have been defined, if the business need is to report facts in the form of aggregates and summarize for each of these segments, it is better to have a separate conformed dimension for every segment of the chart of accounts that is important for reporting. In my experience, company, account and cost center are the most common segments that have a need to report at a summary level and hence demand to be treated as distinct dimensions.

Slowly Changing Dimensions: Type 1, 2, or 3?

If you are not familiar with slowly changing dimensions and the different types of SCDs, start with Ralph Kimball’s primer on SCDs.

Accounting Flexfields and hierarchies do not change frequently. Natural account segments change little over time. Cost Center or division hierarchies, however, typically change once every year. This is most common for Sales organizations where regions are redefined and typically change every year. In such cases, the requirement is often to compare fact aggregates (e.g. sales revenue) of the current year’s hierarchy with the previous (or any of the past 5 years’) hierarchy. Requirements of this nature are common and are typical examples of where Type 3 SCDs – the least common of the SCD techniques, are used. This concept is very lucidly explained in this article – “Many Alternate Realities”, again by Kimball.

In the most common scenario, the Chart of Accounts dimension is a non SCD dimension (or Type 1 SCD), i.e. changes are not versioned and the dimension values are overwritten. Company, and Account are also usually in the same category. Cost Center, however, is a conformed dimension having Type 3 SCD technique applied to handle change. Chart of Accounts also are linked to Set of Books (see Part 1 of this series). Set of Books is also a conformed dimension that is used in most financial analysis.

Design of the data mart:

Here is a preliminary design of the Financial Data Mart for GL Balances.
 

To get details of the transactions that make up the balances in General Ledger, we need to drill into the Journal Entries. Here is how that will look:

Budgets will have a similar set of dimensions (not Journal Entry Document or Categories). In Oracle GL, you can create multiple budgets for the same chart of account combination. Hence you will need an extra dimension – Budget (or Budget Version, to be more accurate) to qualify budget amounts for a particular combination of accounting Flexfield segments.

Notice the Journal Entry Document dimension above. This contains various attributes of journal lines like batch name, journal name, description, etc. This dimension plays a key role in “reconstructing” transactions in the data warehouse at the atomic level. It may also be used as a placeholder for several non-additive facts as well. I will explain this very neat concept of Document dimensions in a later post. We have used Document dimensions extensively in my current project.

I have left out several key issues here, most notably that of security and currency conversions. These are subjects that have some unique features and will require much discussion. For now though, I will get on with this basic framework that I have outlined here. In part four of this series, I will explain the data structures in the General Ledger module of Oracle eBusiness suite and will explain how that data can be extracted to this data mart.

If you have found these discussions helpful, please feel free to comment or suggest improvements.

Links to Part 1, Part 2.

Advertisement