It's likely that you'll want to add some more attributes to your dimension, and to do so, you can simply drag columns from the tables displayed in the Data Source View pane on the right-hand side of the screen, into the Attributes pane on the left-hand side. Once again, there are a couple of important properties you'll want to set on each of your attributes once you've created them:

Building a Time dimension is really just the same as building a regular dimension, although there are a few extra properties that need to be set. Going back to the Select Creation Method step of the Dimension wizard, you'll see that several of the available options are specifically for building Time dimensions, but we still recommend that you do not use them and construct a Time dimension yourself; they're only useful for proof-of-concepts. Getting SSDT to build and populate a Time dimension table in your data source can be useful as a quick way to get started on building your own dimension. However, it's very likely that you'll want to make changes to what the wizard creates, not just because it uses a DateTime column as a primary key rather than an integer. The option to generate a Time dimension on the server—so that there is no relational data source needed—suffers from the same problems, but is even more inflexible since there's no way to customize things such as member name formats beyond the options the wizard gives you. There are a lot of resources on the Web that can help you with building your own Time dimension table, such as the one shown at http://tinyurl.com/BuildTimeDim.

The important thing to do with a Time dimension is to tell Analysis Services that it is in fact a Time dimension, so you can access some of the special functionality that Analysis Services has in this area. You can do this by setting the dimension's Type property to Time and then setting attributes' Type property to the nearest approximation of what they represent, as shown in the following screenshot. So, for a Year attribute you would set Type to Years, a Month attribute to Months, and so on.

The benefits of doing this are:

User hierarchies, the multilevel hierarchies that you create in the central Hierarchies pane in the Dimension Editor, can best be described as being something like views on top of attribute hierarchies. They allow you to take two or more attribute hierarchies and combine them into a more complex drillpath. For example, you could take your Year, Month, and Date attributes and combine them into a single hierarchy with Year, Month, and Date levels, so that the user could then easily drill from a year down to see all the months in that year and from a month down to all the dates in that month.

When Analysis Services 2005 was first released and developers started coming to grips with the concept of attribute hierarchies and user hierarchies, one question that came up quite frequently was, "Should I expose attribute hierarchies or user hierarchies to my users?". As usual, the answer is that it depends. There are pros and cons to each and you'll probably end up using a mixture of both types of hierarchy. User hierarchies are more user-friendly because drilling down is just a matter of a double-click. On the other hand, rigidly defined drillpaths might be too limiting, and users might prefer the flexibility of being able to arrange attribute hierarchies whichever way they want. Certainly there are some things you can't do with user hierarchies, such as putting different levels of the same hierarchy on different axes in a query. For you as a developer, user hierarchies have some benefits. They make writing the MDX for certain types of calculation easier (members in a multilevel user hierarchy have meaningful "parents" and "children"), and "natural" user hierarchies, which we'll talk about next, are materialized on disk and so offer query performance benefits and also make the aggregation design process much easier.

If you do create a user hierarchy, you should definitely set the Visible property of its constituent attribute hierarchies to False. If you have too many hierarchies available for a user to choose from in a dimension, you run the risk of confusing the user, and having the same members appear more than once in attribute hierarchies and user hierarchies will make matters worse. Simplicity is a virtue when it comes to dimension design.

Attribute relationships allow you to model one-to-many relationships between attributes. For example, you might have Year, Month, and Date attributes on your Time dimension, and you know that there are one-to-many relationships between Year and Month, and Month and Date, so you should build the attribute relationships between these three attributes to reflect this. Why? The short answer is performance. Setting attribute relationships optimally can make a very big difference to query performance, and the importance of this cannot be overstated—they drastically improve Analysis Services' ability to build efficient indexes and make use of aggregations. However, attribute relationships do not have anything to do with how the dimension gets displayed to the end user, so don't get them confused with user hierarchies.

It's very likely that there will be many different ways to model attribute relationships between the same attributes in a dimension, so the important thing is to find the best way of modeling the relationships from a performance point of view. When you first build a dimension in SSDT, you will get a set of attributes relationships that while correct, are not necessarily optimal. This default set of relationships can be described as looking a bit like a bush. Every non-key attribute has a relationship defined either with the key attribute, or with the attribute built on the primary key of the source table in a snowflaked dimension. You can visualize these relationships on the Attribute Relationships tab of the Dimension Editor, as shown in the following screenshot:

