SqlServer analysis services

Wed Dec 5, 2007

Couple of evolving practices for my current team:

Do not try to set up an analysis services project on a full database. I always cut down the data to a small representative sample first.

Whenever possible, let the business folks write the calculation formulas. Don’t let your development team get bogged down with change requests for things the business people can do. Again, you will want to set these folks up with your representative sample so they can reprocess formula changes at will. Thus, the sample should be small but as complete as possible. I like to use a filtered set from the production database, pulled nightly.

Set process tasks nightly on the QA environment, even if no one is planning on looking at it this week. Make sure the unit tests are running, and if any fail, have them create cases in Bugzap in failure fixtures.

Do not forget to create performance unit tests. Meaning, if the unit test takes longer than xx milliseconds, you create a case to examine.

This isn’t necessarily analysis services, but I have found a special teams approach much more effective than a ‘you own everything approach’. Let two or three people concentrate on performance, two or three on testing coverage, etc. If you try to make everyone learn it all then no one has time to get to the bits, and that is extremely inefficient. Rotate the teams often, but always pair up someone new to the focus area with an experienced person – the cross pollination of ideas helps a lot.