Creating Models in Azure Analysis Services

This document guides you through the process of creating and managing tabular models in Azure Analysis Services. These models represent your business data in a way that is easy to consume by business intelligence tools like Power BI and Excel.

Overview of Tabular Models

Tabular models store data in memory using a columnar database engine (VertiPaq) optimized for analytical workloads. They are designed to provide fast query performance for complex analytical queries. Key components include:

• Tables: Collections of related data, similar to tables in a relational database.
• Columns: Attributes that describe the data within a table.
• Relationships: Links between tables that enable data to be joined for analysis.
• Measures: Calculations defined using DAX (Data Analysis Expressions) to aggregate data.
• Hierarchies: Structures that allow users to drill down and up through different levels of data granularity.

Steps to Create a Model

You can create tabular models using several tools, with Visual Studio and SQL Server Data Tools (SSDT) being the primary development environment for complex models. For simpler models or direct integration, you might also use tools like Power BI Desktop.

Using Visual Studio with SSDT

This is the recommended approach for professional model development.

1. Install Visual Studio and SSDT: Ensure you have Visual Studio installed with the SQL Server Data Tools workload.
2. Create a New Project: In Visual Studio, create a new project and select "Analysis Services Tabular Project."
3. Connect to Data Sources: Add a new data source connection to your operational databases (e.g., Azure SQL Database, SQL Server) or data lakes.
4. Import Data: Use the Table Import Wizard to select tables and columns from your data source and bring them into your model.
5. Define Relationships: Create relationships between tables to define how data should be joined.
6. Create Measures and Calculated Columns: Use DAX to define business logic, aggregations, and new calculated fields.
7. Create Hierarchies: Organize columns into hierarchies for user-friendly navigation.
8. Deploy the Model: Deploy your project to an Azure Analysis Services instance.

Using Power BI Desktop

For models that will be primarily consumed by Power BI, you can start in Power BI Desktop.

• Connect to your data sources.
• Shape and transform your data using Power Query.
• Create relationships between tables.
• Define measures using DAX.
• Publish the report to Power BI Service. You can then create an Azure Analysis Services model that consumes data from the Power BI dataset, or create a separate AAS model and connect Power BI to it.

Key Concepts in Model Design

Data Modeling Best Practices

A well-designed model is crucial for performance and usability. Consider the following:

• Star Schema: Organize tables into fact tables (containing metrics) and dimension tables (containing descriptive attributes).
• Data Types: Use appropriate data types for columns to optimize storage and performance.
• Relationships: Ensure relationships are correctly defined with appropriate cardinality and cross-filter direction.
• Naming Conventions: Use clear and consistent naming for tables, columns, and measures.

DAX (Data Analysis Expressions)

DAX is the formula language used in Analysis Services and Power BI. It's essential for creating powerful calculations.

Common DAX functions include:

• Aggregation functions: SUM(), AVERAGE(), COUNT()
• Filter functions: CALCULATE(), FILTER()
• Time intelligence functions: TOTALYTD(), SAMEPERIODLASTYEAR()

-- Example DAX measure for Total Sales Amount
Total Sales = SUM(Sales[SalesAmount])

-- Example DAX measure for Year-over-Year Sales Growth
YoY Sales Growth =
VAR CurrentYearSales = [Total Sales]
VAR PreviousYearSales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
    DIVIDE(CurrentYearSales - PreviousYearSales, PreviousYearSales)

Managing Models

Once deployed, you can manage your models through the Azure portal, using tools like SQL Server Management Studio (SSMS) with Analysis Services support, or programmatically via TOM (Tabular Object Model) or the Analysis Services REST API.