Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 18, 2003

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

By William Pearson

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.

Illustration 47: The Completed Select the Columns that Define the Parent-Child Data Hierarchy Dialog

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.

Illustration 48: The Completed Finish the Dimension Wizard Dialog

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:

Illustration 49: 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.

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:

Illustration 50: 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 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.

Illustration 51: A Quick Visit to the Data Tab Confirms the Correct Account Name Display (Compressed View)

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM