Analysis Services for Data Professionals

Learn to create user-friendly analytic models with SQL Server Analysis Services and Azure Analysis Services

Unlock your potential with Analysis Services!

Course Summary

Are you ready to elevate your skills in working with Analysis Services?

Our comprehensive course is your gateway to becoming proficient at working with both SQL Server Analysis Services, and with Azure Analysis Services. Ask Yourself:

Have you worked with Analysis Services?
Are you looking to get the best out of Analysis Services by building great data models?
Do you have existing multi-dimensional models and need to move to tabular models?
Would you like to be comfortable with how security applies to Analysis Services?
Are you ready to move beyond the basics and learn from an expert?

If any of these relate to you, this course is tailor-made for you! Join us for detailed instruction, engaging practical exercises, and quizzes designed to reinforce your learning.

Don’t miss this opportunity to invest in your career and unlock your full potential in working with Analysis Services! This is one of the most comprehensive courses on the market today.

Enroll now for $295 USD !

Modules and Lessons

Module 0: Getting started

Who is this course for ?
Who is Greg ?
What will I learn in this course ?
Configuring your lab environment

Module 1: Core concepts and tools

Module introduction
What is Analysis Services?
Semantic models in Analysis Services
Choosing tabular vs multi-dimensional
Tabular storage
SQL Server Analysis Services
Azure Analysis Services
Tabular data models in Power BI
Azure AS vs Fabric
Choosing data access modes
Providing suitable source data
Using Analysis Services tooling
SSMS
SQL Server Integration Services
Azure Data Factory
Tabular Editor
DAX Studio
Vertipaq Analyzer
Module 1 quiz

Module 2: Installing or provisioning Analysis Services

Module introduction
Installation sources for SQL Server AS
SQL Server components and instances
Choosing editions
Default vs named instances
Azure Extension
Feature selections and instance configuration
Service accounts and collations
Server and folder configuration
Applying cumulative updates
Viewing and reconfiguring installed instances
Connecting with SSMS
Removing SQL Server Analysis Services
Creating a Resource Group
Provisioning Azure Analysis Services
Connecting SSMS to Azure AS
Using multi-factor authentication
Module 2 quiz
Module 2 lab

Module 3: Creating tabular projects

Module introduction
Analysis Services project templates
Import from PowerPivot
Creating projects and solutions
Configuring workspace databases
Default workspace datatabase
Choosing compatibility levels
Setting project properties
Setting data model properties
Authenticating to Analysis Services
Authenticating to data sources
Changed menu locations
Key development windows
Using Get Data to import data
Deploying a tabular project
Connecting to the deployed model
Module 3 quiz
Module 3 lab

Module 4: Loading and transforming data

Module introduction
Introduction to Power Query
Power Query for Excel
Available data sources in AS
Editing queries to transform data
Editing query settings and steps
Available transformations
Context menus
Adding columns
M language introduction
M language reference
Appending queries
Merging queries
Using queries as functions
Altering data sources
Altering permissions
Module 4 quiz
Module 4 lab

Module 5: Developing data models

Module introduction
Working with data and diagram views
Adding additional tables
Changing existing tables
Filtering table data
Configuring relationships
Relationship filter directions
Relationship properties
Introduction to the DAX language
Table and column references
DAX operators
Creating calculated columns
Correct level for calculated columns
Creating measures
CALCULATE function
Data model vs report measures
Hiding columns from clients
Creating hierarchies
Data access modes
Minimizing model size
Connecting to Analysis Services with Excel
Module 5 quiz
Module 5 lab

Module 6: Creating suitable source data models

Module introduction
Ideal source data model
Analytics over existing relational systems
Naming issues
Schema design issues
Invalid data
Missing data
Data present but not real
Rounding issues
Module 6 quiz

Module 7: What we do

Module introduction
Prefer cloud-based DB
DataLoad schema
SDU_Tools schema
Staging schema
External tables
DataModel schema
Analytics schema
Tabular data model
Power BI
Reporting Services
Azure Data Factory
SSIS
Module 7 quiz

Module 8: Enhancing data models

Module introduction
Creating date tables
Creating tables from SQL queries
Mark as date table
DAX time intelligence
Creating calculated tables
Auto-date table functions
Creating static tables
Allowed data types
Creating and managing KPIs
Configuring table behavior
Configuring data categories
Configuring data formats
Configuring data types
Configuring encoding hints
Using other columns for sorting
Configuring summarization
Configuring other column properties
Creating calculation groups
USERELATIONSHIP
Module 8 quiz
Module 8 lab

Module 9: Processing deployed data models

Module introduction
Processing core concepts
Database processing options
Table processing options
Partition processing options
Scripting processing steps
Scripting databases with TMSL
Scripting databases with TMDL
Processing from agent jobs
Processing from SSIS
Processing options for Azure AS
Introduction to Azure Data Factory
Editing pipelines in ADF
Scheduling pipelines with ADF triggers
Azure AS REST API
Processing Azure AS from ADF
Module 9 quiz
Module 9 lab

Module 10: Securing data models

Module introduction
Overview of Analysis Srvices security
Configuring role based security
Adding role members
Role table permissions
Role row filters
Dynamic role row filters
Testing row filters
Configuring object level security
Identity aims
Introduction to Microsoft Entra ID
Microsoft Entra ID directory models
Microsoft Entra ID Connect
Managed service identities
Assigning ADF permissions to Azure AS
Assigning ADF permissions to SQL Database
Minimizing database attack surfaces
Configuring Azure server firewalls
Enterprise and personal gateways
Typical security architecture
Module 10 quiz
Module 10 lab

Module 11: Advanced Analysis Services concepts

Module introduction
Creating perspectives
Configuring translations
Loading performance and partitions
Database table partitions
Tabular data model partitions
Avoiding string collation issues
Tracing server activity
Backup and restore databases
Querying dynamic management views
Common useful DMV groups
AMO core concepts
XMLA core concepts
Checking database consistency
TOM and TMSL core concepts
Integrating with source control
Extracting VPAX files for optimization
Module 11 quiz

Module 12: Next steps

Summary and further steps

Enroll now for $295 USD !