Attribute Member Values in Analysis Services - Page 2
November 4, 2008
Procedure: Examine Attribute Value Property Settings in Analysis Services 2005
In the practice procedures that follow, we will select and examine a representative dimension attribute within the sample cube, and then focus upon the Value property settings that define and support the selected attribute. We will perform our practice sessions within the SQL Server Business Intelligence Development Studio, from which we will examine the dimension attribute Value property within our Analysis Services database, ANSYS065_Basic AS DB.
In Dimensional Model Components: Dimensions Part I and II, and Dimensional Attributes: Introduction and Overview Parts I through V, respectively, we overviewed the properties underpinning Database and Cube dimensions, and then examined the properties supporting dimension attributes. In Attribute Member Keys Pt I: Introduction and Simple Keys, and in Attribute Member Keys Pt II: Composite Keys we focused upon those properties for a simple attribute Key and a composite attribute Key, respectively. Finally, in Attribute Member Names, we concentrated upon the underlying properties for the settings underlying the attribute member Name. Just as we did in those articles, we will examine the detailed settings for a representative attribute member here, concentrating on those settings within the context of the member Value. To access these settings for the attribute member within a representative dimension, we will need to open that dimension within the Dimension Designer first.
1. Within the Solution Explorer, right-click the Time dimension (expand the Dimensions folder as necessary).
2. Click Open on the context menu that appears, as shown in Illustration 3.
The tabs of the Dimension Designer open.
3. Click the Dimension Structure tab, if we have not already arrived there by default.
The attributes belonging to the Time dimension appear as depicted in Illustration 4.
We note that eight attributes appear within the Attributes pane. Let's get some exposure to the Value property settings associated with attribute members by examining a representative member among the attributes we see here.
Review Attribute Value Property Settings
In Dimensional Attributes: Introduction and Overview Part V, and as a part of our more detailed exploration in Attribute Member Keys Pt I: Introduction and Simple Keys, and in Attribute Member Keys Pt II: Composite Keys we discovered that, within the Source properties of every attribute member lays the Value property. Lets examine the property and the underlying ValueColumn settings for the Month Name attribute, which is supported by a composite key within the sample Analysis Services database, by taking the following steps.
1. Within the Attributes pane of the Dimension Structure tab, right-click the Month Name attribute.
2. Click Properties on the context menu that appears, as shown in Illustration 5.
The Properties pane appears for the Month Name attribute. (The Properties pane likely appeared when we selected the Month Name attribute within the Attributes pane, by default, below the Solution Explorer. It also may have been hidden, set up to float within the Studio, or to be anchored elsewhere.) The design environment can, of course, be customized in many ways to accommodate your local environment and development needs.)
3. Expand the Source group, at the bottom of the Properties pane, by clicking the + sign that appears to the immediate left of its label, if necessary, as depicted in Illustration 6.
The expanded Source properties group of the Properties pane for the Month Name dimension attribute appears as shown in Illustration 7.
Let's take a look at the Value property (and its subproperties), as relevant to the Month Name attribute, discussing the purpose of the property, and examining possible settings with which we can come into contact.
Source Property: ValueColumn
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 ValueColumn property specifies a column within the underlying data source. The ValueColumn property specifies the column containing the attribute member Value.
1. Click the box to the immediate right of the ValueColumn label, just beneath the NameColumn label, within the expanded Source properties group of the Properties pane.
We note that the setting box currently contains (none), indicating that the setting is not pointed to any column within the dimension table at present.
2. Click the selector (the downward pointing arrow) button that appears on the right edge of the ValueColumn property box.
3. Click (new), as depicted in Illustration 8.
The Object Binding dialog appears, with highlighted Column binding defaulted to the top column in the Source column list.
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.
Because the ValueColumn setting of our example dimension attribute (along with the ValueColumn settings in the other attributes of our sample environment) has been left at default of (none), we will assign a table / column combination to enable the underlying settings for the property, so as to permit our further review of each.
4. Click the MonthNumberofYear selection within the Source column list to highlight it, as shown in Illustration 9.
5. Click OK to accept our addition, and to dismiss the Object Binding dialog.
We see that the ValueColumn setting, along the bottom of the Properties pane, is now populated with DimTime.MonthNumberOfYear (UnsignedTinyInt). Unlike the attribute member Name (NameColumn setting), which is converted to a string in Analysis Services, the attribute member Value (ValueColumn setting) retains the data type of the underlying data column. Moreover, we note that the underlying settings for the property have now become enabled (a + sign has appeared alongside the ValueColumn label).
6. Expand the ValueColumn property (by clicking the + sign to the immediate left of the ValueColumn label).
7. Expand the Source properties group in the Properties pane, atop the list that appears under the ValueColumn group that we expanded in our last step, 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.
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 Value 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.
8. 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.
9. Click the downward arrow selector button, to expose the tables for selection, as partially shown in Illustration 11.
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.
10. 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.
11. Click the downward arrow selector button, to expose the types for selection, as partially shown in Illustration 13.