Source Property: KeyColumns
The value we select for the KeyColumns property
specifies a column or
columns within the underlying data source. The KeyColumns property specifies the column(s)
containing the member key(s).
1.
Click the box
to the immediate right of the KeyColumns label, just beneath the expanded CustomRollupPropertiesColumn label, within the expanded Source properties
group of the Properties pane.
2.
Click the
ellipses (“.... “) button that appears on the right edge of
the KeyColumns
property box, as shown in Illustration 9.
Illustration 9: Click
the Ellipses ( “... “) Button to the Right of the KeyColumns Property
The DataItem Collection Editor appears, as depicted in Illustration 10.
Illustration 10: The
DataItem Collection Editor Appears
The
DataItem
Collection Editor is used throughout the Business Intelligence Development
Studio to edit the
collection of data items associated with the KeyColumns property of
various Analysis Services objects. The Members pane on the left
side of the dialog lists the data items contained by the collection. Here, we
can add or remove data items to the Members pane, as well as move the
items up or down as appropriate to meet our business requirements.
3.
Expand the Misc
group in the Properties pane (right half of the Editor) by
clicking the “+” sign that appears to the immediate left of the Misc label, as shown in Illustration
11.
Illustration 11: Expand
the Misc Group in the Properties Pane
4.
Expand the Source
properties group in the Properties pane, atop the list that appears
under the newly expanded Misc group, by clicking the “+” sign
that appears to the immediate left of the Source label.
The Properties pane displays a list of properties available
for the data item that is selected within the Members pane (left half of the Editor), as depicted in Illustration 12.
Illustration 12: The
Expanded Misc Properties Appear
As
we can easily see, the first of the displayed DimGeography.GeographyKey DataItem
properties, Source, expands to make available the TableID and the
ColumnID boxes, where we specify the location of the key within
the underlying database.
5.
Click the box
to the immediate right of the TableID label, just beneath the expanded Source
group label, to enable the downward-pointing selector button.
6.
Click the
downward arrow selector button, to expose the tables for
selection, as partially shown in Illustration 13.
Illustration 13: Source
- TableID Property Value Selection Options (Partial View)
Once
we have selected the TableID, we can select from a context-sensitive list
of columns via the ColumnID selector immediately underneath the TableID
selector, as partially depicted in Illustration 14.
Illustration 14: Source
- ColumnID Property Value Selection Options (Partial View)
7.
Leaving both Source
“subproperties” at their previously established settings, click the box to the
immediate right of the DataType label, just beneath the expanded Source
– ColumnID property, once again to enable the downward-pointing selector
button.
8.
Click the
downward arrow selector button, to expose the types for
selection, as partially shown in Illustration 15.
Illustration 15: DataType
Property Value Selection Options (Partial View)
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. We are thus
afforded yet another element of versatility between these two layers of the
integrated business intelligence solution.
9.
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 default is 255 characters
anytime we do not specify size.
10.
Leaving the DataSize 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
downward-pointing selector button.
11.
Click the
downward arrow selector button, to expose the five options for NullProcessing
selection, as depicted in Illustration 16.
Illustration 16: NullProcessing
Selection Options (Partial View)
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
12.
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.
13.
Click the
ellipses (...) button, to expose the Define Collation dialog, which
appears as shown in Illustration 17.
Illustration 17: The
DefineCollation 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, we often
use it to determine 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
depicted in Illustration 18.
Illustration 18: Available Collation
Options (Partial View)
15.
Leaving the
settings Collation Designator
dialog at their previously established settings,
click the OK button to dismiss the dialog.
16.
Leaving the Collation property at its previously established
setting, click the Format label, just beneath the Collation property label, simply to rest it
there.
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 shown in Illustration 19.
Illustration 19: 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.
|
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
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
downward arrow selector button, to expose the four options for Trimming
selection, as depicted in Illustration 20.
Illustration 20: 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 20 above, the options are self-explanatory.
Finally,
in the case of a simple
key, such as the Geography Key upon which we are focusing within our
practice session, we have a single entry within the Members pane on the left
side of the DataItem
Collection Editor. The
single row is numbered “0,” as shown in Illustration 21.
Illustration 21: Single
Row, Representing Simple Key, within the Members Pane ...
We
will explore this setting and others for a composite key in Part II of this article.
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.
» 
Comment and Contribute
