Business Intelligence Tools

The previous lesson fixed the definition of the metric with six items. One of the items said “the window closes at period 3”. Meter readings are not ready the moment the window closes: some enter the source on the same day, some weeks later. When the same definition is applied to the same dataset on two different days, it gives two different numbers, and both conform to the definition.

The report stands on top of the source, and there is a tool in between. This tool is referred to not by its product but by its parts, and it has four of them: the data source connector that connects to the source, the report data model built on top of the source, the refresh scheme that determines how often the model is refreshed, and the presentation layer that puts the number in front of the reader. The measure of this lesson comes from the second and the third parts: the number that the refresh lag writes into the report and the number of questions the model closes permanently.

• RP13. The dataset is inherited from the Data Collection and Preparation course and is fictional: a reading table of 3,199 rows, 1,329 subscribers, three periods, five regions. The seed is written into the code.
• RP14. The arrival lag of each reading is also fictional and is drawn with a separate seed. The lag does not change the value of the reading, it only determines at which moment the reading becomes visible.
• RP15. Two metrics are tracked: the period 3 reading rate (subscribers with a period 3 reading / registered subscribers) and the period 3 mean consumption.
• RP16. Three refresh schemes: the live query reads the source at report time, the periodic extract is refreshed every seven days, the pre-aggregated model is aggregated once at period close and is not aggregated again.
• RP17. The report data model is at the region-period grain; two designs are tried and they differ only in the fields they store.
• RP18. The accuracy comparison is not made with ==; the threshold is $10^{-9}$ and its rationale is the digit accumulation of floating-point summation.

Connecting to the Source

# refresh.py — MODEL: the fictional dataset from K03 is generated with the same seed; a
# fictional arrival lag is added to each reading and the report is read under three refresh schemes.
import math

SEED, RAW, M32 = 20260218, 1400, 0xFFFFFFFF
REGION = [("north", 0.28, 21), ("south", 0.22, 17), ("east", 0.18, 26),
          ("west", 0.14, 14), ("central", 0.18, 23)]


def rng(t):
    x = ((t ^ (t >> 16)) * 2246822507) & M32
    x = ((x ^ (x >> 13)) * 3266489909) & M32
    s = [(x ^ (x >> 16)) & M32]

    def next_value():
        s[0] = (s[0] * 1664525 + 1013904223) & M32
        return s[0] / 4294967296
    return next_value


def pick(u, w):
    t = 0.0
    for i, x in enumerate(w):
        t += x
        if u < t:
            return i
    return len(w) - 1


SUBSCRIBERS, DISCARDED = [], []
for i in range(RAW):
    r = rng(SEED + i)
    b = REGION[pick(r(), [x[1] for x in REGION])]
    pick(r(), [0.06, 0.24, 0.30, 0.24, 0.11, 0.05])
    pick(r(), [0.08, 0.14, 0.27, 0.34, 0.17])
    (DISCARDED if r() < 0.046 else SUBSCRIBERS).append(
        {"subscriber_id": 10001 + i, "region": b[0], "base": b[2]})
VALID = {k["subscriber_id"] for k in SUBSCRIBERS}
READINGS = {}
for k in SUBSCRIBERS + DISCARDED:
    r = rng(SEED + 7000 + k["subscriber_id"])
    for d in range(pick(r(), [0.05, 0.12, 0.21, 0.62])):
        t = 0.0 if r() < 0.038 else math.floor(
            k["base"] * math.exp((r() + r() + r() - 1.5) * 0.62) * (1 - d * 0.05) * 100
            + 0.5) / 100
        if k["subscriber_id"] in VALID:
            READINGS.setdefault(k["subscriber_id"], {})[d + 1] = t
        r()

# Each reading enters the source some time after the period closes. The lag is fictional and is
# drawn with a separate seed; it does not change the value of the reading, only its visibility.
ARRIVAL_LAG = {}
for k in SUBSCRIBERS:
    r = rng(SEED + 120000 + k["subscriber_id"])
    for d in sorted(READINGS.get(k["subscriber_id"], {})):
        ARRIVAL_LAG[(k["subscriber_id"], d)] = pick(r(), [0.58, 0.19, 0.13, 0.07, 0.03]) * 6


