More Exposure to Settings and Properties in Analysis Services Attribute Relationships - Page 3
February 13, 2009
Procedure: Continue to Define Attribute Relationships and Examine Attribute Relationship Property Settings in Analysis Services 2005
In the practice procedures that follow, we will select and examine select attributes of multiple representative dimensions within the sample cube, and then focus upon the attribute relationship property settings that define and support the selected attributes. We will perform our practice sessions within the SQL Server Business Intelligence Development Studio, from which we will examine the attribute relationship property settings from 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. Just as we did for the respective subject matter objects in those articles, we will examine the detailed settings for representative attribute relationships here.
As I noted earlier, we can organize attribute hierarchies into levels within user hierarchies to provide navigation paths for users in a cube. A user hierarchy can represent a natural hierarchy, such as city, state, and country, (as we shall see in our practice session) or can simply represent a navigation path that fits a local business scenario, such as employee name, title, and department name. Moreover, as we also mentioned earlier, to the information consumer navigating a hierarchy, these two types of user hierarchies are identical.
As a part of my discussion in Introduction to Attribute Relationships in MSSQL Server Analysis Services, I stated that, with a natural hierarchy, if we define attribute relationships between the attributes that make up the levels, Analysis Services can use an aggregation from one attribute to obtain the results from a related attribute. If there are no defined relationships between attributes, Analysis Services will aggregate all non-key attributes from the key attribute.
Lets get further hands-on exposure with defining attribute relationships for the attributes in the natural user hierarchies that exist within our basic UDM. Within our practice session we will work within the following (natural) hierarchies:
Define Attribute Relationships for Attributes in the Sales Territory Hierarchy
We will continue our practice, begun in Attribute Relationships: Settings and Properties, with attribute relationships within the Sales Territory hierarchy in the steps that follow.
1. Within the Solution Explorer, right-click the Sales Territory dimension (expand the Dimensions folder as necessary).
2. Click Open on the context menu that appears.
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 Sales Territory dimension appear as shown in Illustration 3.
We note that three attributes appear within the Attributes pane. We will get further exposure to attribute relationships, by adding / examining representative relationships among the attributes we see here.
We can also see, within the Hierarchies and Levels pane, three levels in the Sales Territory user hierarchy. This hierarchy currently exists as a drill down path for information consumers, and appears as depicted in Illustration 4.
4. In the Attributes pane, expand the Sales Territory Country attribute and the Sales Territory Region attribute.
Once these attributes are expanded, we see two attribute relationships established within the Sales Territory Region attribute and no attribute relationships established within the Sales Territory Country attribute, as shown in Illustration 5.
5. Drag the Sales Territory Group attribute relationship from the Sales Territory Region attribute to the <new attribute relationship> placeholder for the Sales Territory Country attribute.
Once we have made our modification, the attribute relationships established appear as depicted in Illustration 6.
Sales Territory Group is now related to Sales Territory Country, while Sales Territory Country remains related to Sales Territory Region. The RelationshipType property for each of these relationships should be set to Flexible (their defaults) because the groupings of regions within a country might change over time and because the groupings of countries into groups might change over time, as well.
With the Sales Territory Group attribute relationship highlighted, we can observe that, in the Properties window (which appears for the highlighted Sales Territory Group attribute, by default in the right bottom corner of the design environment), the RelationshipType property for this attribute is set to Flexible. This is appropriate because the relationship between a Sales Territory Country and a Sales Territory Group may change over time.
The RelationshipType property, as we noted in Attribute Relationships: Settings and Properties, defines rules for the modification of the key value of the members of the related, dependent attribute (in our example, the Sales Territory Country is the current attribute, whereas the Sales Territory Group is the related attribute). When we define an attribute relationship, we use the RelationshipType property to specify that the relationship is one of two types: Rigid or Flexible.
In Attribute Relationships: Settings and Properties, we discussed the fact that, if we define a relationship as Rigid, Analysis Services retains aggregations when the dimension is updated. If a relationship that is defined as Rigid actually changes, Analysis Services generates an error during processing unless the dimension is fully processed. Specifying the appropriate relationships and relationship properties increases query and processing performance, as we noted in Introduction to Attribute Relationships in MSSQL Server Analysis Services and Attribute Relationships: Settings and Properties.
We note that the RelationshipType property setting in our example is set to Flexible: the key of the related, dependent attribute, and therefore the entire member of the dependent attribute, can be changed anytime. In our example above, the Sales Territory Group is dependent upon the Sales Territory Country attribute with a Flexible relationship, since the territory group can change anytime countries are moved among territory groups within the organization.
While we will not make modifications here, we also see that the Cardinality setting for the Sales Territory Group attribute relationship is set to Many. Cardinality defines the nature of the relationship of the key of related attributes (and their members) when those members are used as member properties of the current attribute (and its members). As we noted in Attribute Relationships: Settings and Properties, the Cardinality setting can have one of two possible values:
Finally, we can see that the Visible setting for the Sales Territory Group attribute relationship is set to True. The Visible setting specifies whether the related attribute is accessible, as a member property of the current member, to the information consumer. The Visible setting can have either of two possible values:
For purposes of our immediate example, we will leave the Visible property as its current setting of True.
Next, we will move to another dimensional hierarchy, where we will define further attribute relationships.