Dashboard des retards Olist

Introduction

30 Second Summary

Quand une commande arrive en retard, la note client raconte souvent une partie de l’histoire. Sans vue d’ensemble, il reste difficile de savoir quelles catégories ou régions concentrent le problème.

Dans ce projet, tu vas prolonger ton pipeline dbt Olist pour créer un dashboard local dans Power BI Desktop. Le rapport lira directement la base DuckDB grâce à une connexion ODBC.

What You'll Build

Tu ouvriras un rapport interactif dont les quatre cartes, la matrice de catégories et le classement des États réagissent à chaque filtre.

By the end of this project, you'll have:

• Une actualisation directe depuis olist.duckdb sans export CSV intermédiaire.
• Des filtres interactifs par catégorie de produit et par État client qui mettent à jour tout le rapport grâce à un schéma en étoile.
• Des mesures DAX au niveau commande qui comparent les retards et les notes sans surpondérer les commandes comportant plusieurs articles.
• Secret Mission: Une tendance mensuelle qui suit le taux de retard et la note moyenne sous les mêmes filtres de catégorie et de région.

Quels sont les prérequis ?

Tu dois déjà disposer du dépôt local kribien/olist-data-pipeline, de sa base olist.duckdb et d’une exécution antérieure réussie du pipeline.

Il te faut un PC Windows 10 ou Windows 11 64 bits avec des droits administrateur pour installer le pilote ODBC.

Before We Start

Ce point de départ fixe la question métier avant tout travail pratique. Ton dashboard aidera un responsable métier à repérer les catégories de produits et les États clients où la fiabilité des livraisons doit être améliorée.

Set Up Power BI and DuckDB

Your Olist pipeline already stores its analytics data in a local DuckDB database. Power BI turns that database into the interactive dashboard your business stakeholder needs.

Power BI needs a Windows data connection before it can read olist.duckdb. In this step, you will prepare Visual Studio Code for the project. You will also create an ODBC connection to the database.

In this step, get ready to:

• Verify Visual Studio Code and open the existing Olist repository.
• Install the 64-bit version of Microsoft Power BI Desktop.
• Install the DuckDB ODBC Driver and connect a system DSN to the local database.

Verify Visual Studio Code and open the repository

Visual Studio Code gives you one place to inspect the existing repository during the next step. Start by locating the repository and recording its absolute path.

• Locate the existing olist-data-pipeline folder in File Explorer.
• Select the File Explorer address bar to reveal the folder's absolute path.
• Record the path here: C:\Users\you\path\to\olist-data-pipeline.
• Press the Windows key to open Windows search.
• Type Visual Studio Code to check whether the editor is installed.
• If the editor opens, select Help from the top menu.
• Select About to find the installed version.

✔️ I see version 1.141 or higher

Your installed version meets the project requirement. The editor is ready to open the repository.

ⓧ I see an older version

The older editor needs an update before you work with the repository.

• Visit the official Visual Studio Code download page.
• Download the User setup for 64-bit Windows.
• Run the downloaded installer.
• Complete the installer with its default options.

The updated editor should report version 1.141 or higher from its About dialog.

ⓧ Visual Studio Code is not installed

The Windows User setup installs the editor for your account. It also adds the code command to your system path.

• Visit the official Visual Studio Code download page.
• Download the User setup for 64-bit Windows.
• Run the downloaded installer.
• Complete the installer with its default options.

Visual Studio Code is now available for your Windows account.

A fresh terminal session picks up the code command after an installation or update. You can now open the repository from its recorded location.

• Close any PowerShell window that was open during the Visual Studio Code installation.
• Press the Windows key to open Windows search.
• Type PowerShell and press Enter.
• Open the existing Olist repository in Visual Studio Code by running these commands:

cd "[[REPOSITORY_PATH="C:\Users\you\path\to\olist-data-pipeline"]]"
code .

What do these commands do?

• The first command moves PowerShell into the existing repository folder.
• The second command opens that folder as the active Visual Studio Code workspace.

• Confirm that the Explorer sidebar shows the files from olist-data-pipeline.

Repository does not open?

Check that the recorded repository path matches the address shown in File Explorer. Quotation marks around the path protect folder names that contain spaces.

If Windows cannot find the editor command, restart PowerShell after completing the User setup. Help me open my existing repository in Visual Studio Code.

Install and verify Power BI Desktop

Microsoft Power BI Desktop provides the report canvas for your delivery dashboard. This project requires the 64-bit application at version 2.157.1354.0 or higher.

• Press the Windows key to open Windows search.
• Type Power BI Desktop to check whether the application is installed.
• If the application opens, select Help from the ribbon.
• Select About to check the Version line.

✔️ I see version 2.157.1354.0 or higher

Your installed Power BI Desktop version meets the requirement. Keep this installation for the rest of the project.

ⓧ I see an older version

An update gives you the Power BI features used throughout this project.

• Close the older Power BI Desktop application.
• Visit the official Microsoft Download Center.
• Select Download.
• Choose the 64-bit file named PBIDesktopSetup_x64.exe.
• Select Next to download the installer.
• Run PBIDesktopSetup_x64.exe.
• Complete the installation prompts.

ⓧ Power BI Desktop is not installed

The 64-bit installer adds the free desktop application used to build the local report.

• Visit the official Microsoft Download Center.
• Select Download.
• Choose the 64-bit file named PBIDesktopSetup_x64.exe.
• Select Next to download the installer.
• Run PBIDesktopSetup_x64.exe.
• Complete the installation prompts.

• Press the Windows key to open Windows search.
• Type Power BI Desktop and press Enter.
• Select Help from the ribbon.
• Select About.

