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 Jan 6, 2003

Introduction to MSSQL Server 2000 Analysis Services Custom Cubes: Financial Reporting (Part I) - Page 15

By William Pearson

At this point, we will give the Account dimension members in our cube meaningful names - names that make sense to accounting / finance knowledge workers. 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.

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

38.  Click the Advanced tab in the Properties pane.

39.  Modify the All Caption to read All Accounts.

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

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

42.  Change the Name to Account.

43.  Select the Member Name Column property.

44.  Type the following expression into the property field:

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

Note that we are simply concatenating the Account ID (an Integer) 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 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. Cube processing will fail if we do not make this conversion.

While MDX (which underlies the expressions we use in working with member properties) has no conversion functions to meet the immediate need, MDX allows us to access external functions; CStr is one of many VBA functions automatically included in MDX. (A list of available external functions can be obtained from the SQL Server 2000 Books Online, which references resources for more details within the MSDN Library and other documentation.

45.  Press the Enter key.

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

Illustration 44: The Modified Properties, Account Level Member of the Account Dimension

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