More Exposure to Settings and Properties in Analysis Services Attribute Relationships - Page 2

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.