Creating Multidimensional Models Using SQL Server Data Tools
Applies to: 
SQL Server Analysis Services 
Azure Analysis Services
Fabric/Power BI Premium
Microsoft SQL Server provides two different environments for building, deploying, and managing SQL Server Analysis Services solutions: SQL Server Data Tools 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 is a development environment, based on Microsoft Visual Studio 2010, used for creating and modifying business intelligence solutions. With SQL Server Data Tools, you create SQL Server Analysis Services projects that contain definitions of SQL Server Analysis Services objects (cubes, dimensions, and so on), which are stored in XML files that contain SQL Server 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, you can develop SQL Server Analysis Services projects as part of a solution that is independent of any particular SQL Server Analysis Services instance. You can deploy the objects to an instance on a test server for testing during development, and then use the same SQL Server 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 SQL Server 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 SQL Server Analysis Services project in SQL Server Data Tools using SQL Server Analysis Services, see Create an Analysis Services Project (SSDT). You can also use SQL Server Data Tools to connect directly to an existing SQL Server Analysis Services instance to create and modify SQL Server Analysis Services objects, without working with a project and without storing object definitions in XML files. For more information, see Multidimensional Model Databases, 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 SQL Server Analysis Services, SQL Server, Integration Services, and Reporting Services. With SQL Server Management Studio, you can manage SQL Server Analysis Services objects (perform back-ups, processing, and so on), and you can also create new objects directly on an existing SQL Server 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 SQL Server 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 SQL Server Analysis Services, see Analysis Services Scripts Project in SQL Server Management Studio.
Introducing Solutions, Projects, and Items
Both SQL Server Data Tools 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 contains the following projects under the Business Intelligence Projects project type.
| Project | Description |
|---|---|
| SQL Server Analysis Services Project | Contains the object definitions for a single SQL Server Analysis Services database. For more information about how to create an SQL Server Analysis Services project, see Create an Analysis Services Project (SSDT). |
| Import SQL Server Analysis Services 2008 Database | Provides a wizard that you can use to create a new SQL Server Analysis Services project by importing object definitions from an existing SQL Server 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 |
|---|---|
| SQL Server Analysis Services Scripts | Contains DMX, MDX, and XMLA scripts for SQL Server Analysis Services, as well as connections to SQL Server 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, SQL Server Analysis Services, Integration Services, and Reporting Services. SQL Server Data Tools is designed for developing business intelligence solutions that include functionality from SQL Server Analysis Services, Integration Services, and Reporting Services.
The following are some of the differences between SQL Server Management Studio from SQL Server Data Tools.
SQL Server Management Studio provides an integrated environment for connecting to instances of SQL Server Analysis Services, SQL Server, and Reporting Services to configure, manage, and administer objects within an instance of SQL Server Analysis Services. Through the use of scripts, you can also use SQL Server Management Studio to create or modify SQL Server Analysis Services objects themselves, but SQL Server Management Studio does not provide a graphical interface for object design and definition.
SQL Server Data Tools provides an integrated development environment for developing business intelligence solutions. You can use SQL Server Data Tools in project mode, which uses XML-based definitions of SQL Server Analysis Services, Integration Services, and Reporting Services objects contained in projects and solutions. Using SQL Server Data Tools in project mode means that changes to SQL Server Analysis Services objects in SQL Server Data Tools are made to these XML-based object definitions and not applied directly to an object on an SQL Server Analysis Services instance until the solution is deployed. You can also use SQL Server Data Tools in online mode, which means connecting directly to an SQL Server Analysis Services instance and working with objects in an existing database.
SQL Server Data Tools enhances the development of business intelligence applications because you can work on SQL Server Analysis Services projects in a source-controlled, multi-user environment without requiring an active connection to an SQL Server 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 SQL Server Analysis Services databases. However, once a project has been deployed into the production environment, care must be taken when working with an SQL Server Analysis Services database and its objects with SQL Server Management Studio and SQL Server Data Tools. This is to avoid overwriting changes made to objects directly in an existing database, and changes made to the SQL Server 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
Create an Analysis Services Project (SSDT)
Analysis Services Scripts Project in SQL Server Management Studio
Multidimensional Model Databases