More Exposure to Settings and Properties in Analysis Services Attribute Relationships

This article concludes our overview of Attribute Relationships within an extended examination of the dimensional model of the integrated Microsoft Business Intelligence solution.

This article continues the overview of Attribute Relationships in Analysis Services, which we began in Introduction to Attribute Relationships in MSSQL Server Analysis Services, and continued in Attribute Relationships: Settings and Properties. Both this article and its predecessor extend the examination of the dimensional model that we began in Dimensional Model Components: Dimensions Parts I and II. After taking up various additional components of the dimensional model in subsequent articles, we performed hands-on exploration of the general characteristics and purposes of attributes in Dimensional Attributes: Introduction and Overview Parts I through V. We then fixed our focus upon the properties underlying attributes, based upon the examination of a representative attribute within our sample cube., extending our overview into attribute member Keys, Names and Values.

This article continues the focus upon attribute relationships, which define the possible associations between attributes, including a discussion surrounding why these relationships are important, and how they define the properties of association that a given attribute has with other attributes. Our concentration here will be to continue the detailed examination of the properties underlying attribute relationships that we began in Attribute Relationships: Settings and Properties, along with a review of the respective settings associated with each property, based upon attributes of additional representative dimensions within our sample UDM.

Introduction

In Introduction to Attribute Relationships in MSSQL Server Analysis Services, I summarized the articles preceding it within the current subseries surrounding a general introduction to the dimensional model. I noted the wide acceptance of the dimensional model as the preferred structure for presenting quantitative and other organizational data to information consumers. The articles of the series then undertook an examination of dimensions, the analytical “perspectives” upon which the dimensional model relies in meeting the primary objectives of business intelligence, including its capacity to support:

• the presentation of relevant and accurate information representing business operations and events;
• the rapid and accurate return of query results;
• “slice and dice” query creation and modification;
• an environment wherein information consumers can pose questions quickly and easily, and achieve rapid results datasets.

We extended our examination of dimensions into several detailed articles. These articles are comprised of Dimensional Model Components: Dimensions Parts I and II, noting that dimensions, which represent the perspectives of a business or other operation, and reflect the intuitive ways that information consumers need to query and view data, form the foundation of the dimensional model. We noted that each dimension within our model contains one or more hierarchies. (We noted that we learn in other articles of this series, two types of hierarchies exist within Analysis Services: attribute hierarchies and user - sometimes called “multi-level” - hierarchies.)

We next introduced dimension attributes within the subseries, and conducted an extensive overview of their nature, properties, and detailed settings in Dimensional Attributes: Introduction and Overview Parts I through V. We noted that attributes help us to define with specificity what dimensions cannot define by themselves. Moreover, attributes are collected within a database dimension, where we can access them to help us to specify the coordinates required to define cube space.

Throughout the current subseries, I have emphasized that dimensions and dimension attributes should support the way that management and information consumers of a given organization describe the events and results of the business operations of the entity. Because we maintain dimension and related attribute information within the database underlying our Analysis Services implementation, we can support business intelligence for our clients and employers even when these details are not captured within the system where transaction processing takes place. Within the analysis and reporting capabilities we supply in this manner, dimensions and attributes are useful for aggregation, filtering, labeling, and other purposes.

Having covered the general characteristics and purposes of attributes in Dimensional Attributes: Introduction and Overview Parts I through V, we fixed our focus upon the properties underlying them, based upon the examination of a representative attribute within our sample cube. We then continued our extended examination of attributes to yet another important component we had touched upon earlier, the attribute member Key, with which we gained some hands-on exposure in practice sessions that followed our coverage of the concepts. In Attribute Member Keys – Pt I: Introduction and Simple Keys and Attribute Member Keys – Pt II: Composite Keys, we introduced attribute member Keys in detail, continuing our recent group of articles focusing upon dimensional model components, with an objective of discussing the associated concepts, and of providing hands-on exposure to the properties supporting them. We first discussed the three attribute usage types that we can define within a containing dimension. We then narrowed our focus to the Key attribute usage type (a focus that we developed, as we have noted, throughout Attribute Member Keys – Pt I: Introduction and Simple Keys and Attribute Member Keys – Pt II: Composite Keys), discussing its role in meeting our business intelligence needs. We next followed with a discussion of the nature and uses of the attribute Key from a technical perspective, including its purpose within a containing dimension within Analysis Services.

