Reporting, Analytics, and Read Model Design

1. Core Idea

Operational data model and reporting data model have different goals.

Operational model optimizes:

• correctness,
• lifecycle transitions,
• command handling,
• invariants,
• transactions,
• service ownership,
• audit,
• fulfillment/billing execution.

Reporting/analytics model optimizes:

• queryability,
• aggregation,
• KPI consistency,
• historical trend,
• roll-up,
• performance,
• business interpretation,
• decision support.

Read model optimizes:

• UI/query access patterns,
• denormalized display,
• search/filter,
• support timeline,
• operational dashboard.

Mental model:

Operational source-of-truth is where business state is changed. Reporting/read models are derived views designed to answer questions safely and consistently.

2. Why Reporting and Read Model Design Matters

Jika reporting langsung membaca tabel operational secara sembarang:

• KPI berbeda antar team,
• revenue double counted,
• quote revisions counted wrongly,
• cancelled orders included/excluded inconsistently,
• active product count double counts bundle children,
• reporting uses current hierarchy instead of historical hierarchy,
• invoice revenue mixed with booked quote amount,
• dashboard query overloads production DB,
• stale projection used for operational decision,
• metrics have no owner,
• data lineage unclear during dispute.

Reporting data model harus punya semantics, lineage, freshness, and governance.

3. Operational Read Model vs Analytical Model

Do not use one model for all purposes.

4. Source of Truth and Derived Data

Derived data must record source.

Example read model:

customer_order_summary
- customer_id
- open_order_count
- latest_order_status
- projection_source = order-events
- last_event_id
- last_event_version
- projected_at

Analytical fact:

fact_order
- order_id
- source_system
- source_updated_at
- ingested_at
- transformation_version

Lineage is important because derived data can be stale, wrong, or rebuilt.

5. Read Model Design

Operational read model should be designed from query use case.

Example support needs:

• customer identity,
• open quotes,
• open orders,
• active products,
• open fallout,
• latest invoice,
• billing account status.

Read model:

customer_support_summary
- customer_id
- customer_number
- customer_name
- open_quote_count
- open_order_count
- open_fallout_count
- active_product_count
- latest_invoice_status
- billing_health_status
- projected_at

This avoids many synchronous calls during support UI load.

6. Read Model Freshness

Derived read models can lag.

Fields:

projected_at
last_event_id
last_aggregate_version
source_watermark
freshness_status

Freshness status examples:

• fresh,
• stale,
• rebuilding,
• failed,
• partial.

UI/API should not hide stale data when correctness matters.

Example:

{
  "data": {...},
  "projectionFreshness": {
    "status": "STALE",
    "projectedAt": "2026-07-12T10:00:00Z"
  }
}

7. Projection Update Patterns

Projection can be updated by:

Each pattern needs:

• checkpoint,
• idempotency,
• replay/rebuild,
• schema versioning,
• error handling,
• data quality checks.

8. Analytical Fact and Dimension

Analytics often uses dimensional model.

Fact table:

fact_quote
fact_order
fact_order_item
fact_charge
fact_invoice
fact_product_instance_snapshot
fact_subscription_snapshot
fact_usage

Dimension table:

dim_customer
dim_account
dim_product
dim_catalog_offering
dim_date
dim_billing_account
dim_site
dim_sales_rep
dim_organization_hierarchy

Facts store measurable events/states. Dimensions store descriptive context.

9. Fact Grain

Fact grain must be explicit.

Examples:

If grain is unclear, metrics become wrong.

10. Snapshot Facts

Some metrics need point-in-time snapshots.

Examples:

• active product count at month end,
• active subscription count daily,
• MRR by month,
• installed base by region,
• open order backlog by day,
• open fallout aging,
• billing account aging.

Snapshot fact:

fact_product_instance_daily_snapshot
- snapshot_date
- product_instance_id
- customer_id
- account_id
- billing_account_id
- product_offering_id
- status
- recurring_charge_amount

Snapshot prevents current-state overwrite from corrupting historical trend.

11. Event Facts

Event facts represent business events.

Examples:

• quote created,
• quote submitted,
• quote approved,
• quote accepted,
• order created,
• order completed,
• product activated,
• product terminated,
• invoice issued,
• payment received.

Event fact fields:

event_type
event_time
effective_time
entity_id
customer_id
amount
currency
source_event_id

Event facts support funnel and cycle-time metrics.

12. Event Time vs Effective Time vs Ingest Time

Analytics must distinguish:

Example:

Product activated effective July 1
Event received July 3
Ingested into warehouse July 4
Invoice issued August 1

Metric definitions must say which time is used.

13. KPI Definitions

Metrics must be defined, not inferred.

Examples:

Quote conversion rate

Questions:

• denominator: all quotes, submitted quotes, approved quotes, or accepted quotes?
• numerator: converted to order, accepted, or completed order?
• time basis: quote created date, accepted date, or converted date?
• revision handling: latest quote only or all revisions?
• cancelled/expired excluded?

Order completion rate

Questions:

• completed means fulfilled or billing-ready?
• partial completion counted?
• cancellation excluded?
• fallout counted as failure?

MRR

Questions:

• based on subscription, recurring charge, invoice, or billing system?
• includes discounts?
• includes tax?
• includes suspended subscriptions?
• currency conversion?
• point-in-time or average?

Without definition, dashboard becomes debate.

14. Metric Governance

Metric should have owner and definition.

Fields:

metric_definition
- metric_code
- metric_name
- description
- owner
- grain
- numerator
- denominator
- filters
- time_basis
- source_tables
- refresh_frequency
- caveats
- version

This is especially important for executive KPI.

15. Funnel Model

Quote-to-cash funnel:

Quote created
Quote configured
Quote priced
Quote submitted
Quote approved
Quote accepted
Order created
Order submitted
Order fulfilled
Product activated
Charge activated
Invoice issued
Payment received

Funnel fact can use event timestamps:

quote_id
quote_created_at
priced_at
submitted_at
approved_at
accepted_at
order_created_at
order_completed_at
product_activated_at
charge_activated_at
invoice_issued_at
payment_received_at

This supports conversion and cycle time.

16. Cycle Time Metrics

Common cycle times:

• quote creation to pricing,
• pricing to approval,
• approval to acceptance,
• acceptance to order creation,
• order creation to decomposition,
• decomposition to fulfillment start,
• fulfillment start to activation,
• activation to billing trigger,
• billing trigger to invoice,
• invoice to payment.

Cycle time needs clear start/end events and handling for missing events.

17. Revenue Metrics

Separate:

Do not mix quoted, booked, billed, collected, and recognized revenue.

18. Active Product / Installed Base Metrics

Installed base metrics need product inventory semantics.

Questions:

• count bundle parent only, children only, or both?
• include suspended products?
• include pending activation?
• include terminated during period?
• use current status or month-end snapshot?
• group by product offering version or product family?
• group by service address or billing address?

Define installed base carefully.

19. Churn Metrics

Churn can be:

• product churn,
• subscription churn,
• revenue churn,
• customer churn,
• account churn,
• site churn.

Churn date can be:

• cancellation requested date,
• effective cancellation date,
• product termination date,
• billing end date,
• contract end date.

Revenue churn can be based on recurring charge amount at time of churn.

Do not use one churn metric for all business questions.

20. Backlog Metrics

Operational backlog examples:

• open quote backlog,
• pending approval backlog,
• open order backlog,
• fulfillment fallout backlog,
• billing activation backlog,
• data quality issue backlog.

Backlog is point-in-time or snapshot metric.

Need define:

• included statuses,
• aging start date,
• owner group,
• severity,
• cutoff time,
• timezone.

21. Reporting Hierarchy

Hierarchy affects roll-up.

Options:

• current customer hierarchy,
• historical hierarchy at event time,
• billing account hierarchy,
• service site hierarchy,
• sales territory hierarchy,
• legal entity hierarchy.

For financial/historical reports, historical hierarchy often matters.

For current account management, current hierarchy may matter.

Metric definition must specify.

22. Slowly Changing Dimensions

Dimensions change.

Examples:

• customer segment,
• account owner,
• product category,
• organization hierarchy,
• billing account status,
• site region.

Use SCD type depending need:

For historical reporting, Type 2 is common.

23. Data Lineage

Each reportable field should trace back to source.

Lineage fields:

source_system
source_entity_type
source_entity_id
source_event_id
source_updated_at
ingested_at
transformation_version

Lineage answers:

• where did this metric come from?
• when was source updated?
• what transformation produced it?
• can we reproduce it?
• why does dashboard differ from source UI?

24. Read Model Physical Design

Example operational read model:

create table customer_support_summary (
  customer_id uuid primary key,
  customer_number text,
  customer_name text,
  open_quote_count integer not null default 0,
  open_order_count integer not null default 0,
  open_fallout_count integer not null default 0,
  active_product_count integer not null default 0,
  latest_invoice_status text,
  billing_health_status text,
  last_event_id uuid,
  last_source_version integer,
  projected_at timestamptz not null,
  freshness_status text not null
);

Projection checkpoint:

create table read_model_checkpoint (
  read_model_name text primary key,
  source_name text not null,
  last_event_id uuid,
  last_offset text,
  last_processed_at timestamptz,
  status text not null,
  error_message text
);

25. Analytical Fact Examples

Fact order:

