SQL Server 2008: Best Practice Design Alerts

Once upon a time, there was such a thing as a talking car. I never owned one, but I did get to drive one for a week in Quebec while a colleague and I were working with a client up there back in the late 80s. Normally, we were supposed to rent a compact car when out on business, but we had to pick up a bunch of computer equipment at air cargo and there was no way our luggage and the equipment was fitting into a compact. As it turned out, the only car that accommodated us was a New Yorker (and even then it was pretty tight). We quickly discovered that the New Yorker was one of those talking cars – with a French male voice. We named him Pierre and proceeded to try out things to see what he would say and add to our French vocabulary while we were at it. I don’t think we had an owner’s manual to simply peruse the list of errors we could commit (and should presumably avoid) for which Pierre would gently scold us. As time has shown, demand for Pierre and his counterparts simply didn’t hold up in the market. Maybe people can accept warning lights, but not a warning voice?

In SQL Server 2008, the cube and dimension designers in Analysis Services now come with best practice design warnings, but fortunately Dev Studio doesn’t read them aloud to you. A visual indicator – which I’ll call the blue squiggly – will appear on screen to highlight the offending object. The first warning you’re likely to see when you create dimension is associated with the dimension object at the top of the attribute tree. This warning says (in the July CTP), “Create hierarchies in non-parent child dimensions.” As soon as you create a user hierarchy, the blue squiggly goes away, right? Nope… now you probably have a new warning on the dimension object if the attributes you selected are all visible – “Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies.” And the hierarchy object now probably has a blue squiggly to let you know that there are no attribute relationships defined between one or more levels in the hierarchy. (Remember this is a brand new dimension).

Don’t worry about more warnings appearing as you do your design work. Just go about your normal business, and hopefully all will clear up before you’re ready to deploy the project. Many of the 48 warnings (in the July CTP) are well-known best practices to experienced Analysis Services developers. So what’s the point of including best practices if they are so well-known? Well, not everyone implementing SQL Server for the first time has access to experienced developers, so their experience will be much more positive with Analysis Services if they are warned about the pitfalls before they fall in.

Rather than haphazardly try out something to see whether or not it conforms to best practices, as I did with Pierre, you can jump straight to Books Online to see complete list of the warnings (including links to more information about each). Search for the topic, “Design Warning Rules.” The warnings are organized into categories (in the July CTP BOL) as follows: Dimensions, Aggregations, Partitions, Attributes and Attribute Relationships, Measures and Measure Groups, User-defined Hierarchies, ROLAP and MOLAP storage, Data Providers, and Error Handling. Some warnings come with better explanations about best practices than others. I hope this will improve over time, because for the unitiated these warnings without explanation are little more than “because I said so” instead of the educational opportunity it could be.

Like Pierre’s reminders that we were doing something contrary to the established best practices of driving, the Analysis Services design warnings are there to alert you to potential hazards, but won’t stop you from ignoring them. For example, I’m not certain that I agree that one should always “Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies.” This is a matter best decided in conjunction with users, in my opinion, after explaining the pros and cons of this approach. Some implementations may not have this luxury, in which case I would defer to the best practice recommendation.

Some best practices earn a chuckle from me, such as “Define a time dimension.” I have yet to meet a cube without one. I had a student insist once that they had seen one, but when pressed could not describe the purpose of the cube. I’m still waiting for a cube without a time dimension. I’m not saying it’s not possible, but I can’t imagine why you would want one as time-series analysis is one of the most compelling reasons to build a cube in the first place.

Some best practices contradict default values for dimensions (in the July CTP), which also amuses me, such as “Change the UnknownMember property of dimensions from Hidden to None” or “Define attribute relationships as ‘Rigid’ where appropriate”. It seems to me the Analysis Services dev team could easily make the change for default values to accomodate these best practices, as they did with “Do not ignore duplicate key errors. Change the KeyDuplicate property of the error configuration so that it is not set to IgnoreError”. To clarify, in SQL Server 2005, the default KeyDuplicate property value is IgnoreError, but this is changed to ReportAndStop in SQL Server 2008.

As mentioned earlier, before you deploy your project, you should clean up – to the extent you wish – the current warnings in your project. Warnings won’t stop your deployment, but you should make a conscious decision whether to ignore the surfaced warnings. A comprehensive list of all warnings in your project can be found in the Error List window (which you can open with Ctrl+E). Double-click on an error to access the designer and fix the problem. Alternatively, you can right-click the error and click dismiss to clear it off the list if you don’t intend to fix it. You can even add a comment to document your reason for ignoring this error. This method of clearing the error is instance-based and will not clear the same error if it’s found in a different dimension or cube. To globally dismiss a particular type of error, whether proactively before you start development or after the fact, you can access the new Warnings tab in the Database editor (which you can open on the Database menu by clicking Edit Database). Incidentally, the Warnings tab also contains a list of the warnings dismissed individually and the related comment.

All in all, I think this is a nice feature in SQL Server 2008 Analysis Services, particuarly for the many folks out there who are just getting started with this technology. Just as long as the warnings stay visual. As much as I like technology in general, I still don’t think I’m ready for Dev Studio to start talking to me like Pierre and I suspect many other people feel the same way. –Stacia

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.