The Select the
Columns that Define the Parent-Child Data Hierarchy dialog appears. We
will "fill in" the selector boxes in the following steps:
Select account_id as the
We will use account_id
as the Index that clearly identifies our member accounts.
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.
as the Member Name.
The dialog appears with
our selections below.
The Completed Select the Columns that Define the Parent-Child Data Hierarchy Dialog
Click Next again, to
skip the Select Advanced Options dialog.
The Finish the
Dimension Wizard dialog appears.
Type the word Account
into the Dimension Name box.
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 Completed Finish the Dimension Wizard Dialog
provides a scrollable view of the hierarchy that our selections will generate.
Click Finish to close
the Dimension Wizard.
The dialog disappears,
leaving the view of the Schema tab as depicted below, after arrangement:
Cube Editor - Schema View
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.
Click the Account dimension,
to select it in the cube tree.
Click the Advanced tab
in the Properties pane.
Modify the All Caption
to read All Accounts.
Click the Account_Id
level that appears below the Account dimension to select it.
Select the Basic tab,
then the Name property.
Change the Name property
field to Account.
Select the Member Name
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.
Press the Enter key.
pane, with the modifications, should appear as shown below:
The Modified Properties, Account Level Member of the Account Dimension
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
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
A Quick Visit to the Data Tab Confirms the Correct Account Name Display