Developing and Implementing Using Business Intelligence Development Studio
Microsoft SQL Server provides two different environments for building, deploying, and managing Analysis Services solutions: Business Intelligence Development Studio and SQL Server Management Studio. Both of these environments implement a project system. For more information about Visual Studio projects, see Projects as Containers in the MSDN Library.
Business Intelligence Development Studio is a development environment, based on Microsoft Visual Studio 2008, used for creating and modifying business intelligence solutions. With Business Intelligence Development Studio, you create Analysis Services projects that contain definitions of Analysis Services objects (cubes, dimensions, and so on), which are stored in XML files that contain Analysis Services Scripting Language (ASSL) elements. These projects are contained in solutions that can also contain projects from other SQL Server components, including SQL Server Integration Services and SQL Server Reporting Services. In Business Intelligence Development Studio, you can develop Analysis Services projects as part of a solution that is independent of any particular Analysis Services instance. You can deploy the objects to an instance on a test server for testing during development, and then use the same Analysis Services project to deploy your objects to instances on one or more staging or production servers. The projects and items in a solution that includes Analysis Services, Integration Services, and Reporting Services can be integrated with source code control, such as Microsoft Visual SourceSafe. For more information about creating an Analysis Services project in Business Intelligence Development Studio using Analysis Services, see Defining an Analysis Services Project. You can also use Business Intelligence Development Studio to connect directly to an existing Analysis Services instance to create and modify Analysis Services objects, without working with a project and without storing object definitions in XML files. For more information, see Defining an Analysis Services Database, and How to: Connect in Online Mode to an Analysis Services Database using Business Intelligence Development Studio.
SQL Server Management Studio is a management and administration environment, based on Visual Studio 2005, used primarily to administer instances of Analysis Services, SQL Server, Integration Services, and Reporting Services. With SQL Server Management Studio, you can manage Analysis Services objects (perform back-ups, processing, and so on), and you can also create new objects directly on an existing Analysis Services instance by using XMLA scripts. SQL Server Management Studio provides an Analysis Server Scripts project in which you can develop and save scripts written in Multidimensional Expressions (MDX), Data Mining Extensions (DMX), and XML for Analysis (XMLA). Usually, Analysis Server Scripts projects are used for performing management tasks or re-creating objects, such as databases and cubes, on Analysis Services instances. Such projects can be saved as part of a solution and integrated with source code control. For more information about creating an Analysis Server Scripts project in SQL Server Management Studio using Analysis Services, see Developing and Implementing Using SQL Server Management Studio.
Introducing Solutions, Projects, and Items
Both Business Intelligence Development Studio and SQL Server Management Studio provide projects, which are organized into solutions. A solution can contain multiple projects, and a project typically contains multiple items. A new solution is automatically generated when you create a project, and you can add additional projects as needed to an existing solution. The objects that a project contains depend on the type of the project. The items in each project container are saved as files in project folders in the file system.
Business Intelligence Development Studio contains the following projects under the Business Intelligence Projects project type.
Project |
Description |
|---|---|
Analysis Services Project |
Contains the object definitions for a single Analysis Services database. For more information about how to create an Analysis Services project, see Defining an Analysis Services Project. |
Import Analysis Services 2008 Database |
Provides a wizard that you can use to create a new Analysis Services project by importing object definitions from an existing Analysis Services database. |
Integration Services Project |
Contains the object definitions for a set of Integration Services packages. For more information, see SQL Server Integration Services. |
Report Project Wizard |
Provides a wizard that guides you through the process of creating a Report project using Reporting Services. For more information, see SQL Server Reporting Services. |
Report Model Project |
Contains the object definitions for a Reporting Services report model. For more information, see SQL Server Reporting Services. |
Report Server Project |
Contains the object definitions for one or more Reporting Services reports. For more information, see SQL Server Reporting Services. |
SQL Server Management Studio also contains several project types that focus on various queries or scripts, as shown in the following table.
Project |
Description |
|---|---|
Analysis Services Scripts |
Contains DMX, MDX, and XMLA scripts for Analysis Services, as well as connections to Analysis Services instances against which these scripts can be executed. For more information, see Developing and Implementing Using SQL Server Management Studio. |
SQL Server Compact Scripts |
Contains SQL scripts for SQL Server Compact, as well as connections to SQL Server Compact instances against which these scripts can be executed. |
SQL Server Scripts |
Contains Transact-SQL and XQuery scripts for a SQL Server Database Engine instance, as well as connections to SQL Server Database Engine instances against which these scripts can be executed. For more information, see SQL Server Database Engine. |
For more information about solutions and projects, see "Managing Solutions, Projects, and Files," either in the Microsoft Visual Studio .NET documentation or in the MSDN Library.
Choosing Between SQL Server Management Studio and Business Intelligence Development Studio
SQL Server Management Studio is designed for administering and configuring existing objects in SQL Server Database Engine, Analysis Services, Integration Services, and Reporting Services. Business Intelligence Development Studio is designed for developing business intelligence solutions that include functionality from Analysis Services, Integration Services, and Reporting Services.
The following are some of the differences between SQL Server Management Studio from Business Intelligence Development Studio.
SQL Server Management Studio provides an integrated environment for connecting to instances of Analysis Services, SQL Server, and Reporting Services to configure, manage, and administer objects within an instance of Analysis Services. Through the use of scripts, you can also use SQL Server Management Studio to create or modify Analysis Services objects themselves, but SQL Server Management Studio does not provide a graphical interface for object design and definition.
Business Intelligence Development Studio provides an integrated development environment for developing business intelligence solutions. You can use Business Intelligence Development Studio in project mode, which uses XML-based definitions of Analysis Services, Integration Services, and Reporting Services objects contained in projects and solutions. Using Business Intelligence Development Studio in project mode means that changes to Analysis Services objects in Business Intelligence Development Studio are made to these XML-based object definitions and not applied directly to an object on an Analysis Services instance until the solution is deployed. You can also use Business Intelligence Development Studio in online mode, which means connecting directly to an Analysis Services instance and working with objects in an existing database.
Business Intelligence Development Studio enhances the development of business intelligence applications because you can work on Analysis Services projects in a source-controlled, multi-user environment without requiring an active connection to an Analysis Services instance. SQL Server Management Studio provides direct access to existing objects for querying and testing, and can be used to more quickly implement previously scripted Analysis Services databases. However, once a project has been deployed into the production environment, care must be taken when working with an Analysis Services database and its objects with SQL Server Management Studio and Business Intelligence Development Studio. This is to avoid overwriting changes made to objects directly in an existing database, and changes made to the Analysis Services project that originally generated the deployed solution. For more information, see Working with Analysis Services Projects and Databases During the Development Phase, and Working with Analysis Services Projects and Databases in a Production Environment.