267.338.3300 Ext 510

Designing the Financial Data Warehouse

By Dan Hughes

Copyright @ 2016 Claritee Group, LLC

Introduction / Summary

Building a data warehouse to support financial decision-making can be one of the most challenging endeavors a data-warehousing manager can undertake. Senior Management visibility, political territory, domain expertise, complex transaction systems, and a need to understand the business at a cross-functional level are just a few of the challenges. But working in this area can also be a boon to one’s career and very rewarding. This article is the first in a series that will explore the process of applying dimensional modeling design techniques to financial analysis. In this article, we will describe the typical transaction system landscape supporting Finance and review some limitations of the current financial analysis model. We will then argue for an approach that will overcome these limitations. Subsequent articles will focus on technical and non-technical challenges you will encounter using this approach, along with recommended solutions we have tested.

Currently, there are many vendors promoting the “data warehouse in a box” or “rapid implementation through zero programming” idea. Although this may be achievable for single source first level data marts, we believe the real golden nuggets of information, and thus the real challenges, exist when the corporate financial repository, the general ledger (GL), is joined with the transaction subsidiary ledgers (sub-ledgers) to create a single repository of financial information. The design of such a sophisticated data warehouse must be approached with great care. As we have designed financial data warehouses, it has been beneficial for us to step back and develop an approach that not only looks at the sub-ledger, but also looks at how the data is integrated into the GL.

GL – The Core Financial Application

At the highest level of the organization, all financial activity is summarized into the financial statements: the balance sheet and income statement. The balance sheet is an accounting of the assets, liabilities, and equity of the firm, while the income statement is an accounting of income and expenses. The financial statements are sourced from the GL.

The GL is a transaction system designed to record financial transactions in accordance with accounting standards defined by historical precedent and the various worldwide standards bodies. In addition, the GL is used to organize enterprise financial activity into a common structure known as the chart of accounts (COA). The COA is a data structure designed to numerically represent the physical organization of the business. COA segments, such as business units, cost centers, products, projects, and accounts, are common ways of classifying financial measures. All financial activity, regardless of the source, is consolidated into a format that will allow for common performance measurement of the enterprise.

The Role of the Sub-Ledgers

The detail transactions of the business, such as sales orders, corporate debt, accounts payable invoices, purchase orders, and customer receivables, are recorded in transaction systems categorized as sub-ledgers. The purpose of the sub-ledger is to track the transaction level detail of financial activity, such as customer and vendor information, product attributes, quantities ordered, transaction dates, and amounts. In addition, the sub-ledger captures COA segment information like business unit, cost center, and account. The segments are used to aggregate the financial activity recorded in the sub-ledger into journal entries for posting to the GL. These systems may or may not be part of an integrated Enterprise Resource Planning (ERP) system. The number and sophistication of the sub-ledger systems will vary depending on the nature of the enterprise and its organization. Our experience has shown us that integrated ERP applications generally account for the core business functions (i.e. inventory management, accounts payable, fixed assets, purchasing, accounts receivable, and cash management), while industry specific functions, such as credit card servicing, property management, or fixed income securities, are tracked through niche software products or spreadsheets. Most mid to large size organizations will have a wide variety of disparate sub-ledger applications.

The Accounting Close Process

At the end of each period, usually a month, the accounting group begins a process called “closing the books.” Closing the books simply means compiling all of the sub-ledger transactions for a given period and postings them to the GL. On the surface this process seems to be straight forward, but in our experience, the process of reconciling the sub-ledger data to its source, and preparing it for posting to the GL, is an extremely arduous task. Analysts spend many days each accounting period gathering and compiling the data to record the journal entries. Once the data is recorded in the GL, the accounting period is closed, meaning no more entries are permitted in the GL for that period. Financial statements are then created from the GL and distributed to consumers of the information. The consumers include personnel from all levels of the organization as well as external entities, such as banks, investors, and regulatory bodies.

Current Financial Statement Analysis Model

The users of the financial statements have a summary view of the underlying transactions originated in the sub-ledger systems. The problem with this model is that analysis of the financial statements is limited to the intelligence captured in the COA. Any analysis requiring information captured in the transaction sub-ledgers must be separately researched and retrieved. An example is accounts receivable analysis:

Once a sale is complete, the order is entered into the accounts receivable module to record the sale and amount owed by the customer. Periodically, the sub-ledger aggregates the transactions and posts summary entries to the GL to record the revenue and receivable activity. Because the entries are aggregated, the GL does not contain receivables transaction details, such as sales date or customer information. Therefore, if management wanted to perform accounts receivable analysis on the balance sheet to analyze the age of their receivables, or identify the top ten customers by receivables balance, it would not be possible because the underlying information is not contained in the GL. A separate report would have to be generated from the accounts receivable sub-ledger. This is a simple example of how transaction details are not contained in the GL, and how this limits the analytical reporting capabilities of traditional financial statement analysis.

Because of the separation between the GL and sub-ledgers, many of the more difficult questions about enterprise performance cannot be answered in a reasonable and efficient manner. By integrating the GL with the sub-ledgers, all of the summary and underlying data are in a single repository, thus providing all consumers of financial data access to transaction level details.

Approach

As we think through the design of a financial data warehouse, we always start with the GL and gain a firm understanding of the COA. Whenever a requirement to analyze sub-ledger data comes to us, we look at how the financial measures roll up to the GL. Here are a few key questions we ask:

Where is the information represented on the financial statements?

Are there users of the financial statements who could benefit from having access to the detail information contained in the sub-ledger?

What are the data keys (i.e. COA segments and system identification links) that tie the two systems together?

This approach allows us to evaluate the business requirement of providing the user access to data contained in the sub-ledger, while thinking about the organization from a global financial statement analysis perspective. Once the first sub-ledger is integrated into the data warehouse, future sub-ledgers can be incrementally added. Over time the repository becomes more valuable to the enterprise and eventually the single platform for financial decision support. This approach gives the users of the sub-ledgers a tool to analyze the transaction data, while the financial statement users have greater insight into the numbers at an enterprise level. The requirements of both groups of users can be satisfied.

Conclusion

If you agree that there is value in this approach, your next question will undoubtedly be, “So where do I start?” The first design deliverable in all of this is to create a dimensional model that captures the information recorded in the GL. In the next article, we will describe a dimensional model that supports basic balance sheet and income statement analysis. With this in-hand, you will be ready to tackle the sub-ledgers.