While SQL Server Management Studio is used to develop relational database projects and administer and configure existing objects in SQL Server 2005, the new Business Intelligence (BI) Development Studio is used to create Business Intelligence solutions.

Unlike the SQL Server Management Studio, the BI Development Studio is not really designed to be an administrative tool. You use the BI Development Studio to work with Analysis Services projects, to develop and deploy Reporting Services reports, and to design Integration Services (SSIS) packages. The BI Development Studio is accessed using the Start | Programs | Microsoft SQL Server | Business Intelligence Development Studio menu option.

The BI Development Studio, like the SQL Server Management Studio, is built on the Visual Studio 2005 IDE. It provides a solution-oriented development environment and contains one or more projects in a solution. BI Development Studio enhances the development of business intelligence applications by allowing project development in a source-controlled, multiuser environment without requiring an active connection to a server. Each of the project types will contain the specific object definitions for those projects. For example, a Reporting Services project will contain Report definitions, while an SSIS project will contain SSIS package objects. Like the SQL Server Management Studio, the BI Development Studio doesn’t allow you to compile VB.NET, C#, J#, or VC++. Instead, the BI Development Studio is designed expressly for working with BI projects like SSIS and Reporting Services. The BI Development Studio is also integrated with Visual SourceSafe for source code version control.

The Business Intelligence Development Studio User Interface

The Business Intelligence Development Studio user interface is an excellent environment for developing business intelligence solutions, including cubes, data sources, data views, reports, and data transformation packages.

BI Development Studio User Interface Windows This section gives you an overview of the BI Development Studio user interface main windows, including:

The Designer window

The Solutions Explorer window

The Properties window

The Toolbox window

The Output window

The Designer window provides a graphical view of an object and is the central window in the BI Development Studio. A different designer type inhabits the designer window in response to the current BI Development Studio object type. For example, if you are developing an SSIS package, the Designer window provides the design surface to drag and drop objects from the Control Flow toolbox to the project, while the Report Designer provides the design surface to create and preview reports.

Solution Explorer

Like the SQL Server Development Studio, the BI Development Studio has a Solution Explorer window. The Solution Explorer is shown in the upper right-hand corner of the screen. The Solution Explorer provides a hierarchical tree view of the projects and files that compose a BI Development Studio solution. The top item in the Solution Explorer hierarchy is the solution name. The solution can have one or more project items under it. The BI Development Studio Solution Explorer provides project templates, including: Analysis Services Project, Integration Services Project, Import Analysis Services 9.0 Database, Report Project, Report Project Wizard, and Report Model Project. As in the SQL Server Management Studio, BI Development Studio solutions are not restricted to one project. You can create solutions that are made up of any of the supported project types. You can see the Business Intelligence Development Studio’s New Project dialog.

A. Analysis Services Project Analysis Services projects contain the definitions for the objects in an Analysis Services database. These include designing and creating Analysis Services databases, data source views, cubes, and dimensions, as well as working with the data mining features.

A.1. - Analysis Services database An Analysis Services database created using BI Development Studio includes the XML de. nitions for the database and its objects for later deployment to a speci. c instance of Analysis Services. To create an Analysis Services database, you select the File | New | Project option from the main BI Development Studio menu. Then from the dialog displayed, select the Analysis Services Project template from the Business Intelligence project type.

A.2. - Data source views A data source view is a document that describes the schema of an underlying data source. Such a view contains names and descriptions of selected database objects, such as tables, views, and relationships, that online analytical processing (OLAP) and data mining objects in Analysis Services reference. These objects can be organized and con. gured to provide a complete schema. You can develop an Analysis Services project without having to have an active connection to the data source, because the data source view caches the metadata from the data source it is built upon. Using a data source view, you can de. ne a subset of data from a larger data warehouse.

A.3. - Cube Wizard A cube is a multidimensional structure that contains dimensions and measures; where dimensions de. ne the structure of the cube, and measures provide the numerical values that the end user is interested in. The Cube Wizard is a visual tool that you can use to quickly create OLAP cubes. It is started by double-clicking the Cube node shown under an Analysis Services project or by right-clicking the Cube node and selecting View Designer.

A.4. - Dimension Wizard A dimension is a collection of objects that describe the data that is provided by the tables in a data source view. You can organize these dimensions into hierarchies that allow you to navigate paths to the data in a cube. Typically, users will base their analyses on the description attributes contained in the dimensions, such as time, customers, or products. A Dimension Wizard is provided in the BI Development Studio to guide you through the steps for specifying the structure of a dimension.

A.5. - Data Mining Designer A Data Mining Designer is provided as a primary environment, which allows you to work with mining models in Analysis Services. You can access the Data Mining Designer by selecting an existing item in a mining structure project, or you can use the Data Mining Wizard to create a new item. Using the Data Mining Designer, you can modify a mining structure, create new mining models, compare models, or create prediction queries.

B. Integration Services Project Integration Services projects contain folders and files that allow you to manage the object definitions of data sources, data source views, and packages for SSIS solutions. Data sources are defined as project-level, which means you can have multiple projects in your solution that reference a single data source object. Data source views can be referenced by sources, transformations, and destinations in your project, and packages contain a collection of connections, control flow elements, data flow elements, event handlers, variables, and configurations.

C. SSIS Designer The BI Development Studio contains an SSIS Designer, which is a graphical tool for creating packages. It has four tabs, one each for building the four elements of the SSIS project, including: the package control flow, the data flows, the event handlers, and one tab for viewing the contents of a package. A fifth tab that appears at run time allows you to view the execution progress of a package. After the package completes its run, the execution results can be viewed.

Separate design surfaces exist for building the control flow, data flows, and event handler elements in packages. Dialog boxes and windows, such as the variable window and the breakpoint window, are also included to help you add and configure variables and to troubleshoot your project. Wizards are included to add functionality and advanced features.

D. Import Analysis Services 9.0 Database The Import Analysis Service 9.0 Database project enables you to create a new SQL Server 2005 Analysis Services project by importing the definitions for an existing SQL Server 2000 Analysis Services or SQL Server 7 OLAP Server database.

E. Report Project, Report Project Wizard, and Report Model Project The BI Development Studio contains the Report Project template, the Report Project Wizard template, and the Report Model Project template to create Reporting Services projects. These reporting templates are used to design reports and control their deployment. Reporting Services project templates start the Reporting Services Designer, where you can select data sources and visually lay out reports.

The Properties window

Like the Properties window in SQL Server Management Studio, the BI Development Studio Properties window allows you to view the properties of files, projects, or solutions. The Properties window  is used at design time to set the properties of the objects selected in the Solution Explorer. If the Properties window is not already displayed, you can show it by selecting the View | Properties Window option from the BI Development Studio menu. The Properties window displays different types of editing fields, depending on the type of object selected.

The Toolbox window

The Toolbox window in the BI Development Studio is shown on the left side of the screen. The Toolbox is used by the SSIS Designer and the Reporting Services Designer to drag and drop components onto their respective design surfaces.

The Output Window

The Output window displays the results when a solution is built.