Creating Multidimensional Models Using SQL Server Data Tools (SSDT)
Microsoft SQL Server provides two different environments for building, deploying, and managing Analysis Services solutions: SQL Server Data Tools (SSDT) 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.
SQL Server Data Tools (SSDT) is a development environment, based on Microsoft Visual Studio 2010, used for creating and modifying business intelligence solutions. With SQL Server Data Tools (SSDT), 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 SQL Server Data Tools (SSDT), 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 SQL Server Data Tools (SSDT) using Analysis Services, see Create an Analysis Services Project (SSDT). You can also use SQL Server Data Tools (SSDT) 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 Multidimensional Model Databases (SSAS), and Connect in Online Mode to an Analysis Services Database.
SQL Server Management Studio is a management and administration environment, 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 Analysis Services Scripts Project in SQL Server Management Studio.
Introducing Solutions, Projects, and Items
Both SQL Server Data Tools (SSDT) 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.
SQL Server Data Tools (SSDT) 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 Create an Analysis Services Project (SSDT). |
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 Reporting Services (SSRS). |
Report Model Project |
Contains the object definitions for a Reporting Services report model. For more information, see Reporting Services (SSRS). |
Report Server Project |
Contains the object definitions for one or more Reporting Services reports. For more information, see Reporting Services (SSRS). |
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 Analysis Services Scripts Project in 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 SQL Server Data Tools
SQL Server Management Studio is designed for administering and configuring existing objects in SQL Server Database Engine, Analysis Services, Integration Services, and Reporting Services. SQL Server Data Tools (SSDT) 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 SQL Server Data Tools (SSDT).
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.
SQL Server Data Tools (SSDT) provides an integrated development environment for developing business intelligence solutions. You can use SQL Server Data Tools (SSDT) in project mode, which uses XML-based definitions of Analysis Services, Integration Services, and Reporting Services objects contained in projects and solutions. Using SQL Server Data Tools (SSDT) in project mode means that changes to Analysis Services objects in SQL Server Data Tools (SSDT) 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 SQL Server Data Tools (SSDT) in online mode, which means connecting directly to an Analysis Services instance and working with objects in an existing database.
SQL Server Data Tools (SSDT) 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 SQL Server Data Tools (SSDT). 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.
In This Section
Working with Analysis Services Projects and Databases During the Development Phase
Working with Analysis Services Projects and Databases in a Production Environment
See Also
Concepts
Create an Analysis Services Project (SSDT)
Analysis Services Scripts Project in SQL Server Management Studio