Careful with that Attribute Relationships tab in SQL 2008!

A new feature in Analysis Services 2008 is the Attribute Relationships tab in the Dimension Designer. This tool is part of the Business Intelligence Development Studio (BIDS) which is really Visual Studio in disguise. In SSAS 2005, we could define attribute relationships one by one using the attribute list but there was no overall graphic to display the relationships so this was regarded as a hidden feature. Now there’s a dedicated tab with graphics so all is good – or is it?

Once you get this tab working it does show the multiple attribute relationships on a single graphic. This is very useful and it allows SSAS to efficiently store the attributes and corresponding aggregations. However, I have a major gripe: it’s counter intuitive.

Having defined a Hierarchy on the Dimension Structure tab of the Dimension Designer by dragging and dropping higher-order objects then lower-order objects to create levels, you notice a blue squiggly line under the Hierarchy name that advises you to proceed to create attribute relationships. That’s OK but then to create the attribute relationships you have to do so in reverse – lower-order objects then higher-order objects. This always catches me out but here’s the real kicker – there is no undo available.

If you make that mistake you typically don’t find out until after you have attempted a Deploy of the cube for testing. This results in errors because, dummy, you have done things in the same order not in reverse order. And a Deploy does a Save, so you need to get back to where you were on your own. And that’s even more counter-intuitive. To delete a relationship, you need to click on the relationship itself, not the object. Then to re-assimilate the attribute with the key attribute you need to do that in reverse order too – you have to drag the Key onto the orphan attributes. All this just to get back to the state before the Deploy request. Then you can attempt to create the attribute relationships in the correct (reverse) order. Yikes!

I remember demonstrating the Undo feature in a Database Design Tool called Bachman back in 1991. I was always surprised to see jaws drop and eyes light up. That single simple feature closed many deals in those days. Almost 20 years later, I am afraid we are still re-inventing the wheel.

Author: Brian Egler

Related Courses

Maintaining, Troubleshooting, and Developing Solutions with Microsoft SQL Server 2008 Reporting Services (M6236)

Implementing and Maintaining SQL Server 2008 Analysis Services and Business Intelligence Solutions (M6234)

SQL Server 2008 for Administration (M6231, M6232)

In this article

Join the Conversation