The Finance department is almost always the heaviest user of the data warehouse. They get consolidated numbers from all business operations and this is where concern for profit, cash flow and balance sheet is the greatest. The Finance department analyzes all costs, all revenues and provides information to management that drive initiatives or corrective action across the business. People in Finance understand how the board and banks see the numbers and how the shareholders and the investment community value them. The company I am currently doing a project was recently sold to an investment firm on the strength of these numbers.

Let’s look at the four areas of financial analyses and reporting that is usually done from the GL:

Financial Statements:

• Income (Profit and Loss) Statement analysis
• Balance Sheet Analysis
• Cash Flow Analysis

Key financial ratios:

Ratios like current ratio (current assets to short-term debt), quick ratio (current assets excluding inventory, to short-term debt), Total Asset Turnover (Net sales to total assets) etc. are analyzed by comparing these values over prior periods or comparing them to budgeted values. In General Ledger, most metrics (e.g. revenues, costs) are usually analyzed as actual vs. budget to measure deviations from preset goals. Actual vs. budget reports also help point out areas that need the greatest immediate attention from the management.

Revenue and profit analyses are also usually done on just billings, usually summarized from sub ledgers (e.g. receivables invoices) directly. This is important when an organization wants to understand total billed revenue as opposed to just the revenue that can be recognized. A case in point may be subscription revenues or software maintenance revenues that are billed in advance for the entire year, but can only be recognized in part every month.

Statistical Analyses:

The GL often contains summarized information at the cost center level for various statistical units e.g. asset counts or, count of employees.

Operational Detail:

Lastly, no one in Finance will just look at high level statements and analyses in the financial data mart, if these numbers cannot be supported by the details or a record of the atomic level transactions. Hence, in addition to keeping summary account balances, atomic or line level detail of all journal entries is needed in the data mart. In cases where such infrastructure cannot be built in the data mart, a direct link should be provided in the report to get to the underlying transactions.

In this context, I want to point out the capabilities offered by the different pre-built data warehouses sold by different vendors. Oracle’s Daily Business Intelligence allows the user to drill down to see the lowest level of detail, but does not drill into the transaction itself. Cognos Performance Apps provides access to the line level details inside the data mart as well. Oracle BI Apps (previously Siebel Analytic Apps) provides “Action Link” buttons inside reports that allows the user to drill into the transaction itself while maintaining the context or parameter values in the report.

There is one other interesting point to note about analysis done in the Finance Department. Financial analysis revolves greatly around the chart of accounts, period vs. period comparisons and the organizations / divisions / cost centers. Product and Customer dimensions that drive cost and margin are not in the picture. Product revenues and margin are accounted for, to an extent in the chart of accounts, but other key dimensions like customers and sales persons are not present, thus limiting their view of the business. In an enterprise-scale data warehouse, however, cross functional analysis is possible. If Finance suspects the cause of certain variations from actual to budget numbers, they can drill into the other areas to pinpoint the cause of such deviations.

In the next part, I will put up some of the most common data warehouse reports that are created from a Financial Data Mart, based upon my experience in different projects. I will try to glean the basic data requirements from these and identify the dimensions, their hierarchies, fact tables and metrics that we want to put in the EDW. We will be ready to create the basic design for the financial data mart.