Datasets, Dataflows & Semantic Models — Explain datasets, dataflows, and semantic models at a high level (conceptual)

Historical Context & Motivation

Before self-service business intelligence tools existed, enterprise reporting relied on rigid, centrally-managed data warehouses with long development cycles. Analysts who needed a new metric or a freshly joined table often waited weeks for IT to provision the change, creating a bottleneck that slowed decision-making across organizations. Microsoft Power BI emerged as part of a broader industry shift toward self-service analytics, empowering business users and developers alike to connect to data, shape it, and build interactive reports without requiring a full data-warehousing project. To make this vision practical at scale, Power BI introduced three layered abstractions—datasets, dataflows, and semantic models—each solving a distinct problem in the data-to-insight pipeline.

The core architectural question these abstractions answer is deceptively simple: How do you move data from heterogeneous sources into a form that supports fast, interactive, and governed analytics? Understanding the boundaries between datasets, dataflows, and semantic models is essential for designing Power BI solutions that are maintainable, performant, and secure—skills that map directly onto software-engineering principles like separation of concerns and single responsibility.

Core Principles & Definitions

At the highest level, Power BI's data architecture follows a pipeline pattern familiar to anyone who has studied compiler design or Unix pipelines: raw input is progressively refined through discrete stages, each of which adds structure and meaning. The three primary abstractions—dataflows, datasets (now formally called semantic models), and the broader semantic layer—each own a specific stage in this pipeline. Grasping their roles prevents the common anti-pattern of embedding all ETL logic and business rules into a single, monolithic .pbix file.

Visual Explanation — The Data Pipeline

Notice how each layer in the diagram encapsulates a distinct responsibility. The dataflow knows nothing about DAX measures or report visualizations; the semantic model is agnostic to whether data came from a SQL database or a REST API; and the reports simply issue DAX queries against the semantic model without caring how the underlying tables were populated. This decoupling is structurally identical to the layered architecture pattern taught in software engineering: each layer depends only on the layer immediately below it, and changes in one layer do not cascade upward unless the interface contract changes.

How Each Abstraction Works Under the Hood

Dataflows — Cloud-Hosted ETL

A dataflow is authored in the Power BI Service (or Power Apps) through a browser-based Power Query editor. Each dataflow contains one or more entities—analogous to tables—that are defined by M-language scripts. When the dataflow refreshes, the Power BI backend spins up a compute engine that connects to the specified sources, executes the M transformations, and writes the results as compressed Parquet/CSV files into a Common Data Model (CDM) folder structure in Azure Data Lake Storage Gen2. Because the output resides in open-format storage, other services—Azure Synapse, Databricks, or even a second Power BI workspace—can also consume it, turning the dataflow into a reusable data contract.

Semantic Models — The VertiPaq Engine

When data is imported into a semantic model, the VertiPaq engine (also called the xVelocity in-memory analytics engine) compresses each column using a combination of run-length encoding (RLE) and dictionary encoding. Low-cardinality columns (e.g., country codes) compress extremely well under RLE, often achieving 10:1 or better compression ratios, while high-cardinality columns (e.g., transaction IDs) fall back to dictionary encoding. This columnar storage model is optimized for the scan-and-aggregate access pattern typical of analytical queries, and it is the reason Power BI can perform sub-second aggregations over millions of rows on commodity hardware.

DirectQuery vs. Import Mode

Not every semantic model imports data into VertiPaq. In DirectQuery mode, the semantic model retains the schema, relationships, and DAX measures but stores no data locally. Instead, each visual interaction generates a DAX query that is transpiled into the source system's native query language (e.g., T-SQL for SQL Server). This trades latency for freshness—every visual renders with real-time data, but aggregation performance depends entirely on the source system's capabilities. Composite models blend both modes within a single semantic model, importing high-volume fact tables while keeping frequently-changing dimension tables in DirectQuery.

Detailed Breakdown — Dataflows Gen1 vs. Gen2 and Semantic Model Connectivity Modes

The distinction between Gen1 and Gen2 dataflows matters architecturally. Gen1 dataflows store their output as CDM-formatted files in Azure Data Lake, which other Power BI datasets then import. Gen2 dataflows, embedded in Microsoft Fabric, can write directly to a Lakehouse backed by Delta tables, enabling Spark-based downstream processing without re-ingestion. From a systems-design perspective, Gen2 reduces the number of data copies in the architecture—an application of the single writer principle that minimizes consistency bugs.

For semantic models, the connectivity mode determines the caching strategy. Import mode is analogous to an eagerly populated cache: data is fully materialized in RAM at refresh time, yielding sub-second query performance but potentially stale data between refreshes. DirectQuery is a cache-miss strategy: every query hits the source, so data is always current at the expense of latency. Composite mode is a hybrid cache—hot tables imported, cold tables queried on demand—echoing L1/L2 cache hierarchies in CPU architecture.

Worked Example — Designing a Multi-Source Sales Analytics Solution

Suppose you are a data engineer at a mid-size e-commerce company. Sales transactions live in an Azure SQL Database, product catalog data is in a SharePoint list, and marketing spend data arrives as weekly CSV exports from a third-party vendor. You need a Power BI solution that lets analysts explore revenue by product category, compare marketing ROI across channels, and filter by region—all from a single dashboard that refreshes daily.

Strengths, Limitations & Trade-Offs

Connection to Microsoft Fabric & Advanced Theory

Microsoft Fabric represents the next evolutionary step for these abstractions. In Fabric, the boundaries between dataflows, semantic models, and the underlying storage are further blurred by the OneLake unified storage layer, which provides a single copy of data in Delta Parquet format accessible by every Fabric workload—Data Engineering (Spark), Data Warehousing (T-SQL), Data Science (notebooks), and Power BI. This eliminates the CDM-to-VertiPaq ingestion hop that Gen1 dataflows required, reducing data duplication and refresh latency. The semantic model in Fabric can issue Direct Lake queries—a new mode that reads columnar data directly from Delta tables in OneLake without a full import refresh, achieving near-Import performance with near-DirectQuery freshness.

Looking forward, the trajectory is clear: the industry is converging on open table formats (Delta, Iceberg) as the canonical storage layer, with semantic models becoming thin, metadata-rich overlays that define business meaning without owning the data. This mirrors the evolution from thick-client database applications to modern REST API architectures where the schema and business rules live in a service layer while the raw data lives in a shared data store. As a computer science student, understanding this architectural pattern prepares you not just for Power BI, but for any modern data platform—Snowflake, Databricks, Google BigQuery—all of which are adopting similar semantic-layer strategies.

Practice Problems

Lesson Summary