SQL Server Analysis Services 2017 Tabular & Azure AS

Azure Analysis Services and SQL Server Analysis Service 2017 Tabular Foundations 3-day training course.  This course may be adapted (2 to 4 days) and combined with related training modules on DAX, Power Query & Power BI.

Objectives:

Learn to plan, develop and deploy AAS & SSAS Tabular solutions.  Understand the relationship between: SSAS multidimensional, SSAS Tabular, Power Pivot & Power BI.  Access and use the Azure Portal and AAS console.  Understand the unique features & capabilities of AAS & SSAS Tabular and the business cases for each.   Realize the advantages & trade-offs with storage modes.

Topic Outline:

  • Prerequisites and Setup Steps
  • Azure Analysis Services Overview
  • Introducing Azure Analysis Services
  • Tabular Model Design
  • AAS: part of the larger Azure universe
  • Planning the Solution
  • Azure Analysis Services Solutions
  • Reporting & Usability Requirements
  • To Cache, or Not to Cache
  • Tabular Model Design
  • Setting up the Environment
  • Development & Management Tools
  • The Big Two
  • Tools and Data Sources
  • Integrated Workspace vs Workspace Server
  • Project Planning & Setup Checklist
  • Creating an AAS Instance
  • Finding AAS in the Azure Portal
  • Create the AAS Server
  • AAS Server Address
  • Common Settings
  • Migrating a Power BI Model to AAS
  • Getting Started with SQL Server Data Tools
  • Analysis Services Tabular Project
  • Workspace Database
  • Get Data & Power Query
  • Extract Transform Load Process
  • Power Query SSDT Integration
  • Transformations Available
  • Power Query SSDT Integration
  • Data Model Table Queries
  • Views or Power Query? (Try to Pick One)
  • Import from Data Source
  • Create Data Model Tables
  • Loading & Processing
  • Power Query Checklist
  • Data Modeling Essentials
  • Tabular Model Design
  • Relationships
  • Creating Relationships
  • Hiding Key Fields Users Don’t Care About
  • Multi-Select to Make Life Easier
  • Analyze with Excel
  • Pivot Table Basics
  • Data Modeling Checklist
  • Calculations: Columns and Measures
  • Calculated Columns
  • Measures (Aggregation Algorithms)
  • Designing Measures in SSDT
  • Hands On: Ragged Chart of Accounts
  • Ragged Hierarchies & Blank Level Members
  • Creating a Hierarchy
  • Deploying & Processing
  • Deployment: Almost Live!
  • Project Deployment Server
  • Deploy & Process
  • Scripting Processing Options
  • Tabular Modeling Script Language (TMSL)
  • Generating TMSL Processing Script
  • Generating Partition Processing Script
  • Processing with PowerShell
  • Designing Partitions
  • Table Partitions
  • Common Partition Strategies
  • Table Partitions
  • Data Storage and Performance
  • Database Objects vs Power Query
  • SQL Server Table-valued UDF for partitioning (examples)
  • Create Multiple Parameters
  • Modern Partition Design with Power Query
  • Check for Query Folding
  • Data Storage and Performance
  • Sort Optimization
  • Dynamic Duo: Storage and Formula Engine
  • Browsing & Reporting with AAS
  • Connecting to AAS from Excel
  • Creating a Connection
  • Creating Another Pivot Table
  • Converting to Cube Functions
  • Connecting to AAS from Power BI
  • On-premises Gateway
  • Gateway Flow
  • Gateway Requirements
  • Row Level Security
  • Row Level Security, Simple
  • Row Level Security, Dynamic
  • Perspectives
  • Questions & Answers
  • Performing Analysis on an AAS Source
  • Basic DAX (hands-on)
  • Advanced DAX
  • AAS Database & Server Management
  • Resources