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

In this, the first segment of a two-part article, we introduced an objective that integrates many concepts we have examined individually in preceding lessons: To build a simple cube to meet some illustrative business requirements of a hypothetical corporate financial reporting function. We expanded upon many of the concepts we have considered throughout the series, focusing the kindred methods and procedures on our objective to create a Financial Reporting Cube. We built a core cube, based upon the expense fact table provided within the FoodMart 2000 sample database, to begin our efforts, discussing the realities of multiple fact tables in data sources that we often encounter in the business environment. We also explored some of the challenges that accompany cube design for financial reporting, both from the outset of the lesson, and at appropriate points through which we progressed the design and development of the Financial Reporting Cube structure.

As a part of my commitment to making lessons standalone, where members of the audience can complete each without being hamstrung by the absence of objects and structures created in earlier lessons that they did not see fit to complete, we created the basic Financial Reporting Cube (expense portion) from scratch; We created an OLAP database, to which we linked a data source, to support the creation of our new cube. We then began the cube design and development process around a single fact table, focusing on various concepts contained within the effective use of the Cube Editor, including expanding the utility of the cube through the use of various member properties setpoints. Our efforts also included the creation of a parent-child dimension, using the Dimension Wizard for this part of our project.

In our next article, Custom Cubes: Financial Reporting Part II, we will continue the design and construction of the Financial Reporting Cube, focusing our attention initially on the refinement of our Account dimension. (We will, therefore, use the data source and other objects that we have created in this lesson in Part II, both to save time and to provide continuity). Next, we will discuss options for the entrainment of the revenue data that resides in the separate Sales fact tables of the sample data source to complete the design of a cube that supports the financial reporting needs of the organization. We will introduce rollup and aggregation concepts and considerations, as well as methods of accomplishing sign control within our calculations and presentation. Moreover, we will address formatting, and other considerations that arise as we create a cube to meet the needs of our information consumers.