Mental Models - Attributes in Analysis Services
I had many happy debates with a business analyst on one of my recent projects because he was bought up an an early olap product called HOLOS and he couldn't see how the dimensional model I was presenting was ever going to work. If we had been using HOLOS, and pretty well any other OLAP engine including analysis services in 2000, then he would have been absolutely correct.
The key thing that's different in SQL server Analysis Services 2005(SSAS) from any other product I have come across is the way that any field/column that is made into an attribute is essentially a dimension in its own right. If you open BIDS (BI Dev Studio) and open the product dimension in the adventure works sample project You will see this:
The left hand pane shows all of the attributes that have been used from the three product related tables (the blue boxes) . There are 5 hierarchies defined in this dimension so if we were in analysis services 2000 for example this would show up as five separate dimensions in whatever browser you were using e.g. excel, ProClarity.
What's interesting here is that all of those attributes on the left hand side are also dimensions even if they're referenced in a hierarchy. In the screen grab above, the focus is on the weight attribute so the properties on the right will relate to it. The second property from the top, AttributeHierarchyEnabled, is set to true and this means that it can be used to slice the data and so there will be a dimension called "weight". As it stands weight will have a level, All, which will then expand out to one member for each distinct weight value, so we could easily end up with a dimension that has a leaf level row for each product which is either completely irrelevant or at best not at all meaningful.
If you want to be able to get at the weight of each product then you could set AttributeHierarchyEnabled to false in which case it will behave like a member property in analysis services 2000. If you do want to make a dimension from it then you might wish to put the weights into bands e.g. less than 1 kilo, 1-5 kilos etc. This will give meaningful analysis without seriously affecting the cube.
For product this situation isn't to bad, but if you have a streeline1 attribute in a customer dimension with 6 million rows in it then that attribute will not only be of little value but when a user clicks on "all" to expand the attribute this will probably kill theirPC as all 6 million possible streeline1 values are downloaded. You couldn't do this in the old version of analysis services, as a member could only have 65,0000 children but now you can go large with your dimensions and attributes, which needs to be treated with the same caution as large quantities of burgers. However My main point here is that you may not have wished to do this at all, you may have followed the dimension wizard without realising the implications of all of those steps and checkboxes, or you may not be aware of how the users see these attributes.
As ever, in the next version things get better and analysis services 2008 introduces the blue squiqglies which will underline performance issues and let you know about best practice where red squiqqlies will indicate actual errors.
Technorati Tags: analysis services2005, analysis services 2008, dimensions, olap