Week 9: Power BI Fundamentals — Importing & Modeling Data

1. The Power BI Desktop Tour

Power BI Desktop (free, Windows-only) opens into three views, switchable from icons down the left edge:

Report view — the canvas where visuals get placed and arranged; this is what gets published and what viewers actually see.
Data view — a plain grid of every loaded table's rows, the same shape as an Excel sheet, useful for spot-checking that data loaded the way you expected.
Model view — a diagram of every loaded table and the relationship lines between them; this is where Section 5's relationships get built and inspected.

The Fields pane on the right lists every loaded table and column regardless of which view is active — dragging a field from there onto the Report canvas is how a visual gets built, starting in Week 11. The ribbon's Home tab holds the two buttons that matter most this week: Get Data (Section 2) and Transform Data (Section 3).

2. Connecting to Data Sources

Get Data opens a searchable list of connectors — Excel Workbook, Text/CSV, and a database connector (SQL Server, PostgreSQL, MySQL all have native ones) cover most of what this course has produced so far. Picking a database connector prompts for a server and database name, then asks a question that matters more than it looks:

Import mode — Power BI copies a snapshot of the data into the report file itself. Fast to interact with once loaded, but it only reflects the data as of the last refresh (Week 8's materialized-view trade-off, one layer up).
DirectQuery mode — no data is copied; every filter or click sends a live query back to the source database. Always current, but only as fast as the source database answering that query — and every DAX measure written in Week 10 becomes a query the source has to run.

After connecting, a preview window offers Load (bring the table in as-is) or Transform Data (open Power Query first) — almost every real dataset benefits from at least a quick pass through the latter.

3. Power Query in Power BI

This is the exact same engine and the exact same Applied Steps panel from Week 4's Excel Power Query — the skills transfer directly, just aimed at a slightly different job: preparing data before it becomes part of a model, rather than before a PivotTable.

Promoting the first row to headers, fixing column data types (a "Sales" column imported as text won't sum in a visual)
Removing helper or export-artifact columns (blank rows, a repeated report title row at the top of a CSV export)
Appending several monthly CSV exports into one combined table — the same Append Queries operation from Week 4, now feeding a model instead of a single sheet

Once satisfied, Close & Apply runs every step and loads the result into the model — from that point on, the Fields pane on the right shows the cleaned table, not the raw import.

4. Star Schema: Facts & Dimensions

Weeks 1–8 mostly worked against flat, denormalized tables — every row repeating customer and category details, exactly the shape Week 5 moved away from in SQL. A Power BI model does the same split, and gives the two resulting table types names:

Fact table — one row per event or transaction (an Orders table: one row per order, holding measures like amount and foreign keys like customer_id).
Dimension tables — descriptive lookup tables the fact table's keys point to (Customers, Products, and almost always a dedicated Date table).

Arranged with one fact table in the middle and its dimensions around it, the diagram in Model view visually resembles a star — hence the name. This shape isn't just tidiness: DAX measures (Week 10) and visuals (Week 11) both perform dramatically better, and stay far easier to reason about, against a proper star schema than against one giant flat export.

A snowflake schema is a star schema taken one step further

If a dimension table itself gets split into smaller lookup tables (e.g. Products pointing to a separate Category table), the diagram starts to resemble a snowflake instead of a star. It's a valid extension of the same idea — usually reached for only once a dimension itself gets genuinely large or reused elsewhere.

5. Relationships & Cardinality

In Model view, dragging a key column from a fact table onto the matching column in a dimension table draws a relationship line between them — the same primary/foreign key idea from Week 5's SQL joins, expressed visually instead of in an ON clause. The Manage Relationships dialog (Home tab) shows every relationship in the model and lets each one be edited directly.

Two settings on every relationship matter immediately:

Cardinality — almost always one-to-many, with the dimension table (one row per customer) on the "one" side and the fact table (many orders per customer) on the "many" side.
Cross-filter direction — Single means filtering the dimension filters the fact table (selecting a customer filters their orders); Both lets it work in reverse too, which is occasionally needed but easy to misuse.

Bi-directional filtering can create ambiguous filter paths

Setting cross-filter direction to Both on several relationships at once can let a filter reach a table through more than one path, and Power BI won't always resolve that the way it looks like it should. Leave relationships as Single direction by default, and only switch to Both when a specific, well-understood need for it shows up.

6. Hands-on Exercise

Hands-on

Build a star-schema model in Power BI Desktop

Import Week 5's orders, customers and a small products table (as CSV exports, or connected directly to your SQL database) and model them properly.

Requirements:

Get Data into three tables: Orders (fact), Customers (dimension), Products (dimension).
In Power Query, fix at least one data type issue and remove any blank or header-artifact rows before Close & Apply.
In Model view, create relationships: Orders[customer_id] → Customers[customer_id], and Orders[product_id] → Products[product_id], both one-to-many with Orders on the "many" side.
Confirm both relationships show Single cross-filter direction, and write one sentence explaining why that's the safer default.
Switch Orders to DirectQuery mode against your SQL database (if available) and note, in a sentence, what changed about how fast the Data view feels to browse.

Hint

No SQL Server/Postgres instance handy? Export the three tables from Week 8's database as CSVs and use the Text/CSV connector instead — the modeling steps (relationships, cardinality) are identical either way; only Step 5's Import-vs-DirectQuery comparison needs an actual live database connection.

7. Knowledge Check

Four quick questions. Expand each to check your answer.

What's the core trade-off between Import mode and DirectQuery mode?

Import mode copies a snapshot into the report, which is fast to browse but only as current as the last refresh. DirectQuery mode sends a live query to the source on every interaction, which is always current but only as fast as the source database — the same freshness-vs-speed trade-off Week 8 covered for plain views vs. materialized views.

In a star schema, does the fact table or a dimension table hold one row per customer?

A dimension table — Customers holds exactly one row per customer, describing them. The fact table (Orders) holds one row per event — an order — and can hold many rows referencing the same customer, which is exactly what makes the relationship between them one-to-many.

When building the `Orders` → `Customers` relationship, which side should be the "many" side?

Orders, the fact table — a single customer can place many orders, so Orders is the side that can legitimately repeat the same customer_id across multiple rows. Customers stays the "one" side, since each customer appears exactly once in that table.

Why is Single the safer default cross-filter direction, over Both?

Turning on Both across several relationships at once can let a filter reach the same table through more than one path through the model, which creates ambiguity Power BI doesn't always resolve the way it visually looks like it should. Single avoids that ambiguity entirely, so it's the right starting point until a specific, well-understood case calls for bi-directional filtering.