How to use SSAS 2008 Warning Framework effectively

By: Siddharth Mehta | Read Comments | Print Siddharth is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence. Related Tips: More

Problem

During the SSAS solution design and development phase, at one time or another you will come across a number of warnings in BIDS. Many of these might be just best practices suggestions, but some may also be critical for the build of the project. In this tip we would look at how to effectively control and use the warning framework in SSAS.

Solution

Before we proceed with the discussion, it should be kept in view that the SSAS version discussed throughout this article is SSAS 2008. Also this article expects some basic level of SSAS experience.

Warnings reported when we design and develop a SSAS solution using BIDS, can be categorized into two types.

1. First is the warning reported by BIDS itself after analyzing the design implemented in your solution. You would generally see a blue squiggly under hierarchies or dimensions or any other object in your solution. This is a warning generated by BIDS, as it might not be in accordance with the recommended best practices. Mostly as a result of these warnings you will find a warning message as well in your Error List window.
2. Second category of warnings are the warnings that are reported by SQL Server Analysis Service itself. When you build the project and the XML/A is sent to a SSAS instance where your solution database is stored, SSAS may report warnings which in most cases would be accompanied by an error.

Now the obvious question that comes to mind is how to identify these two types of warnings, as they may not apparent by looking at the different warnings in the Error List window. The key to this question is that warnings reported by SSAS cannot be ignored and warnings reported by BIDS are mere suggestions and can be ignored.

To simulate the problem and solution in question, we can use the AdventureWorks SSAS enterprise sample solution. In order to see types of warnings mentioned above, we need to make the solution fail. In my AdventureWorks cube I have a Dim Geography database dimension, and it consists of a Country - State - City hierarchy with one-to-many attribute relationships. I have intentionally set up the key columns is a way that would create duplicate keys for the City attribute and will fail cube processing. You can try it with your own flavor of a bug creation that should be critical enough to fail the build. Duplicate key error is one of the common mistakes developers make while configuring dimensions, and you can try to plant such a bug in your sample project to cater the issue at hand.

When I build and deploy the project, I can see warnings as listed in the below screenshot. If you look carefully, when I right-click on the first warning, I can see the "Dismiss" option enabled, which can be considered as a sign that it's a BIDS reported warning.

When I right-click on the last warning, I am not provided with the "Dismiss" option and it's disabled. The reason for this is that this warning has been reported by SSAS and is critical to be resolved to deploy the change. When you have a lot of warnings every time you deploy a set of changes, it might be worth dismissing the warnings that you feel do not impact your solution. By this practice, incrementally, you will reach a stage when you would start treating your warnings with the same importance as you treat errors.

The warnings issued by BIDS are also controllable and you can find the exact design rule / criteria due to which these warnings are generated. Right-click on your solution and select "Edit database", and you should find a screen similar to the one shown in the below screenshot.

All the design time warning rules are categorized and listed here with their respective severities. You can opt to turn off these rules and thereafter when you design your solution against these rules, you will no longer find warnings issued for them. Also when you dismiss any warning, you will find the dismissed warning listed here along with the reason you provided during the dismissal.

Next Steps

• Check out the warnings that might be appearing repeatedly in your solution. Either dismiss the warning with proper reason and create a repository of design rules implemented contrary to best practices, or turn off the rule to stop warnings from appearing.
• Check out whether dismissing a particular type of warning for a dimension / hierarchy / measure or any other object, dismisses that warning for all the same type of objects or if you need to disable the rule for each.

Related Tips: More | Become a paid author

Last Update: 1/4/2011

Share:

Share

Tweet

Free SQL Server Learning Introduction to SQL Azure Join Herve Roggero on Wednesday, June 13 @ 3:00 PM EST to learn more.

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Try the award winning SQL diagnostic manager as a free 14-day trial! Time-strapped database professionals use SQL Monitor to look after their servers. Try it online. What grade do you think your SQL Servers get? Find out with Edgewood's SQL Server Health Check starting at $995. Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise. Join the over million SQL Server Professionals who get their issues resolved daily. Free Learning - Introduction to SQL Azure Delivered by Herve Roggero on Wednesday, June 13 @ 3:00 PM EST