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

The Select the Columns that Define the Parent-Child Data Hierarchy dialog appears. We will "fill in" the selector boxes in the following steps:

68.  Select account_id as the Member Key.

We will use account_id as the Index that clearly identifies our member accounts.

69.  Select account_parent as the Parent Key.

The account parent is the account to which the account "rolls" in summary fashion, and is identified in the account table to provide for hierarchical design just such as this. The parent key acts as the "pointer" to guide rollups of data, as we shall see in short order.

70.  Select account_description as the Member Name.

The dialog appears with our selections below.

71.  Click Next.

72.  Click Next again, to skip the Select Advanced Options dialog.

The Finish the Dimension Wizard dialog appears.

73.  Type the word Account into the Dimension Name box.

74.  Uncheck the checkbox (click it once) for Share This Dimension with Other Cubes.

The dialog appears (with expanded Preview), with our selections as shown below.

The Preview provides a scrollable view of the hierarchy that our selections will generate.

75.  Click Finish to close the Dimension Wizard.

The dialog disappears, leaving the view of the Schema tab as depicted below, after arrangement:

Now let's take a couple of small steps to refine the new Account dimension before processing our enhanced Revenue cube.

At this point, we will give the Account dimension members in our cube meaningful names - names that make sense to accounting / finance knowledge workers. As we mentioned in Part 1, while many accountants and / or their counterparts in finance know the chart of accounts by account codes, from daily usage and coding conventions, other information consumers will need to know the account names. To strike a useful compromise, let's give them both, by using the Member Name Column property of our new Account dimension.

76.  Click the Account dimension, to select it in the cube tree.

77.  Click the Advanced tab in the Properties pane.

78.  Modify the All Caption to read All Accounts.

79.  Click the Account_Id level that appears below the Account dimension to select it.

80.  Select the Basic tab, then the Name property.

81.  Change the Name property field to Account.

82.  Select the Member Name Column property.

83.  Type the following expression into the property field:

Cstr("account"."account_id")+ '   ' +"account"."account_description"

As we discussed in Part 1, we are simply concatenating the Account ID and the Account Name, either or both of which we have determined might be helpful to information consumers in the use of the cube we are designing. The CStr function in the expression, as we noted, allows us to combine different data types in the concatenation, converting the Account ID (an integer data type) to a string to make it compatible with the string data type of the Account Description. This conversion must be made or attempts at cube processing will result in failure.

A discussion of MDX (upon which the expression above is based), and its use of external functions, can be found in Part 1.

84.  Press the Enter key.

The Properties pane, with the modifications, should appear as shown below:

As we noted in the first half of this article, every dimension level comes equipped with member key and member name properties, each of which can be easily modified by the developer. Please see Part 1 and preceding lessons for more details on member properties.

I like to check my calculations before kicking off cube processing whenever possible, to ensure that I have not made any blunders that would simply waste time with a failed processing event. A quick way to do this in the current example is to take the following steps:

85.  Click the Data tab.

We are duly warned, both in the fleeting message that appears when we initially select the tab, as well as with the static warning message at the bottom of the Cube Editor, that we are viewing sample data, and that the cube has not been processed. The sample data provided, however, does provide us a prospective view of the account names, which acts to confirm the accuracy of our calculation in this case. The Data tab information should resemble that presented in Illustration 51 below.