THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

Katmai June CTP - Attribute Relationships tab

As everybody knows by now, the first widely public CTP of Katmai has been released on Microsoft connect site. It is accompanied by documentation about what are the new features expected in SQL 2008. But this documentation is a little bit too high level, and also it doesn't tell which piece of the planned functionality is included in the June CTP and which one isn't. I am going to cover one feature for the Analysis Services. Since none of the interesting OLAP engine features and performance improvements are included in this release, I will talk about UI part - specifically about new tab in the dimension editor called "Attribute Relationships". It has been explained many times and on many occasions that attribute relationships are very important both for the performance of the system and for the correctness of the results. Yet, in the Business Intelligence Development Studio (aka BIDS) the UI for defining attribute relationships was not optimal (drag and drop in the tree view), and the visual representation of the relationship lattice was missing. The BIDSHelper project by Greg Galloway and Darren Gosbell somewhat filled the gap for the visualization in SSAS 2005 with the Visualize Attribute Lattice addin. In Katmai, SSAS team rightfully decided to give the subject full attention by dedicating special tab in the dimension editor. I decided to test how well this UI can handle less trivial scenarios. As a test case I have chosen simplified version of the Time dimension. The structure that I wanted was the one outlined in my blog about calculations in the Time dimension - on the picture on the below.


First, I have built this dimension in SSAS2005, and used BIDSHelper addin to visualize it. The result is shown below:

It looks similar to the picture above. The edges between vertices do not have arrows, but the convention is that arrow always points upwards. The biggest problem he is that edges of the graph cross themselves. While it is not always possible, the graph for this dimension is a planar graph. If only the vertex for Year attribute could have moved to the far right side. The code in BIDSHelper doesn't even attempt to do it though, so we are somewhat lucky that there is only one vertex at the wrong place, more lines could've crossed themselves. But otherwise, the picture is pretty clear. Now, let's move the same project to SSAS2008 and open the same dimension in the Attribute Relationship tab.

We notice few things here. First, the edges still cross themselves, even after pressing 'Auto-Arrange' button. But it is better then with BIDSHelper, because here we only need to swap Year and Quarter of Year attributes. Since this is read/write environment, the boxes representing attributes can be moved around the screen. It was not obvious to me how to do it, because clicking inside the box invoked drag-and-drop functionality. But clicking precisely on the border of the boxes change mouse cursor and switches to the move mode. Next thing to notice is the fact that there is something strange about Day of Month attribute. While all other attributes got their own boxes, this one didn't. It shows inside the box of Day of Quarter attribute. I talked to the lead PM and dev lead for this feature, and they explained the rules of when attribute gets its box and when it doesn't. I thought that the rules were unnecessary complex and definitely not intuitive, since Year and Day of Month are really symmetric with respect to attribute relationships, yet Year got its box and Day of Month didn't. Next thing to notice is that some edges are blue and have a warning sign on them. The warning warns about diamond shape relationships which don't have hierarchies going across such relationships. It is a limitation of SSAS2005, which hopefully will be eliminated in SSAS2008, but so far it is still there. It is really almost a bug, that attribute decoding won't work correctly for the attributes which have more than one incoming relationship unless there are hierarchies across these relationships. It is possible to turn off this warning by going to Project pane, right click on database -> Edit Database -> Warnings -> Dimension Design -> uncheck "Avoid diamond-shape relationships not covered by hierarchies". Not sure why turning off warnings is under Edit database, but there it is. And last thing that I noticed was that the key attribute, which is always a tree of the graph, is positioned at the left upper corner, and the graph grows to right and down. I thought that the BIDSHelper's layout, which is from bottom up, and with root in the middle looked better, but this is probably matter of taste. Anyway, had the root been positioned in the middle of the screen, the graph would've looked more symmetric. After manually moving the shapes, here is how the result looks. If it could do this by default, I would be happy :)



Published Thursday, June 7, 2007 8:41 PM by mosha
Filed under:
Anonymous comments are disabled
Privacy Statement