1. Purpose
Purpose, scope, and design intent for this phase.
This page documents a proposed approach for the fictional GreenBox portfolio. Decisions and outcomes are illustrative until validated against business requirements, source data, security constraints, and measured implementation results.
Design area 01
Why a semantic layer
Physical tables alone do not guarantee consistent business meaning. The semantic layer defines approved measures, dimensions, relationships, filters, and calculation rules in a reusable form.
Design area 02
Metric definition contract
Each metric should include: name; business meaning; owner; approval status; formula; grain; time basis; inclusions and exclusions; currency treatment; source lineage; permitted dimensions; caveats; effective date and change history.
Design area 03
Candidate metrics
Gross Revenue; Returned Revenue; Net Revenue; Gross Margin; Gross Margin %; OTIF; On-Time Delivery %; Inventory Value; Supplier On-Time Delivery %; Campaign-Attributed Revenue; Case Volume; Average Resolution Time; Customer Retention Rate. All remain proposed until business rules are approved.
Design area 04
Governance boundaries
Finance approves financial definitions. Supply Chain owns operational delivery measures, with cross-functional agreement for enterprise OTIF. Procurement/Supply Chain define supplier performance. Marketing owns campaign attribution methodology but not enterprise Revenue. Customer Service owns case measures. Retention ownership remains unresolved.
2. Implementation considerations
Controls and decisions to make the design supportable, explainable, and testable.
Avoiding double counting
Do not sum order-line amounts across duplicated shipment or invoice joins. Measures must respect their declared grain and relationships. Use distinct facts, controlled relationships, or purpose-built aggregates where needed.
Consumption design
Expose friendly names, descriptions, valid dimensions, and approved measures to Power BI. For AI consumption, expose only approved, access-controlled metrics with clear descriptions and limitations.
Change management
Changes to definitions require an owner, rationale, impact analysis, version/effective date, testing and communication. Retain historical definitions where reporting comparability requires it.
Acceptance criteria
Each released metric has an accountable owner, approved definition, documented lineage, tested calculation, clear grain and agreed reconciliation. Unapproved metrics are visibly labelled draft, not presented as authoritative.
3. Acceptance criteria and handoff
Evidence required before this phase should be considered complete.
Step 07
Next step
Build reports that answer prioritised business questions using the governed metrics.
- Requirements and assumptions are documented and traceable.
- Business owners and technical owners are identified where applicable.
- Security, quality, and operational implications are considered.
- Validation evidence is captured; proposed designs are not presented as implemented outcomes.
← Project roadmapPrevious: Step 6 · Synthetic Source Data & dbt EngineeringNext: Step 8 · BI & Reporting →