Chapter 7 : Accounting

Chapter 7 : Accounting

General Ledger Data
The general ledger (G/L) is a core foundation financial system because it ties
together the detailed information collected by the purchasing, payables (what
you owe to others), and receivables (what others owe you) subledgers or systems.
In this case study we’ll focus on the general ledger rather than the subledgers,
which would be handled as separate business processes and fact tables.
As we work through a basic design for G/L data, we discover, once again, that
two complementary schemas with periodic snapshot and transaction-grained
fact tables working together are required.
Financial analysts are constantly looking to streamline the processes for
period-end closing, reconciliation, and reporting of G/L results. While operational
G/L systems often support these requisite capabilities, they may be
cumbersome, especially if you’re not dealing with a modern G/L. In this chapter
we’ll focus on more easily analyzing the closed financial results rather than
facilitating the close. However, in many organizations, G/L trial balances are
loaded into the data warehouse to leverage the capabilities of the data warehouse’s
presentation area to find the needles in the G/L haystack and then
make the appropriate operational adjustments before the period ends.
The sample schema in Figure 7.1 supports the access and analysis of G/L
account balances at the end of each account period. It would be very useful for
many kinds of financial analysis, such as account rankings, trending patterns,

and period-to-period comparisons.

In general, to-date totals should be calculated, not stored in the fact table.

Role of OLAP and Packaged Analytic Solutions

Given the standard nature of G/L processing, purchasing a G/L package

rather than attempting to build one from scratch has been a popular route for

years. Nearly all the operational package providers also offer a complementary

analytic solution, sometimes in partnership with one of the multidimensional

OLAP vendors. In many cases these canned analyses based on the

cumulative experience of the vendor are a sound way to jump-start a financial

data warehouse implementation with potentially reduced cost and risk. The

analytic solutions often have tools to assist with the extraction and staging of

operational financial data, as well as tools to assist with analysis and interpretation.

However, as we discussed in Chapter 6, when leveraging packaged

solutions, we need to be cautious about avoiding stovepipe applications. One

could easily find oneself in a situation with separate financial, CRM, human

resources, and ERP packaged analytic solutions from as many different vendors,

none of which integrates with other internal data. We need to conform

dimensions across the entire data warehouse environment regardless of

whether we’re building our own solution or implementing packages. Packaged

analytic solutions can turbocharge your data warehouse implementation;

however, they do not alleviate the need for conformance. Most

organizations inevitably will rely on a combination of building, buying, and

integrating for a complete solution

The budget line item identifies the purpose of the proposed spending, such as

employee wages or office supplies. Typically, several levels of summarization

categories are associated with a budget line item. As we discussed in Chapter 5,

all the budget line items may not have the same number of levels in their summarization

hierarchy, such as when some have only a category roll-up but not

a subcategory. In this case we may populate the dimension attributes by replicating

the category name in the subcategory column to avoid having line items

roll up to a “Not Applicable” subcategory bucket. The budget line-item dimension

also would identify the budget year and budget version.

The effective month is the month during which the budget changes are posted.

The first entries for a given budget year would show the effective month when

the budget is first approved. If the budget is updated or modified as the budget

year gets underway, the effective months would occur during the budget

year. If we don’t adjust the budget at all throughout the year, then the only

entries would be the first ones when the budget is approved. This is what we

meant when we specified the grain to be the net change. It is critical that you

understand this point, or you won’t understand what is in this budget fact

table or how it is used.

Consolidated Fact Tables

When facts from multiple business processes are combined in a consolidated fact

table, they must live at the same level of granularity and dimensionality. Optimally, the

separate facts naturally live at a common grain. Otherwise, we are forced to eliminate

or aggregate some dimensions to support the one-to-one correspondence or keep

them in separate fact tables. Project teams should not create artificial facts or dimensions

in an attempt to force fit the consolidation of differently grained fact data