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
Within the Solution
Explorer, right-click the Time dimension (expand the Dimensions
folder as necessary).
on the context menu that appears, as shown in Illustration 3.
Illustration 3: Opening the Dimension via the Dimension Designer ...
tabs of the Dimension Designer open.
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: The Member Attributes, Time Dimension
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
Review Attribute Value Property
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
within the sample Analysis Services database, by taking the
Within the Attributes
pane of the Dimension Structure tab, right-click the Month
on the context menu that appears, as shown in Illustration 5.
Illustration 5: Select Properties from the Context Menu ...
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.)
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.
Illustration 6: Expand the Source Group in the Properties Pane
The expanded Source properties group of the Properties
pane for the Month Name dimension attribute appears as shown in Illustration 7.
Illustration 7: The Source Properties for the Month Name Dimension Attribute
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.
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.
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
note that the setting box currently contains (none), indicating that
the setting is not pointed to any column within the dimension table at present.
Click the selector
(the downward pointing arrow) button that appears on the right edge
of the ValueColumn
as depicted in Illustration 8.
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.
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.
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.
the MonthNumberofYear selection within the Source column list to highlight it, as shown in Illustration 9.
Illustration 9: The Object Binding Dialog Appears
OK to accept our addition, and to dismiss the Object Binding dialog.
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
Expand the ValueColumn
property (by clicking the + sign to the immediate left of the ValueColumn label).
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
as depicted in Illustration 10.
Illustration 10: The Expanded Source Properties Appear
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
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.
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)
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)
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
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)