Share via

Create an Analysis Services Project (SSDT)

  • Article

You can define an Analysis Services project in SQL Server Data Tools (SSDT) either by using the Analysis Services Project template or by using the Import Analysis Services Database Wizard to read the contents of an Analysis Services database. If no solution is currently loaded in SQL Server Data Tools (SSDT), creating a new Analysis Services project automatically creates a new solution. Otherwise, the new Analysis Services project will be added to the existing solution. Best practices for solution development call for creating separate projects for different types of application data, using a single solution if the projects are related. For example, you might have a single solution that contains separate projects for Integration Services packages, Analysis Services databases, and Reporting Services reports that are all used by the same business application.

An Analysis Services project contains objects used in a single Analysis Services database. The deployment properties of the project specify the server and database name by which the project metadata will be deployed as instantiated objects.

This topic contains the following sections:

Create a New Project Using the Analysis Services Project Template

Create a New Project Using an Existing Analysis Services Database

Add an Analysis Services Project to an Existing Solution

Build and Deploy the Solution

Analysis Services Projects

Analysis Services File Types

Analysis Services Item Templates

Create a New Project Using the Analysis Services Project Template

Use these instructions to create an empty project in which you define Analysis Services objects that you can then deploy as a new Analysis Services database.

  1. In SQL Server Data Tools (SSDT), click New Project. In the New Project dialog box, in the Installed Template pane, select Business Intelligence Projects, and then select Analysis Services.

  2. Select Analysis Services Multidimensional and Data Mining Project.

  3. In the Name text box, type the name of the project. The name you enter will be used as the default database name.

  4. In the Location drop-down list, type or select the folder in which to store the files for the project, or click Browse to select a folder.

  5. To add the new project to the existing solution, in the Solution drop-down list, select Add to Solution.

    —or—

    To create a new solution, in the Solution drop-down list, select Create new Solution. To create a new folder for the new solution, select Create directory for solution. In Solution Name, type the name of the new solution.

  6. Click OK.

Create a New Project Using an Existing Analysis Services Database

Use the Import Analysis Services Database Wizard to create a project based on the objects in the existing Analysis Services database. When you define an Analysis Services project based on an existing Analysis Services database, the metadata for that database will open in an Analysis Services project in SQL Server Data Tools (SSDT). These objects can then be modified within the project without affecting the original objects, and then be deployed to the same Analysis Services database if the deployment properties specify that database, or to a newly created Analysis Services database for comparison testing. Until the changes are deployed, no changes made will affect the existing Analysis Services database.

You can also use the Import Analysis Services Database template to create a project from a production database to which changes have been made directly since the original Analysis Services project was deployed.

Before you process or deploy the project, you might need to change the data provider that is specified in the data sources. If the SQL Server software you are using is newer than the software used to create the database, the data provider specified in your project might not be installed on your computer. During processing, the service account will be used to retrieve the data in your Analysis Services database. If the database is on a remote server, check whether the local service has process and read permissions on that server.

  1. In SQL Server Data Tools (SSDT), click New Project. In the New Project dialog box, in the Installed Template pane, select Business Intelligence Projects, and then select Analysis Services.

  2. Select Import from Server (Multidimensional and Data Mining).

  3. Enter property information for the project and solution, including name and location for the files. Click OK.

  4. On the Welcome to the Import Analysis Services Database Wizard page, click Next.

  5. On the Source Database page, specify the server and the database from which the wizard will extract the contents and create the Analysis Services project, and then click Next.

    Supported databases include those created in the following versions of Analysis Services: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012.

    You can either type the database name or query the server to view the existing databases on the server. If the database is on a remote server or production server, you might need to request permission to read the database. Firewall configuration settings can further restrict access to a database. If you get an error while attempting to connect to the database, check permissions and firewall settings first.

  6. When the wizard finishes extracting the contents of the Analysis Services database, click Finish on the Completing the Wizard page.

  7. Open the Solution Explorer window to view the contents of the project.

Add an Analysis Services Project to an Existing Solution

If you already have a solution that contains all the source files of a business application, you can add a new Analysis Services project to that solution.

Adding an existing project to a solution associates, but does not copy, the project with the solution. If the Analysis Services project was created in a different solution, the project files remain with the original solution for which it was created. This means that any changes you make to the project through either solution will operate on the same set of source files. If this behavior is not what you intend, you should copy or move the project files to the new solution folder first, and then add the project to the solution.

  1. Open the solution in SQL Server Data Tools (SSDT). In Solution Explorer, right-click the solution, point to Add, and then click Existing Project to select the project you want to add.

  2. Select a .dwproj file to add to the solution.

Build and Deploy the Solution

