November 5, 2008 at 1:01 pm
I get the warning message "Attribute relationships do not exist between one or more levels in this hierachy..." in the dimension editor for every hierachy in every cube I've ever created - whether my own design or a book sample. Even on hierachies created by the wizards.
Is this just a bug in Visual Studio 2005 (Business Intelligence Development Studio)?
November 6, 2008 at 6:06 am
No, it is not a bug.
If you have auto-generated dimensions, you do not get attribute relationships defined and you will probably find that if you expand the attribute relationships under the key value for the dimension that you have everything at a single level.
This is a cube design no-no. The attribute relationships are the most important query and aggregation processing performance consideration and need to be handled correctly.
If you do not know anything about the attribute relationships, I suggest you get a book on SSAS. I also recommend you search for and download the "BIDS Helper" visual studio add-in because it has some nice utilities to checking and displaying attribute relationships.
November 7, 2008 at 12:13 pm
Thanks for your help. The ‘BIDS Helper’ is a great recommendation.
I already have books (and print-outs from Microsoft Books Online); unfortunately, most authors can’t see the forest for the trees. Documentation and instruction these days seems to consist only of walk through examples – the student is supposed to abstract the general case from specific examples. The art of clearly explaining concepts beginning at a high level and working to more detailed levels, and linking related concepts together seems to be lost. (If you don’t believe me, look through your own references for a clear and concise discussion of how and what aggregates are created in SSAS and how this relates to things like dimension design. Good luck!) OK, I’m off my soapbox now.
I was working with the date dimension on an initial demo project. As you predicted, every attribute of my date dimension (which has the grain of date) was relating only to the key. When I created a Hierarchy of Year -> Quarter -> Month -> Day (of month) in the Hierarchies and Levels pane , the warning message was telling me that none of attributes related to each other – they each related only to DateKey. Still, everything worked OK – I could slice measures by any combination of date attributes. A calculation I created using ParalledPeriod, to allow comparing the difference in measures during corresponding periods one year apart, worked also.
As I understand it, the consequence of this is that no aggregates would be created except at the date level. Totals for intervals such as months and years would have to be calculated on the fly. This is Not Good, but to a certain extent also Not Bad. Consider a small database that records an average of 2000 sales per day. In the default setup, SSAS would aggregate these sales by day only (Is this true? Or would it not aggregate at all?). However, to create a sum of sales for a year, SSAS would only have to add 365 numbers, not the 730,000 it would have to with no aggregations at all. This is obviously a big improvement.
Still, this is not exploiting the full power of SSAS. I found a fairly good article on correctly setting up the initial hierarchal relationships in a date dimension at: http://www.sqlmag.com/Article/ArticleID/98699/98699.html. It demonstrates setting up correct hierarchies (such as Year -> Quarter -> Month -> Day), stops to illustrate how browsing with date attributes would be completely messed up at this point, then shows how to set the KeyColumns of the attributes so that they match the hierarchy and everything works OK again.
After doing this, I could set up my new hierarchy (in the Hierarchies and Levels pane) with no warning messages and also no need to add any attribute relationships, since they were already declared in the Attributes pane earlier. Also (I assume!) SSAS should now create aggregates at other levels, such as month and year.
Interestingly, having done this, I lost the ability to answer certain questions, at least by drag and drop in the cube browser. The setup is great for showing sales by years, quarters, months, etc. But supposed I want to see sales only by the day of the month over a range of years (to see if sales activity is greater at certain times of the month)? This worked before, but now, because I set the KeyColumns for Day to Year -> Month -> Day, dragging Day to Rows in the browser just creates a repeating set of Days (1 to 28 ...31) for as many months and years as are in my query. Hmmmmm.
A quick answer to this is to create another attribute, AbsoluteDay, in the Date dimension that is an alias of Day, and only relates to DateKey. Now I can drag this to Rows in the cube browser, set the filter for a range of years, select the sales measure, and there’s my answer in 31 rows. There is probably a better way to do this, but I haven’t gotten that far yet.
Anyway, thanks again for your response. I'm in the initial learning curve and all help is appreciated.
November 7, 2008 at 12:32 pm
You can create the extra attribute hierarchies to support the "Day of Month" type of view you are talking about. I assume for your attribute hierarchy you used the Year/Month/Day as the key value for your attribute so the attribute relationships would be valid.
Create another attribute with the Key and the Name just the day of the month and relate this attribute directly to the key attribute (as it was originally). This attribute will do what you want, but the other attribute will be used in the hierarchy. Finally, make the other attribute hierarchy invisible (leave it enabled).
It is important to have attribute hierarchies even if you have a small cube. They not only allow "smarter" aggregations to be designed, but they also allow aggregations that represent rigid relationships (look this up too - it is important). Rigid aggregations don't get dropped when you update dimensions so when you are processing your cube it does less work.
There is no way to describe all of this in a couple of posts, but it sounds like you are on the right track. I have failed to find a really good book on SSAS so far. The training documentation is pretty awful. One of the best documents I have found is the Project REAL documentation from Microsoft - they did a good job.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply