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 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.
