Create a Model in Azure Analysis Services
This article guides you through the process of creating a new tabular model in Azure Analysis Services using SQL Server Data Tools (SSDT).
Prerequisites
- An Azure Analysis Services server instance.
- SQL Server Data Tools (SSDT) installed. You can download it from the Visual Studio Marketplace. Ensure you have the latest version.
- Access to the data source(s) you want to use for your model.
Steps to Create a Model
-
Open SQL Server Data Tools
Launch SSDT from your Start Menu.
-
Create a New Project
In SSDT, go to File > New > Project.
In the New Project dialog box, navigate to Business Intelligence > Analysis Services.
Select the Tabular Project template.
Enter a Name for your project (e.g.,
MySalesModel) and choose a Location. Click OK. -
Configure the Model in the Wizard
The Model Designer will open. You'll be prompted to choose a connection mode:
- Import from a database (Tabular): Recommended for new models. This creates a new model and imports data from an existing source.
- Connect Live (Tabular): Connects directly to an existing Analysis Services or Power BI semantic model.
- Import from a Tabular Model (Tabular): Imports from an existing tabular model file.
For this guide, select Import from a database (Tabular).
-
Specify Connection Manager
Click the New... button under Data Sources to create a connection to your data source.
In the Table Import Wizard:
- Select Data Source Type: Choose the appropriate source (e.g., SQL Server, Azure SQL Database, Oracle).
- Specify Server Name and Database Name: Enter the details for your data source.
- Authentication: Select the appropriate authentication method and provide credentials.
- Select Tables and Views: Choose the tables and views you want to import into your model. You can also transform data using Power Query here if needed.
Click Finish when you're done selecting tables.
-
Create and Deploy the Model
Once the data is imported, SSDT will generate the model structure. You can now:
- Add Relationships: Define relationships between your imported tables in the Diagram View.
- Create Measures: Write DAX expressions to create calculations.
- Create Hierarchies: Organize data for better navigation.
- Define Roles: Set up row-level security.
When your model is ready, right-click on the Solution Explorer project and select Deploy.
In the Analysis Services Deployment Wizard:
- Server: Enter the name of your Azure Analysis Services server.
- Database: You can create a new database or overwrite an existing one.
Follow the remaining steps to deploy your model.