GreenBox Step 4: Enterprise Data & Dimensional Modelling

1. Modelling approach

The model starts from business questions and process grain, not from a direct copy of source-system tables. Each fact represents a clearly defined business event or state, with dimensions providing consistent analytical context.

Principle 01

Declare the grain first

Write one precise sentence describing what one row represents before choosing measures, keys, or dimensions.

Principle 02

Keep different processes separate

Orders, shipment lines, invoices, returns, inventory movements, and snapshots have different grains and should not be collapsed into one fact.

Principle 03

Conform shared dimensions

Customer, Product, Location, and Date should have consistent meaning across eligible business processes.

Principle 04

Preserve atomic detail

Retain appropriate transactional detail for traceability and flexible analysis; use aggregates when performance needs justify them.

Principle 05

Make relationships explicit

Model one-to-many and many-to-many relationships carefully, especially split shipments, multiple invoices, and returns.

Principle 06

Separate facts from metrics

Facts store business events or states. Approved metric definitions belong in a governed semantic layer rather than being reinvented per report.

Core rule: A join is not safe just because two tables share an order ID. Validate the grain and cardinality before combining measures, or duplicated rows can inflate revenue, quantity, cost, and delivery counts.

2. Initial Enterprise Bus Matrix

The matrix shows which shared dimensions are expected to support each business process. This is a proposed logical design and must be validated against source data and business definitions.

Business process / fact candidate	Date	Customer	Product	Location	Supplier	Order	Campaign	Account
Sales order line	Yes	Yes	Yes	Yes	No	Yes	No	No
Return line	Yes	Yes	Yes	Yes	No	Yes	No	No
Purchase order line	Yes	No	Yes	Yes	Yes	No	No	No
Supplier delivery line	Yes	No	Yes	Yes	Yes	No	No	No
Inventory movement	Yes	No	Yes	Yes	No	No	No	Conditional
Inventory snapshot	Yes	No	Yes	Yes	No	No	No	Conditional
Shipment line	Yes	Yes	Yes	Yes	No	Yes	No	No
Invoice line	Yes	Yes	Yes	No	No	Yes	No	Conditional
Payment application	Yes	Yes	No	No	No	No	No	Yes
Lead / campaign performance	Yes	Conditional	No	No	No	No	Yes	No
Customer case	Yes	Yes	Conditional	No	No	Conditional	No	No

● Candidate dimension relationship. *Conditional or role-specific relationship requiring validation. “Order” may be a degenerate dimension or a related order identifier, depending on the model. Date is role-playing where a process has multiple meaningful dates.

3. Fact table catalogue

Each fact table has a declared grain and a business purpose. Measure definitions and exact source mappings remain subject to validation and business approval.

