Chapter 11 - Hierarchies and Relationships

Chapter 11 –Hierarchies and Relationships

Chapter 11 - Hierarchies and relationships

11.1Dimension Hierarchies Explained

Our Example

For most of this chapter, we will assume that we have a Data Source View using a star schema:

Default Primary Key Attribute Hierarchy

Whenever you create a dimension, as explained previously Analysis Services automatically builds a hierarchy by the primary key attribute:

Natural Hierarchies

You can create a natural hierarchy from a dimension when each child has one parent:

Unnatural or User-Defined Hierarchies

In an unnatural or user-defined hierarchy, each attribute member may have lots of different parents. For example, suppose we view the transaction values by region and shopping unit size:

This is an unnatural hierarchy, because a transaction can occur in (for example) a North region shopping centre of Middling size, or a West region centre of Small size.

11.2Creating a Hierarchy

The Hierarchies Created by Default

When using the dimension wizard, you can specify as many attributes as you like. For each attribute for which you enable browsing, Analysis Services will build an attribute hierarchy:

The Hierarchy for this Worked Example

For our example, we need to create the hierarchy shown on the right.

Step 1 - Adding the Necessary Attributes

The first step towards creating a hierarchy for a dimension is to add in attributes for each level:

Step 2 - Creating the Hierarchy

There are (at least) two ways to create a hierarchy:

Step 3 - Adding Levels

You can now add all of your levels:

Step 4 - Renaming the Hierarchy and its Levels

You can right-click on levels and the hierarchy title and choose to rename them:

Step 5 - Testing your Hierarchy

You could continue to create the unnatural hierarchy shown above:

When you deploy your dimensions, you'll be able to browse them as follows:

11.3Using Hierarchies within Cubes

Once you’ve created the two hierarchies as shown on the previous page (and deployed their dimensions), you can use them within a cube:

You can also filter by hierarchies:

Hiding Dimensions (AttributeHierarchyVisible Property)

For our example, once you’ve created a brand tree hierarchy it’s unlikely you’d want to browse by either the species, animal or product separately, so you could hide these:

11.4Creating Attribute Relationships

Attribute Relationships Warning Messages for Hierarchies

When you’ve created a user-defined hierarchy, Analysis Services will warn you that you might want to create relationships between attributes:

If you have a natural hierarchy such as this one linking species, animals and products, Analysis Services will run more quickly if you create relationship between the attributes:

The Attribute Relationships Pane

This pane of the dimension designer allows you to create relationships between attributes:

Creating a Relationship by Drag and Drop

The easiest way to create a relationship is by clicking and dragging:

Creating a Relationship Manually

Another way to create a relationship is using the right mouse button:

Rigid and Flexible Relationships

Relationships that you create are flexible by default. This is what the two terms means:

Term / What it means / Our Example
Flexible / Children can move to different parents / Once you’ve allocated an animal to a species, is this allocation likely to change? Barring data entry mistakes, this seems unlikely, so you should make this a rigid relationship.
Rigid / Once a child belongs to one parent, it will not move to another

Here’s what the Microsoft website has to say about the point of rigid relationships:

11.5Hierarchies for a Snowflake Schema

When using a snowflake schema, virtually the only difference for hierarchies is that Analysis Services will create some attributes relationships automatically for you.

Creating our dimension

For this example, assume that we create a snowflake schema dimension on the films table:

To create this dimension:

Creating Snowflake Schema Hierarchies

The process for creating a snowflake schema hierarchy is the same as for a star schema one: