Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to use SSAS 2008 Warning Framework effectively


By:   |   Last Updated: 2011-01-04   |   Comments   |   Related Tips: > Analysis Services Development

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.

 how to effectively control and use the warning framework in SSAS.

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.

warnings reported by SSAS cannot be ignored and warnings reported by BIDS are mere suggestions and can be ignored.

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.

warnings issued by BIDS are also controllable

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.


Last Updated: 2011-01-04


get scripts

next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools