Designing and Building the Financial Reporting Cube

Financial reporting relies heavily upon the summarization and aggregation of the contents of accounts, "containers" that hold the transactional details of an organization's daily operations. The details that reside in a given account are often summarized in hierarchical fashion in a "rollup" or parent account, but, alternatively, may be netted against rollup or peer accounts, or handled in an isolated fashion, among other possibilities. The seemingly variable manner with which these combinations, or the absence of same, occur is understandably confusing to many. For that matter, the mere assignment of debit and credit transactions to accounts, while "rational and systematic," perhaps, to an accountant, often seems arbitrary, at best, to those not familiar with accounting concepts. Suffice it to say that these and other such nuances are complicating factors in a financial reporting cube.

We will create a basic Financial Reporting Cube in much the same manner as we have created the numerous other cubes we have built within this series. We will then introduce the concepts to which we have alluded in the discussion thus far, where we can see practical applications of these ideas in action. As we learned in Lesson One, and have practiced in the creation of subsequent cubes in later lessons, we first create an OLAP database to organize cubes, roles, data sources, shared dimensions, and other objects. We will call our OLAP database FinRepCube, setting it up inside Analysis Manager, then linking a data source to our database before we start to construct our cube, as we have done previously.

  1. Start Analysis Manager.
  2. Expand the Analysis Servers folder by clicking the "+" sign to its immediate left.
  3. Right-click on the Analysis Server name (mine is MOTHER, in this example), to see the context menu shown below:

Illustration 1: Right-Click on the Analysis Server

  1. Click New Database.

The Database dialog box appears.

  1. Fill in the Database Name - let's make it FinRep_DB - along with a description that might be of value to a user or developer down the road.

Let's simply add "Financial Reporting Database," here - the description is optional, of course.

The dialog appears as below.

Illustration 2: The Database Dialog Box

  1. Click OK.

FinRep_DB appears at a point below the existing FoodMart 2000 database.

  1. Expand FinRep_DB's database / cube icon by clicking in the plus (+) sign to the left of the icon.

Note that FinRep_DB appears in the left-side tree, complete with predefined, empty folders for object storage later, as shown in Illustration 3 below.

Illustration 3: The New Database with Directory Structure