create table fact_order (
  order_id uuid primary key,
  order_number text,
  customer_id uuid,
  account_id uuid,
  billing_account_id uuid,
  source_quote_id uuid,
  order_created_at timestamptz,
  order_submitted_at timestamptz,
  order_completed_at timestamptz,
  order_cancelled_at timestamptz,
  final_status text,
  channel text,
  source_system text,
  ingested_at timestamptz not null,
  transformation_version text
);

Fact invoice line:

create table fact_invoice_line (
  invoice_line_id uuid primary key,
  invoice_id uuid,
  billing_account_id uuid,
  customer_id uuid,
  product_instance_id uuid,
  order_id uuid,
  order_item_id uuid,
  charge_id uuid,
  line_type text,
  amount numeric(18,4),
  tax_amount numeric(18,4),
  discount_amount numeric(18,4),
  currency char(3),
  invoice_date date,
  billing_period_start date,
  billing_period_end date,
  source_system text,
  ingested_at timestamptz not null
);

These are illustrative. Analytics warehouse may use different technology.

26. Data Freshness and SLA

Each report/read model needs freshness expectation.

Examples:

Freshness fields:

last_refreshed_at
source_watermark
freshness_sla
freshness_status

Dashboard should show freshness.

27. Rebuild Strategy

Derived models must be rebuildable.

Need:

• source events or source tables,
• deterministic transformation,
• transformation version,
• checkpoint reset,
• idempotent upsert,
• validation after rebuild,
• comparison against previous output.

If read model cannot be rebuilt, it becomes hidden source of truth.

28. Data Quality for Reporting

Reporting data quality checks:

• fact row count vs source,
• duplicate fact keys,
• missing dimension references,
• null critical fields,
• negative amount where not allowed,
• currency mismatch,
• snapshot completeness,
• KPI outlier detection,
• source watermark lag,
• metric reconciliation to finance/billing source.

Example:

-- Invoice fact total vs source invoice total
select invoice_id, sum(amount + tax_amount - discount_amount) as fact_total
from fact_invoice_line
group by invoice_id;

Compare with source invoice totals.

29. Security and Access

Reporting can expose sensitive data:

• pricing,
• discounts,
• margin,
• cost,
• invoice/payment,
• customer hierarchy,
• product/service usage,
• site/location.

Controls:

• dataset-level permissions,
• row-level security,
• column masking,
• aggregation threshold,
• access audit,
• export controls,
• PII minimization,
• finance-only metrics.

Analytics copies must follow retention/privacy rules too.

30. Dashboard Anti-Patterns

Avoid:

• dashboard reads primary OLTP directly with heavy joins,
• KPI has no definition,
• chart uses current status for historical metric,
• quote revisions double counted,
• invoice and charge revenue mixed,
• tax included sometimes but not always,
• stale read model not marked stale,
• ownerless dashboards,
• manual spreadsheet corrections,
• cached metric with no lineage,
• operational decision based on stale analytics snapshot.

31. Failure Modes

32. PR Review Checklist

When reviewing reporting/read model changes, ask:

• Is this operational read model or analytics model?
• What is the grain?
• What is source of truth?
• What is lineage?
• What is freshness SLA?
• Is this current-state or historical snapshot?
• What event/effective/ingest time is used?
• Are metric definitions updated?
• Are quote revisions handled correctly?
• Are order/product/billing statuses semantically correct?
• Are bundle parent/child counting rules clear?
• Are dimensions historical or current?
• Is query load safe?
• Can model be rebuilt?
• Are data quality checks available?
• Are sensitive fields protected?
• Is dashboard owner defined?

33. Internal Verification Checklist

Verify these in the internal CSG/team context:

• Existing reporting/analytics architecture.
• Whether operational dashboards read OLTP, replicas, read models, or warehouse.
• Whether event projections/read models exist.
• Whether KPI definitions are documented.
• Whether quote conversion, order completion, MRR, churn definitions exist.
• Whether revenue metrics use quote, order, charge, invoice, payment, or finance source.
• Whether historical customer/account hierarchy is supported.
• Whether product bundle counting rules are documented.
• Whether data freshness is visible in dashboards.
• Whether reporting data lineage is tracked.
• Whether sensitive metrics like margin/cost are access-controlled.
• Whether reporting rebuild/backfill process exists.
• Whether data quality checks exist for analytics facts.
• Whether incidents mention KPI mismatch, stale dashboard, double counting, or OLTP dashboard query impact.

34. Summary

Reporting and read models are derived data products with their own modelling discipline.

A strong model must define:

• operational source-of-truth,
• read model purpose,
• analytical grain,
• fact/dimension semantics,
• snapshot vs event facts,
• current vs historical view,
• event/effective/ingest time,
• KPI definitions,
• metric ownership,
• lineage,
• freshness,
• rebuild strategy,
• data quality checks,
• security controls.

The key principle:

Reporting correctness is not automatic. A dashboard is only trustworthy when its grain, source, time basis, metric definition, lineage, freshness, and access controls are explicit.