Objective

Any project involving Microsoft BI stack technologies, i.e. SSIS / SSAS / SSRS, starts with the installation of SQL Server and BIDS and then as the project progresses and the spectrum of requirements starts getting broader, developers and DBAs starts hunting for accessories i.e. tool and utilities to make their life easier. This Booster Kit is a collection of technical document templates, tools and utilities that would help to boost the speed of development of your project at various stages by helping to address different project requirements whether it may be documentation or technical challenge.

This article expects at least some basic level of understanding of Microsoft Business Intelligence Framework. It would be easier and helpful to the reader, if one has got some hands-on experience on any of the Microsoft Business Intelligence Components i.e. SSIS / SSAS / SSRS.

Keywords

Below is a list of acronyms that would be used throughout this article.

1. SSIS - SQL Server Integration Services
2. SSAS - SQL Server Analysis Services
3. SSRS - SQL Server Reporting Services
4. BIDS - Business Intelligence Development Studio
5. SSMS - SQL Server Management Studio
6. MDX - Multi Dimensional Expressions
7. DBA - Database Administrator
8. BI - Business Intelligence
9. WBS - Work Breakdown Structure

For this reason, all the items in this booster kit which are listed and discussed below, have been classified into two major categories:

1. Technical Documentation
2. Free tools and utilities apart from BIDS and SSMS

Microsoft Business Intelligence Project Booster Kit: Technical Documentation

Below is a list of documents / document templates / documenting methods that can prove useful in one way or other at sooner or later time in a MS BI project life-cycle.

Standards for T-SQL Coding - This is one of the inevitable documents required for the project, which would be used by team members from beginners to pro level. This document would be very important for purposes like quality compliance and project development standards. A sample of one such document can be found here.

SSIS Package / Task / Components Naming Conventions - When the task of listing down naming conventions starts, there are a lot of tasks and components for which this would have to be done. A quick reference can be taken from this article by Jamie Thomson, in which she has listed down a naming convention for almost all of the tasks and components in a very standard and professional manner. Taking reference from this article would make this document almost ready for use.

SSRS Report to SP mapping document- Create this document in advance to avoid the uncontrolled creation of stored procedures. The more the number of reports and parameters in your project, the more stored procedures that will required for them. It's very easy to mess up a stored procedure used by a report or re-create an SP that might already had been written in some other report. It needs not to be a very detailed document, and the only details that should be captured in the document should be:

1. Report Name
2. Parameters on the report with its data-type
3. Stored Procedure used for each parameter including the body
4. Stored procedures used for any sub-reports in a special case.

MDX Query Templates and/or Named Set list - My experience is that MDX is not a piece of cake and developers are generally not that well versed with MDX. Creating a template for commonly used MDX queries would be quite handy to speed up development for calculated measures or named-sets. Some of the most commonly used MDX queries can be downloaded from here.

Complexity measure of deliverables to create a WBS for delivery time estimation - After creating a WBS, the next task is allocation it to team members based on their experience. Creating a complexity measure document can be on the foundation of the complexity of controls that would be required to be used to develop a particular object. Each complexity band can be allocated a particular number of man days for development, and depending upon the result, the same can be allocated to developers based on their experience.

Best practices document for SSIS / SSAS / SSRS - These documents or links to the best practices document should be incorporated as a part of the project documents with the MUST and MUST NOT list. The SQLCAT Team publishes many best practices articles for each aspect of MS BI.

Review Check List for Testing of the deliverables - Unit Testing and Peer Review checklist should be kept ready once a few basic deliverables are developed. This partially gives advantage of test driven development to the project. Also this makes peer review easy, when the time is ripe.

This checklist can contain three major fields: higher level categories of the check to be made on the object, description of the check, and object to be validate against these checks. For example in SSIS, there can be high level categories like Connections, Variables, Package Configurations, User Expressions, etc. And there can be checks in each category like, only one OLE DB connection should be created in each package, connection string of the connection object should be configured with an expression, connection string should be read from a user variable that is in turn read from a master package etc in the Connection Category.

