We have mentioned
in several other articles of this series that the data type options within
Analysis Services 2005 have been expanded over those of previous
versions. The DataType property allows us to convert the data types
from those applicable to the data within the underlying relational database to
different data types that we might require for the corresponding member data
within Analysis Services. As we observed for the attribute Key in Attribute
Member Keys – Pt I: Introduction and Simple Keys, and Attribute
Member Keys – Pt II: Composite Keys, and unlike the situation we noted, in Attribute
Member Names, for the attribute member Name (which can
be assigned only data types that can then be converted to the text-only data
type that Name allows), the attribute member Value can accept a
wide range of data types. We are therefore
still afforded a degree of versatility between these two layers of the
integrated business intelligence solution.
12.
Leaving the DataType property at its previously established
setting, click the DataSize label, just beneath the DataType property label, simply to rest it
there.
The
DataSize property allows us to specify (for either binary or text data) a size
(in bytes and characters, respectively). The setting we see in our example is
“0.” (The default is 255 characters anytime we do
not specify size.)
13.
Leaving the Data Size property at its previously established
setting, click the box to the immediate right of the NullProcessing
label, just beneath the DataSize property, once again to enable the associated
downward-pointing selector button.
14.
Click the
selector button, to expose the five options for NullProcessing
selection, as depicted in Illustration 14.
Illustration 14: NullProcessing Selection Options
Here
we can select a value to dictate the manner in which Analysis Services
processes null attribute member data. These values are explained in
detail in Table 1.
|
Value
|
Explanation
|
|
Preserve
|
Analysis
Services
preserves the null
value.
NOTE: This selection dictates the expenditure of additional
resources in the storage and processing of null data.
|
|
Error
|
The Analysis
Server displays an error message, because the null value is
disallowed.
|
|
UnknownMember
|
The Analysis
Server associates the null value with an unknown member
(which dictates that the value is to be treated in accordance with established
unknown member rules).
|
|
ZeroOrBlank
|
Analysis
Services
converts the null value to a blank (when the data type is a string)
or to a zero (when the data type is other than a string).
|
|
Automatic
|
The Analysis
Server selects the value based upon its determination of context.
|
Table 1: Options for NullProcessing Rule Selection
15.
Leaving the NullProcessing property at its previously established
setting, click the box to the immediate right of the Collation label,
just beneath the NullProcessing property,
this time to enable the ellipses (...) button to its right.
16.
Click the
ellipses (...) button, to expose the Define Collation dialog, which
appears as shown in Illustration 15.
Illustration 15: The Define Collation Dialog
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 Order options 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.
As we
noted in Attribute
Member Names, 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) can differ among settings. 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 Values (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, related
attribute entirely as a basis for our sort orders, if Key, Name,
or even Value is somehow unsatisfactory. For more information on this
versatile option, see my article Alternatively
Sorting Attribute Members in Analysis Services 2005.
17.
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)
18.
Leaving the
settings in the Collation Designator dialog at their previously established values, click the Cancel button
to dismiss the dialog.
19.
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.
20.
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.
21.
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
22.
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.
23.
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.
24.
Click the
selector button, to expose the four options for Trimming selection, as depicted
in Illustration 18.
Illustration 18: 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.
25.
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.
26.
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 other articles of this subseries.
27.
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 Value
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 might be used to support the selection and delivery of
enterprise data in a more focused and consumer-friendly manner, without
generating unexpected or undesirable results.
We focused, throughout our examination of attribute member
Value, 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 Values,
we discussed, at appropriate points, some of the differences between attribute Value,
and each of the Name and Key, properties. Moreover, we gave some
examples of uses for attribute member Value, as an alternative to Key
or Name. Finally, we performed a detailed review of the settings
associated with the attribute member Value property, based upon the
examination of a representative dimension attribute within our
sample UDM.
About the Series ...
This
article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to
provide hands-on application of the fundamentals of MS SQL Server Analysis
Services (“Analysis Services”), with each installment progressively
presenting features and techniques designed to meet specific real-world needs.
For more information on the series, please see my initial article, Creating Our First Cube. For the software components, samples and tools
needed to complete the hands-on portions of this article, see Usage-Based Optimization in Analysis Services 2005, another article within this
series.
»
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.
» 
Comment and Contribute
