Microsoft Docs

Tutorial: Create a model in Azure Analysis Services

This tutorial guides you through creating a tabular model in Azure Analysis Services using Visual Studio. This model will be used to store and analyze sales data.

Prerequisites

Steps

  1. Create a New Project

    In Visual Studio, go to File > New > Project.

    In the Create a new project dialog, search for "Analysis Services". Select the Tabular project template and click Next.

    Enter a Project name (e.g., SalesModel) and a Location. Click Create.

  2. Configure Project Properties

    In the Tabular model designer, you'll be prompted to configure the project properties.

    • Solution Explorer: Right-click on your project and select Properties.
    • Server Name: Enter the fully qualified domain name (FQDN) of your Azure Analysis Services server instance.
    • Database Name: Specify a name for your Analysis Services database (e.g., SalesDB).
    • Data Source Mode: For Azure Analysis Services, this is typically set to Import or DirectQuery. For this tutorial, we'll use Import.

    Click OK.

  3. Add Data Sources

    In Solution Explorer, right-click on Data Sources and select Add Data Source.

    Choose your data source type (e.g., SQL Server, Azure SQL Database). Enter the connection details, including the server name, database name, and credentials.

    Click Test Connection to ensure the connection is successful, then click OK.

    Note: Ensure your Azure Analysis Services server has network access to your data source. You might need to configure firewall rules or use a virtual network.

  4. Import Tables

    In Solution Explorer, right-click on Tables and select Add Table.

    The Table Import Wizard will open. Select the data source you created in the previous step.

    Choose the tables you want to import into your model. For this tutorial, select tables like SalesOrderHeader, SalesOrderDetail, Product, and Customer.

    Click Next to review the imported data and make any necessary adjustments. Click Finish.

    Important: Select only the necessary columns from your source tables to optimize model performance.

  5. Create Relationships

    Once tables are imported, the model designer will display them.

    Go to the Model menu and select Create Relationships.

    Drag and drop columns from one table to the corresponding column in another table to define relationships. For example, drag SalesOrderID from SalesOrderHeader to SalesOrderID in SalesOrderDetail.

    Ensure the relationships are correctly defined with appropriate cardinalities (one-to-many, one-to-one).

  6. Create Measures

    Measures are calculations performed on your data.

    In Solution Explorer, right-click on your project and select Add Measure.

    Alternatively, you can right-click on a table in the model designer and select New Measure.

    In the measure editor, enter a Name for your measure (e.g., Total Sales).

    Enter a DAX (Data Analysis Expressions) formula. For example, to calculate total sales:

    SUM('SalesOrderDetail'[LineTotal])

    Click OK.

  7. Deploy the Model

    Right-click on your project in Solution Explorer and select Deploy.

    The Analysis Services Deployment Wizard will open.

    In the Server name field, ensure your Azure Analysis Services server FQDN is correctly entered.

    In the Database name field, confirm the name of your Analysis Services database.

    Click Deploy.

    After a successful deployment, you can connect to your Azure Analysis Services server using tools like Power BI or Excel to analyze the data in your newly created model.

Tip: Regularly deploy your changes to your Azure Analysis Services server to keep your model up-to-date.