T14: Multi-Interval Models

Step 1 — A case where minutely is genuinely necessary#

Electricity billing is the classic example. A demand charge is based on the highest demand in the month, measured over short intervals — so an hourly average hides exactly the peak you are billed for.

Under a new folder Utilities → Power, create a metric Demand:

Maximum all the way up. This works correctly because the maximum of maxima is the maximum — unlike an average of averages, which is only correct when every period carries equal weight.

Step 2 — See why the interval mattered#

Compare Peak Demand Hourly against a plain hourly average of the same tag.

The average smooths the peak away. A line drawing 8 kW for two minutes and 3 kW for the rest of the hour averages to something modest — and the demand charge is based on the 8.

That is what "the interval must match the question" means. Here, minutely is not a luxury.

Step 3 — Now count the cost#

Look at what you just built.

A minutely measure produces about 526,000 values a year. An hourly measure produces about 8,800. So one minutely measure costs roughly as much as sixty hourly ones, in storage and in calculation — and everything above it inherits that volume.

Step 4 — Build the energy chain properly#

Contrast with energy consumption, where minutely adds nothing. In the same metric:

Note two things.

Hourly is the base. Nobody needs minutely energy consumption; the monthly bill is the same either way.

The chain is contiguous — each level derives from the one below. That gives you every intermediate period people ask for, and it means you can walk down from a wrong month to the wrong day to the wrong hour.

Step 5 — Watch the branch#

Energy Weekly and Energy Monthly both derive from Energy Daily. That is correct — weeks and months are parallel views of the same days.

What you must not do is build Energy Monthly from Energy Weekly. A week frequently spans a month boundary, so the arithmetic simply does not work, and the monthly figure will not reconcile with its days.

If you ever see a monthly number that disagrees with the sum of its days, this is the first thing to check.

Step 6 — Year-on-year comparison#

Add a measure comparing this month against the same month last year:

Measure_<energyMonthlyId>[-12].Value

And the variance:

SafeDivide(
    Measure_<energyMonthlyId>.Value - (Measure_<energyMonthlyId>[-12].Value ?? 0),
    Measure_<energyMonthlyId>[-12].Value) * 100

On a fresh instance there is no data twelve months back, so these will be empty. That is correct behaviour — and it is worth seeing, because a comparison measure that quietly returns zero instead of nothing would understate every variance.

Step 7 — Rolling windows instead of extra measures#

A "trailing twelve months" total is not an aggregation. It is a calculation with a window:

Measure_<energyMonthlyId>[-12, 0].Values.Where(v => v.HasValue).Sum()

Twelve measures plus a sum would also work, and would be three times the model and would break the day somebody asks for six months instead.

Step 8 — Choose the interval per audience#

The interval should match the decision the measure supports:

A monthly measure is useless for diagnosing this morning; a minutely one is useless in a board pack. Building one and hoping it serves both is the most common cause of dashboards nobody uses.

Step 9 — Prune#

Look at what you have built and ask which intervals anyone will actually open.

Energy Quarterly — does anybody report quarterly? If not, delete it and let Energy Yearly derive from Energy Monthly.

What you have learned#

Match the interval to the question. Peak demand needs minutely; consumption does not.

Maximum aggregates correctly up a chain, because the maximum of maxima is the maximum.

Minutely costs about sixty times hourly, and everything above it inherits that.

Chains are contiguous and branch from the right level. Weeks and months both come from days; months never come from weeks.

Reach other periods with [-12] for year-on-year, and [-12, 0] for a rolling window.

A rolling window is one measure, not twelve.

Only build the intervals someone uses.

Try this before moving on#

Work out the annual value count for your model.

Count your measures at each interval, multiply by the values-per-year table above, and total it. Then ask whether the minutely measures in that total are each earning their place.

On a real model this exercise usually finds a chain built to minutely for a reason nobody remembers, which alone accounts for a large share of the database.

Next: T15: Model Attributes — one expression serving every instance.

See also#

• T13: OEE
• T15: Model Attributes
• Interval Types and Time Schemes
• Engine Performance and Tuning