Introduction to MSSQL Server 2000 Analysis Services Custom Cubes: Financial Reporting (Part I)

The cubes with which we have worked in previous lessons have been somewhat specific, and simply constructed. They have also been built largely within function-oriented constraints, and have not generally focused upon data relating to functions or operations outside the relatively strict constraints of the specific purpose or mission detailed in the lesson scenario; for example, the warehouse cube chiefly consisted of data that might be of interest to personnel working within warehouse operations, while the sales cube dealt more with the sales function, including the stores, products and other dimensions from a sales perspective. While dimensions were shared to some extent between the two cubes, they were only used within the context of those dimensions within each functional organization. Within these limited perspectives, they allowed the manipulation of data in a way that was useful to the respective information consumers, from the standpoint of performing their daily functions and making decisions within the realm of their associated operating units.

As a recovering CPA and a business intelligence consultant / data architect, I typically come into contact with complex cube structures in working with my clients from day to day; indeed, most of us experience OLAP reporting in a world of complex table designs, multiple data sources, and a host of other complicating factors. In this lesson, we will take a look at some scenarios that draw closer to the real world, and will explore a few of the options that MSSQL Server 2000 Analysis Services offers us in handling these requirements with sophistication.

We will examine a cube that contains numerous high-level elements of what one might expect to find in standard financial reporting. While the requirements of financial reporting span a wide range of possibilities and nuances, based upon diverse industry, regulatory and other drivers, we find a few common concepts in most systems: Balance Sheets, Income (or Profit and Loss) Statements, accounts receivable and accounts payable reports, and others are probably familiar to most of us. We will take a look at the components of a cube that support such statements, focusing largely on the Income / Profit & Loss Statement, and introduce new concepts that apply, while re-tracing many of the common steps we transition in building any OLAP data source, as we construct a sample finance cube for a hypothetical financial reporting department.

In this article, Part I, we will perform general setup of the core cube, focusing first on the expense side of the Income Statement. The sample database that we will use does not include a "financial statements" fact table, per se, as it focuses more on the revenues side of the equation to illustrate the construction of the Sales cube that it presents as a simple model for tutorial and other purposes. This offers us an opportunity to demonstrate the construction of a cube in a way that reflects some basic realities that we encounter in the real world - together with a scenario where the components that we want to analyze are scattered in various tables throughout the database. We continue this theme into Part II, where we will examine ways to pull the sales data into our new financial reporting cube, and show how we can then integrate the expense and revenue data to match revenues with their related expenses to illustrate the production of meaningful statements to reflect the results of company operations.