SQL Server Magazine

You might think that financial reporting should be a straightforward implementation of the OLAP model. Almost every vendor that offers a product in the business intelligence (BI) arena touts financial reporting as one of the primary implementations of its tool. But if you're going to use SQL Server 2000 Analysis Services to implement financial reporting, you can expect some complicated problems to come your way. Although Analysis Services can readily provide the detailed information that users want, it isn't designed to present the information in the form they require.

Fortunately, many OLAP developers encounter these problems, and you can learn from the experiences of others. In this article, I describe some of the problems that my team encountered when we tried to create a financial-reporting system for a large banking group, and I share the solution we used to overcome these problems. Our solution uses the Analysis Services database, not the presentation layer. To create presentation-layer reports, you can use an OLAP client tool, such as those that ProClarity or IntelligentApps provide, or a reporting tool, such as SQL Server 2000 Reporting Services.

If you want to practice the technique I describe in this article, you can download a sample Microsoft Access database and OLAP archived database, along with the included MDX queries, to reproduce our team's model. To use the sample databases, download the .zip file at InstantDoc ID 41783, open the file in a directory of your choice, and restore the OLAP database. You'll need to change the path of the Access database, which is the source for the OLAP database, to the correct path on your system. You can examine the sample MDX queries by using the MDX Sample Application that comes with Analysis Services.

Getting to the Bottom Line
When you're an OLAP developer, the problems you're concerned with aren't accounting problems. I assume that you've already solved problems such as eliminating intercompany transactions and converting currency by using your financial or enterprise resource planning (ERP) system and that you're starting with a clean trial balance. Now, you're ready to start OLAP modeling.

The starting point of financial reporting is the chart of accounts, a hierarchical list of accounts that make up the organization's financial system. Usually, this chart is an unbalanced parent-child list. In our team's starting OLAP model, ChartOfAccounts became the first dimension in our cube. Other dimensions included Time (most financial reports compare figures across several periods), Organization (a list of subsidiaries that's also a parent-child dimension), and business-specific dimensions such as Currencies and Responsibilities.

Accountants call the data source for our model the trial balance. In our starting model, TrialBalance is a table that has keys relating to each account, period, organization, and any other dimensions. We used the TrialBalance table as a fact table for our OLAP cube. The TrialBalance table contains several data fields, including the opening balance, transactions over the period, and the closing balance. Figure 1 shows a model of table relationships in the starting model.

Reporting Complications
Problems surfaced when we tried to create reports from this model. If you've ever looked at a company's annual report, you know that it contains some standard statements (i.e., reports) such as a balance sheet, a statement of operation (aka a profit-and-loss statement), and a report about changes in shareholders' equity. In addition, annual reports typically contain notes that provide further information about data in the main statements.

Statements and notes contain several lines representing various accounts, with subtotals at several levels. Figure 2 shows a typical profit-and-loss statement. Each of the lines in the report represents the total of several accounts in the ChartOfAccounts table. So, for example, a large number of accounts are consolidated into the Cost of Sale line in Figure 2. Figure 3 shows how the relationships between accounts and lines in a report might look. You can see that account 203, for example, is directly included in report line A and indirectly included in report lines B and C through account 203's parent account, 210.