Microsoft Business Intelligence Project Booster Kit: Tools and Utilities

BIDS is usually installed whenever any development on SSIS / SSAS / SSRS is to be done. But my experience is that BIDS is not always sufficient and there are tools and utilities available that can make life of a developer more easy. There are still few functionalities missing in BIDS which are available from other tools and a few of these tools are available even for free.

For example, using BIDS when a connection is configured with expression in SSIS there is no symbolic indication and one needs to open up each connection to verify the same, changing the scope of user variables in SSIS is still not supported in the 2008 version of BIDS too, there is no way to create an installer using BIDS for an SSRS reporting solution, etc.. are the limitation of BIDS as of the date of draft of this article.

There are few really useful tools and utilities that comes for free, and adds much value to a project. Below is a list of the same.

BIDS Helper - It is a Visual Studio.Net add-in with features that extends and enhances the functionality of the SQL Server 2005 and SQL Server 2008 BIDS. I have used this utility most while working on SSIS. There are a lot of great features, and one can browse the description about each feature from it's homepage by clicking on the BIDS Helper link.

SSMS Tools Pack - I like this utility most for one of its spectacular features. It generates INSERT statements from the table for the scope you specify. This is very handy as many a times a developer needs to transfer records between one server to another, for example from DEV environment to QA. There are many other features which are quite useful, such as query text history and additional templates.

RSS Scripter - Often during the time of reports deployment, one encounters the requirement of creation of automated report deployment. This free utility creates a batch file to deploy all the reports to a specified server using the RS utility. Mind that this wont be useful if the deployment is on a report server that is configured in Sharepoint Integrated mode.

DBA Dashboard - This utility is available for free from www.sql-server-performance.com. It provides a nice view of the overall activity on the development server. The dashboard consists of a set of reports that take advantage of the Custom Reporting feature of SSMS and is similar to Performance Dashboard available from Microsoft. In SQL Server 2008, Management Data Warehouse provides an exhaustive data collection and analysis of the activities and health on the server.

MDX Script Performance Analyzer - MDX is not an easy query language and needs some good practice to gain fluency. Many a times cube starts performing slow and one might need to tune MDX for the calculated measures. This tool can be of quite help for MDX performance analysis.

AdventureWorks Sample Database - With SQL Server 2008, Adventureworks should be installed for developers to facilitate experimenting code. Many a times developers fall short of test data and this sample database would come really handy at times. And when even this falls short, tools like DB Pro or Data Generator Source Adapter in SSIS can be used to facilitate the same.

Code Gallery: Websites such as Microsoft SQL Server Script Center Gallery for T-SQL Scripts or SSIS Tasks and Components Gallery can serve as a cookbook kind of thing and can save lots of effort that one might waste on re-developing the logic that has already been developed and available as a re-useable piece of code or component.

DB Pro - It's not a free tool, but is available with Visual Studio for Database Professionals and requires a special mention. It's a brilliant tool, and has very nice features for data generation, objects comparison and script generation. Its one of the very handy tool while working and transferring objects across environments like DEV, QA and PROD.

Conclusion

The above list is not exhaustive, but these are tools that I have found most useful and have proved sufficient for the technical challenges and/or requirements that were not possible to be catered using BIDS. Hope this article helps to create a booster kit for a MS BI project to address various kind of regular requirements in a shorter time. I have found the above documents or documenting methods, tools and utilities useful to gear any MS BI project faster.

I would be more than happy to receive any comments, queries and/or feedback on this article. I can be reached on siddhumehta_brain@yahoo.co.in. My technical blog can be read on http://siddhumehta.blogspot.com

My Bio

My name is Siddharth Mehta. I hold 7 years of working experience in the IT industry and have a few professional certification titles on my name like MCTS - Business Intelligence , SQL Server Implementation and Maintenance, Performance Point Server Office Applications and MCSD.Net. My primary work area comprises of working on projects involving Microsoft Business Intelligence technologies in the capacity of Design or Technical Lead.