You should see version 2.157.1354.0 or higher on the Version line. Power BI Desktop is now ready to open a local report.

Power BI version still looks old?

Confirm that you ran the 64-bit file named PBIDesktopSetup_x64.exe. Close every Power BI Desktop window before running the installer again.

If Windows search cannot find the application after installation, restart Windows search from a fresh Start menu. Help me verify my Power BI Desktop installation.

Install the DuckDB ODBC driver and create the DSN

The DuckDB ODBC Driver lets Windows applications connect to a DuckDB database file. The driver must remain in a permanent folder because Windows registers the extracted driver files from that location.

Why ODBC instead of a CSV export?

ODBC keeps the report connected to olist.duckdb. A manual CSV export would add another file that could become outdated.

This connection preserves the path from your dbt pipeline to the Power BI report. Future database updates can flow into the report through a refresh.

This installation changes Windows driver registration. The administrator prompt grants odbc_install.exe permission to register only the ODBC driver.

✔️ I already have version 1.5.6.0

Your current driver meets the project requirement. Its extracted files should remain in their permanent local folder.

ⓧ I have an older version

Installing the newer release replaces the registered DuckDB ODBC driver files.

• Visit the official DuckDB ODBC release page.
• Download duckdb_odbc-windows-amd64.zip from version 1.5.6.0.
• Create a folder named duckdb_odbc inside your Windows Documents folder.
• Extract duckdb_odbc-windows-amd64.zip into the permanent duckdb_odbc folder.
• Right-click odbc_install.exe inside the extracted folder.
• Select Run as administrator.
• Approve the User Account Control prompt.

The installer registers the updated DuckDB ODBC driver with Windows.

ⓧ The driver is not installed

The release archive contains the driver files and the Windows registration utility.

• Visit the official DuckDB ODBC release page.
• Download duckdb_odbc-windows-amd64.zip from version 1.5.6.0.
• Create a folder named duckdb_odbc inside your Windows Documents folder.
• Extract duckdb_odbc-windows-amd64.zip into the permanent duckdb_odbc folder.
• Right-click odbc_install.exe inside the extracted folder.
• Select Run as administrator.
• Approve the User Account Control prompt.

The installer registers the DuckDB ODBC driver with Windows.

A system DSN gives Power BI a reusable name for the local database connection. You will name this connection Olist DuckDB.

• Press the Windows key to open Windows search.
• Type odbcad32.exe and press Enter.
• Select the System DSN tab.
• Add a new DuckDB configuration.
• Set the DSN name to Olist DuckDB.
• Set the Database path to the olist.duckdb file inside C:\Users\you\path\to\olist-data-pipeline.
• Save the new DSN configuration.
• Close the Windows ODBC Data Source Administrator.

Before you check the result, which DSN name and database file path do you expect to see?

• Press the Windows key to open Windows search.
• Type odbcad32.exe and press Enter.
• Select the System DSN tab.
• Select Olist DuckDB from the list.
• Open the selected DSN configuration.

You should see the system DSN named Olist DuckDB with an absolute database path ending in olist.duckdb. That connection is now the bridge between your local pipeline and Power BI.

DSN missing or pointing to the wrong file?

If you only see the default DuckDB entry, add a separate system DSN named Olist DuckDB. Select the database file from the existing repository folder.

If the DuckDB driver is unavailable, run odbc_install.exe as an administrator from the permanent extracted folder. Help me troubleshoot my DuckDB system DSN.

Your Windows BI workspace is ready. Next, you will enrich the dbt models with delivery dates and customer reviews.

Enrich the dbt Fact Table

Your ODBC route into olist.duckdb is ready. Now the analytical table needs delivery outcomes and customer reviews before Power BI can answer the business question.

The current fact table contains order items and amounts. It lacks the promised delivery date. It also lacks the actual delivery date and customer score. In this step, you will enrich the dbt pipeline while preserving one row per order item.

In this step, get ready to:

• Add typed delivery dates and one aggregated review score per order.
• Join the new fields into the fact table without multiplying order-item rows.
• Validate the dimensional keys and rebuild the local analytics mart.

Add delivery dates and order-level reviews

A reliable join starts with a clear table grain. The review staging model produces one row per order_id before the fact table joins it.

• Select models/staging/stg_orders.sql in the repository file tree.
• Replace the file contents with this staging model:

SELECT
    order_id,
    customer_id,
    order_status,
    TRY_CAST(order_purchase_timestamp AS TIMESTAMP) AS order_purchase_timestamp,
    TRY_CAST(order_approved_at AS TIMESTAMP) AS order_approved_at,
    TRY_CAST(order_delivered_customer_date AS TIMESTAMP) AS order_delivered_customer_date,
    TRY_CAST(order_estimated_delivery_date AS TIMESTAMP) AS order_estimated_delivery_date
FROM {{ source('olist_raw', 'orders') }}

What Does This Model Do?

• The model keeps the existing order identifiers and status.
• Each TRY_CAST converts a source value into a timestamp.
• A failed timestamp conversion becomes a null value instead of stopping the model.
• The actual and estimated delivery timestamps provide the two dates needed to calculate lateness.

• Save models/staging/stg_orders.sql.
• Create models/staging/stg_order_reviews.sql beside the existing staging models.
• Build the order-level review model by pasting this code:

SELECT
    order_id,
    AVG(TRY_CAST(review_score AS DOUBLE)) AS review_score
FROM {{ source('olist_raw', 'order_reviews') }}
GROUP BY order_id

Why Aggregate Before the Join?

The GROUP BY order_id produces one review row for each order. The average handles any order that has more than one source review row.