def snapshot(day):
    """The source snapshot in which the readings whose lag does not exceed the day value are visible."""
    g = {}
    for (a, d), lag in ARRIVAL_LAG.items():
        if lag <= day:
            g.setdefault(a, {})[d] = READINGS[a][d]
    return g


def metrics(data):
    """Two metrics: the period 3 reading rate and the period 3 mean consumption."""
    d3 = [o[3] for o in data.values() if 3 in o]
    return len(d3) / len(SUBSCRIBERS) * 100, sum(d3) / len(d3), len(d3)


FULL = snapshot(10 ** 6)
final = metrics(FULL)
print(f"fictional dataset (seed {SEED}): {len(SUBSCRIBERS)} subscribers, {len(ARRIVAL_LAG)} readings")
print(f"arrival lag distribution (days): " + ", ".join(
    f"{g}: {sum(1 for v in ARRIVAL_LAG.values() if v == g)}" for g in (0, 6, 12, 18, 24)))
print(f"final figures at the source — reading rate {final[0]:.3f}, "
      f"period 3 mean {final[1]:.3f} m3 ({final[2]} readings)")

fictional dataset (seed 20260218): 1329 subscribers, 3199 readings
arrival lag distribution (days): 0: 1841, 6: 608, 12: 444, 18: 213, 24: 93
final figures at the source — reading rate 63.055, period 3 mean 18.321 m3 (838 readings)

Of the 3,199 readings, 1,841 are in the source on the closing day and the remaining 1,358 enter on later days. The final figures of the source are also read from this list, and the rest of the lesson is measured against these two numbers.

Three Refresh Schemes

# Three refresh schemes. The report is produced 12 days after the close of period 3; the schemes
# differ only in how far behind they see the source.
REPORT_DAY = 12
SCHEME = [("live query", REPORT_DAY, 0),
          ("periodic extract (every 7 days)", 7, REPORT_DAY - 7),
          ("pre-aggregated model", 0, REPORT_DAY)]
print(f"report day: {REPORT_DAY} days after the close of period 3\n")
print(f"{'scheme':<32}{'refresh lag':>19}{'reading rate':>13}{'mean':>10}"
      f"{'readings':>9}{'gap from final':>15}")
for name, cut, lag in SCHEME:
    k, o, n = metrics(snapshot(cut))
    print(f"{name:<32}{str(lag) + ' days':>19}{k:>13.3f}{o:>10.3f}{n:>9}"
          f"{o - final[1]:>+10.3f} m3")
print(f"{'final value at source':<32}{'-':>19}{final[0]:>13.3f}{final[1]:>10.3f}{final[2]:>9}"
      f"{0.0:>+10.3f} m3")

report day: 12 days after the close of period 3

scheme                                  refresh lag reading rate      mean readings gap from final
live query                                   0 days       56.659    18.128      753    -0.193 m3
periodic extract (every 7 days)              5 days       48.081    18.160      639    -0.161 m3
pre-aggregated model                        12 days       36.042    18.166      479    -0.155 m3
final value at source                             -       63.055    18.321      838    +0.000 m3

The choice among the three schemes is not a choice of accuracy but a trade-off. The live query gives the freshest number and loads its cost onto the source every time a report is opened; the pre-aggregated model never strains the source and pays its cost in freshness; the periodic extract sits between the two. Which end of the trade-off was chosen is not written into the number itself.

Same day, same source, same metric definition: the reading rate comes out 56.659, 48.081 and 36.042. All three are computed correctly and all three are below the final 63.055. That even the live query is incomplete shows that the lag is not in the report but in the source itself: on the report day those readings have not entered yet.

The second column carries the real lesson. The same lag moves mean consumption only between 18.128 and 18.166, that is by only 0.038 m³; it moves the reading rate by 20.617 points. The reason is that the two are a count and a ratio: late-arriving readings enlarge the numerator, not the denominator, but their values are not systematically different from those of the early arrivals. Of two metrics standing side by side on a dashboard, one can be almost insensitive to the refresh scheme while the other depends on it entirely, and this comes not from the definition of the metric but from its kind.

