Customize Dataverse for Complex Business Apps

From “works on my form” to “works at 100k records.”

---

Outcomes (you’ll walk away with)

• A relationship-first data model that won’t buckle under growth.

• A blueprint for first-class business logic (not scattered flows).

• Calculated/Rollup patterns that stay accurate across hierarchies.

• Alternate key strategy for clean imports & stable integrations.

• A lightweight performance & governance checklist for Dataverse.

---

Why simple builds break

• Spreadsheet mindset → ad-hoc fields & flows → hidden coupling.

• Logic lives outside the model → inconsistent triggers & edge cases.

• No lineage for metrics → dashboards drift from reality.

• Imports use GUIDs only → dupes, orphans, broken lookups.

---

Architecture at a glance

Model → Logic → Automation → Integration → Observability

1. Model: Normalize core entities; map 1:N, N:N, and hierarchy tables.

2. Logic: Prefer server-side (business rules, synchronous plug-ins, real-time workflows) for invariants; flows for orchestration & notifications.

3. Automation: Calculated & rollup fields where relationships are strong; avoid duplicating “source of truth”.

4. Integration: Use alternate keys (business IDs) to upsert safely; enforce uniqueness.

5. Observability: Log rule hits, flow decisions, and rollup refreshes; add usage & SLA dashboards.

---

Relationship design patterns (copy-ready)

• Hierarchy: Region (1) → Store (N) → Transaction (N)

  • Rollups: Region.Sum(Store.Sum(Transaction.Amount))

• Ownership & security: Business Unit (1) → Account (N); add team ownership for sharing.

• Reference: Product (1) → PriceListItem (N); Currency (1) → MonetaryFields

• Event sourcing: Order (1) ← (N) OrderLine + OrderStatusHistory table for audits.

• Many-to-many: Contact ⟷ Campaign via association table when attributes (e.g., Role) are needed.

Guardrails

• Avoid circular dependencies in rollups.

• Keep lookups single-purpose (one meaning per relationship).

• Use required relationships where business rules require existence.

---

First-class business logic (where it belongs)

• Use: Real-time workflows/plug-ins for validations, invariants, defaulting, cascade rules.

• Use: Power Automate for user workflows (notifications, handoffs), long-running processes, cross-system orchestration.

• Avoid: Duplicating the same rule in multiple flows/apps.

• Document: Rule catalog (table, trigger, condition, action, owner).

Decision matrix

• Must block bad data? → Synchronous server logic.

• Can run after save? → Async server logic.

• Cross-app messaging/approvals? → Flow/Teams.

• Heavy compute/complex joins? → Dataverse plug-in or Dataflow.

---

Calculated & rollup fields that don’t lie

• Tie formulas to stable relationships; don’t compute across optional links.

• Prefer rollup fields for sums/counts over child records; schedule recalcs if volumes are high.

• Use calculated fields for deterministic math on same-row or parent-row data.

• For volatile data, store source value + compute in view/report layer to reduce recalcs.

• Add “Last Recalculated On” and “Rollup Status” fields to monitor freshness.

---

Alternate keys: imports that stay clean

• Pick business identifiers: VendorCode, EmployeeID, SKU, ExternalCaseId, Email.

• Create unique alternate keys on those columns; validate data quality before enabling.

• Use Upsert (Dataverse API) with alternate keys to avoid duplicates.

• For composites (e.g., CompanyCode + EmployeeNumber), define composite keys.

• Keep keys immutable; if business demands changes, add ValidFrom/ValidTo with a surrogate stable key.

---

Performance checklist (quick wins)

• Index lookups used in filters; keep views lean (only needed columns).

• Replace gallery-wide formulas with delegable queries; prefer server filters/sorts.

• Limit synchronous plug-in chains; log execution time; break long logic into async.

• Batch operations via ExecuteMultiple / Batch in flows.

• Offload heavy transforms to Dataflows Gen2 or Fabric for scale.

---

Governance & resilience

• Environments: Dev → Test → Prod with managed solutions.

• ALM: Source-control your solution (PAC CLI); peer review plug-ins & flows.

• Telemetry: Central log table for rule hits, exceptions, rollup lags, flow outcomes.

• Backstops: Alternate-key conflict alerts; orphan-detection jobs; data-quality rules.

• Docs: ERD, rule catalog, integration map, and SLOs (e.g., rollup freshness ≤15 min).

---

12-step implementation plan (pilot in 1–2 days)

1. Whiteboard ERD (entities, keys, relationships, ownership).

2. Mark business IDs; create alternate keys.

3. Build core tables & required relationships; seed reference data.

4. Add calculated/rollup fields for top KPIs.

5. Implement server-side validations for must-not-pass rules.

6. Create views with delegable filters; index common predicates.

7. Add Power Automate only for orchestration/notifications.

8. Import a controlled data slice using upsert via keys.

9. Wire telemetry (log table + Power BI page for health).

10. Load scale test (5–10x expected volume); tune indexes & rollup cadence.

11. Package in a managed solution; promote to Test.

12. Draft runbook: imports, key rotation policy, recovery steps.

---

Common pitfalls (and fixes)

• Dupes after import → Missing/weak alternate keys → Add keys, re-upsert, merge dupes.

• Rollups wrong/stale → Optional relationships, cycle, or throttled recalc → Simplify graph, schedule recalc, add freshness KPI.

• Inconsistent rules → Same logic spread across 4 flows → Consolidate into server-side rule; refactor flows to orchestration.

• Slow galleries → Non-delegable formulas → Switch to delegable predicates; reduce columns; precompute where possible.