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 Oct 13, 2008

MSSQL Analysis Services - Attribute Member Names - Page 4

By William Pearson

The Collation property affords us a way to specify the rules we wish to invoke for text data string comparisons. While collation in general has multiple purposes, it is often used to support the determination of whether the members of a given pair of strings are alike or different. Several Sort Orders are also available, with the Designator and Sort Order selections defaulting to server settings (in effect here, where the Collation property appears empty within the Properties pane.

Because Collation can have an impact upon the uniqueness of attribute member Names, the setting becomes particularly significant when we are specifying Names. Examples given in various books and other documentation surrounding this consideration often cite the fact that treatment of capitalized and non-capitalized letters (some Collation settings ignore capitalization entirely, whereas others treat capitalized letters, etc., as different characters than the same, non-capitalized letters). Collation also impacts sorting, so when information consumers dictate, say, in a report they create in Reporting Services that they wish to retrieve a group of attribute members in order of their respective Names (versus in order of the attribute member Key), results might not match expectations if the Collation property setting is not taken into consideration .

NOTE: We always have the option of using another attribute entirely as a basis for our sort orders, if Key or Name is somehow unsatisfactory. For more information on this versatile option, see my article Alternatively Sorting Attribute Members in Analysis Services 2005.

15.  Click the downward arrow selector button to the right of the box labeled Collation designator, to expose the collations available for selection, as partially depicted in Illustration 16.

Illustration 16:  Available Collation Options (Partial View)
Illustration 16: Available Collation Options (Partial View)

16.  Leaving the settings in the Collation Designator dialog at their previously established values, click the Cancel button to dismiss the dialog.

17.  Click the Format label, just beneath the Collation property label, simply to rest it there.

We have noted in other articles of this series that the Format property purports (via the Books Online and other documentation) to allow us to specify - using Visual Basic (Format function) format types - the conventions used in transforming numeric data to text, if such a transformation is required. The reality is that the only member formatting supported within the Unified Dimension Model (UDM) is the Trimming setting that we discuss below. (We can, of course, employ named calculations or column calculations (at the data source view level) within the cube to achieve our formatting ends, as alternative approaches.

18.  Leaving the Format property blank, click the box to the immediate right of the InvalidXmlCharacters label, just beneath the Format property, once again to enable the downward-pointing selector button.

19.  Click the downward arrow selector button, to expose the three selection options for InvalidXmlCharacters, as shown in Illustration 17.

Illustration 17:  Selection Options for InvalidXmlCharacters
Illustration 17: Selection Options for InvalidXmlCharacters

The InvalidXmlCharacters property is applicable in cases where we expect data to be received in the XML format, and where we wish to dictate the handling of such data, if, and when, invalid characters appear. The values are explained in Table 2.




Analysis Services preserves (e.g., does not change) invalid characters.


Analysis Services removes invalid characters.


Analysis Services replaces invalid characters with a question mark (“?”)

Table 2: Options for InvalidXmlCharacters Selection

20.  Leaving the InvalidXmlCharacters property at its previously established setting, click the MimeType label, just beneath the InvalidXmlCharacters property label, simply to rest it there.

The MimeType property allows us to specify the binary data type, where necessary to meet our needs.

21.  Leaving the MimeType property blank, click the box to the immediate right of the Trimming label, just beneath the MimeType property, as before, to enable the downward-pointing selector button.

22.  Click the selector button, to expose the four options for Trimming selection, as depicted in Illustration 18.

Illustration 28: Trimming Property Value Selection Options
Illustration 28: Trimming Property Value Selection Options

The Trimming property allows us to specify the desired treatment of trailing spaces at the beginning / end of a string. As we see in Illustration 18 above, the options are self-explanatory.

23.  Leave the Trimming setting as it currently exists.

NOTE: Please consider saving the project we have created to this point for use in subsequent related articles of this subseries, so as to avoid the need to repeat the preparation process we have undertaken initially, to provide a practice environment.

24.  Select File -> Save All to save our work, up to this point, within the originally chosen location, where it can be easily accessed for our activities within subsequent articles of this subseries.

25.  Select File -> Exit to leave the design environment, when ready, and to close the Business Intelligence Development Studio.


In this article, we examined the attribute member Name property, which we briefly touched upon in Dimensional Attributes: Introduction and Overview Part V. We examined the details of this property, and shed some light on how it can most appropriately be used without degrading system performance or creating other unexpected or undesirable results.

We focused, throughout our examination of attribute member Name, on the general nature of the property, and its possible roles in helping to meet the primary objectives of business intelligence. We reviewed its roles from a technical perspective, including its purpose within its containing dimension within Analysis Services.

In gaining hands-on exposure to attribute member Names, we discussed, at appropriate points, some of the differences between attribute Name and Key properties. We also mentioned significant differences between Analysis Services 2000 and Analysis Services 2005, particularly regarding the use of expressions within our Name column references. Finally, we performed a detailed review of the settings associated with the attribute member Name property, based upon the examination of a representative dimension attribute within our sample UDM.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

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