T16: Multi-Source Integration

Step 1 — Why this is harder than it looks#

Combining sources is technically straightforward: create connections, create measures, write a calculation.

What makes it hard is that different systems disagree about time, and the disagreement is silent.

A cost-per-unit figure combining production from a historian and price from an API is only correct if both describe the same period. If the API's timestamps are read as local when they were UTC, the price is offset by hours, and the KPI is wrong in a way that looks entirely plausible.

Step 2 — Establish what each source means by a timestamp#

Before building anything, for each source, answer:

1. What time zone are its timestamps in?
2. Do they carry an explicit offset?
3. If numeric, are they seconds or milliseconds?

Then verify rather than assume: retrieve one value and compare its timestamp against what the source system shows for the same record. This is five minutes and it prevents the entire class of problem.

Step 3 — The historian source#

You have this already: Bottles Daily from the simulator, via the Counter-then-Sum chain.

This is your reference. Historian data is usually the best-behaved of the three, and it is what the other sources have to align with.

Step 4 — The SQL source#

Production orders, material costs, and quality results commonly live in a relational database.

Create a Microsoft SQL Database connection, then a retrieved measure with a query:

SELECT MaterialCost AS Value
FROM   ProductionCosts
WHERE  LineNo = '[ModelAttribute("Filler Number")]'
  AND  CostDate >  [PeriodStart]
  AND  CostDate <= [PeriodEnd]

Three things in that query matter:

The half-open range. > on the start, <= on the end. With both bounds inclusive, a row on a boundary is counted in two consecutive periods, and daily totals come out slightly high — by an amount too small to notice and large enough to matter over a month.

The model attribute token, so one query on the connection serves every line.

Index the filtered columns. This query runs once per measure per interval. Without an index on LineNo and CostDate, every retrieval scans the table — and that is the single largest cause of Flow-related database load complaints.

Test the query in a database tool with real bounds substituted, and look at the execution plan, before building measures on it.

Step 5 — The REST source#

An energy price, a carbon intensity figure, a weather value — anything with an HTTP API.

Create a Web Service connection:

Then a measure describing where the values are in the response:

Two header notes that cause avoidable failures:

A header needs a name and a value. Authorization:Bearer [Key], not Bearer alone. Bearer is part of the value; Authorization is the name.

Enable caching. Many APIs return a document containing dozens of values. Without caching, twenty measures reading it make twenty identical calls per interval — and public APIs meter calls.

Step 6 — Align the intervals#

Your three sources will not naturally agree on interval:

• Historian: continuous, aggregated to Daily in Infohub
• SQL: one row per day, already daily
• API: perhaps half-hourly, aggregated to Daily in Infohub

Build each to the same interval before combining. A calculation reads each dependency at its own interval, so every input must exist at Daily before the KPI can use it.

This is the practical reason the aggregation chain comes before the calculation, and it is worth stating because the temptation with multiple sources is to reach across intervals in one clever expression.

Step 7 — Build the KPI#

SafeDivide(
    (Measure_<materialCostId>.Value ?? 0)
      + (Measure_<energyDailyId>.Value ?? 0) * (Measure_<energyPriceId>.Value ?? 0),
    Measure_<bottlesDailyId>.Value) * 1000

Guarded, and each input null-coalesced so one missing source does not void the whole calculation — though consider whether that is what you want. A cost figure computed with a missing material cost is wrong, not partial, and returning null might be more honest:

(Measure_<materialCostId>.Value == null || Measure_<bottlesDailyId>.Value == null)
    ? (double?)null
    : SafeDivide(...) * 1000

Decide deliberately. This is the multi-source version of the zero-versus-null question from T04, and the stakes are higher because sources fail independently.

Step 8 — Expect the sources to fail separately#

A historian on the plant network, a SQL server in the data centre, and an API on the internet have completely different failure modes and availability.

Design for it:

Guard for missing inputs, as above.

Put limits on each input measure, so a source that has quietly stopped is visible rather than silently producing a wrong KPI.

Watch for staleness. A cost figure computed from yesterday's price because the API failed is worse than no figure, because it looks current.

Do not alert on every failed API call. Cloud and internet sources fail intermittently as normal behaviour; alerting on each one trains people to ignore the alerts within a week.

Step 9 — Verify against reality#

Before anyone reports on a cross-source KPI, verify it end to end:

1. Take one day.
2. Get the production figure from the plant's own records.
3. Get the material cost from the source database directly.
4. Get the energy price from the API directly.
5. Compute the KPI by hand.
6. Compare against Infohub.

If they differ, the difference is almost always a period alignment problem — one source's day is not the same day as another's. That is exactly what step 2 was for.

What you have learned#

Combining sources is a time problem, not a connection problem. Establish and verify what each source means by a timestamp.

Half-open ranges in queries. Both bounds inclusive double-counts boundary rows.

Index the columns you filter on. Your query runs thousands of times a day.

Capture a real API response before configuring paths. And check the timestamp unit.

Headers need a name and a value. Authorization:Bearer [Key].

Enable response caching and respect rate limits.

Align intervals before combining.

Sources fail independently. Guard, limit, and decide whether a partial answer is honest.

Verify by hand once, before anyone relies on it.

Try this before moving on#

Add a staleness measure for the API source: how long since its last value arrived, with a limit on it.

Then put it on the dashboard beside the cost KPI. A cost figure has no visible expiry date, and a stale price silently produces a confident, wrong number for as long as nobody checks. Making the freshness visible is what turns a fragile integration into a trustworthy one.

Next: T17: Pareto Analysis — turning your downtime events into an improvement plan.

See also#

• T15: Model Attributes
• T17: Pareto Analysis
• SQL Databases
• Web Service Data Sources