When reporting logic belongs upstream instead of in the BI layer

Move reporting logic upstream when changing it would change what the business believes the number means.

Leave presentation logic in the BI layer. The boundary is semantic consequence, not whether the expression is technically difficult.

Presentation belongs close to the report

Keep logic local when it affects only how one report communicates governed data:

• display labels and formatting
• sort order
• chart-specific reference lines
• visual grouping used by one audience
• optional parameters for exploration
• ratios or percentages whose components are already governed and whose use is genuinely local

This work benefits from proximity to the interface. Forcing every label and visual calculation through the warehouse increases delivery time without improving shared trust.

The local rule should remain easy to inspect and shouldn’t acquire consumers outside the report.

Shared meaning belongs in a governed model

Move logic when it defines or changes:

• KPI numerator or denominator
• inclusion and exclusion policy
• entity status or classification
• source precedence
• currency or timezone treatment
• attribution
• deduplication
• historical restatement
• customer, product, order, or campaign identity
• a field used by several reports, exports, or workflows

These decisions need version control, tests, ownership, lineage, and one implementation. A calculated field copied into three dashboards is already a shared model, merely one with a hostile development environment.

Upstream can mean a warehouse transformation, a version-controlled semantic layer, or another governed service that owns the definition. It doesn’t have to mean one enormous SQL table. It does need to mean the logic is reusable and reviewable outside one report’s editing UI.

Reuse is evidence, not the only threshold

The second copy makes the problem obvious, but some logic should move before reuse.

A report-local formula that controls executive compensation, regulatory reporting, customer communication, or operational automation has high semantic weight even if only one dashboard consumes it. Consequence alone justifies stronger ownership.

Conversely, the same harmless display bucket can appear in two reports without demanding a warehouse project. Apply judgment to the failure cost, not a mechanical copy count.

Fix grain before centralizing the expression

Repeated BI logic often compensates for a missing model boundary.

A calculated distinct count may hide an order-to-line join that multiplied rows. A local status rule may combine source states at an undefined grain. A blend may reconcile campaign and customer names because no shared identifiers exist.

Don’t merely copy the expression upstream. Define the decision model: one row per what, identified how, valid at which time, sourced from where, and corrected through which mechanism.

The blending problem is usually solved by comparable upstream grains and keys, not by transplanting the same join into a scheduled query.

Choose the smallest governed home

Not every shared metric needs a new physical table.

Use:

• a view when the logic is cheap, stable, and useful live
• a scheduled or incremental table when repeated computation or latency matters
• a semantic model when governed measures and dimensions can be reused safely there
• code when the transformation isn’t naturally relational
• a mapping or configuration table when business owners need controlled changes

The location should make definition, change, and serving cost legible. “Put everything in BigQuery” isn’t any more thoughtful than “leave everything in the dashboard.”

Migrate without creating two truths

Inventory every report and export that implements the old rule. Build the governed field or measure, compare outputs over representative periods, investigate differences, and switch consumers deliberately.

Remove or deprecate the local calculation after migration. Keeping both implementations “for safety” creates the exact drift the move was meant to remove.

Version material definition changes. Decide whether history should be restated or whether the new definition applies from an effective date. A renamed metric with silently rewritten history damages trust even when the new logic is better.

Keep the BI layer from rebuilding the model

Restrict or review report editing for governed metrics. Provide discoverable fields with clear names and descriptions. Mark raw columns that shouldn’t be recombined casually. Give analysts a development path for proposing new definitions without waiting through a ceremonial queue.

If the governed route is painfully slow, local logic will return because people still need answers. The platform owns that outcome. Enforcement without a usable contribution path merely moves semantic drift into personal spreadsheets.

Use a simple review test

Ask what happens if two reports implement this rule differently.

If the answer is cosmetic, keep it local. If people would make different decisions, reconcile different financial values, route different work, or lose confidence in the metric, move it upstream.

The BI layer should decide how governed meaning is presented. Once it decides the meaning itself, the report has become an application with no proper code review and a surprisingly large user base.