SQL Server Analysis Services

Documentation: Modeling

Create Databases in SQL Server Analysis Services

This document guides you through the process of creating new databases within SQL Server Analysis Services (SSAS). Databases in SSAS are containers for your analytical models, such as tabular models or multidimensional models.

Using SQL Server Data Tools (SSDT)

SQL Server Data Tools (SSDT) is the primary tool for developing SSAS databases. It provides a rich, integrated environment for designing, deploying, and managing your Analysis Services projects.

Step 1: Launch SQL Server Data Tools

Open SSDT. If you have multiple versions installed, ensure you are using the version compatible with your SQL Server Analysis Services instance.

Step 2: Create a New Analysis Services Project

  1. Go to File > New > Project....
  2. In the New Project dialog box, expand Business Intelligence, and then select Analysis Services.
  3. Choose the appropriate project template:
    • Analysis Services Tabular Project for tabular models.
    • Analysis Services Multidimensional and Data Mining Project for multidimensional models.
  4. Enter a Name for your project (e.g., SalesAnalysisTabular or CompanyDWMultidimensional).
  5. Choose a Location for your project files.
  6. Click OK.

Step 3: Configure the Project Settings

After creating the project, you'll be prompted to configure project settings. This is where you define the SSAS database name and the server instance to which you will deploy.

  • Database Name: This will be the name of your SSAS database. It's often derived from your project name but can be customized.
  • Server Name: Enter the name or IP address of your SQL Server Analysis Services instance. For local development, this is typically localhost or the name of your SSAS instance.

You can modify these settings later by right-clicking the project in Solution Explorer and selecting Properties.

Step 4: Design Your Model

Once the project is created and configured, you can start designing your model. This involves:

  • Connecting to data sources.
  • Defining tables, relationships, calculations, and other model elements.
  • For multidimensional models, creating dimensions, measures, and cubes.
  • For tabular models, creating tables, relationships, and DAX measures.

Refer to the specific documentation for tabular or multidimensional model design for detailed guidance.

Step 5: Deploy the Database

After designing your model, you deploy it to the Analysis Services server. This process creates the actual SSAS database on the server.

  1. Right-click the project in Solution Explorer.
  2. Select Deploy.

SSDT will build the project and create the database on the specified Analysis Services instance.

Note

When you deploy a project for the first time, SSDT creates the specified Analysis Services database. Subsequent deployments will update the existing database.

Using SQL Server Management Studio (SSMS) for Existing Databases

While SSMS is not the primary tool for *creating* new SSAS databases from scratch (that's SSDT's role), it is essential for managing and interacting with existing SSAS databases.

You can use SSMS to:

Tip

For advanced scenarios or scripting, you can use AMO (Analysis Management Objects) or the TOM (Tabular Object Model) via PowerShell or C# to programmatically create and manage Analysis Services databases.

Key Considerations