Power BI Desktop, Power Query and the star schema

Power BI Desktop in one picture

Power BI Desktop (free, Windows) has three views — Report (visuals), Table/Data (rows), Model (relationships) — plus the Power Query editor for preparation. The workflow is always:

Get data → Transform (Power Query) → Model (relationships, measures) → Visualise → Publish.

Data connections

For the Orion datasets: Get Data → Text/CSV (or Excel workbook) → select orders, order_items, products, stores, customers, calendar → Transform Data.

Power Query in Power BI

The same engine you used in Excel. Typical steps for Orion:

• Set types: order_date → Date, discount_pct → Decimal, IDs → Whole number.
• Filter out nothing at this stage that analysts may need later (keep cancelled orders; filter in measures).
• Add Net Revenue to order lines: [quantity] * [unit_price] * (1 - [discount_pct]) (Add Column → Custom Column) — or compute it in a DAX measure (next lesson).
• Remove columns you will never use (smaller model, faster refresh).

Facts and dimensions

• Fact tables record events and numbers to aggregate: order_items (units, price, discount), sales_targets (plan). They are long and narrow.
• Dimension tables describe the "who, what, where, when": products, stores, customers, calendar. They are short and wide, with one row per member and a unique key.

The star schema

Place facts in the centre and connect each dimension to them with one-to-many relationships:

           customers          products
                 \              /
   calendar ── FactSales (order lines + order attributes) ── stores

For Orion, flatten orders attributes (date, store, customer, status, channel) onto the order-line fact in Power Query (Merge order_items with orders), giving one fact table FactSales at line grain. Each dimension then filters the fact directly.

Why a star?

1. Correct filtering: a slicer on product category filters sales through one clear path.
2. Performance: the engine (VertiPaq) is optimised for stars.
3. Simple DAX: measures aggregate one fact table.

Keys and cardinality

A relationship connects a column in one table to a column in another.

The dimension key must be unique. If products contained a duplicated product_id, Power BI would refuse a 1:* relationship or, worse, you might be pushed into many-to-many with surprising totals. Check uniqueness in Power Query (Keep Duplicates should return zero rows).

Plan and actual at different grains

sales_targets is store × month; FactSales is order line × day. Both relate to stores; for time, relate sales_targets[year_month] to a calendar[year_month] column? That would be many-to-many (each month appears many times in the calendar). Two professional solutions:

1. Add a MonthStart date to sales_targets (first day of month) and relate it to calendar[Date] — targets sit on the 1st, and month-level visuals aggregate correctly. Day-level visuals must not show plan.
2. Create a separate month dimension shared by both facts.

Cross-filter direction

Filters flow from the "one" side to the "many" side: selecting a store filters its sales. Single direction is the default and the right choice almost always. Both direction lets the fact filter the dimension (e.g. a product slicer that lists only products sold in the selected store) but can create ambiguity and slow models. Prefer a measure-based filter on the slicer (visual-level filter [Revenue] > 0) to bidirectional relationships.

The date table

Time intelligence (YTD, same period last year) requires a dedicated date table:

• one row per day, no gaps, covering every date in all facts;
• columns: Date, Year, Quarter, Month number, Month name (sorted by month number), Year-Month, Week, Weekday, IsWeekend, IsHoliday;
• marked as a date table (Table tools → Mark as date table).

The Orion calendar table already satisfies this for 2023–2025. In DAX you can also create one:

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2025, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "MonthNo", MONTH ( [Date] ),
    "YearMonth", FORMAT ( [Date], "YYYY-MM" )
)

Turn off Auto date/time (File → Options) in professional models — it creates hidden date tables for every date column and bloats the model.

Practice

Summary

• Import is the default mode; DirectQuery and Live connection have specific uses.
• Model as a star: fact tables at a clear grain, flattened dimensions with unique keys.
• Use one-to-many, single-direction relationships; avoid many-to-many and bidirectional filters unless justified.
• Use one marked date table; disable Auto date/time.