Any project involving Microsoft BI stack technologies, such as SSIS, SSAS, and SSRS, starts with the installation of SQL Server and BIDS. As the project progresses and the requirements become more complex, developers and DBAs often find themselves in need of additional tools and utilities to make their lives easier. In this article, we will explore a collection of technical document templates, tools, and utilities that can help boost the speed of development in your SQL Server projects.

Technical Documentation

When it comes to technical documentation, there are several key documents and templates that can prove useful throughout the lifecycle of a Microsoft BI project:

• Standards for T-SQL Coding: This document is essential for ensuring quality compliance and project development standards. It provides guidelines for team members at all levels of expertise.
• SSIS Package / Task / Components Naming Conventions: This document provides a standardized and professional naming convention for tasks and components in SSIS. It can be used as a quick reference for developers.
• SSRS Report to SP Mapping Document: This document helps avoid the creation of duplicate stored procedures by mapping reports to the stored procedures they use. It captures important details such as report name, parameters, and the corresponding stored procedures.
• MDX Query Templates and/or Named Set List: MDX can be challenging for developers. Creating templates for commonly used MDX queries can greatly speed up development for calculated measures or named sets.
• Complexity Measure of Deliverables: This document helps estimate delivery time by assigning complexity measures to different objects. It allows for better allocation of tasks based on developers’ experience.
• Best Practices Document for SSIS / SSAS / SSRS: Incorporating best practices documents into your project ensures adherence to industry standards and helps avoid common pitfalls. The SQLCAT Team publishes many best practices articles for each aspect of MS BI.
• Review Checklist for Testing of the Deliverables: This checklist helps ensure thorough unit testing and peer review of project deliverables. It provides a structured approach to validating objects against predefined checks.

Tools and Utilities

In addition to BIDS and SSMS, there are several free tools and utilities available that can enhance your SQL Server projects:

• BIDS Helper: This Visual Studio.Net add-in extends and enhances the functionality of BIDS. It offers a range of features that can greatly improve your SSIS development experience.
• SSMS Tools Pack: This utility provides various useful features, such as generating INSERT statements from tables, query text history, and additional templates. It can save developers a significant amount of time and effort.
• RSS Scripter: This free utility creates a batch file for automated report deployment using the RS utility. It simplifies the process of deploying reports to a specified server.
• DBA Dashboard: Available for free from www.sql-server-performance.com, this utility provides a comprehensive view of the overall activity on the development server. It is similar to the Performance Dashboard available from Microsoft.
• MDX Script Performance Analyzer: This tool helps analyze the performance of MDX queries and provides insights for optimizing calculated measures in cubes.
• AdventureWorks Sample Database: Installing the AdventureWorks sample database can provide developers with a ready source of test data for experimenting with code.
• Code Gallery: Websites like the Microsoft SQL Server Script Center Gallery offer a wealth of reusable code and components that can save developers time and effort.
• DB Pro: While not free, DB Pro is a powerful tool available with Visual Studio for Database Professionals. It offers features for data generation, object comparison, and script generation, making it invaluable for transferring objects across environments.

These tools and utilities can greatly enhance your SQL Server projects, addressing technical challenges and requirements that may not be possible to cater to using BIDS alone.

Conclusion

While the list provided in this article is not exhaustive, the tools, techniques, and documentation methods discussed here have proven to be highly useful in accelerating MS BI projects. By incorporating these resources into your projects, you can save time and effort, and ensure adherence to best practices. If you have any comments, queries, or feedback on this article, feel free to reach out to me at siddhumehta_brain@yahoo.co.in. You can also visit my technical blog at http://siddhumehta.blogspot.com.

About the Author:

Siddharth Mehta is an experienced IT professional with 7 years of working experience in the industry. He holds several professional certifications, including MCTS in Business Intelligence, SQL Server Implementation and Maintenance, Performance Point Server Office Applications, and MCSD.Net. Siddharth specializes in Microsoft Business Intelligence technologies and has worked as a Design or Technical Lead on numerous projects.