By default, SQL Server Data Tools (SSDT) deploys a project to the default instance of Analysis Services on the local computer. You can change this deployment destination by using the Property Pages dialog box for the Analysis Services project to change the Server configuration property.

Note

By default, SQL Server Data Tools (SSDT) processes only objects changed by the deployment script and dependent objects when deploying a solution. You can change this functionality by using the Property Pages dialog box for the Analysis Services project to change the Processing Option configuration property.

Build and deploy the solution to an instance of Analysis Services for testing. Building a solution validates the object definitions and dependencies in the project and generates a deployment script. Deploying a solution uses the Analysis Services deployment engine to send the deployment script to a specified instance.

After you deploy the project, review and test the deployed database. You can then modify object definitions, build, and deploy again until the project is complete.

After the project is complete, you can use the Deployment Wizard to deploy the deployment script, generated when you build the solution, to destination instances for final testing, staging, and deployment.

Analysis Services Project Folders

An Analysis Services project contains the following folders, which are used to organize items included in the project.

Folder

Description

Data Sources

Contains data sources for an Analysis Services project. You create these objects with the Data Source Wizard and edit them in Data Source Designer.

Data Source Views

Contains data source views for an Analysis Services project. You create these objects with the Data Source View Wizard and edit them in Data Source View Designer.

Cubes

Contains cubes for an Analysis Services project. You create these objects with the Cube Wizard and edit them in Cube Designer.

Dimensions

Contains dimensions for an Analysis Services project. You create these objects with the Dimension Wizard or the Cube Wizard and edit them in Dimension Designer.

Mining Structures

Contains mining structures for an Analysis Services project. You create these objects with the Mining Model Wizard and edit them in Mining Model Designer.

Roles

Contains database roles for an Analysis Services project. You create and manage roles in Role Designer.

Assemblies

Contains references to COM libraries and Microsoft .NET Framework assemblies for an Analysis Services project. You create references with the Add Reference dialog box.

Miscellaneous

Contains any type of file except for Analysis Services file types. Use this folder to add any miscellaneous files, such as text files that contain notes on the project.

Analysis Services File Types

A SQL Server Data Tools (SSDT) solution can contain several file types, depending on what projects you included in the solution and what items you included in each project for that solution. Typically, the files for each project in a SQL Server Data Tools (SSDT) solution are stored in the solution folder, in a separate folder for each project.

Note

Copying a file for an object to a project folder does not add the object to the project. You must use the Add command from the project's context menu in SQL Server Data Tools (SSDT) to add an existing object definition to a project.

The project folder for an Analysis Services project can contain the file types listed in the following table.

File type

Description

Analysis Services project definition file (.dwproj)

Contains metadata about the items, configurations, and assembly references defined and included in the Analysis Services project.

Analysis Services project user settings (.dwproj.user)

Contains configuration information for the Analysis Services project, for a specific user.

Data source file (.ds)

Contains Analysis Services Scripting Language (ASSL) elements that define metadata for a data source.

Data source view file (.dsv)

Contains ASSL elements that define metadata for a data source view.

Cube file (.cube)

Contains ASSL elements that define metadata for a cube, including measure groups, measures, and cube dimensions.

Partition file (.partitions)

Contains ASSL elements that define metadata for the partitions of a specified cube.

Dimension file (.dim)

Contains ASSL elements that define metadata for a database dimension.

Mining structure file (.dmm)

Contains ASSL elements that define metadata for a mining structure and associated mining models.

Database file (.database)

Contains ASSL elements that define metadata for a database, including account types, translations, and database permissions.

Database role file (.role)

Contains ASSL elements that define metadata for a database role, including role members.

Analysis Services Item Templates

If you use the Add New Item dialog box to add new items to an Analysis Services project, you have the option of using an item template, a predefined script or statement which demonstrates how to perform a specified action.

The item templates, listed in the following table, are available in the Analysis Services Project Items category in the Add New Item dialog box.

Category

Item template

Description

Analysis Services Project Items

Cube

Starts the Cube Wizard to add a new cube to the Analysis Services project.

 

Data Source

Starts the Data Source Wizard to add a new data source to the Analysis Services project.

 

Data Source View

Starts the Data Source View Wizard to add a new data source view to the Analysis Services project.

 

Database Role

Adds a new database role to the Analysis Services project, and then displays Role Designer for the new database role.

 

Dimension

Starts the Dimension Wizard to add a new database dimension to the Analysis Services project.

 

Mining Structure

Starts the Data Mining Wizard to add a new mining structure and associated mining model to the Analysis Services project.

See Also

Concepts

Configure Analysis Services Project Properties (SSDT)

Build Analysis Services Projects (SSDT)

Deploy Analysis Services Projects (SSDT)