In Attribute Member Keys – Pt I: Introduction and Simple Keys and Attribute Member Keys – Pt II: Composite Keys, we introduced the concepts of simple and composite keys, narrowing our exploration in Part I to the former, where we reviewed the Properties associated with a simple key, based upon the examination of a representative dimension attribute within our sample UDM. In Part II, we revisited the differences between simple and composite keys, and explained in more detail why composite keys are sometimes required to uniquely identify attribute members. We then reviewed the properties associated with a composite key, based upon the examination of another representative dimension attribute, Date within our sample UDM.

In Attribute Member Names, we examined the attribute member Name property, which we had briefly introduced in Dimensional Attributes: Introduction and Overview Part V. We examined the details of the attribute member Name, and shed some light on how they might most appropriately be used without degrading system performance or creating other unexpected or undesirable results. Finally, we examined the “sister” attribute member Value property (which we introduced along with attribute member Name in Dimensional Attributes: Introduction and Overview Part V) in Attribute Member Values in Analysis Services. As we did in our overview of attribute member Name, we examined the details of Value. Our concentration was also similarly upon its appropriate use in providing support for the selection and delivery of enterprise data in a more focused and consumer-friendly manner, without the unwanted effects of system performance degradation, and other unexpected or undesirable results, that can accompany the uninformed use of the property.

In Introduction to Attribute Relationships in MSSQL Server Analysis Services, we introduced another part of the conceptual model, Attribute Relationships. In this overview, we discussed several best practices and design, and other, considerations involved in the use of attribute relationships. Our focus was upon the general exploitation of attribute relationships in providing support for the selection and delivery of enterprise data. Finally, we continued our exploration of attribute relationships in our previous article, Attribute Relationships: Settings and Properties, where we examined attribute relationships in a more detailed manner, similar to previous articles within this subseries. We performed a detailed examination of the properties underlying attribute relationships, along with a review of the respective settings associated with each property. As a part of our exposure to the attribute relationships within a representative dimension of our sample UDM, we obtained hands-on practice in creating, modifying and deleting attribute relationships within several dimension attributes.

We will continue our exploration of attribute relationships in this article, where we will examine the attribute relationships of additional representative dimensions in a detailed manner, similar to the procedures we undertook in Attribute Relationships: Settings and Properties. We will again concentrate in detail upon the properties and settings that underlie these relationships.

Our continuing examination will include:

• A review of the nature of the attribute relationship, and its possible roles in helping to meet the primary objectives of business intelligence, based upon and extending the discussion we initiated in Introduction to Attribute Relationships in MSSQL Server Analysis Services.
• A continued examination of the properties underlying attribute relationships, along with a review of the respective settings associated with each property, based upon the attributes of a representative dimension within our sample UDM.
• Hands-on practice in creating and modifying attribute relationships within the attributes of several additional dimensions within our sample UDM.

Attribute Relationships: More Properties and Settings

As we have learned, attributes serve as the foundation for our dimensions and cubes. Moreover, in Analysis Services 2005, attributes within a dimension are always related, either directly or indirectly, to the key attribute. Assuming the definition of a dimension based upon a star schema, where all dimension attributes are derived from the same relational table, an attribute relationship is automatically defined between the key attribute and each non-key attribute of the dimension. Alternatively, if we assume the definition of a dimension based upon a snowflake schema, where dimension attributes are derived from multiple related tables, Analysis Services automatically defines an attribute relationship in the following manner:

• Between the key attribute and each non-key attribute associated with columns in the main dimension table;
• Between the key attribute and the attribute associated with the foreign key in the secondary table that links the underlying dimension tables;
• Between the attribute associated with the foreign key in the secondary table and each non-key attribute associated with columns from the secondary table.

As we noted in Introduction to Attribute Relationships in MSSQL Server Analysis Services, there are a number of reasons to change the assigned default attribute relationships. For example, you might want to define a natural hierarchy, a custom sort order, or dimension granularity based on a non-key attribute (we focus upon these activities in other articles of this series). We might also want to performance tune the default relationships to optimize processing in general.

Relationships representing natural hierarchies are enforced by creating an attribute relationship between the attribute for a level and the attribute for the level below it. For Analysis Services, this specifies a natural relationship and potential aggregation. For example, as we saw in Attribute Relationships: Settings and Properties, in the Customer dimension of the sample Adventure Works UDM, a natural hierarchy exists for the Country, State-Province, City, Postal Code, and Customer attributes. The natural hierarchy for {Country, State-Province, City, Postal Code, Customer} was established through the addition of the following attribute relationships:

• The Country attribute as an attribute relationship to the State-Province attribute;
• The State-Province attribute as an attribute relationship to the City attribute;
• The City attribute as an attribute relationship to the Postal Code attribute.

We will establish additional relationships within the UDM as part of the practice session that follows.

As we have noted in other articles of this series, we can also create a user-defined hierarchy that does not represent a natural hierarchy in the data (which is called an ad hoc or reporting hierarchy), for purposes of navigating data in the cube. For example, we could create a user-defined hierarchy based on Customer {Education, Gender}. Information consumers of the data would see no difference in how the two hierarchies behave, although the natural hierarchy benefits from aggregating and indexing structures — invisible to the consumer — that account for the natural relationships in the source data.

The attribute relationship, as we have learned, defines the possible associations that exist between attributes within a given dimension. In doing so, the attribute relationship affects virtually all functions of Analysis Services. The attribute relationship defines the properties of association that exist (including whether another attribute can be accessed via the given attribute) between a given attribute and another attribute. (A given attribute is treated as a member property of the “current” attribute when it can be accessed via the “current” attribute.)

We will gain hands - on exposure to attribute relationship properties and settings in the practice session that follows. Before we get started working within a sample cube clone, we will need to prepare the local environment for the practice session. We will take steps to accomplish this within the section that follows.

Preparation: Locate and Open the Sample Basic UDM Created Earlier

In Dimensional Model Components: Dimensions Part I, we created a sample basic Analysis Services database within which to perform the steps of the practice sessions we set out to undertake in the various articles of this subseries. Once we had ascertained that the new practice database appeared to be in place, and once we had renamed it to ANSYS065_Basic AS DB, we began our examination of dimension properties. We continued with our examination of attributes within the same practice environment, which we will now access (as we did within the earlier articles of this subseries) by taking the following steps within the SQL Server Business Intelligence Development Studio.

We briefly see a splash page that lists the components installed on the PC, and then Visual Studio .NET 2005 opens at the Start page.

The Connect to Database dialog appears.

SQL Server Business Intelligence Development Studio briefly reads the database from the Analysis Server, and then we see the Solution Explorer populated with the database objects. Having overviewed the properties of dimension attributes in previous articles, we will continue to get some hands-on exposure to attribute relationships – this time for attribute members of multiple representative dimensions within our practice UDM.

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.

Define Attribute Relationships for Attributes in the Product Model Lines Hierarchy

We will next continue our practice with attribute relationships within the Product Model Lines hierarchy of the Product dimension.

The tabs of the Dimension Designer open.

The attributes belonging to the Product dimension appear as shown in Illustration 7.

We note that eighteen attributes appear within the Attributes pane. We will gain 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 Product Model Lines user hierarchy. This hierarchy currently exists, again, simply as a drill down path for information consumers, and appears as depicted in Illustration 8.

Once these attributes are expanded, we see seventeen attribute relationships established within the Product Name attribute and no attribute relationships established within the Model Name attribute, as shown in Illustration 9.

Once we have made our modification, the established attribute relationships appear as depicted in Illustration 10.

Product Line is now related to Model Name. The RelationshipType property for this relationship should be set to Flexible (the default) because the relationship between a product line and a model name might change over time.

Next, we will move to the Fiscal Time hierarchy, where we will define further attribute relationships.

Define Attribute Relationships for Attributes in the Fiscal Time Hierarchy

We will next continue our practice with attribute relationships within the Fiscal Time hierarchy of the Time dimension.

The tabs of the Dimension Designer open.

The attributes belonging to the Time dimension appear as shown in Illustration 11.

We note that eight attributes appear within the Attributes pane. We will gain 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, two hierarchies, named Calendar Time and Fiscal Time, as depicted in Illustration 12.

We will be concerned with the Fiscal Time hierarchy in this section (we focus on the Calendar Time hierarchy in our next section). There are five levels in the Fiscal Time user hierarchy. This hierarchy currently exists, once again, as a drill down path for information consumers.

Once these attributes are expanded, we see seven attribute relationships established within the Date attribute and no attribute relationships established within the Fiscal Quarter, Fiscal Semester, and Month Name attributes, as shown in Illustration 13.

Once we have made our modifications, the attribute relationships established appear as shown in Illustration 15.

Fiscal Quarter is now related to Month Name, Fiscal Semester is now related to Fiscal Quarter, and Fiscal Year is now related to Fiscal Semester. The Rigid RelationshipType property is appropriate for these attribute relationships, because they will not change over time.

