13.
Click the
downward arrow selector button that appears to the immediate
right of the NameColumn label, to expose the three basic
options for selection, as shown in Illustration 15.
Illustration 15: NameColumn
Property Value Selection Options
The
three selection options that are available are “None,” and “New,”
and a preselected column, such as the one we see in our example attribute, “DimGeography.GeographyKey”
(simply a notation of the Table / Column).
14.
Select the “New”
option within the selector.
The Object
Binding dialog appears, once again. As
we mentioned earlier, we use the Object Binding dialog box in Business
Intelligence Development Studio to define bindings between the property of
an Analysis Services object and a table or column in a data source
view. As we have seen, selecting (new) from the drop-down list for
this and several other property values (such as the CustomRollupColumn, CustomRollupPropertiesColumn
and UnaryOperatorColumn, as we saw within an earlier segment of this
multi-part article)
calls the Object Binding dialog box.
As
we have noted, we can use the Object Binding dialog to select Binding type, Source table
and Source column as appropriate to our needs, and then save our
changes. Because we will not be making changes to these property settings
in our practice example, we will simply dismiss the dialog via the Cancel
button, as we have done earlier.
15.
Click the Cancel
button at the bottom of the Object Binding dialog box to dismiss the dialog.
We are
returned to the Properties pane, where we will examine the settings that
we can see within the expanded NameColumn property.
Source
Property:
NameColumn – Source
The value
we select for the Source property specifies, in a manner similar to the KeyColumns
property we examined in Dimension Attributes: Introduction and
Overview, Part IV,
a column or columns
within the underlying data source. The KeyColumns property specifies the column(s) containing the member
key(s).
16.
Click the
ellipses (“ ... “) button that appears to the immediate
right of the Source
property label, as depicted in Illustration
16.
Illustration 16: Click
the Ellipses ( “... “) Button to the Right of the Source Property
The
Object Binding dialog again appears, where we can,
as we are now probably well aware, select Binding type, Source table
and Source column as appropriate to our needs, and then save our
changes. Because we will not be making changes to these property settings
in our practice example, we will simply dismiss the dialog via the Cancel
button, once again.
17.
Click the Cancel
button at the bottom of the Object Binding dialog box to dismiss the dialog,
as we have done earlier.
Source
Property:
NameColumn - DataType
We use
the DataType property to specify the data type
used by Analysis Services.
18.
Click the
downward arrow selector button that appears to the immediate
right of the DataType label, to expose many options for
selection, as partially shown in Illustration 17.
Illustration 17:
DataType Property Value Selection Options (Partial View)
The
fifteen standard selection options that are available (as of this writing)
include the following:
-
BigInt
-
Binary
-
Boolean
-
Currency
-
Date
-
Double
-
Integer
-
Single
-
SmallInt
-
TinyInt
-
UnsignedBigInt
-
UnsignedInt
-
UnsignedSmallInt
-
UnsignedTinyInt
-
WChar
(the value assigned our example selection)
Having
the DataType property setting available at the Analysis
Services level affords us the advantage of being able to specify a data
type here that can differ from the data type of the corresponding
column in the underlying data source.
Source
Property:
NameColumn – DataSize
The value
we select for the DataSize property specifies the data size used
by Analysis Services. Having the DataSize
property setting available at the Analysis
Services level affords us the advantage of being able to specify a data size
here that can differ from the data size of the corresponding column in
the underlying data source.
Source
Property:
NameColumn – NullProcessing
The
NullProcessing property dictates the processing of null values.
19.
Click the
downward arrow selector button that appears to the immediate right
of the NullProcessing label, to expose our options for
selection, as depicted in Illustration 18.
Illustration 18:
NullProcessing Property Value Selection Options
The
five selection options, each of which dictates what action the Analysis Server should
take when it encounters a NULL value, include the following:
-
Preserve: Server preserves the NULL value.
The server has the ability to store NULL just like any other value.
-
Error: Server generates a data
integrity error and discards the record; a NULL value is illegal in a data item
with this setting.
-
UnknownMember: Server interprets the NULL
value as the unknown member. Server will also generate a data
integrity error. This option is applicable only for attribute key
columns (such as the selection for our practice example, Geography Key).
-
ZeroOrBlank: Server converts the NULL value
to a zero (for numeric data items) or a blank string (for string data items). (This
was how NULLS were managed in Analysis Services 2000).
-
Automatic:
(Conditional Default) Implies assignment of ZeroOrBlank value for dimensions and
cubes, and UnknownMember value for mining structures and models.
Source
Property:
NameColumn – Collation
We use the Collation property
to specify the collation applied by Analysis Services. The Collation string
consists of the locale identifier (LCID) and the comparison flag, separated by
an underscore character. (For example, Latin1_General_CI_AS is an
acceptable string for the Collation property value).
The value that we provide sets the character set and case
sensitivity for the column under consideration. When collation
is not set at the column level it inherits the setting from the parent
object, usually all the way up to the default settings specified at the Analysis
Server level (typically first done when Analysis Services is
installed).
Source Property: NameColumn - Format
The Format
property affords us a means of specifying the
format string applied by Analysis Services.
Source
Property:
NameColumn – InvalidXmlCharacters
The
InvalidXmlCharacters property specifies the treatment of any characters
that exist in the data that are invalid in XML.
20.
Click the
downward arrow selector button that appears to the immediate right
of the InvalidXmlCharacters
property label,
to expose the options for selection, as shown in Illustration 19.
Illustration 19:
InvalidXMLCharacters Property Value Selection Options
The
three selection options, each of which dictates what action the Analysis Server should
take when it encounters an invalid XML character(s), include the following:
-
Preserve: (Default) Server preserves invalid
XML characters.
-
Remove: Server removes invalid XML
characters.
-
Replace: Server replaces invalid XML
characters with a question mark (“?”) character.
To enhance performance in general, Analysis Services does
not check for invalid XML characters unless we set the InvalidXmlCharacters
property. For this reason, if such characters exist, the response the
Analysis Server sends to the client may contain invalid XML.
Source
Property:
NameColumn – MimeType
The MimeType property value reflects the Multipurpose Internet Mail
Extensions (MIME)
type, if applicable, of the data represented by the parent DataItem
element. The default value is None (blank), and the value data type
is a string.
Source
Property:
NameColumn – Trimming
The value that we supply for Trimming specifies how data from the data
source is trimmed.
21.
Click the
downward arrow selector button that appears to the immediate
right of the Trimming
property label,
to expose the options for selection, as depicted in Illustration 20.
Illustration 20:
Trimming Property Value Selection Options
The
four selection options, each of which dictates what action the Analysis Server should
take when specifying the details for the trimming of strings, include the following:
-
Left: Data is trimmed on the left.
-
Right: (Default) Data is trimmed on the
right.
-
LeftRight: Data is trimmed on the left and
the right.
-
None: Data is not trimmed.
Having
examined the settings that we can see within the expanded NameColumn property, we are ready to overview
the last member of the Source property group, ValueColumn.
Source
Property:
ValueColumn
The
ValueColumn property allows us to specify the column within the
underlying data source from which Analysis Services derives the value
of the attribute. ValueColumn is similar to the NameColumn
property we discussed earlier, in that it offers a downward arrow selector
button that appears to the immediate right of the property label. We can use this selector
to choose either the None or the New option (a third
option representing a preselected column will exist if a column has already
been selected – not the case in our current example, but possibly different
elsewhere).
If
we select the New
option within the selector, the Object Binding dialog appears, just as
it does in our examination of the NameColumn property above. We can use this dialog to define bindings between the property of
an Analysis Services object and a table or column in a data source
view, via the same Binding type, Source table and Source
column settings that we used for NameColumn and other attribute properties.
If
we specified a value in the NameColumn property, then the same value is
used as the default in ValueColumn. If we did not specify a value in
the NameColumn property, and the KeyColumns collection of the attribute
contains a single KeyColumn element representing a key column with
a string data type, the same values are used as default values for
the ValueColumn element.
This
article concludes our examination of the dimension attributes properties
groups, which we began in Dimension
Attributes: Introduction and Overview, Part I.
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.
1.
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.
2.
Select File
-> Exit to leave the design environment,
when ready, and to close the Business Intelligence Development Studio.
Conclusion
In this,
the final part of a multi-part article introducing dimension attributes,
we continued our current subseries focusing upon dimensional model
components, with an objective of discussing the associated concepts, and of
providing hands-on exposure to the properties supporting each. 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 review of dimension attributes in general.
We overviewed many of the general characteristics and
purposes of attributes, including their names, and the names of the
groups within which each is classified. We then continued our focus upon the properties
underlying them, based upon the examination of a representative attribute within
our sample cube. In this article, we extended our discussion beyond the Advanced,
Basic, Misc, and Parent-Child attribute properties, which
we covered in Part II, Part III, and Part IV, and examined the attribute properties belonging to the Source group, including what they define and support, and how we
can manage them.
»
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