Fact candidate	Proposed grain — one row per…	Purpose / example measures	Key relationships	Design notes
`fct_sales_order_line`	Product line on a customer sales order	Ordered quantity, unit price, line discount; commercial value only after definition approval	Customer, Product, Order, order dates, selling location	Keep order-line grain; don't add shipment-level attributes directly without handling cardinality.
`fct_return_line`	Product line on a return transaction	Returned quantity and approved return value	Customer, Product, original order line, return date, location	Separate from sales. Netting rules belong in approved metrics.
`fct_purchase_order_line`	Product line on a purchase order	Ordered quantity, unit cost, requested date, committed date	Product, Supplier, Location, purchase order, dates	Capture original commitments and changes if source supports them.
`fct_supplier_delivery_line`	Product line received against a supplier delivery	Received quantity, receipt timestamp, delivery variance	Product, Supplier, Location, purchase order, receipt date	A PO line may have multiple receipts; preserve that relationship.
`fct_inventory_movement`	One stock movement for a product and location	Signed quantity, movement type, movement timestamp	Product, Location, Date; optional source transaction	Transactional event fact. Do not treat as a snapshot.
`fct_inventory_snapshot`	Product × location × snapshot timestamp or date	On-hand quantity and other approved stock balances / valuation	Product, Location, Snapshot Date	Periodic snapshot; valuation and inventory status rules require Finance/Supply Chain approval.
`fct_order_fulfilment`	Order-line fulfilment allocation or lifecycle instance	Allocated, picked, packed, fulfilled quantities; milestone timestamps	Order line, Product, Location, Customer, relevant dates	Accumulating snapshot candidate. Decide how repeated allocations and reopened fulfilments are represented.
`fct_shipment_line`	Order-line allocation within a shipment	Shipped quantity, dispatch time, delivery time, delivery status	Order line, Shipment, Product, Customer, ship-from location, dates	Supports split shipments. Delivery commitment and OTIF rules need approval.
`fct_invoice_line`	Product or charge line on an invoice	Invoiced quantity, line amount, approved tax/discount attributes	Customer, Product or charge, Invoice, Order, dates	Invoice grain differs from order and accounting-entry grain.
`fct_payment_application`	Payment amount applied to an invoice or receivable	Applied amount and application date	Customer, Invoice, Account, Date	One payment may apply to multiple invoices; unapplied cash needs separate treatment.
`fct_lead / fct_opportunity`	One lead record / one opportunity record or lifecycle instance	Lead status, stage changes, conversion milestones	Campaign, Customer/Contact, owner, dates	Decide event fact versus accumulating snapshot based on questions and source history.
`fct_customer_case`	One customer service case	Case count, resolution duration, first-contact resolution indicator when defined	Customer, dates, optional Product/Order references	Reopened cases, duplicate cases, and business-hours calculations need explicit rules.
`fct_campaign_performance`	Proposed campaign × channel × reporting period (to validate)	Spend, impressions, clicks, leads; attributed revenue only under approved rules	Campaign, Date, channel; optional Customer	Aggregate fact candidate. Do not join it to order lines as if the grains were identical.

4. Conformed dimensions and key strategy

Dimensions provide stable, shared business context across facts. The final attributes and keys will be determined after source profiling and business identity rules are documented.

dim_customer

Enterprise customer identity and reporting attributes used across sales, shipments, invoices, payments, and cases.

Warehouse surrogate key
Enterprise business identifier
Source-system identifiers and source mapping
Customer status, segment, hierarchy, region (subject to validation)

dim_product

Shared product identity for sales, procurement, inventory, and fulfilment.

Warehouse surrogate key and enterprise product ID
Source product codes and cross-reference mapping
Product category, packaging type, status (to validate)
Product mapping/standardisation before conformed use

dim_location

Consistent representation of warehouses, branches, and other operational locations.

Location surrogate key and source identifiers
Location type and operating region
Effective-dated attributes if location history matters

dim_date

Reusable calendar dimension with role-playing relationships for different business dates.

Order date, ship date, delivery date, invoice date, payment date
Fiscal calendar and business-day attributes after confirmation

dim_supplier / dim_campaign

Conformed context for supplier performance and campaign analysis.

Supplier identity across procurement and receipts
Campaign identifiers and hierarchy
Attribution details are not assumed to be dimension attributes

Degenerate and role-specific dimensions

Transaction identifiers such as Order Number or Invoice Number may remain directly in facts where useful, without a separate dimension table.

Use role-playing dates for multiple date contexts
Use bridge tables only when a real many-to-many relationship needs representation

Key strategy: use warehouse surrogate keys for dimensional joins, preserve source-system identifiers for traceability, and maintain enterprise business IDs where entity resolution is possible. Never assume source IDs are globally unique across ERP instances or acquired systems.

Slowly Changing Dimensions (SCD) — initial strategy

Type 1 may be suitable for correcting attributes where history is not required. Type 2 may be appropriate where historical analysis must preserve the attribute value effective at the time of a transaction, such as a customer segment or product classification. The correct policy is attribute-specific and must be agreed with the business; do not apply Type 2 to every column by default.

5. Order-to-fulfilment lifecycle

The model deliberately separates the major stages because an order can have multiple fulfilment allocations, shipments, deliveries, invoices, and payments.

