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
Click the Process Cube
button on the top toolbar.
Click Yes, when asked if
saving the cube is desired.
Click No to bypass the Storage
Ensuring that the Full
Process method is selected on the Select the Processing Method
dialog, click OK to begin processing.
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.
Click Close to dismiss
the Process status dialog.
Click the Data tab to
view the results so far.
Ensure that the Account
dimension is in the row axis of the data tab.
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.
Expand the 3000 Total Sales
level now appearing within the expanded Net Income level (to its right),
as shown in Illustration 52.
The Fin_Rptg Cube Layout - Data Tab View Expanded (Compressed View)
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
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.
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.
Within the Analysis Manager
console, and from the cube tree, click
the new Fin_Rptg cube (our Expense cube) to select it.
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
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
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:
- Within the Cube Editor, select the Account
level immediately under the Account dimension in the cube tree.
- Click the Advanced Properties tab.
- Select the Custom Members property.
- Click the ellipsis (..) button to the right of
the property box.
The Define Custom Member Column dialog appears.
- Click to place a check in the box to the left of Enable
- Click-select the Use an Existing Column radio
- 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.
Illustration 53: The Define Custom
Member Column Dialog with Settings