This controlled grain stops the later join from duplicating order items. Every item in an order receives the same order-level score.

• Save models/staging/stg_order_reviews.sql.
• Switch back to the pipeline terminal from earlier.
• Test the new staging models by running this command:

dbt build

What Should You See?

The build runs the project models and their data tests. The final summary should include stg_orders and stg_order_reviews with no errors.

Build Blocked by the Database File?

• Close Power BI Desktop if it currently has olist.duckdb open.
• Confirm the terminal still uses the pipeline environment from the existing repository.
• Compare both staging files with the code blocks above if the build reports a SQL parsing problem.

Still stuck? Help me troubleshoot my dbt staging build.

Good progress. Your staging layer now exposes the delivery timeline and one review score per order.

Protect the item grain in the fact table

The fact table has one row per order item. Joining it to the order-level review model preserves that grain because each order_id matches no more than one staged review row.

• Select models/marts/fact_order_items.sql in the repository file tree.
• Replace the file contents with this first runnable version:

SELECT
    CAST(order_items.order_id AS VARCHAR)
        || '-'
        || CAST(order_items.order_item_id AS VARCHAR) AS order_item_key,
    order_items.order_id,
    order_items.order_item_id,
    orders.customer_id,
    order_items.product_id,
    order_items.seller_id,
    orders.order_status,
    orders.order_purchase_timestamp,
    orders.order_approved_at,
    orders.order_delivered_customer_date,
    orders.order_estimated_delivery_date,
    order_items.shipping_limit_date,
    order_items.item_price,
    order_items.freight_value,
    order_items.item_price + order_items.freight_value AS gross_item_value,
    reviews.review_score
FROM {{ ref('stg_order_items') }} AS order_items
INNER JOIN {{ ref('stg_orders') }} AS orders
    ON order_items.order_id = orders.order_id
LEFT JOIN {{ ref('stg_order_reviews') }} AS reviews
    ON order_items.order_id = reviews.order_id

How Does the Join Protect the Grain?

• The composite order_item_key continues to identify each item row.
• The order join contributes the customer key and delivery timestamps.
• The left review join keeps order items when an order has no review.
• The aggregated review model contributes at most one matching score per order.

• Save models/marts/fact_order_items.sql.
• Switch back to the pipeline terminal.
• Confirm the review join preserves a runnable project by running this command:

dbt build

What Does This Check Prove?

A successful build proves that fact_order_items can resolve both staging dependencies. Its existing unique test also confirms that order_item_key has not been duplicated by the review join.

Fact Model Failing After the Join?

• Confirm models/staging/stg_order_reviews.sql was saved before the build.
• Check that the review model groups by order_id.
• Check that the left join matches order_items.order_id to reviews.order_id.

Need another pair of eyes? Help me diagnose my fact table join.

That checkpoint confirms the order-level review join preserves the item-level fact table. The next edit turns the two delivery timestamps into analysis-ready fields.

• Find the final selected field in models/marts/fact_order_items.sql:

    reviews.review_score

Why Edit This Line?

The review score currently ends the selected column list. Adding the delivery calculations here keeps every analytical field before the model joins.

• Replace that final selected field with this expanded block:

    reviews.review_score,
    CASE
        WHEN orders.order_delivered_customer_date IS NOT NULL
            AND orders.order_estimated_delivery_date IS NOT NULL
        THEN TRUE
        ELSE FALSE
    END AS is_delivery_analyzable,
    CASE
        WHEN orders.order_delivered_customer_date IS NOT NULL
            AND orders.order_estimated_delivery_date IS NOT NULL
            AND orders.order_delivered_customer_date > orders.order_estimated_delivery_date
        THEN TRUE
        ELSE FALSE
    END AS is_late_delivery,
    CASE
        WHEN orders.order_delivered_customer_date IS NULL
            OR orders.order_estimated_delivery_date IS NULL
        THEN NULL
        WHEN orders.order_delivered_customer_date > orders.order_estimated_delivery_date
        THEN DATE_DIFF(
            'day',
            orders.order_estimated_delivery_date,
            orders.order_delivered_customer_date
        )
        ELSE 0
    END AS delivery_delay_days

What Do the Delivery Fields Mean?

• The is_delivery_analyzable flag identifies rows with both required delivery timestamps.
• The is_late_delivery flag becomes true when the actual delivery timestamp is later than the estimate.
• The delivery_delay_days field returns null when the comparison is unavailable.
• An on-time delivery receives a delay of 0 days.

• Save models/marts/fact_order_items.sql.

Add tests and rebuild the mart

The YAML schema records the contract for each model. The new delivery flags need non-null tests because every order item must be classified as true or false.

• Select models/marts/schema.yml in the repository file tree.
• Find the fact_order_items model entry.
• Replace its description value with this line:

    description: Une ligne par article de commande avec les montants, les clés dimensionnelles, la livraison et la note de la commande.

What Does the Description Capture?

The model description now records the item-level grain. It also names the added delivery and review data.

• Locate this existing test block under fact_order_items:

      - name: freight_value
        data_tests:
          - not_null

Why Use This Anchor?

The freight test is the final existing test in the fact model. Replacing this block places the two new flag tests inside the same column list.

• Replace that existing block with this expanded version:

      - name: freight_value
        data_tests:
          - not_null
      - name: is_delivery_analyzable
        data_tests:
          - not_null
      - name: is_late_delivery
        data_tests:
          - not_null

What Do These Tests Protect?

The tests reject missing values in both delivery flags. A null delay remains valid because some orders lack the timestamps required for analysis.

• Save models/marts/schema.yml.

Before you run the final build, decide whether you expect the enriched fact model and its new tests to pass.

• Close Power BI Desktop if it currently has the local database open.
• Switch back to the pipeline terminal from earlier.
• Build the enriched analytics mart by running this command:

dbt build

What Should the Final Build Confirm?

The final summary should show that the models and data tests completed without errors. It should include stg_order_reviews and fact_order_items.

A successful result means analytics.fact_order_items is refreshed inside olist.duckdb. The unique order_item_key test confirms the item grain survived every join.

Final Build Still Failing?

• Close any application that still has olist.duckdb open if the build reports a file access problem.
• Check the indentation beneath data_tests if the schema file cannot be parsed.
• Compare all four files with the full-code reference below if a model reports a SQL problem.

Want help reading the failure? Help me troubleshoot the final dbt build.

You have closed the grain gap. The local mart now combines item-level values with order-level delivery outcomes and customer reviews.

✔️ Awesome, I've got everything!

All four dbt files are saved. The successful build confirms the enriched mart is ready for analysis.

ⓧ I'd like to double check the full code

SELECT
    order_id,
    customer_id,
    order_status,
    TRY_CAST(order_purchase_timestamp AS TIMESTAMP) AS order_purchase_timestamp,
    TRY_CAST(order_approved_at AS TIMESTAMP) AS order_approved_at,
    TRY_CAST(order_delivered_customer_date AS TIMESTAMP) AS order_delivered_customer_date,
    TRY_CAST(order_estimated_delivery_date AS TIMESTAMP) AS order_estimated_delivery_date
FROM {{ source('olist_raw', 'orders') }}

This staging model exposes the order timeline as timestamp values.

SELECT
    order_id,
    AVG(TRY_CAST(review_score AS DOUBLE)) AS review_score
FROM {{ source('olist_raw', 'order_reviews') }}
GROUP BY order_id

This staging model produces one average review score per order.

SELECT
    CAST(order_items.order_id AS VARCHAR)
        || '-'
        || CAST(order_items.order_item_id AS VARCHAR) AS order_item_key,
    order_items.order_id,
    order_items.order_item_id,
    orders.customer_id,
    order_items.product_id,
    order_items.seller_id,
    orders.order_status,
    orders.order_purchase_timestamp,
    orders.order_approved_at,
    orders.order_delivered_customer_date,
    orders.order_estimated_delivery_date,
    order_items.shipping_limit_date,
    order_items.item_price,
    order_items.freight_value,
    order_items.item_price + order_items.freight_value AS gross_item_value,
    reviews.review_score,
    CASE
        WHEN orders.order_delivered_customer_date IS NOT NULL
            AND orders.order_estimated_delivery_date IS NOT NULL
        THEN TRUE
        ELSE FALSE
    END AS is_delivery_analyzable,
    CASE
        WHEN orders.order_delivered_customer_date IS NOT NULL
            AND orders.order_estimated_delivery_date IS NOT NULL
            AND orders.order_delivered_customer_date > orders.order_estimated_delivery_date
        THEN TRUE
        ELSE FALSE
    END AS is_late_delivery,
    CASE
        WHEN orders.order_delivered_customer_date IS NULL
            OR orders.order_estimated_delivery_date IS NULL
        THEN NULL
        WHEN orders.order_delivered_customer_date > orders.order_estimated_delivery_date
        THEN DATE_DIFF(
            'day',
            orders.order_estimated_delivery_date,
            orders.order_delivered_customer_date
        )
        ELSE 0
    END AS delivery_delay_days
FROM {{ ref('stg_order_items') }} AS order_items
INNER JOIN {{ ref('stg_orders') }} AS orders
    ON order_items.order_id = orders.order_id
LEFT JOIN {{ ref('stg_order_reviews') }} AS reviews
    ON order_items.order_id = reviews.order_id

This fact model preserves one row per order item while adding delivery outcomes and the order-level review score.

version: 2

models:
  - name: dim_customers
    description: Une ligne par identifiant client rattaché à une commande Olist.
    columns:
      - name: customer_id
        data_tests:
          - unique
          - not_null
      - name: customer_unique_id
        data_tests:
          - not_null

  - name: dim_products
    description: Une ligne par produit avec sa catégorie source et sa traduction disponible.
    columns:
      - name: product_id
        data_tests:
          - unique
          - not_null

  - name: dim_sellers
    description: Une ligne par vendeur Olist.
    columns:
      - name: seller_id
        data_tests:
          - unique
          - not_null

  - name: fact_order_items
    description: Une ligne par article de commande avec les montants, les clés dimensionnelles, la livraison et la note de la commande.
    columns:
      - name: order_item_key
        data_tests:
          - unique
          - not_null
      - name: order_id
        data_tests:
          - not_null
      - name: customer_id
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('dim_customers')
                field: customer_id
      - name: product_id
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('dim_products')
                field: product_id
      - name: seller_id
        data_tests:
          - not_null
          - relationships:
              arguments:
                to: ref('dim_sellers')
                field: seller_id
      - name: item_price
        data_tests:
          - not_null
      - name: freight_value
        data_tests:
          - not_null
      - name: is_delivery_analyzable
        data_tests:
          - not_null
      - name: is_late_delivery
        data_tests:
          - not_null

This schema validates the dimension keys and the enriched fact table contract.

Your refreshed mart is ready. Next, you will bring its first delivery KPI into Power BI.

Build Your First Delay KPI

Your dbt pipeline now produces delivery dates, delay flags, and review scores at the correct item grain. You have reliable data ready for reporting.

A single-table model in Power BI Desktop produces a quick delay KPI. It cannot yet explain which categories or regions drive those delays.

In this step, get ready to:

• Import the refreshed fact table through the existing ODBC connection.
• Create three DAX measures that count delivered orders and late orders.
• Build two KPI cards and expose the limits of the single-table model.

Import the fact table through ODBC

An ODBC connection lets Power BI read your local DuckDB database through the System DSN you configured earlier. The connector loads the selected table into the report using Import mode.

• Press the Windows key to open Windows search.
• Type Power BI Desktop into the search field.
• Select Power BI Desktop from the search results.
• Select Get Data from the Home ribbon.
• Select ODBC from the connector list.
• Select Olist DuckDB from the DSN dropdown.
• Select OK.

Power BI now asks how it should authenticate with the local connection. This project does not require credentials.

• Choose Default or Custom as the authentication method.
• Leave the credential fields empty.
• Select Connect.

The selection window now lists the schemas and tables available through the DSN.

• Select analytics.fact_order_items.
• Select Load.

Good progress. Power BI now holds an imported snapshot of the refreshed fact table.

What should you see?

• The imported identifiers include order_id, product_id, and customer_id.
• The delivery analysis fields include is_delivery_analyzable, is_late_delivery, and delivery_delay_days.
• The supporting fields include order_purchase_timestamp and review_score.

Why ODBC with Import mode?

ODBC connects the report directly to olist.duckdb. A separate CSV export would add a manual handoff to every refresh.

The verified Power BI ODBC connector supports Import mode. Import mode stores a snapshot inside the report until you refresh it from DuckDB.

Cannot load the fact table?

• Confirm that you selected the System DSN named Olist DuckDB.
• Confirm that the DSN still points to the absolute path of olist.duckdb.
• Close any other process that still holds the DuckDB file open.

Still unable to import the table? Help me troubleshoot my Power BI ODBC connection to the Olist DuckDB System DSN.

Create the order-level delay measures

A DAX measure calculates a result from the current report context. Distinct order counts protect these KPIs from the repeated order IDs created by the item-level grain.

• Select fact_order_items in the Data pane.
• Select Modeling in the ribbon.
• Select New measure.
• Create the delivered-order measure by entering this formula:

Delivered Orders =
CALCULATE(
    DISTINCTCOUNT(fact_order_items[order_id]),
    fact_order_items[is_delivery_analyzable] = TRUE()
)

What does this measure do?

• The DISTINCTCOUNT function counts each order_id once.
• The CALCULATE function applies the delivery-analysis filter before counting.
• The TRUE() condition excludes orders without both delivery dates.

• Press Enter to validate the formula.
• Confirm that Delivered Orders appears under fact_order_items with a calculator icon.

Delivered Orders does not validate?

Check that the table name is fact_order_items. Check that the filter column is is_delivery_analyzable.

Need another pair of eyes? Help me fix my Delivered Orders DAX measure.

• Return to Modeling in the ribbon.
• Select New measure.
• Create the late-order measure by entering this formula:

Late Orders =
CALCULATE(
    DISTINCTCOUNT(fact_order_items[order_id]),
    fact_order_items[is_late_delivery] = TRUE()
)

How does the late count work?

• The distinct count again evaluates each order once.
• The is_late_delivery filter keeps orders delivered after their estimated date.
• The measure supplies the numerator for the final rate.

• Press Enter to validate the formula.
• Confirm that Late Orders appears under fact_order_items with a calculator icon.

Late Orders does not validate?

Confirm that is_late_delivery exists in the imported table. Compare every parenthesis with the formula above.

Still seeing a formula problem? Help me debug my Late Orders DAX measure.

• Return to Modeling in the ribbon.
• Select New measure.
• Create the delay-rate measure by entering this formula:

Late Delivery Rate =
DIVIDE(
    [Late Orders],
    [Delivered Orders]
)

How does the rate work?

• The Late Orders measure provides the numerator.
• The Delivered Orders measure provides the denominator.
• The DIVIDE function handles the division safely when no delivered orders are available.

• Press Enter to validate the formula.
• Confirm that Late Delivery Rate appears with a calculator icon.
• Select Late Delivery Rate in the Data pane.
• Open Format in the Measure tools ribbon.
• Select Percentage.

Late Delivery Rate does not validate?

Confirm that both supporting measures were saved before you created the rate. Their names must match Late Orders and Delivered Orders exactly.

Still stuck on the rate? Help me debug my Late Delivery Rate DAX measure.

✔️ Awesome, I've got everything!

Great. Confirm that all three measures appear under fact_order_items before you build the cards.

ⓧ I'd like to double check the full code

Delivered Orders =
CALCULATE(
    DISTINCTCOUNT(fact_order_items[order_id]),
    fact_order_items[is_delivery_analyzable] = TRUE()
)

Late Orders =
CALCULATE(
    DISTINCTCOUNT(fact_order_items[order_id]),
    fact_order_items[is_late_delivery] = TRUE()
)

Late Delivery Rate =
DIVIDE(
    [Late Orders],
    [Delivered Orders]
)

Build the cards and expose the missing dimensions

A card visual turns one measure into a value you can read at a glance. These first cards prove that the imported table supports a working delivery KPI.

• Select a blank area of the report canvas.
• Select Build visual in the Visualizations pane.
• Select the Card visual.
• Drag Delivered Orders from the Data pane into Values.
• Select another blank area of the report canvas.
• Select Build visual in the Visualizations pane.
• Select the Card visual.
• Drag Late Delivery Rate from the Data pane into Values.

Before you inspect the values closely, predict whether both cards contain usable results. Check your prediction against the report now.

• Confirm that the Delivered Orders card displays a non-empty count.
• Confirm that the Late Delivery Rate card displays a non-empty percentage.
• Confirm that the rate falls between 0% and 100%.

That is your first live delay KPI. The report now counts delivered orders once and shows the share that arrived late.

Before you inspect the field list, predict whether this imported table can break the KPI down by product category or customer state. Test that prediction now.

• Expand fact_order_items in the Data pane.
• Look for product_category in the available fields.
• Look for customer_state in the available fields.

Neither field is available in the single imported table. That limitation is the intended result because the category and region belong to separate dimensions.

Cards are blank or show an unexpected rate?

• Confirm that each card uses a measure with a calculator icon.
• Confirm that the first card uses Delivered Orders.
• Confirm that the second card uses Late Delivery Rate.

Need help tracing the result? Help me troubleshoot my Delivered Orders and Late Delivery Rate cards.

Your delay KPI works from the fact table. Next up, you will connect the product and customer dimensions so category and region filters can explain where delays concentrate.

Connect Categories and Regions

Vos premières cartes dans Power BI Desktop calculent déjà le nombre de commandes livrées et leur taux de retard.

Le premier KPI ne peut pas encore expliquer où les retards se concentrent. Les catégories de produits et les régions vivent dans des dimensions séparées.

Vous allez étendre le modèle avec un schéma en étoile. Les filtres pourront ainsi se propager des dimensions vers la table de faits.

In this step, get ready to:

• Importez les dimensions des produits et des clients.
• Créez deux relations actives avec la table de faits.
• Ajoutez des slicers qui mettent à jour les cartes KPI.

Importer les dimensions de produits et de clients

La connexion ODBC permet à Power BI de charger plusieurs tables depuis DuckDB. Le mode Import conserve ensuite un instantané de ces tables dans le rapport.

• Dans Power BI Desktop déjà ouvert, sélectionnez Get Data.
• Sélectionnez ODBC dans la liste des sources.
• Choisissez le DSN système Olist DuckDB.
• Sélectionnez OK.
• Choisissez Default or Custom comme méthode d’authentification.
• Laissez les champs d’identification vides.
• Sélectionnez Connect.
• Cochez analytics.dim_products dans la liste des tables.

La dimension des produits est maintenant sélectionnée pour l’import.

• Cochez analytics.dim_customers dans la liste des tables.
• Sélectionnez Load.

Vous voyez maintenant dim_products et dim_customers dans la liste des tables du modèle. La table fact_order_items reste disponible avec les mesures existantes.

Pourquoi conserver un schéma en étoile ?

Les dimensions portent les attributs utilisés pour analyser les commandes. dim_products fournit les catégories de produits. dim_customers fournit les États clients.

Cette structure conserve une source directe pour l’actualisation du rapport. Elle permet aussi aux mêmes mesures de répondre aux filtres de plusieurs dimensions.

Créer les relations avec la table de faits

Une relation un-à-plusieurs utilise la clé unique d’une dimension pour filtrer les lignes correspondantes dans la table de faits. La direction de filtre Single maintient ce flux depuis la dimension vers les commandes.

• Sélectionnez Modeling dans le ruban.
• Sélectionnez Manage relationships.
• Sélectionnez New.
• Sélectionnez dim_products[product_id] comme côté un de la relation.
• Sélectionnez fact_order_items[product_id] comme côté plusieurs de la relation.
• Réglez Cardinality sur un-à-plusieurs.
• Réglez Cross filter direction sur Single.

La relation est prête à propager un filtre de produit vers les articles de commande.

• Activez Make this relationship active.
• Validez la relation.

La relation de produit est maintenant enregistrée dans le modèle.

• Dans Manage relationships, sélectionnez de nouveau New.
• Sélectionnez dim_customers[customer_id] comme côté un de la relation.
• Sélectionnez fact_order_items[customer_id] comme côté plusieurs de la relation.
• Réglez Cardinality sur un-à-plusieurs.
• Réglez Cross filter direction sur Single.
• Activez Make this relationship active.
• Validez la relation.
• Confirmez que les deux relations sont actives dans Manage relationships.

Vous devez voir une relation sur product_id. Vous devez aussi voir une relation sur customer_id.

Comment les filtres circulent-ils ?

• La relation sur product_id transmet une catégorie sélectionnée aux lignes correspondantes de fact_order_items.
• La relation sur customer_id transmet un État sélectionné aux lignes correspondantes de fact_order_items.
• La direction Single garde la propagation orientée depuis chaque dimension vers la table de faits.

La relation ne peut pas être créée ?

Vérifiez que le côté un utilise la colonne de la dimension. Vérifiez ensuite que le côté plusieurs utilise la colonne correspondante de fact_order_items.

Si Power BI refuse la cardinalité un-à-plusieurs, actualisez la dimension concernée. Le modèle dbt teste déjà l’unicité de sa clé.

Besoin d’un diagnostic ciblé ? Aidez-moi à corriger ma relation Power BI entre une dimension et fact_order_items.

Ajouter les slicers et tester les filtres

Les relations deviennent visibles lorsque les dimensions pilotent les mesures existantes. Deux slicers permettent de tester séparément la catégorie de produit et l’État client.

• Revenez à la page du rapport qui contient les deux cartes KPI.
• Ajoutez un visuel de type slicer depuis le panneau des visualisations.
• Déposez dim_products[product_category] dans le champ du premier slicer.
• Ajoutez un deuxième visuel de type slicer depuis le panneau des visualisations.
• Déposez dim_customers[customer_state] dans le champ du deuxième slicer.

Avant le test final, quelles cartes pensez-vous voir changer lorsque vous sélectionnez une catégorie ?

• Sélectionnez une catégorie dans le slicer de produits.

Les cartes Delivered Orders et Late Delivery Rate affichent maintenant les valeurs de cette catégorie.

• Sélectionnez un État dans le slicer de clients.

Les deux cartes se recalculent avec les filtres de catégorie et d’État. Vous ne voyez aucun message d’erreur de relation.

Les cartes ne réagissent pas aux slicers ?

Vérifiez que le slicer de catégorie utilise dim_products[product_category]. Vérifiez que le slicer d’État utilise dim_customers[customer_state].

Confirmez aussi que chaque relation est active. Sa direction doit être Single depuis la dimension vers fact_order_items.

Les filtres restent bloqués ? Aidez-moi à comprendre pourquoi mes slicers Power BI ne mettent pas à jour mes cartes.

Beau progrès. Votre schéma en étoile peut maintenant expliquer les retards par catégorie et par État.

Dans la prochaine étape, vous corrigerez les moyennes pour que chaque commande compte une seule fois.

Finalize the Dashboard

Your Power BI star schema already lets the category and state slicers filter your delivery KPIs. You can now see where late deliveries concentrate.

The fact_order_items table stores one row per order item. A direct average would give orders containing several items extra weight.

You'll use DAX iterators to evaluate each order once in the current filter context. Then you'll turn those measures into the finished dashboard.

In this step, get ready to:

• Create order-level averages for delivery delay plus review score.
• Assemble four KPI cards plus a category matrix.
• Finish a saved dashboard where both slicers update every visual.

Create order-level averages

Each order can appear across several item rows. Iterating over distinct order IDs keeps the delay calculation at order grain.

• Select Modeling in the ribbon.
• Select New measure.
• Create the order-grain delay measure by pasting this formula into the formula bar:

Average Delay Days =
AVERAGEX(
    VALUES(fact_order_items[order_id]),
    CALCULATE(MAX(fact_order_items[delivery_delay_days]))
)

Why iterate over orders?

• The VALUES(fact_order_items[order_id]) expression produces one distinct entry for each order in the current filter context.
• The AVERAGEX function evaluates one delay value for every distinct order.
• The MAX(fact_order_items[delivery_delay_days]) expression retrieves the delay repeated across that order's item rows.
• A direct column average would weight orders according to their item counts. This iterator preserves the business meaning of an average delay per order.

• Press Enter to create the measure.
• Add Average Delay Days to a new card on the report canvas.

Good, your new card shows a non-empty delay value that counts each order once.

Is the delay card blank?

• Clear the current category selection if it leaves no analyzable deliveries.
• Confirm the measure references fact_order_items[delivery_delay_days].
• Check that every opening parenthesis has a matching closing parenthesis.

Still stuck? Help me troubleshoot my blank Average Delay Days card.

The review score has the same grain problem. The score belongs to an order even though it appears on every item row from that order.

• Select Modeling in the ribbon.
• Select New measure.
• Create the order-grain review measure by pasting this formula into the formula bar:

Average Review Score =
AVERAGEX(
    VALUES(fact_order_items[order_id]),
    CALCULATE(MAX(fact_order_items[review_score]))
)

What does this measure do?

• The distinct order list gives each order one turn in the calculation.
• The MAX(fact_order_items[review_score]) expression retrieves the single aggregated review score attached to that order.
• The outer average combines those order-level scores without rewarding orders that contain more items.

• Press Enter to create the measure.
• Add Average Review Score to a new card on the report canvas.

You'll see a non-empty review score on the new card. The dashboard now has both order-level averages.

Is the review card missing a value?

• Confirm the formula references fact_order_items[review_score].
• Clear the slicers to check whether the current selection has review data.
• Verify that Average Review Score appears as a measure in the model.

Need another pair of eyes? Help me diagnose my Average Review Score measure.

Build the KPI cards and category matrix

The cards summarize the current selection. A matrix reveals whether the same performance measures move together across product categories.

• Select the existing Late Orders card.
• Remove the selected visual by pressing Delete.

Four required cards remain for delivered orders, late-delivery rate, average delay, and average review score.

• Arrange the four remaining cards in one row at the top of the report canvas.

You can now scan the four headline results without moving around the page.

• Add a matrix visual below the cards.
• Add dim_products[product_category] to the matrix Rows field.

You'll see one matrix row for each available product category.

• Add Late Delivery Rate to the matrix Values field.
• Add Average Delay Days to the matrix Values field.

The matrix now compares the frequency of delays with their average duration for each category.

• Add Average Review Score to the matrix Values field.

The review-score column lets you compare delivery performance with customer satisfaction across the same categories.

Does the matrix show repeated totals?

• Confirm dim_products[product_category] is in the matrix rows.
• Confirm the product relationship from the previous step remains active.
• Test the category slicer against the Delivered Orders card.

If the categories still do not filter the values, help me inspect the product relationship and matrix fields.

Add the state chart and verify every filter

The final chart ranks customer states by late-delivery rate. The existing slicers then become a single control layer for the entire dashboard.

• Add a bar chart visual beside the matrix.
• Add dim_customers[customer_state] to the chart axis.

You'll see the customer states listed along the chart axis.

• Add Late Delivery Rate as the chart value.

The bars now compare the proportion of late deliveries across customer states.

• Select File in the upper-left corner.
• Select Save as.

The save dialog opens so you can name the local report.

• Enter olist_delivery_impact.pbix as the filename.
• Select Save.

You'll see olist_delivery_impact.pbix in the Power BI Desktop title bar.

Before you test the finished dashboard, which visuals do you expect both slicers to update?

• Select one product category in the existing category slicer.
• Select one customer state in the existing state slicer.

You'll see all four cards recalculate. The category matrix and state bar chart update without a relationship error.

Does one visual ignore the slicers?

• Confirm the bar-chart axis uses dim_customers[customer_state].
• Confirm the matrix rows use dim_products[product_category].
• Check that both one-to-many relationships remain active with single-direction filtering toward fact_order_items.

If a visual still stays fixed, help me trace why my Power BI slicers are not updating every visual.

That's the grain problem solved. Your saved dashboard now compares delivery reliability with customer satisfaction across products and regions.

Secret mission

Ajouter la tendance mensuelle

Ton dashboard compare déjà les catégories et les régions. Cette mission ajoute une tendance mensuelle qui montre comment la fiabilité des livraisons et la satisfaction client évoluent sous les mêmes filtres.

Clean Up Your Resources

Clean Up Your Resources

Tout fonctionne localement dans Power BI Desktop. Aucun service cloud ne génère de coût récurrent. Choisis de conserver tes ressources, de fermer les applications ou de supprimer les éléments créés pour ce projet.

Resources you used:

• Le modèle dbt modifié dans models/staging/stg_orders.sql.
• Le modèle dbt créé dans models/staging/stg_order_reviews.sql.
• Le modèle dbt modifié dans models/marts/fact_order_items.sql.
• Les tests dbt modifiés dans models/marts/schema.yml.
• Le DSN système ODBC nommé Olist DuckDB.
• Le dossier permanent de DuckDB ODBC Driver version 1.5.6.0.
• Le rapport local olist_delivery_impact.pbix.

Keep everything running

Aucune action n'est nécessaire. Choisis cette option pour continuer à actualiser ton modèle local ou présenter ton dashboard.

• Conserve le DSN système Olist DuckDB pour les prochaines actualisations.
• Conserve le dossier permanent du pilote DuckDB ODBC.
• Conserve les quatre modèles dbt enrichis dans ton dépôt local.
• Conserve olist_delivery_impact.pbix pour présenter le dashboard.
• Rouvre le rapport lorsque tu veux analyser une nouvelle version de olist.duckdb.

Pause - I'll come back to this later

Cette option libère les applications utilisées pendant le projet. Tes modèles, ton DSN et ton rapport restent disponibles pour ta prochaine session.

• Ferme Power BI Desktop.
• Ferme Visual Studio Code.
• Laisse le DSN système Olist DuckDB inchangé.
• Laisse le dossier du pilote DuckDB ODBC à son emplacement permanent.
• Conserve olist_delivery_impact.pbix dans son dossier actuel.

Delete - I don't want to use this again

Cette option retire la connexion ODBC, les fichiers du pilote et le rapport Power BI. Elle rétablit aussi les modèles dbt dans leur état antérieur.

Conserve ce qui compte

Cette suppression peut sembler radicale parce qu'elle retire le livrable que tu viens de terminer.

• Conserve une copie du rapport ou des modèles enrichis si tu souhaites réutiliser ton analyse.

Supprimer la connexion locale

• Ferme Power BI Desktop pour libérer la connexion à olist.duckdb.
• Appuie sur la touche Windows pour ouvrir la recherche.
• Saisis odbcad32.exe dans la recherche Windows.
• Appuie sur Entrée pour lancer le gestionnaire ODBC.
• Affiche la liste des DSN système.
• Sélectionne Olist DuckDB dans cette liste.
• Utilise la commande de suppression affichée pour retirer ce DSN.
• Vérifie que Olist DuckDB ne figure plus dans la liste des DSN système.

Supprimer les fichiers locaux

• Retrouve le dossier permanent dans lequel tu as décompressé le pilote DuckDB ODBC.
• Supprime ce dossier avec le gestionnaire de fichiers Windows.
• Retrouve le dossier dans lequel tu as enregistré olist_delivery_impact.pbix.
• Supprime olist_delivery_impact.pbix.

Rétablir les modèles dbt

L'historique Git du dépôt permet de récupérer l'état précédent de chaque modèle.

• Rétablis la version antérieure de models/staging/stg_orders.sql depuis l'historique Git du dépôt.
• Rétablis la version antérieure de models/staging/stg_order_reviews.sql depuis l'historique Git du dépôt.
• Rétablis la version antérieure de models/marts/fact_order_items.sql depuis l'historique Git du dépôt.
• Rétablis la version antérieure de models/marts/schema.yml depuis l'historique Git du dépôt.

Vérifier le nettoyage

• Conserve olist.duckdb si tu veux garder le pipeline Olist existant.
• Vérifie que olist_delivery_impact.pbix ne figure plus dans son dossier d'enregistrement.
• Vérifie que les quatre fichiers dbt ne sont plus signalés comme modifiés dans ton dépôt.

Nice Work!

Nice Work!

Bravo ! Tu as transformé un pipeline Olist existant en dashboard décisionnel avec Power BI. Ton rapport olist_delivery_impact.pbix montre où les retards se concentrent ainsi que leur impact sur la satisfaction client.

Tu as appris à :

• Enrichir les modèles dbt avec les dates de livraison ainsi que les avis clients. L’agrégation des avis par commande protège le grain de la table de faits.
• Connecter DuckDB à Power BI avec un DSN ODBC Windows. Tu as construit un schéma en étoile où les catégories de produits ainsi que les États clients filtrent les indicateurs.
• Créer des mesures DAX au niveau commande qui évitent de surpondérer les commandes comportant plusieurs articles. Tu as assemblé quatre cartes KPI avec une matrice des catégories ainsi qu’un graphique des États.
• Secret Mission : Ajouté une tendance mensuelle qui compare le taux de retard avec la note moyenne. Le graphique réagit aux filtres de catégorie ainsi que de région.

Prêt à tester tes connaissances ?