Azure Data Warehouse, Azure Databricks and Power BI

The modern data warehouse

The cloud requires to reconsider some of the choices made for on-premisses data handling. This module introduces the different services in Azure that can be used for data processing, and compares them to the traditional on-premisses data stack.

• From traditional to modern data warehouse
• Lambda architecture
• Overview of Big Data related Azure services
• Getting started with Azure

Staging data in Azure

This module discusses the different types of storage available in Azure Storage as well as data lake storage. Also some of the tools to load and manage files in Azure storage and Data lake storage are covered.

• Introduction Azure Blob Storage
• Compare Azure Data Lake Storage Gen 2 with traditional blob storage
• Tools for uploading data
• Storage Explorer, AZCopy, ADLCopy, PolyBase

Azure Data Warehouse

Azure SQL Databases have their limitations in compute power since they run on a single machine, and their size is limited to the Terabyte range. Azure Data Warehouse is a service aiming at an analytical workload on data volumes hundreds of times larger than what Azure SQL databases can handle. Yet at the same time we can keep on using the familiar T-SQL query language, or we can connect traditional applications such as Excel and Management Studio to interact with this service. Both storage and compute can be scaled independently.

• Architecture of Azure Data Warehouse
• Loading data via PolyBase
• CTAS and CETAS
• Setting up table distributions
• Indexing
• Partitioning
• Performance monitoring and tuning

Advanced data processing with Databricks

Azure Databricks allows us to use the power of Spark without the configuration hassle of Hadoop clusters. Using popular languages such as Python, SQL and R data can be loaded, visualized, transformed and analyzed via interactive notebooks.

• Introduction Azure Databricks
• Cluster setup
• Databricks Notebooks
• Connecting to Azure Storage and Data Warehouse
• Processing Spark Dataframes in Python
• Using Spark SQL
• Scheduling Databricks jobs

Introduction to Power BI

Power BI is a product that covers many things: Power BI in Excel, Power BI Desktop, the Power BI online service, ... . In this introduction we set the scene for the rest of the training, introducing the different aspects of the Power BI product.

• The need for Business Intelligence
• Self-Service BI versus Enterprise BI
• Power BI basics
• Overview of Power BI Desktop
• Introducing the Power BI Service

Creating Queries using Power BI Desktop

In this chapter you will learn how to create queries in Power BI Desktop to extract data from source systems like SQL Server, Oracle, Excel files, CSV files,... Applying transformations that can filter, sort and clean the extracted data will also be covered.

• Overview of supported Data Sources
• Importing data from Data Warehouse
• Loading data from Databricks
• Combining data from multiple sources
• Applying basic transformations
• Query Folding

Building a Data Model

Participants learn why and how to create a data model, including data sorting, data types and formatting, hiding tables and columns, creating hierarchies, ... . We will also have a look at the storage of data in import mode, including the techniques that are used for compression. We will see the Vertipaq analyzer as a tool to analyze and reduce storage costs.

• Why do we need a Data Model?
• Authoring data models in Power BI Desktop
• Data model storage
• Vertipaq Analyzer