SQL Server Analysis Services Tabular Model

This course covers the areas to know in order to implement the SQL Server Analysis Services Tabular model. It includes installation, set up of analysis services, designing and implementing models up to deployment.

Course Outline

Day 1: Introduction to SSAS Tabular and Installation

Module 1: Overview of SSAS Tabular Modeling

• Introduction to SSAS Tabular Model

• Benefits and Use Cases

• Key Components and Architecture

Module 2: Installation and Configuration

• Installing SQL Server Analysis Services (SSAS)

• Installing via Setup Wizard

• Command Line Installation

• Required Tools and Designers

Lab 1: Installing and Setting Up SSAS Tabular

Module 3: Tabular Model Designer and Workspace Database

• Project Templates and Development Environment

• Windows and Menus in the Tabular Model Designer

• Configuring a Workspace Database

• Managing Workspace Database in SSMS

Lab 2: Creating a Workspace Database in SSMS

Day 2: Data Modeling and DAX Formulas

Module 4: Tabular Model Compatibility and Properties

• Compatibility Levels and Their Impact

• Setting and Checking Compatibility Level

• Default Properties in SSAS Tabular Models

Lab 3: Tabular Model Compatibility and Properties

Module 5: Importing Data into the Tabular Model

• Supported Data Sources (Cloud & On-Premises)

• Data Types in SSAS Tabular

• Handling Blanks, Impersonation, and Security

Lab 4: Importing Data from Multiple Sources

Module 6: Introduction to DAX (Data Analysis Expressions)

• DAX Formulas in Calculated Columns, Measures, and Row Filters

• DAX Data Types, Operators, and Functions

• Understanding Context in DAX

Lab 5: Creating Calculated Columns and Measures Using DAX

Day 3: Advanced Data Modeling Techniques

Module 7: Working with Tables, Columns, and Relationships

• Adding and Deleting Tables/Columns

• Changing Column Data Types and Filters

• Establishing Relationships and Cross Filters

Lab 6: Creating Relationships and Cross Filters in a Tabular Model

Module 8: DirectQuery Mode and Partitioning

• Benefits and Limitations of DirectQuery Mode

• Enabling DirectQuery in Visual Studio and SSMS

• Creating and Managing Partitions

Lab 7: Creating Partitions

Module 9: Hierarchies, KPIs, and Calculation Groups

Defining and Using Hierarchies in Tabular Models

• Creating and Managing KPIs

• Using Calculation Groups for Efficient Aggregations

Lab 8: Implementing DirectQuery and Testing in Excel

Day 4: Security, Perspectives, and Translations

Module 10: Implementing Security in SSAS Tabular

• Understanding Roles and Permissions

• Row-Level Security and Object-Level Security

• Defining and Testing Roles in SSMS

Lab 9: Implementing Role-Based Security and Object-Level Security

Module 11: Perspectives and Translations

• Creating and Managing Perspectives

• Importing and Exporting Translation Files

• Connecting to a Model Using Locale Identifiers

Lab 10: Creating Perspectives and Adding Translations

Day 5: Deployment, Processing, and Optimization

Module 12: Deploying and Processing SSAS Tabular Models

• Deployment Process from Visual Studio

• Deployment Properties and Best Practices

• Processing Models, Tables, and Partitions

Lab 11: Deploying and Processing a Tabular Model

Module 13: Monitoring and Optimization

• Performance Optimization Strategies

• Monitoring Query Execution (xEvents, SQL Profiler)

• Best Practices for Large-Scale SSAS Tabular Models

Lab 12: Performance Tuning and Monitoring Query Execution

NOTE:

1. Each module will have a module summary that includes the recap of key learnings and Q&A.

2. We will also be performing and learning the following:

   - Best Practices for Real-World SSAS Tabular Implementations.

   - Open Q&A Session on the last day.