The data model (PBIP / TMDL)

This note explains the Power BI semantic model that lives in powerbi/ — what it contains and why it is built the way it is. It is written for someone meeting these ideas for the first time.

Why the model is stored as text

Instead of a binary .pbix, this project uses the PBIP save format. The data model is written in TMDL (Tabular Model Definition Language) — readable text files, one per table. Benefits:

• every change is visible in a git diff (see ADR 0002);
• the model can be authored and reviewed as code;
• it demonstrates a modern, engineering-grade Power BI workflow.

A TMDL table file looks like this (abbreviated):

table dim_customers
    measure 'Distinct Customers' = DISTINCTCOUNT('dim_customers'[customer_unique_id])
    column customer_id
        dataType: string
        isKey
    partition dim_customers = m
        mode: import
        source = // Power Query (M) that reads dim_customers.csv

Three ideas in one file: columns (the fields), a partition (an M query that loads the data), and measures (DAX calculations).

The star schema

The model is a star schema: dimension tables describe things, fact tables record events, and facts point at dimensions through relationships.

Dimensions (who/what/when)	Facts (events/measures)
dim_customers, dim_products, dim_sellers, dim_date	fact_order_items, fact_orders, fact_payments, fact_reviews

Each fact carries customer_id and order_purchase_date, so it links directly to dim_customers and dim_date. There are 10 relationships, all many-to-one (fact → dimension) with single-direction filtering. See the data dictionary for every column.

flowchart TD
    C[dim_customers] --- OI[fact_order_items]
    P[dim_products] --- OI
    S[dim_sellers] --- OI
    D[dim_date] --- OI
    C --- O[fact_orders]
    D --- O
    C --- PM[fact_payments]
    C --- R[fact_reviews]

The DAX measures

Measures are calculations evaluated in the filter context of a visual. The model ships with a starter set, for example:

Measure	Definition (in words)
Revenue (R$)	Sum of item prices
Orders with Items	Distinct count of order ids
Avg Order Value (R$)	Revenue ÷ orders
On-Time Delivery %	Average of the 1/0 is_on_time flag
Avg Delivery Days	Average actual delivery time
Avg Review Score	Average 1–5 rating
Distinct Customers	Distinct count of customer_unique_id

How the data is loaded

Each table's partition is a Power Query (M) script that reads its CSV from the folder given by the DataFolder parameter. That parameter defaults to a local absolute path; anyone else must point it at their own data/processed folder (Transform data → Edit parameters in Power BI Desktop).

Opening the project — the one-time setup

Power BI Desktop needs three preview features enabled to open a PBIP/TMDL/PBIR project (File → Options and settings → Options → Preview features):

1. Power BI Project (.pbip) save option
2. Store semantic model using TMDL format
3. Store reports using enhanced metadata format (PBIR)

Restart Desktop, then open powerbi/OlistAnalytics.pbip, adjust DataFolder if needed, and refresh. The report page (Overview) is currently empty — building its visuals is the next phase.