MSSQL Analysis Services - Attribute Member Names - Page 3

October 13, 2008

Source Property: NameColumn

Much like the KeyColumns property, which we examined in Attribute Member Keys – Pt I: Introduction and Simple Keys, and Attribute Member Keys – Pt II: Composite Keys, the value we select for the NameColumn property specifies a column or columns within the underlying data source. The NameColumn property specifies the column(s) containing the attribute member Name.

1.  Click the box to the immediate right of the NameColumn label, just beneath the KeyColumns label, within the expanded Source properties group of the Properties pane.

We note that the setting box currently contains “DimTime.MonthName”, indicating that the setting is pointed to a column within the dimension table that was likely designed for the purpose of populating the attribute Name.

2.  Click the selector (the downward pointing arrow) button that appears on the right edge of the NameColumn property box.

3.  Click “(new)”, as depicted in Illustration 8.

Illustration 8:  Click the Selector Button, then Select “(new)” ...
Illustration 8: Click the Selector Button, then Select “(new)” ...

The Object Binding dialog appears, with highlighted Column binding defaulted to the top column in the Source column list, TimeKey as shown in Illustration 9.

Illustration 9:  The Object Binding Dialog Appears
Illustration 9: The Object Binding Dialog Appears

As we have noted in other articles of this series, the Object Binding dialog is used throughout the Business Intelligence Development Studio to edit / add the column binding of data items associated with properties of various Analysis Services objects. The Object Binding dialog is typically made available for single column selection options, where selection is made by simply clicking the appropriate column within the Source column list.

4.  Click the Cancel button on the dialog to dismiss it without making changes to the column binding.

5.  Expand the Source properties group in the Properties pane, atop the list that appears under the NameColumn group that we expanded above, by clicking the “+” sign that appears to the immediate left of the Source label.

The TableID and ColumnID settings appear, as depicted in Illustration 10.

Illustration 10:  The Expanded Source Properties Appear
Illustration 10: The Expanded Source Properties Appear

As we can see, the first of the displayed DimTime.CalendarYear DataItem properties, Source, expands to make available the TableID and the ColumnID boxes, where we can also specify the location of the Name within the underlying database. If we click on the Source label, or on the box to its right, an ellipses (...) button becomes enabled. This affords us another access point to the Object Binding dialog we saw earlier, where we can, once again, select the Table and Column that we want.

6.  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.

7.  Click the downward arrow selector button, to expose the tables for selection, as partially shown in Illustration 11.

Illustration 11:  Source - TableID Property Value Selection Options (Partial View)
Illustration 11: 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 12.

Illustration 12:  Source - ColumnID Property Value Selection Options (Partial View)
Illustration 12: Source - ColumnID Property Value Selection Options (Partial View)

8.  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.

9.  Click the downward arrow selector button, to expose the types for selection, as partially shown in Illustration 13.

Illustration 13:  DataType Property Value Selection Options (Partial View)
Illustration 13: DataType Property Value Selection Options (Partial View)

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. Unlike the data types we observed for the attribute Key in Attribute Member Keys – Pt I: Introduction and Simple Keys, and Attribute Member Keys – Pt II: Composite Keys, however, only a text data type (or a type that can be converted to text) can be assigned for an attribute Name. We are therefore still afforded a degree of versatility between these two layers of the integrated business intelligence solution.

10.  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 “15.” (The default is 255 characters anytime we do not specify size.)

Microsoft best practices dictate we keep the following considerations in mind as we specify attribute member Names:

  • We should keep attribute member Names as short as is practical: the longer the Names, the more resources required for retrieval and storage (therefore system performance can be negatively impacted (the loading of long Names into memory can cause significant degradation with regard to system speed, and can be costly with regard to disk space).
  • We should avoid using special characters and spaces in attribute member Names. These characters and spaces can make the use of member Names cumbersome with regard to resources, ordering and so forth.

11.  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 associated downward-pointing selector button.

12.  Click the selector button, to expose the five options for NullProcessing selection, as depicted in Illustration 14.

Illustration 14:  NullProcessing Selection Options
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

13.  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.

14.  Click the ellipses (...) button, to expose the Define Collation dialog, which appears as shown in Illustration 15.

Illustration 15:  The Define Collation Dialog
Illustration 15: The Define Collation Dialog








The Network for Technology Professionals

Search:

About Internet.com

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