Data Warehousing Intro

As more and more financial data is created in sophisticated transactional systems (the General Ledger or “GL”) and summarized in complex reporting systems (Oracle, Hyperion, Lawson, Great Plains), it seems that the reports that are actually “Delivered” to management continue to come from the more traditional sources such as Excel and Word. Until XML/XBRL really takes off, we’ll continue to face challenges in summarizing our detail transactional data such as discussed below.

Here is a real world example of the challenge – While payments and accruals for rent expense may be captured by the GL, it takes a completely different analysis and solution to populate financial reports for internal management (number of square feet represented by the expense), or external readers (five year maturity schedule of rent payments). In this example, the delivered document provides data that is NOT captured by the GL (square footage and future commitments). Nonetheless, for the deliverable to be reliable, a comparison must be made to the recorded rent expense.

In creating this comparison, the rent expense is typically “Manually” entered on the deliverable alongside the other data. Manual manipulation of the source data is the root of many reliability issues in financial reporting. While the rent expense may seem secondary to the square footage and rent commitment analysis, you can rest assured that the stakeholders will be looking at the amounts when making decisions.

The reliance of such a presentation can be greatly increased if the rent expense were LINKED DIRECTLY to the source data in some manner. When I am generating a solution for a client, the first thing I consider is where can I obtain and store the source data for the simplest and most reliable retrieval when needed. Data-Warehousing of some sort will typically play a role. The latest technique that I have found to be most efficient is the use of PivotTables as a “mini” Data Warehouse. You can populate a surprising amount of GL data into a PivotTable. Once there, using the GETPIVOTABLEDATA function in Excel makes it very easy to retrieve data easily and across multiple dimensions……drop me a line if you’d like an example.

Tagged as: DataMining, pivottable