The dimension editor in SQL Server 2008 Analysis Services (in the July CTP) is supposed to improve the development experience by making it easier to work with attribute relationships. I read a comment from Vladimir Chtepa on Mosha’s blog that no UI improvement was needed for attribute relationship design, but from my experience this person’s opinion is not the norm. At least not in my classrooms. The people who generally have the hardest time understanding the arrangement of attribute relationships in SQL Server 2005 Analysis Services were those who had experience with Analysis Services 2000 (AS2K). Compared to defining levels in AS2K, which displays the relationship between levels in a top-down approach withhigher levels on top, the attribute relationships in Analysis Services 2005 (Yukon) seem upside-down by taking a bottom-up approach. That is, you connect a “higher-level” attribute to a “lower-level” attribute. For example, in a time dimension, in AS2K, you define Year-Quarter-Month-Day in that order from top to bottom whereas in Yukon you add Month as an attribute relationship to Day, add Quarter as an attribute relationship to Month, and Year as an attribute relationship to Quarter. My students who started their Analysis Services experience with Yukon had less trouble with this concept, but still commented that it seemed backwards and required some extra thought. I think the problem is related to how people visualize hierarchical information and the Yukon UI does not lend itself well to visualization of attribute relationships particularly if there are a lot of attributes in a dimension and the related attributes are spread far apart from one another. Therefore, I think the new attribute relationship designer will help a lot.
Before I started working with the new designer, I created a dimension from the snowflaked tables – DimProduct, DimProductSubcategory, and DimProductCategory. I set EnglishProductName as the Name column for the key attribute. I also selected the following columns: ProductAlternateKey (with enable browsing cleared), Color, ModelName, ProductLine, Size, ProductSubcategoryKey, and ProductCategoryKey. I renamed attributes as follows: Product Key to Product, Product Subcategory Key to Subcategory, and Product Category Key to Category. Then I set the Name column for Subcategory to EnglishProductSubcategoryName and for Category to EnglishProductCategoryName.
Next I wanted to see what the attribute relationship designer looks like before I start adding user-defined hieararchies. The designer has a diagram pane and an attribute relationships pane which containts a list of attributes and a list of attribute relationships. If I click on an attribute in the list, the attribute it’s related to in the diagram is highlighted with a bold outline. In the list of attribute relationships, I can also see how each attribute is related to either the Product or Subcategory attributes (each is the key attribute of a table in the snowflake structure) as I would expect.
After setting up hierarchies like this:
The attribute relationship diagram designer looks like this:
Nothing has changed in terms of the actual attribute relationships, but the levels of each hierarchy are represented. The correct attribute relationships are defined for the Products hierarchy because of the foreign key relationships defined between the tables in the snowflake. However, Product Line and Model Name come from the same table, and therefore by default are related only to the key attribute, Product. I need to fix this. In fact, note the warning in the picture above which lets me know there are no attribute relationships defined between the levels of the Models hierarchy. I can improve query performance by creating the attribute relationship. To do this, I click on the “lower-level” attribute Model Name, and drag it on top of the “higher-level” attribute Product Line. This sets up the attribute relationship properly, as shown here:
Pretty easy and pretty clear graphically. I can see exactly how each level in the hierarchy relates back to the dimension’s key attribute – and if I click Expand All in the designer’s toolbar, I can see all other attributes that are related to Product as well.
There may be cases when an attribute that isn’t in a hierarchy is not related to the key attribute. An example is in the Time dimension in which I have EnglishMonthName and MonthNumberOfYear. This is not a hierarchical-relationship as I showed in the previous example, but a member property relationship that I can use for sorting purposes. To get the behavior I want for sorting, I need to define an attribute relationship without the hierarchy. In the Time dimension designer, the MonthNumberOfYear is related by default to the Time Key. In the diagram, I expand the Time Key object, then click English Month Name, and drag it onto Month Number of Year inside the Time Key object. The relationship is adjusted like this:
As a side note, because the cardinality in this relationship is one-to-one, I need to change the Cardinality property in the property pane from Many (the default) to One. Now that the relationship exists, I can set the OrderBy property on EnglishMonthName to AttributeKey and the OrderByAttribute property to Month Number Of Year.
While I don’t think moving the interface to another tab really simplifies my work, or enables me to build relationships faster in terms of mouse clicks or keystrokes, I do feel like the movements to set up the attribute relationships are more intuitive. The part I like best about the attribute relationship diagram is the visualization of flexible and rigid relationships. Best practice design says relationships should be rigid when members aren’t shifting around. So, for example, March is always part of calendar quarter 1 and will never, ever be part of calendar quarter 4. Therefore, the relationship between month and calendar quarter should be rigid. Now, as I mentioned in my previous post I think rigid should be the default, but it’s not, so I have to deal with it here. I simply right-click on the arrow representing the attribute relationship, point to Relationship Type, and select Rigid. Notice how the diagram reflects the Rigid relationship with the black arrow tip as compared to the Flexibile relationship with the white arrow tip:
I like this feature of the attribute relationship designer best because one of the first things I do when cleaning up someone else’s cube is to fix up the relationship types. In Yukon, I would have to look at each relationship individually whereas the diagram here allows me to see at a glance whether I need to fix anything. Currently, there are no warnings to draw my attention to potential problems in the dimension when some attributes are defined with flexible relationships and two with a rigid relationship. –Stacia