Dimensional Modeling with Azure Analysis Services

Introducing Analysis Services Tabular

In a world where self-service business intelligence becomes more and more important it becomes crucial to understand the power of Online Analytical Processing (OLAP). In this introduction we show the key importance of OLAP systems and discuss the key differences between two OLAP systems that Microsoft Analysis Services provides: Tabular and multi-dimensional.

• What is Online Analytical Processing?
• Microsoft Analysis Services

The need for Data Modeling

When building a business intelligence solution, data has different modeling requirements than what is needed for operational applications. This module describes the challenges that can arise when storing data, and how good data models can overcome these. It presents the data model as an abstraction layer between the operational part and the analytical part of the corporate IT stack.

• Data model definitions and terminology
• Advantages and disadvantages of normalizing data
• Issues when reporting on operational databases
• Differences between data warehouse, data flow, and data set
• Data model advantages
• Conducting user interviews
• Prioritizing the requirements
• Documenting the requirements
• LAB: Compare different data models

Modeling Dimensional Tables

Dimensional modeling is based on the idea of splitting data in the things that are (dimensions) and the things that happen (facts). This module describes the properties of dimension tables: Keys, attributes, hierarchies, ... .

• Introducing dimensional modeling
• The need for dimension tables
• Properties of dimension tables
• Surrogate keys
• Star versus snowflake dimensions
• LAB: Building dimensional tables

Modeling Factual Tables

Dimensional modeling is based on the idea of splitting data in the things that are (dimensions) and the things that happen (facts). This module describes the most common types of fact tables

• Properties of Fact tables
• Additive, Semi-additive and none-additive measures
• Transactional Fact Table
• Snapshot Fact Table
• Accumulating Snapshot Fact Table
• Inspecting the size of the fact tables in Power BI
• LAB: Building fact tables

Designing the Date Dimension

Everybody wants to analyze over time, so this is the most common dimension in data models. Based on the principles introduced in the previous module a date dimension is built.

• Why do we need a date dimension
• The danger of the auto-generated date tables in Power BI
• Designing the date dimension
• Working with Time
• Multiple time zones
• Useful tools and scripts
• LAB: Building a Date dimension in Power BI

Working with Slowly Changing Dimensions (SCD)

Since the dimension tables describe the things that are, they don't change as often as fact tables. Yet also properties of products, customers and other dimensions change over time as well. The different ways to register this in the data warehouse are discussed in this module.

• Why do we need Slowly Changing Dimensions
• Type I, II and III slowly changing dimensions
• Designing for SCD
• Advanced SCD types
• LAB: Working with Slowly Changing Dimensions

Building Tabular Models

Tabular models are developed in Visual Studio. We can start creating models from scratch or we can use models business people already made in Excel and import them in Visual Studio. Not only do you learn to build a basic model, this module also discuss how to extend the model with objects such as display folders, key performance indicators, meta-data translations and more!

• Creating SSAS Tabular projects in Visual Studio
• Creating databases from scratch Using Power Query (M)
• M versus T-SQL
• Linking tables
• Building hierarchies
• Extending the model with KPIs, translations, ...
• Working with Direct Query
• LAB: Building a tabular model in Visual Studio

An Introduction to DAX

This chapters provides a introduction to the DAX language. Students will learn the different use-cases of the DAX language.

• DAX use cases
• Understanding calculated columns and measures