Training » Course

Zero to SQL Server 2017 Analysis Services

This three-day course empowers BI developers to design, develop and publish enterprise-grade models. It focuses on multidimensional (aka cubes) and tabular development design approaches.

Attendees will learn to:

  • Describe the deliverables of SQL Server 2017 Analysis Services
  • Design, develop and manage multidimensional models (aka cubes)
  • Design, develop and manage tabular models (using the Tabular project)
  • Optimize tabular models for Power BI
  • Translate business requirements into the appropriate model designs

Duration

3 days

Products

This course is based on SQL Server 2017 Analysis Services. It is also relevant to SQL Server 2016, SQL Server 2019 and Azure Analysis Services (tabular models only).

This course does not focus on Multidimensional Expressions (MDX), Data Analysis Expressions (DAX) or Power BI service report development.

Prerequisites

The organization must have:

  • A SQL Server 2017 relational database instance, to host the lab database, granting attendees with data read privileges
  • An instance of SQL Server 2017 Analysis Services, multidimensional mode, granting attendees with admin privileges
  • An instance of SQL Server 2017 Analysis Services, tabular mode, granting attendees with admin privileges

Attendees must have:

  • A general knowledge of data storage technologies and formats, including relational databases
  • Report development skills, with any analytic reporting tool

Attendees are required to have their own computer that must meet minimum requirements:

  • A Windows 7 computer, or later, network-enabled for Internet access
  • SQL Server Data Tools (SSDT) for SQL Server 2017 (Visual Studio 2015, or later)
  • SQL Server Management Studio (SSMS), latest version
  • Microsoft Office 2010, or later
  • Power BI Desktop, latest version—either 32- or 64-bit

Delivery Format

A classroom delivery includes:

  • Instructor presentation and demonstrations
  • Course book
  • Labs and activities

Course Outline

Module 01: Introducing Analysis Services Modeling

  • Microsoft’s BI Strategy and Vision
  • Introducing Analysis Services
  • Introducing Design Approaches
  • Describing Features by Edition
  • Describing Data Model Capabilities

Module 02: Developing Multidimensional Models

  • Preparing the supporting schema and data
  • Creating a Multidimensional and Data Mining Project
  • Developing data sources and data source views
  • Developing dimensions
  • Developing cubes
  • Enhancing the cube design, with calculations, KPIs, actions, perspectives and translations
  • Configuring storage, processing and permission requirements
  • Deploying the project

Lab 02A – Developing the Data Source View
Lab 02B – Developing the Date Dimension
Lab 02C – Developing Additional Dimensions
Lab 02D – Developing the Cube
Lab 02E – Enhancing the Cube with Calculations
Lab 02F – Enhancing the Cube with Features
Lab 02G – Deploying and Managing the Multidimensional Database

Module 03: Developing Tabular Models

  • Preparing the supporting schema and data
  • Creating a Tabular Project
  • Configuring the model query mode
  • Defining queries to load model tables
  • Defining relationships
  • Enhancing the model design, with calculations, KPIs, actions, perspectives and translations
  • Configuring storage, processing and permission requirements
  • Deploy the project

Lab 03A – Creating the Tabular Project
Lab 03B – Enhancing the Tabular Model
Lab 03C – Managing the Tabular Model

Module 04: Optimizing Models for Power BI

  • Leveraging Power BI
  • Optimizing Models for Power BI

Activity 04B – Optimizing the Tabular Model for Power BI
Activity 04A – Exploring Live Connections

Module 05: Comparing Design Approaches

  • Selecting the Design Approach
  • Comparing Design Approaches

Certification

This course has not been designed to prepare attendees for Microsoft certification. However, the course content can serve as a useful resource for introducing theory, products, capabilities and recommended design practices that are assessed by Microsoft certification exams.

Cartoon depiction of a business analyst integrating different data formats

Peter’s enthusiasm and excitement made the course very engaging. I can already imagine how SSAS will help me model my data.

Robert

To the point. Informative and concise.

Adrian

Thanks, Peter very well delivered.

Anu