PowerBI_Design_Guide.md

# Power BI Dashboard Design Guide - Supply Chain Delay Analysis

This guide provides instructions and structural layouts to build an interactive, professional dashboard in Power BI using the populated MySQL database (`supply_chain_db`).

---

## 1. Data Connection & Model Schema

### Connecting Power BI to MySQL
1. Open **Power BI Desktop**.
2. Click **Get Data** > **MySQL database**.
3. Set **Server** to `localhost:3306` and **Database** to `supply_chain_db`.
4. In the credentials prompt, use the Database tab and input:
   - **Username**: `root`
   - **Password**: `admin123`
5. Select all tables: `departments`, `categories`, `customers`, `products`, `orders`, and `order_items`. Click **Load**.

### Data Modeling Relationships (Star/Snowflake Schema)
Ensure Power BI sets up the correct schema relationships under the **Model View**:
- `customers` (customer_id) `1 ---- *` `orders` (order_customer_id)
- `orders` (order_id) `1 ---- *` `order_items` (order_id)
- `products` (product_card_id) `1 ---- *` `order_items` (order_item_cardprod_id)
- `categories` (category_id) `1 ---- *` `products` (product_category_id)
- `departments` (department_id) `1 ---- *` `products` (No direct relationship; create link if needed or link through custom schemas. Otherwise, `departments` can be linked to `products` via category if category stores department, or directly as normalized in schema.)

---

## 2. Calculated Measures (DAX Formulas)

Create a new table `_Measures` and write the following DAX calculations:

1. **Total Orders**:
   ```dax
   Total Orders = COUNT(orders[order_id])
   ```

2. **Total Sales (Gross)**:
   ```dax
   Total Sales = SUM(order_items[sales])
   ```

3. **Total Profit**:
   ```dax
   Total Profit = SUM(order_items[benefit_per_order])
   ```

4. **Delayed Orders**:
   ```dax
   Delayed Orders = CALCULATE([Total Orders], orders[late_delivery_risk] = 1)
   ```

5. **Late Delivery Rate**:
   ```dax
   Late Delivery Rate = DIVIDE([Delayed Orders], [Total Orders], 0)
   ```

6. **Average Shipping Delay (Actual vs Scheduled)**:
   ```dax
   Avg Actual Days = AVERAGE(orders[days_for_shipping_real])
   ```
   ```dax
   Avg Scheduled Days = AVERAGE(orders[days_for_shipping_scheduled])
   ```

---

## 3. Dashboard Layout & Visualization Pages

To ensure a premium design, use a **Dark Slate Theme** (Background: `#1A1D24`, Cards: `#252932`, Accents: Teal `#00B4D8` for on-time, Coral/Red `#FF5C5C` for delays, and Gold/Yellow `#F2C94C` for warnings).

### Page 1: Executive Logistics Overview
*Purpose: High-level dashboard for logistics managers.*

*   **Top KPI Cards** (3-4 columns):
    *   Total Sales (Format: `$0.0M`)
    *   Total Profit (Format: `$0.0M`)
    *   Total Orders (Format: `0.0K`)
    *   **Late Delivery Rate** (Format: `0.0%` with conditional text color: Red if >50%, Green if <10%)
*   **Main Visual (Left)**: Map or Filled Map
    *   Location: `orders[order_country]`
    *   Bubble Size: `[Total Orders]`
    *   Color Saturation: `[Late Delivery Rate]` (diverging from light teal to deep red)
*   **Main Visual (Right)**: Donut Chart
    *   Legend: `orders[shipping_mode]`
    *   Values: `[Total Orders]`
*   **Trend Visual (Bottom)**: Area Chart
    *   Axis: `orders[order_date]` (grouped by Month)
    *   Values: `[Late Delivery Rate]` and `[Avg Actual Days]`

### Page 2: Supply Chain Delay Bottlenecks
*Purpose: Deep-dive into delayed areas and bottlenecks.*

*   **Slicers (Top Ribbon)**: Market, Customer Segment, Shipping Mode
*   **Horizontal Bar Chart**: Delay Rate by Region
    *   Y-Axis: `orders[order_region]`
    *   X-Axis: `[Late Delivery Rate]`
*   **Column Tree/Matrix Visual**: Delay Rate by Department & Category
    *   Rows: `departments[department_name]`, `categories[category_name]`
    *   Columns: `[Total Orders]`, `[Delayed Orders]`, `[Late Delivery Rate]`
*   **Scatter Plot**: Sales vs Profit Loss by Country
    *   X-Axis: `[Total Sales]`
    *   Y-Axis: `[Late Delivery Rate]`
    *   Details: `orders[order_country]`

### Page 3: Financial Risk & ML Insights
*Purpose: Analyzing the business cost of delays and predictions.*

*   **KPI Cards**:
    *   **Profit Lost on Late Deliveries**: 
        ```dax
        Profit Lost = CALCULATE([Total Profit], orders[delivery_status] = "Late delivery")
        ```
*   **Bar Chart**: Sales & Profits by Delivery Status
    *   X-Axis: `orders[delivery_status]`
    *   Y-Axis: `[Total Sales]` and `[Total Profit]` side-by-side
*   **Python Prediction Integration**:
    *   You can load the test predictions or use a Python Script inside Power Query Editor to run the saved model `best_xgb_model.pkl` on new orders, adding a `Predicted_Delay_Risk` column to flag future high-risk orders.