MSDN / SQL Server / Analysis Services / Multidimensional Modeling / Creating a Database

Creating a Database in SQL Server Analysis Services

This guide walks you through the process of creating a new multidimensional database within SQL Server Analysis Services (SSAS) using SQL Server Data Tools (SSDT).

Prerequisites

Steps to Create a New SSAS Database

1. Launch SQL Server Data Tools and Create a New Project

Open SQL Server Data Tools and create a new project. Select the Analysis Services Multidimensional Project template. Provide a name for your project and a location to save it.

2. Define a Data Source

Once the project is created, you need to define a connection to your source data. This is typically a relational database.

  1. In the Solution Explorer, right-click on the Data Sources folder and select New Data Source....
  2. The Data Source Wizard will launch. Click Next.
  3. Choose to create a new data source connection and click New....
  4. In the Connection Manager dialog, configure the server name, authentication mode, and database name for your source data. Click Test Connection to verify.
  5. Once the connection is successful, click OK, then Next.
  6. Review the connection details and click Finish.

3. Define a Data Source View

A Data Source View (DSV) is a logical representation of your relational data that Analysis Services will use. It allows you to select, rename, join, and manipulate tables from your source data before they are used in your multidimensional model.

  1. In the Solution Explorer, right-click on the Data Source Views folder and select New Data Source View....
  2. The Data Source View Wizard will launch. Click Next.
  3. Select the data source you created in the previous step from the dropdown list.
  4. From the Tables and Views pane, select the tables and views that contain the data you need for your analysis. You can use the search bar to find specific items.
  5. Click Next.
  6. Review the selected tables and views. You can rename them or create joins between them if they are not already defined in the source database.
  7. Click Finish.
Note: It's best practice to include only the necessary tables and columns in your Data Source View to optimize performance.

4. Creating the SSAS Database

The project itself represents your Analysis Services database. When you deploy the project, it will be created or updated on your SSAS instance.

The database name in SSAS will be derived from your project name by default. You can change this by:

  1. Right-clicking on the project name in Solution Explorer.
  2. Selecting Properties.
  3. Under the Deployment tab, you can change the Database Name property.
Tip: For clarity and organization, ensure your project name and chosen database name are descriptive and reflect the business domain it represents.

5. Next Steps

After creating your data source and data source view, you are ready to start designing the core components of your multidimensional model:

Related Topics