DP-900: Microsoft Azure Data Fundamentals

Analyzing Data

Introduction

In this lesson, we dive into Online Analytical Processing (OLAP)—the backbone of modern data analysis. Unlike Online Transaction Processing (OLTP), OLAP systems are designed to:

CharacteristicDescription
No in-place updatesOnly a continuous load process appends new data; no record modifications or deletions.
Large-scale data handlingEfficiently processes terabytes of historical data to derive business insights and trends.
Optimized for read-heavy workloadsUses specialized storage engines tailored for fast aggregations and complex queries.

We’ll explore how OLAP transforms raw data into actionable intelligence by stitching together various Azure services.


Case Study: Sales Forecast Analysis

Consider a scenario where you must predict next year’s revenue. Your data sources include:

Data SourceStorage ServiceDescription
Sales forecastsAzure Blob StorageExcel files detailing each customer’s projected product volumes.
Actual salesAzure Cosmos DB Table StorageSemi-structured, globally replicated transaction records.
Customer & product mastersAzure SQL DatabaseStructured relational tables for customer and product details.

The image is a diagram titled "Analyzing Our Sales," showing Azure SQL with icons representing databases and shopping carts, indicating customer and product information stored as structured data.


Three Phases of Data Preparation (ETL)

  1. Extract
    Retrieve blobs from Azure Storage, tables from Cosmos DB, and rows from Azure SQL.

  2. Transform
    Standardize formats, cleanse data, and perform aggregations (e.g., sum of sales by region).

  3. Load
    Persist the cleansed and transformed dataset into a high-performance analytical store such as Azure Synapse Analytics or Azure Data Lake Storage Gen2.

Note

Learn more about ETL best practices in the Azure Data Factory documentation.

The image illustrates three kinds of tools for data management: data gathering and transformation, housing the data, and analyzing the selected data, each with corresponding icons and brief descriptions.

After loading, you can run complex queries and generate dashboards using tools like Power BI or Azure Analysis Services.

The image illustrates the Extract-Transform-Load (ETL) process, showing the flow from data extraction from various sources, through transformation, storage, and finally analysis and reporting.


Warning

Data transfer charges apply when moving data across Azure regions. To minimize egress fees, deploy your ETL pipelines and analytical storage within the same region as your source systems.

Watch Video

Watch video content

Previous
Roles and Responsibilities