If you have BIDS Helper installed, right-clicking on the dimension in the Solution Explorer window and selecting Visualize Attribute Lattice will display something like what's shown in the following screenshot. The built-in functionality, in our opinion, sacrifices clarity in favor of saving space, and the BIDS Helper visualization is easier to understand. BIDS Helper displays each attribute as a separate node in its relationship diagram, whereas on the Attribute Relationships tab, multiple attributes are sometimes displayed in the same node.

You can see that in our example using Year, Quarter, Month, and Date attributes on a Time dimension, Analysis Services knows that a year is made up of many dates, a quarter is made up of many dates, and a month is made up of many dates, but not that years are made up of quarters or that quarters are made up of months. Changing the attribute relationships to reflect these facts gives you something resembling a long chain, and in general the more long chains you see in your attribute relationships the better. As Analysis Services understands transitory relationships, there's no need to define a relationship between Year and Date, for example, because it can see that there is an indirect relationship via Quarter and Month. In fact, defining such redundant relationships can be a bad thing from a performance point of view.

Here's what the optimized set of relationships looks like in the Dimension Editor:

Here's what the same set of relationships looks like in BIDS Helper:

You will also notice a change in how some user hierarchies are displayed once you've optimized your attribute relationships in this way. User hierarchies that have one-to-many relationships defined between the attributes that make up each level are called natural user hierarchies. User hierarchies that don't are called unnatural user hierarchies and display an amber warning triangle in their top left-hand corner. This warning triangle does not signify that the hierarchy is broken or incorrectly configured, just that you should check your attribute relationships to see whether they can be optimized. You may actually want to build an unnatural user hierarchy, and it may not be possible to set up the attribute relationships to make it into a natural user hierarchy, and so long as you are aware that unnatural user hierarchies may not perform as well as natural user hierarchies, you should not be put off by the warning.

It is very important to understand your data before you set up any attribute relationships, because if you set them up incorrectly, then it could result in Analysis Services returning incorrect data. The Dimension Health Check functionality in BIDS Helper, available when you right-click on a dimension in the Solution Explorer, checks whether the attribute relationships you've defined actually reflect the data in your dimension table. Here's an example of a common mistake that is made: your Year attribute has members 2001, 2002, 2003, and 2004; your Quarter attribute has four members from Quarter 1 to Quarter 4. Is there a one-to-many relationship between Year and Quarter? The answer is in fact no, despite what common sense tells you, because, for example, a quarter called Quarter 1 appears in every Year. What you can and should do here is modify your data so that you can build an attribute relationship: a quarter called Quarter 1 2001 only exists in the year 2001. There are two ways to do this:

You can read a more detailed discussion of these techniques at the end of the following article: http://tinyurl.com/attributerelationships. You should not make changes like this at the expense of the users' requirements, but in most cases they will not mind minor modifications such as these and will appreciate the increased query performance.

The RelationshipType property of an attribute relationship indicates whether the relationship between any two members on two related attributes is ever likely to change or not. For example, you would hope that the date January 1st 2001 would always appear under the month January 2001 in your dimension. If it moved to the month March 2002, something would be very wrong with your source data. In this case, you should set the RelationshipType property of the Month-Date attribute relationship to Rigid. On the other hand, it may be true that the relationship between a Customer attribute and a City attribute may change over time if a customer changes his/her residence. In that case, the RelationshipType property should be set to Flexible. Setting RelationshipType to Rigid where possible increases the chances that aggregations will not need to be rebuilt when a dimension undergoes a ProcessUpdate, something that will be discussed in more detail in Chapter 10, Going in Production. Our advice is never to set the RelationshipType property to Rigid unless you are absolutely sure that no UPDATE or DELETE statement is ever run on the underlying dimension table. If a rigid relationship does change, dimension processing will fail.

Finally, attribute relationships also have a second function, and that is to act as member properties. If a Month has many dates, you can think of the relationship in reverse as a Date having a property which is its month. A better example might be a Customer having an Address, and if the Address attribute has its AttributeHierarchyEnabled property set to False then it may only be visible as a property of Customer. Every attribute relationship is visible by default as a member property in most client tools. If you want to hide the member property, you can set the relationship's Visible property to False.