Next, we will move to the Calendar Time hierarchy, where we will define a similar set of attribute relationships.

Define Attribute Relationships for Attributes in the Calendar Time Hierarchy

We will next work with attribute relationships within the Calendar Time hierarchy of the Time dimension. Because we are already within the Time dimension, we can get directly to work. There are five levels in the Calendar Time user hierarchy, as depicted, once again in Illustration 16.

Once these attributes are expanded, we see four attribute relationships remaining within the Date attribute no attribute relationships established within the Calendar Quarter and Calendar Semester attributes, and one attribute relationships established within the Month Name attribute, as shown in Illustration 17.

Once we have made our modifications, the attribute relationships established appear as depicted in Illustration 18.

Calendar Quarter is now related to Month Name, Calendar Semester is now related to Calendar Quarter, and Calendar Year is now related to Calendar Semester. The Rigid RelationshipType property is appropriate for these attribute relationships, because, once again, they will not change over time.

Finally, we will move to the Geography hierarchy, where we will a final set of attribute relationships.

Define Attribute Relationships for Attributes in the Geography Hierarchy

We will conclude our practice with attribute relationships within the Geography hierarchy of the Geography dimension.

The tabs of the Dimension Designer open.

The attributes belonging to the Geography dimension appear as shown in Illustration 19.

We note that five attributes appear within the Attributes pane. We will gain 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, four levels in the Geography user hierarchy. This hierarchy exists, like the others we have examined in this practice session, as a drill down path for information consumers, and appears as depicted in Illustration 20.

Once these attributes are expanded, we see four attribute relationships currently established within the Geography Key attribute, with no attribute relationships established within the City, Postal Code and State Province attributes, as shown in Illustration 21.

Because postal codes within a city have been known to change over time, the appropriate value for the RelationshipType property for the City attribute relationship is Flexible, which is the default setting that we can see in the associated Properties window.

The Rigid RelationshipType property is appropriate for the State-Province attribute relationship because the relationship between a given city and the state within which it is physically located will not change – at least not in a foreseeable way.

The Rigid RelationshipType property is appropriate for the Country-Region attribute relationship because the relationship between a given state and the country within which it is physically located will not change – again, at least not in a foreseeable way.

Once we have made our modifications, the attribute relationships established within the Geography dimension appear as depicted in Illustration 22.

Postal Code remains related to Geography Key, City is now related to Postal Code, State-Province is now related to City, and Country-Region is now related to State-Province. We have established the RelationshipType property as Rigid for all except Postal Code and City, among these attribute relationships, as well.

This will conclude our work with attribute relationships. We encounter these important relationships within other articles of my Introduction to MSSQL Server Analysis Services column, where we make both typical and special settings to meet specific business needs within the context of the focus of each article.

Processing begins, and we see completion of the various steps via the Process Progress viewer. We see a “Process Succeeded” message in the Status bar at the bottom of the viewer, once processing is complete.

Conclusion

In this article, we continued our exploration of attribute relationships, stating that our objective would be to complement the introduction we undertook in Introduction to Attribute Relationships in MSSQL Server Analysis Services and the practice session we began in Attribute Relationships: Settings and Properties, through a continued detailed examination of attribute relationships. Our concentration upon these details was enhanced by our continuing hands-on practice session, where we once again gained exposure to the properties and settings that underlie attribute relationships.

Our examination included a review of the nature of the attribute relationship in Analysis Services, and its possible roles in helping to meet the primary objectives of business intelligence, based upon and extending the discussion we initiated in Introduction to Attribute Relationships in MSSQL Server Analysis Services, and continued in Attribute Relationships: Settings and Properties. We performed more detailed examination of the properties underlying attribute relationships, along with a review of the respective settings associated with each property, based upon additional representative dimension attributes within our sample UDM, as a part of our practice session. Throughout our practice procedures we obtained hands-on exposure to creating and modifying attribute relationships within several representative dimensions within our sample UDM.

About the MSSQL Server Analysis Services Series

This article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server Analysis Services (“Analysis Services”), with each installment progressively presenting features and techniques designed to meet specific real-world needs. For more information on the series, please see my initial article, Creating Our First Cube. For the software components, samples and tools needed to complete the hands-on portions of this article, see Usage-Based Optimization in Analysis Services 2005, another article within this series.

» See All Articles by Columnist William E. Pearson, III