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 Sep 19, 2008

Attribute Member Keys - Pt II: Composite Keys - Page 6

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.

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

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

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

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

We noted in Part I 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.

17.  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.

18.  Click the downward arrow selector button, to expose the three selection options for InvalidXmlCharacters, as depicted in Illustration 20.

Illustration 20:  Selection Options for InvalidXmlCharacters
Illustration 20: 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. 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

19.  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.

20.  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.

21.  Click the selector button, to expose the four options for Trimming selection, as shown in Illustration 21.

Illustration 21: Trimming Property Value Selection Options
Illustration 21: 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 21 above, the options are self-explanatory.

22.  Click the OK button to dismiss the DataItem Collection Editor.

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.

23.  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.

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


In this, the second half of a two-part article introducing Attribute Member Keys, we continued our recent group of articles focusing upon dimensional model components, with an objective of discussing the associated concepts, and of providing hands-on exposure to the properties supporting them. We reviewed our initial introduction to the dimensional model and summarized its role in meeting the primary objectives of business intelligence. Next, we provided a brief overview of dimension attributes in general, referencing a subseries of articles, within my Introduction to MSSQL Server Analysis Services series, where we explore the properties underlying dimension attributes in detail.

We continued our exploration of Attribute Member Keys. First, we re-examined the three Attribute usage types (which we initially discussed in Attribute Member Keys – Pt I: Introduction and Simple Keys) that we can define within a containing dimension. We then narrowed our focus to the Key attribute usage type (a focus that we developed throughout Parts I and II of this article), discussing its role in meeting our business intelligence needs. We next followed with a discussion of the role of the Key attribute from a technical perspective, including its purpose within a containing dimension within Analysis Services.

We then reviewed the concepts of simple and composite keys, narrowing our exploration in this half of the article to the latter. We discussed differences between the two key types, and why composite keys are sometimes required to uniquely identify attribute members. Finally, we reviewed the Properties associated with a composite key, based upon the examination of a representative dimension attribute, Time, 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