Lesson 1: Creating a Basic Multidimensional Model

This lesson guides you through the fundamental steps of creating your first multidimensional model in SQL Server Analysis Services (SSAS). We will use Visual Studio with SQL Server Data Tools (SSDT) for this process.

Prerequisites

  • SQL Server 2019 or later installed with Analysis Services.
  • Visual Studio 2019 or later installed with SQL Server Data Tools (SSDT).
  • A sample database (e.g., AdventureWorksDW) available to connect to.

Steps to Create a Basic Multidimensional Model

1. Create a New Analysis Services Project

  1. Open Visual Studio.
  2. Go to File > New > Project....
  3. In the Create a new project dialog, search for "Analysis Services".
  4. Select Analysis Services Multidimensional Project and click Next.
  5. In the Configure your new project dialog:
    • Project name: Enter MyFirstModel.
    • Location: Choose a directory to save your project.
    • Solution name: Keep the default or enter a name.
  6. Click Create.

2. Create a Data Source

The data source defines the connection to your relational database.

  1. In the Solution Explorer, right-click on Data Sources and select New Data Source....
  2. In the Data Source Wizard, click Next.
  3. On the Select how to specify the connection string page, choose Create a data source from an existing connection and click Next.
  4. If you have an existing connection to your AdventureWorksDW database, select it. Otherwise, click New... to create one:
    • Server name: Enter your SQL Server instance name (e.g., localhost or SERVER\INSTANCE).
    • Authentication: Choose Windows Authentication or SQL Server Authentication.
    • Database name: Select your AdventureWorksDW database.
  5. Click Test Connection to verify.
  6. Click OK and then Next.
  7. On the Impersonation Information page, select the appropriate option (usually default). Click Next.
  8. On the Finish the Wizard page, give your data source a name (e.g., AdventureWorksDWDataSource) and click Finish.

3. Create a Data Source View

A Data Source View (DSV) is a logical representation of data from one or more data sources.

  1. In the Solution Explorer, right-click on Data Source Views and select New Data Source View....
  2. In the Data Source View Wizard, click Next.
  3. On the Select Data Source page, select the data source you just created (AdventureWorksDWDataSource) and click Next.
  4. On the Select Tables and Views page, expand the tables relevant to your model. For a basic model, select at least one fact table (e.g., FactInternetSales) and some related dimension tables (e.g., DimProduct, DimDate, DimCustomer, DimGeography).
  5. Click Next.
  6. On the Finish the Wizard page, name your DSV (e.g., AdventureWorksDWDSV) and click Finish.
Tip: For a basic model, focus on a single fact table and its directly related dimension tables.

4. Create a Cube

A cube is the core object in a multidimensional model, representing data for analysis.

  1. In the Solution Explorer, right-click on Cubes and select New Cube....
  2. In the Cube Wizard, click Next.
  3. On the Select Creation Method page, choose Use existing tables and click Next.
  4. On the Select Fact Table page, select your fact table (e.g., FactInternetSales) from the dropdown. The wizard will automatically suggest related dimension tables. Click Next.
  5. On the Select Dimension Tables page, verify the suggested dimensions and add or remove any as needed. For each selected table, you'll define a dimension. Click Next.
  6. On the Select Measures page, choose the numeric columns from your fact table that you want to analyze. These will become your measures (e.g., SalesAmount, OrderQuantity). Click Next.
  7. On the Finish the Wizard page, name your cube (e.g., InternetSalesCube) and click Finish.

5. Deploy the Model

Deployment compiles your model and deploys it to the Analysis Services instance.

  1. Right-click on the MyFirstModel project in Solution Explorer.
  2. Select Deploy.
  3. The Output window will show the progress. Ensure the deployment completes successfully. If there are errors, review the output for details.
Important: Make sure your project's Target Server property in the project settings points to your Analysis Services instance.

6. Browse the Cube

Once deployed, you can connect to your cube and explore the data.

  1. Right-click on the MyFirstModel project in Solution Explorer.
  2. Select Browse.
  3. The Cube Browser will open. You can drag and drop dimensions onto the rows/columns and measures onto the data area to create reports.

Conclusion

Congratulations! You have successfully created and deployed your first basic multidimensional model in SQL Server Analysis Services. In the next lessons, we will explore how to refine dimensions, define more complex measures, and enhance the model's structure.