Introduction to MSSQL Server 2000 Analysis Services: Custom Cubes: Financial Reporting - Part II - Page 11

We are now ready to process the cube, and to ensure we are progressing toward our objectives with the overall Financial Reporting cube project. Involved cube designs often require several such "updates;" the ease with which Analysis Services facilitates processing a cube is certainly a pronounced (and appreciated) benefit.

86.  Click the Process Cube button on the top toolbar.

87.  Click Yes, when asked if saving the cube is desired.

88.  Click No to bypass the Storage Wizard.

89.  Ensuring that the Full Process method is selected on the Select the Processing Method dialog, click OK to begin processing.

The Process status dialog appears, and details the progression of the processing steps as they are accomplished. The end result is the green Processing Completed Successfully message at the bottom of the dialog, as we have seen earlier.

90.  Click Close to dismiss the Process status dialog.

91.  Click the Data tab to view the results so far.

92.  Ensure that the Account dimension is in the row axis of the data tab.

93.  Expand the 5000 Net Income level of the Account dimension, by double-clicking the "+" sign to the left of the description on the Data tab.

94.  Expand the 3000 Total Sales level now appearing within the expanded Net Income level (to its right), as shown in Illustration 52.

We see that our initial objective, to create a core Revenue cube to complement the Expense cube we created in Part I, appears to have been accomplished.

95.  After examining the expanded Data tab view, click File --> Exit from the top menu to close the Cube Editor.

We have achieved the design and development of our second core cube for financial reporting, having set up the revenue side of the end model, to complement the Expense cube we created in Part I, and to allow for analysis of the components of Sales, Cost of Goods Sold, and Net Sales. Our next objective is to "marry" the two cubes to complete the creation of the Financial Reporting cube.

The most straightforward way to combine the data within the cubes, now that their structures are in alignment from the perspective of dimensions that will prove useful in our objective, will be to bring the data from the smaller (in our case the Revenue cube) to the larger (the Expense cube). As the Revenue cube occupies the least number of accounts and levels in the structure (it is isolated, effectively, to the 3000-series accounts), we will entrain the data in the Revenue cube into the Expense cube to derive an integrated cube for Income Statement Financial Reporting.

Completing the Financial Reporting Cube

If we redirect our focus at this point to the Expense cube, we can see readily that the 3000-series accounts are unpopulated. Having built both the Expense and Revenue cubes, we realize that no single fact table existed that housed the data that comprises both cubes' accounts (the main reason we needed two cubes in the first place). To illustrate this once again, and to explore a means of integrating the data from both cubes, we will turn to the Expense cube via the Cube Editor.

1.      Within the Analysis Manager console, and from the cube tree, click the new Fin_Rptg cube (our Expense cube) to select it.

2.      Right click the Fin_Rptg cube, and select Edit from the context menu that appears.

The Cube Editor opens. We now need to adjust some settings to allow us to entrain the data from the Revenue cube, Fin_Rptg_Rev.

We will recall that, in our visit to the FoodMart 2000 database earlier in the lesson, we made some alterations to the Custom Members fields in the account table. Specifically, we replaced one expression that already existed for Account 3100, Gross Profit, and subsequently added an expression into the field directly below it. We placed these expressions in a column aptly named Custom Members.

While we won't get into the details of MDX at this point, suffice it to say that the Custom Members column acts to house expressions that enable us to entrain the values they specify from another cube. They act to redirect the cube from the fact table as the source of the data for the member with which they are associated, and perform as "pointers" to the alternative source, in our case the Revenue cube.

We have determined in earlier sections why we needed to look beyond the expense_fact table to achieve our objectives of entraining revenue data. Our lesson involves the combination of the Expense and Revenue cubes we have created in this lesson, because the Expense cube houses operating expense data, while the Revenue cube contains Sales / Revenues data, as well as Cost of Goods Sold data. The primary objective in creating our ultimate Financial Reporting cube is to provide information consumers a means of performing Income Statement reporting, based upon the data as it exists in the database, much as we would be expected to do in many real-world scenarios.

Each of the two expressions we added contains a LookupCube function; the two arguments laid out in the function specify the targeted cube (Fin_Rptg_Rev), as well as directing the values in the cube that we wish to entrain from the targeted cube. (For more information on the LookupCube function, as well as upon MDX in general, see the MSSQL Server 2000 Books Online or other comparable sources.)

The expressions are, as we are aware, already in place. Our next action will be to "activate" those expressions by enabling Custom Members for the Fin_Rptg cube. We do this by taking the following steps:

3. Within the Cube Editor, select the Account level immediately under the Account dimension in the cube tree.
4. Click the Advanced Properties tab.
5. Select the Custom Members property.
6. Click the ellipsis (..) button to the right of the property box.

The Define Custom Member Column dialog appears.

7. Click to place a check in the box to the left of Enable Custom Members.
8. Click-select the Use an Existing Column radio button.
9. Using the dropdown Existing Column selector, choose Custom Members.

The Define Custom Member Column dialog appears, with our new settings, as shown in Illustration 53.