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)
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
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.
|
Value
|
Explanation
|
|
Preserve
|
Analysis
Services
preserves (e.g.,
does not change) invalid characters.
|
|
Remove
|
Analysis
Services
removes invalid
characters.
|
|
Replace
|
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
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.
Conclusion
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.
» 