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.

Let’s 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:

• Sales Territory hierarchy (Sales Territory dimension);
• Product Model Lines hierarchy (Product dimension);
• Fiscal Time and Calendar Time hierarchies (Time dimension);
• Geography hierarchy (Geography dimension).

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.

The tabs of the Dimension Designer open.

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.

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.

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:

• One (One – to – One): One, and only one, member of the current attribute is associated with each member of the related attribute. For example, if we were associating the full names of customers with a social security, or other, “unique” identifying code (this attribute does not exist in the example UDM – I am only using it as an illustration here), we would have a one – to – one relationship.
• Many (One – to – Many): A given member of a related attribute can be associated with multiple members of the current attribute. Needless to say, one – to – many relationships occur far more often in Analysis Services than one – to – one relationships.

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:

• False: The related attribute is not visible to the information consumer, and therefore cannot be used as a member property of the current attribute member;
• True: The related attribute is visible to, and can be accessed by, the information consumer as a member property of the current attribute member.

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.