dcsimg

Attribute Member Keys - Pt 1: Introduction and Simple Keys - Page 4

August 29, 2008

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.

Click the Ellipses Button to the Right of the KeyColumns Property
Illustration 9: Click the Ellipses ( “... “) Button to the Right of the KeyColumns Property

The DataItem Collection Editor appears, as depicted in Illustration 10.

The DataItem Collection Editor Appears
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.

Expand the Misc Group in the Properties Pane
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.

The Expanded Misc Properties Appear
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.

Source - TableID Property Value Selection Options
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.

Source - ColumnID Property Value Selection Options
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.

DataType Property Value Selection Options
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.

NullProcessing Selection Options
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.

The DefineCollation Dialog
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.

Available Collation Options
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.

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

Trimming Property Value Selection Options
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.

Single Row, Representing Simple Key, within the Members Pane
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.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers