Power BI for Supply Chain & Manufacturing: Dashboards Ops Teams Actually Use in Chicago Plants

Scenario: Chicago Ops Team Drowning in Reports, Starving for Insight

You’re supporting an operations group with:

• Two plants in the Chicago area (City + suburbs)
• A central warehouse in the region
• ERP on SQL Server hosted in a local data center
• MES data in a separate SQL database
• Inventory snapshots in Excel files on SharePoint

They already have a Power BI workspace called “Supply Chain & Manufacturing”. The problem:

• Morning stand‑up report takes 20–40 seconds to load visuals
• Production supervisors say the numbers don’t match what MES shows
• Planner wants a single view of OTIF (On‑Time In‑Full) by customer and plant
• Plant managers want downtime by line and cause with drill‑through to specific shifts

We’ll design one core dashboard/report that solves these issues, focusing on:

1. Data model and storage mode choices that stay fast at typical manufacturing volumes
2. Refresh patterns that work with overnight batch loads and near‑real‑time MES
3. Layout and interaction patterns that match how ops people actually consume data
4. A few DAX patterns that avoid common pitfalls in production environments

1. Get the Data Model Right Before You Draw a Single Visual

Power BI will let you drag visuals immediately, but for supply chain and manufacturing, the model is where most future pain comes from.

1.1 Choose Storage Modes Based on Source and Use Case

For this scenario:

• ERP (Orders, Shipments, Inventory transactions) on SQL Server
  • Use Import mode for core fact tables (orders, shipments, inventory movements) where daily or intra‑day refresh is acceptable.
• MES (Production events, downtime) on SQL Server
  • If the ops team needs near‑real‑time (5–15 minutes), you can use DirectQuery for a narrow set of tables or views.
• Inventory snapshots (Excel on SharePoint)
  • Use Import mode via Power Query, scheduled refresh.

Key points (current behavior as of late 2026):

• Mixed storage modes in one model are supported via Composite models.
• Visuals that combine Import and DirectQuery tables can trigger DirectQuery queries where relationships cross storage modes.
• DirectQuery performance is constrained by the source, network, and query design; it does not cache like Import.

A practical pattern:

• Keep one primary model in a centralized dataset.
• Limit DirectQuery to MES views specifically optimized for reporting (narrow, indexed, no complex joins).

1.2 Model Design: Facts and Dimensions That Ops Understand

For the Chicago scenario, a clean star schema might look like:

Fact tables:

• FactOrders – order lines with requested and confirmed dates
• FactShipments – shipment lines with shipped quantities and dates
• FactInventoryMovements – receipts, issues, adjustments
• FactProduction – production quantities by line, shift, and time
• FactDowntime – downtime events with duration and reason codes

Dimension tables:

• DimDate
• DimPlant
• DimCustomer
• DimProduct
• DimProductionLine
• DimShift
• DimDowntimeReason

Core practices that matter here:

• Use one DimDate with a continuous date range and mark it as a Date table in Power BI.
• Use single‑direction relationships from dimensions to facts by default.
• Avoid bi‑directional filters except where you can’t solve a requirement another way, and then test performance.

A simple DimDate generation in Power Query (Import mode):

let
    StartDate = #date(Date.Year(DateTime.LocalNow()) - 2, 1, 1),
    EndDate   = #date(Date.Year(DateTime.LocalNow()) + 1, 12, 31),
    Source    = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1,0,0,0)),
    ToTable   = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    AddYear   = Table.AddColumn(ToTable, "Year", each Date.Year([Date]), Int64.Type),
    AddMonth  = Table.AddColumn(AddYear, "Month", each Date.Month([Date]), Int64.Type),
    AddDay    = Table.AddColumn(AddMonth, "Day", each Date.Day([Date]), Int64.Type),
    AddYM     = Table.AddColumn(AddDay, "YearMonth", each Date.ToText([Date], "yyyy-MM"), type text)
in
    AddYM

This gives you a stable calendar that can support OTIF and trend analysis without relying on auto‑date/time.

2. Refresh Strategy for Overnight Batches and Near‑Real‑Time MES

Chicago plants typically run overnight ERP batches and near‑continuous MES feeds. Your Power BI refresh needs to respect both.

2.1 Scheduled Refresh for ERP and Inventory

With Import mode, you can schedule dataset refreshes in the Power BI service (frequency depends on license/capacity):

• For Power BI Pro in shared capacity, up to 8 scheduled refreshes per day are supported.
• For Premium or Fabric capacities, higher refresh frequencies are supported than in shared capacity.
• Typical pattern for ops:
  • Full refresh at ~4:30 AM (after ERP batch completes)
  • Optional mid‑day refresh if planners need updated orders/shipments

Best practices that matter:

• Use incremental refresh for large fact tables like FactOrders and FactShipments.
• Partition by a date column that aligns with how data is loaded (e.g., OrderDate or ShipmentDate).

Example incremental refresh policy for FactShipments (configured in Power BI Desktop using parameters):

• RangeStart and RangeEnd parameters defined as DateTime.
• Filter FactShipments[ShipmentDate] using these parameters.
• Set policy in Model view:
  • Store: last 3 years
  • Refresh: last 10 days

This keeps historical data stable while only reprocessing recent partitions.

2.2 DirectQuery for MES: Where It Helps and Where It Hurts

For MES data, DirectQuery can give near‑real‑time views of downtime and production events, but with trade‑offs:

• Each visual query hits the source database.
• Complex measures and cross‑filtering can generate heavy SQL.
• Import‑like performance is not guaranteed.

To keep the ops dashboard usable:

• Restrict DirectQuery to one or two key fact tables (e.g., FactDowntime and FactProduction) via views that:
  • Pre‑join static reference data where appropriate
  • Expose only columns you actually need
  • Use indexed filter columns (e.g., PlantId, ProductionLineId, EventStartTime)

If the floor team only needs near‑real‑time for open downtime events and last few hours of production, consider:

• Import historical MES data nightly.
• Use DirectQuery only for current‑shift or last‑day slices.

That hybrid pattern keeps the main dashboard fast while still supporting live views when needed.

3. Layout: One Ops Page, Not a BI Toybox

The Chicago ops team wants one page they can use in the daily stand‑up, not six tabs of charts.

3.1 Core Page Structure

Build a single “Ops Overview” page with sections:

1. Filters bar at the top
   • Plant
   • Production line
   • Shift
   • Date range
2. Left column: Supply chain health
   • OTIF summary card
   • Orders due today vs shipped
   • Backorder volume by customer
3. Right column: Manufacturing performance
   • Downtime by line (stacked column or bar)
   • Top downtime reasons
   • Production vs plan by line
4. Bottom: Detail table with drill‑through
   • Unified table showing orders/shipments with key metrics

Keep visuals:

• Limited and purposeful (8–10 visuals max on the main page)
• Using consistent color coding (e.g., red for below target, green for on/above target)

3.2 Use Drill‑Through Instead of More Tabs

Ops users often need to go from summary to detail for a specific line or customer.

Set up drill‑through pages:

• Line Detail page
  • Drill‑through fields: Plant, Production line, Date
  • Visuals: downtime timeline, production by hour, detail table of events
• Customer OTIF Detail page
  • Drill‑through fields: Customer, Month
  • Visuals: OTIF trend, late shipments table

This keeps the main page clean while still supporting deep dives.

4. DAX Patterns that Don’t Break Under Real Production Data

Once the model and layout are set, the measures drive whether the numbers match what the team sees in MES and ERP.

4.1 OTIF: On‑Time In‑Full That Matches Ops Definitions

You need to align with how your ops team defines OTIF. A common pattern:

• An order line is On‑Time if ShipmentDate ≤ RequestedDate.
• It is In‑Full if ShippedQty ≥ OrderedQty.

Assuming FactShipments has one row per order line shipment, and FactOrders has one row per order line, a simple measure:

OTIF Orders = 
VAR OnTimeInFullOrders =
    CALCULATE(
        DISTINCTCOUNT(FactOrders[OrderLineId]),
        FILTER(
            FactOrders,
            FactOrders[ShippedQty] >= FactOrders[OrderedQty]
                && FactOrders[ShipmentDate] <= FactOrders[RequestedDate]
        )
    )
VAR TotalOrders = DISTINCTCOUNT(FactOrders[OrderLineId])
RETURN
    DIVIDE(OnTimeInFullOrders, TotalOrders)

Notes:

• This assumes FactOrders contains ShippedQty and ShipmentDate via ETL or relationships; if not, you’d need a measure that aggregates from FactShipments and compares to ordered quantities.
• Use DISTINCTCOUNT on a stable key (OrderLineId) to avoid double‑counting.

4.2 Downtime Percentage by Line and Shift

For FactDowntime, assume:

• DurationMinutes column
• EventStartTime and EventEndTime
• Relationship to DimProductionLine and DimShift

Total downtime minutes:

Downtime Minutes = SUM(FactDowntime[DurationMinutes])

Total scheduled minutes per line and shift can be a separate table or calculated measure. For a simple model where scheduled minutes are stored in DimShift:

Scheduled Minutes = SUM(DimShift[ScheduledMinutes])

Downtime percentage:

Downtime % = 
DIVIDE(
    [Downtime Minutes],
    [Scheduled Minutes]
)

This works cleanly when filters are applied by plant, line, and date, because both measures respond to the same filter context.

4.3 Production vs Plan: Keep the Measure Simple

For FactProduction with ProducedQty, and a plan table FactProductionPlan with PlannedQty:

Produced Qty = SUM(FactProduction[ProducedQty])
Planned Qty = SUM(FactProductionPlan[PlannedQty])
Production vs Plan % =
DIVIDE(
[Produced Qty],
[Planned Qty]
)

Avoid mixing row‑level logic into these measures unless you have a specific requirement. For ops dashboards, simple aggregate comparisons tend to be more reliable and understandable.

5. Performance Tuning That Ops Actually Notice

Ops teams notice when the main page takes 10+ seconds to load or slicers feel sluggish. They won’t care about your model elegance if it’s slow.

5.1 Reduce Visual and Query Load

On the main ops page:

• Keep visuals to a manageable count; each visual can generate queries.
• Avoid complex visuals that require many queries (e.g., high‑cardinality scatter plots) unless they’re essential.

Use Power BI performance tools:

• Performance Analyzer in Power BI Desktop to inspect visual query times.
• Optimize slow measures by:
  • Reducing use of FILTER over large tables where possible.
  • Using variables (VAR) to avoid repeated calculations.

5.2 Model Size and Cardinality

Large manufacturing and supply chain datasets can grow quickly.

Key actions:

• Remove unused columns in fact tables.
• Avoid storing high‑cardinality text columns in fact tables (e.g., free‑text comments) unless needed for reporting.
• Use surrogate keys instead of long natural keys.

These steps reduce memory footprint and can improve Import model performance, especially in shared or constrained capacities.

5.3 DirectQuery Queries Over MES

For DirectQuery tables:

• Ensure source views are indexed on filter columns.
• Avoid measures that require scanning large DirectQuery tables without filters.

Ops pattern:

• Always filter MES views by Plant and Date/Time range before aggregating.
• Use slicers or page filters to enforce this.

6. Make It Local: Chicago‑Specific Operations Needs

Chicago operations have some practical constraints:

• Weather impacts shipments and plant operations.
• Multiple sites within the region share customers and carriers.

To make the dashboard more useful locally:

• Include Plant region in DimPlant (city vs suburbs) so you can slice performance by site.
• Add a simple weather impact indicator if you track it in ERP or MES (e.g., a flag for weather‑related delays), then use it in OTIF breakdowns.

These additions don’t change the core model but make the dashboard more relevant to regional discussions.

Final Takeaway: Lock the Model and Page Before You Scale

Before you roll the dashboard out to every supervisor and planner in Chicago, freeze two things:

1. The data model contracts (facts, dimensions, key relationships, core measures like OTIF and downtime).
2. The main ops page layout (filters, core visuals, drill‑through paths).

Once those are stable and validated against ERP and MES, you can safely iterate on extra pages and analyses without breaking the daily stand‑up view that ops teams actually rely on.