1 · Sales order
Order header and order lines record what the customer requested.
2 · Allocation & fulfilment
Allocation, picking, and packing track how demand is fulfilled.
3 · Shipment & delivery
Shipment lines capture dispatched quantities and delivery milestones.
4 · Invoice & payment
Invoice lines and payment applications record downstream financial events.

Example: one order line for 100 units may be fulfilled in two shipments of 60 and 40 units. A model that joins the order line to both shipments and then sums the original order value can count that value twice. Keep each fact at its declared grain and relate them through keys or a controlled bridge/relationship design.

Candidate relationship path

Sales order line → fulfilment allocation → shipment line → invoice line → payment application. This is a conceptual path, not a guarantee of one-to-one joins. Real source relationships and exceptions must be profiled.

Returns and adjustments

Return lines should reference the original order line where the source supports it. Credits and accounting adjustments may follow separate processes and should not be inferred from the return alone.

6. Quality, testing, and validation

Tests should prove that the model is structurally sound and that its business meaning matches approved rules. Passing technical tests alone does not establish business correctness.

Structural tests

Primary key uniqueness at declared grain
Not-null required keys
Referential integrity to dimensions
Valid date and status domains

Relationship tests

Profile one-to-many and many-to-many cardinality
Identify orphaned source keys
Validate order-line to shipment mappings
Detect duplicate source records

Business reconciliation

Reconcile source counts and amounts within agreed scope
Validate returns and credit treatment
Check inventory snapshot totals and status rules
Obtain owner approval for KPI calculations

Proposed model acceptance criteria

Every fact model has a written grain statement and documented business purpose.
Measures are additive, semi-additive, or non-additive as appropriate, with limitations documented.
Dimension keys and source identifiers are traceable and tested.
Potential fan-out joins are documented and prevented from inflating measures.
Source-to-target mappings and known data-quality limitations are recorded.
Business-owned definitions are not marked approved until the relevant owner confirms them.

7. Decisions and assumptions to resolve

The model is intentionally a documented design, not a claim that all business rules or source relationships have already been verified.

Decision	Why it matters	Required input	Status
Revenue and Gross Margin definitions	Determines approved measures and reconciliation logic	Finance	Approval required
OTIF grain and delivery commitment	Defines eligible population, date, and completeness rules	Supply Chain + Sales + Finance as needed	Approval required
Enterprise Customer identity	Prevents duplicate or inconsistent customer reporting across systems	Sales, Finance, CRM / data owners	To validate
Product mapping across ERPs and Shopify	Needed for conformed product analysis	Product / Procurement / source owners	To validate
Order-to-shipment source relationships	Determines safe joins and split shipment handling	Sales and Supply Chain source profiling	To validate
Inventory valuation and stock statuses	Impacts inventory value and available stock measures	Finance + Supply Chain	Approval required
SCD policy by attribute	Determines how historical dimension changes are represented	Business owners and data governance	To validate
Campaign attribution methodology	Prevents double-counted or misleading campaign revenue	Marketing + Finance	Approval required

8. Deliverables and transition

Step 4 establishes the logical modelling blueprint. The next phase designs the Snowflake platform structures and operational controls that will host and run these models.

Step 4 outputs

Modelling artefacts

Enterprise Bus Matrix draft
Fact catalogue with grain statements
Conformed dimension strategy
Order-to-fulfilment relationship design
Key, SCD, quality, and validation principles
Open decisions and assumptions register

Next · Step 5

Snowflake Platform Architecture

Define database and schema boundaries, RAW/STAGING/CORE/MARTS responsibilities, roles and grants, warehouse sizing and workload isolation, environment strategy, deployment, cost monitoring, and operational controls.

← Return to the project roadmap Review Step 3 ↗Continue to Step 5 →

Step 4 status: documented design. Fact grains and candidate dimensions are proposed; source mappings, KPI formulas, and business approval remain to be validated before implementation.

← Return to GreenBox project roadmap