Object Promotion Process for Microsoft BI Objects

With Integration Services, Reporting Services, and Analysis Services, their respective business intelligence objects (packages, reports, and cubes) are designed through the Business Intelligence Design Studio (BIDS), which is really the Visual Studio shell incorporated with special SQL Server 2005 business intelligence project templates installed. Using BIDS, or Visual Studio 2005 with the BI templates installed, allows the developer to deploy the projects directly to a server environment. This works fine for a development environment where the developer most likely has access and permissions to deploy objects to the server, but what about Q/A, Beta (Test), and Production environments? Most likely, organizations do not want their developers impacting installed objects on their test and production environments…for obvious reasons. Therefore, a change management/object promotion process must be utilized against the business intelligence environment, allowing non-developer resources to quickly and easily deploy new object types.

Also of consideration is object versioning. When working with Visual Studio, there are multiple product options that allow versioning and check-in-check-out of project objects. From Microsoft, two products exists that enable versioning/checking object code, those being Visual Source Safe, and Team Foundation Server.

The process for deployment "object promotion" to non personal-development environments is accomplished through three different options:

1) Use BIDS/Visual Studio 2005 to open the project/solution, and then deploy the data sources and reports to the appropriate server. The project object allows for multiple configuration settings, and is an exposed set of properties that can identify development, QA, test, and product environment settings.

2) Use a scripting engine (for Reporting Services that scripting engine is RS.EXE, for Analysis Services it’s ASCMD, and for Integration Services it's DTUTIL) to develop a script that deploys, installs, and secures objects to a particular target server. (Note – There are several sample scripts that come with SQL Server 2005 for reference).

3) Use application interfaces to create a custom deployment tool. For example, the Reporting Services management web service endpoint (https://<Server Name>/ReportServer/ReportService2005.asmx?wsdl) could be used to accomplish the same deploy, install, and securing of Reporting Services objects that the RS.EXE scripting command implements.