What the Report Data Model Fixes

The report data model is not a copy of the source; it is a structure whose grain and field set have been chosen. Neither can be loosened afterwards: once the model is aggregated at the region-period grain, every question at the subscriber level is closed permanently.

# Report data model: a structure built on top of the source, with its grain and fields fixed.
# Two designs are tried; both are at the region-period grain, their fields differ.
MODEL_A = {}   # only the mean is stored
MODEL_B = {}   # the sum and the count are stored
for b, _, _ in REGION:
    v = [FULL[k["subscriber_id"]][3] for k in SUBSCRIBERS
         if k["region"] == b and 3 in FULL.get(k["subscriber_id"], {})]
    MODEL_A[b] = sum(v) / len(v)
    MODEL_B[b] = (sum(v), len(v))

raw3 = sorted(o[3] for o in FULL.values() if 3 in o)
single = [k["subscriber_id"] for k in SUBSCRIBERS if FULL.get(k["subscriber_id"], {}).get(3, 0.0) > 0][0]
CORRECT = {"dataset mean": sum(raw3) / len(raw3),
           "regional mean (east)": MODEL_A["east"],
           "reading rate": len(raw3) / len(SUBSCRIBERS) * 100,
           "median consumption": (raw3[len(raw3) // 2 - 1] + raw3[len(raw3) // 2]) / 2,
           "subscribers above 40 m3": float(sum(1 for v in raw3 if v > 40)),
           "one subscriber reading": FULL[single][3]}
ANSWER = {
    "dataset mean": (sum(MODEL_A.values()) / len(MODEL_A),
                     sum(x[0] for x in MODEL_B.values())
                     / sum(x[1] for x in MODEL_B.values())),
    "regional mean (east)": (MODEL_A["east"], MODEL_B["east"][0] / MODEL_B["east"][1]),
    "reading rate": (None, sum(x[1] for x in MODEL_B.values()) / len(SUBSCRIBERS) * 100),
    "median consumption": (None, None), "subscribers above 40 m3": (None, None),
    "one subscriber reading": (None, None)}
EPS = 1e-9


def status(v, d):
    if v is None:
        return "unproducible"
    return "correct" if abs(v - d) < EPS else f"wrong ({v - d:+.3f})"


print(f"{'question':<26}{'source (subscriber-period grain)':>34}{'model A':>18}{'model B':>18}")
counter = [0, 0, 0]
for s, d in CORRECT.items():
    a, b = ANSWER[s]
    for i, v in enumerate((d, a, b)):
        counter[i] += 1 if v is not None and abs(v - d) < EPS else 0
    print(f"{s:<26}{format(d, '.3f'):>34}{status(a, d):>18}{status(b, d):>18}")
print(f"\ncorrectly answered questions: source {counter[0]}/6, model A {counter[1]}/6, "
      f"model B {counter[2]}/6")

question                    source (subscriber-period grain)           model A           model B
dataset mean                                          18.321    wrong (-0.192)           correct
regional mean (east)                                  22.394           correct           correct
reading rate                                          63.055      unproducible           correct
median consumption                                    17.845      unproducible      unproducible
subscribers above 40 m3                                8.000      unproducible      unproducible
one subscriber reading                                21.570      unproducible      unproducible

correctly answered questions: source 6/6, model A 1/6, model B 3/6

The two models are at the same grain and their only difference is the field they store: one stores the mean, the other the sum and the count. This difference opens and closes three questions. When the sum and the count are stored, the dataset mean is built correctly by adding up the sums of the regions and dividing by their counts; when only the mean is stored, it cannot be built, because the weights have been thrown away.

The first row is the most important result of this lesson. Model A does not say unproducible to the dataset mean question; it gives a number that is wrong by 0.192 m³. The mean of the five regional means is mathematically the result of an operation and appears on a dashboard as the dataset mean. An answer that cannot be produced makes itself known; an answer that is produced wrong does not.

The bottom three rows are the cost of the grain. The median, the number of subscribers above a threshold and the reading of a single subscriber cannot be produced by either model, because these are not additive fields: it is impossible to build the median of a region from the medians of its parts, or the dataset median from the medians of five regions. The report data model looks like a speed decision; it is a decision that narrows the set of questions that can be asked, and it has closed three of the six questions permanently.

The Same Metric, Fifteen Reports

# The same metric, the same source, five different report days. The pre-aggregated model is
# aggregated once at period close and is not aggregated again; the extract is refreshed every seven days.
DAYS = (0, 6, 12, 24, 40)
CUT = {"live query": lambda g: g,
       "periodic extract": lambda g: 7 * (g // 7),
       "pre-aggregated model": lambda g: 0}
print(f"{'scheme':<26}" + "".join(f"{'day ' + str(g):>10}" for g in DAYS))
values = set()
for name, f in CUT.items():
    row = []
    for g in DAYS:
        v = metrics(snapshot(f(g)))[0]
        values.add(round(v, 3))
        row.append(v)
    print(f"{name:<26}" + "".join(f"{v:>10.3f}" for v in row))
print(f"{'final value at source':<26}" + "".join(f"{final[0]:>10.3f}" for g in DAYS))
print(f"\n{len(CUT)} schemes x {len(DAYS)} report days = {len(CUT) * len(DAYS)} reports, "
      f"{len(values)} distinct values; all are computed correctly")
print(f"widest spread: {max(values) - min(values):.3f} points "
      f"({min(values):.3f} and {max(values):.3f})")
print(f"the number written into the report therefore cannot be written alone: "
      f"the value, the data cut-off and the refresh lag are written together")

scheme                         day 0     day 6    day 12    day 24    day 40
live query                    36.042    48.081    56.659    63.055    63.055
periodic extract              36.042    36.042    48.081    60.873    63.055
pre-aggregated model          36.042    36.042    36.042    36.042    36.042
final value at source         63.055    63.055    63.055    63.055    63.055

3 schemes x 5 report days = 15 reports, 5 distinct values; all are computed correctly
widest spread: 27.013 points (36.042 and 63.055)
the number written into the report therefore cannot be written alone: the value, the data cut-off and the refresh lag are written together

The first two rows converge: the live query settles on the final value on the twenty-fourth day, the periodic extract on the fortieth. The third row never converges and stays at 36.042, because it was aggregated once at period close and never again. This row is not a flaw but a decision; the flaw is that the decision is not written in the report.

The fifteen reports have five distinct values, and there are 27.013 points between the two at the extremes. All of them were produced from the same source, with the same definition and with correct computation. The reader of a report cannot resolve the difference between “reading rate 36.042” and “reading rate 63.055” by looking at the number; the only things that resolve the difference are the data cut-off and the refresh lag standing next to the number. If these two are not in the report, the value itself cannot be read back.

Summary

• A business intelligence tool is referred to by four parts: data source connector, report data model, refresh scheme and presentation layer. The measure comes from the second and the third parts.
• In a report produced on the same day, the reading rate comes out 56.659 with a live query, 48.081 with a periodic extract and 36.042 with a pre-aggregated model; the final value at the source is 63.055 and all three numbers are correct.
• The same lag moves mean consumption by only 0.038 m³ and the reading rate by 20.617 points; the sensitivity comes not from the definition of the metric but from its kind.
• The report data model fixes a grain and a field set: the model that stores only the mean produces the dataset mean wrong by 0.192 m³, while the model that stores the sum and the count answers the same question correctly.
• The three non-additive questions (the median, the number of subscribers above a threshold, the reading of a single subscriber) cannot be produced by either model; the grain decision closes three of six questions permanently.
• Three schemes and five report days produce fifteen reports, five distinct values and a spread of 27.013 points; the only things that resolve the difference are the data cut-off and the refresh lag standing next to the number.

Next Step

The number now comes with the right definition, from the right model and with a written cut-off. What the reader holds, however, is still a number. For a decision to emerge, the number has to sit in a chain: finding, comparison, candidate cause, uncertainty and recommendation. When a link of the chain is missing, the reader completes it on their own, and which direction they take while completing it is not written down. The next lesson builds this chain and, by removing the links one at a time, counts the inference the reader is forced to make; exactly where the margin of variation of the Statistical Analysis and Exploratory Data Analysis course stands in this chain is also settled there.