Aurea Quantra
Operational BI · synthetic demo · manufacturing & distribution scenario
Synthetic demo Saturday, November 21, 2026 11:43 AM All demos Consulting page Book My BI Diagnostic

Ideal-fit scenario · manufacturing & distribution

Reconciliation cockpit: ERP, CRM, and invoices in one view

Interactive walkthrough of intentional key mismatches and operating variance across synthetic ERP orders, CRM pipeline, and invoices—the pattern mid-market teams need before platform debates.

How this maps to Operational BI

Cross-cutting operational BI for mid-market operating teams.

  • Side-by-side invoice vs order vs pipeline totals
  • Match-status breakdown with clickable orphan keys
  • Shared definitions for revenue, margin, backlog, and pipeline
  • Synthetic data only—illustrates the pattern, not a live client stack
Filters Invoice scope
Server CSV

Filters apply to revenue charts and the first two KPIs. Other tabs use the full demo model. Use Copy link to share this view.

ERP ↔ CRM ↔ invoice reconciliation

Side-by-side operating totals and intentional key mismatches on synthetic data.

Illustrative pattern · synthetic data

Stack

  1. ERP orders
  2. CRM pipeline
  3. Invoices
  4. Conformed customer key
  5. Reconciled KPIs

Illustrative pattern · synthetic data

Invoice revenue

$51,593,817

Illustrative pattern · synthetic data

Order value

$37,656,803

Illustrative pattern · synthetic data

CRM pipeline

$368,361,808

Illustrative pattern · synthetic data

Matched customers %

88.6

Illustrative pattern · synthetic data

Select an orphan below to highlight its match status in the walkthrough.

Illustrative pattern · synthetic data

Customers by CRM/ERP match status

Illustrative pattern · synthetic data

Invoices vs orders vs pipeline (synthetic USD)

Illustrative pattern · synthetic data

KPI recipe: gross margin % SQL

SELECT d.year_month,
       SUM(f.gross_margin) * 1.0 / NULLIF(SUM(f.revenue), 0) AS gross_margin_pct
FROM fact_invoices f
JOIN dim_date d     ON d.date_key = f.date_key
JOIN dim_customer c ON c.customer_key = f.customer_key
WHERE c.match_status = 'matched'
GROUP BY d.year_month;

Illustrative pattern · synthetic data

Semantic objects

ObjectTypeNotes
bridge_crm_erp_keysStar-schema tablebridge
dim_channelStar-schema tabledimension
dim_customerStar-schema tabledimension
dim_dateStar-schema tabledimension
dim_productStar-schema tabledimension
dim_regionStar-schema tabledimension
dim_sales_repStar-schema tabledimension
fact_inventory_backlogStar-schema tablefact
fact_invoicesStar-schema tablefact
fact_opportunitiesStar-schema tablefact
fact_opportunity_stage_histStar-schema tablefact
fact_sales_ordersStar-schema tablefact
fact_samplesStar-schema tablefact
Total RevenueShared metriccertified · Finance
Revenue YTDShared metriccertified · Finance
Gross MarginShared metriccertified · Finance
Gross Margin %Shared metriccertified · Finance
Open BacklogShared metriccertified · Operations
Pipeline ValueShared metriccertified · Sales
Weighted PipelineShared metriccertified · Sales
Sample Conversion %Shared metriccertified · Sales
Draft Channel MixShared metricdraft · Sandbox
Uncertified Discount GuessShared metricdraft · Sandbox
Bookings vs Billings GapShared metricdraft · Sandbox

Illustrative pattern · synthetic data

Data-quality checks

Computed from the demo tables when the app loads them. “warn” marks mismatches this scenario includes on purpose.

CheckTableRows checkedFlaggedStatus
Invoices have a customer key fact_invoices 22,000 0 pass
Invoice customer keys exist in dim_customer fact_invoices 22,000 0 pass
Invoice product keys exist in dim_product fact_invoices 22,000 0 pass
Invoice dates exist in dim_date fact_invoices 22,000 0 pass
Invoice revenue is not negative fact_invoices 22,000 0 pass
Gross margin does not exceed revenue fact_invoices 22,000 0 pass
Opportunity customer keys exist in dim_customer fact_opportunities 4,500 0 pass
Customer keys are unique dim_customer 500 0 pass
Customers matched between CRM and ERP dim_customer 500 90 warn
Every month 2023-01..2026-12 has invoices fact_invoices 48 0 pass

Illustrative pattern · synthetic data

Key mismatches / orphans

CustomerStatusERP idCRM id
Customer 001 erp_only CUST-10000
Customer 004 erp_only CUST-10003
Customer 005 erp_only CUST-10004
Customer 015 erp_only CUST-10014
Customer 023 crm_only ACC-20022
Customer 027 crm_only ACC-20026
Customer 029 crm_only ACC-20028
Customer 031 fuzzy CUST-10030 ACC-20030
Customer 035 erp_only CUST-10034
Customer 042 fuzzy CUST-10041 ACC-20041
Customer 055 fuzzy CUST-10054 ACC-20054
Customer 064 erp_only CUST-10063
Customer 068 crm_only ACC-20067
Customer 069 erp_only CUST-10068
Customer 072 erp_only CUST-10071
Customer 075 fuzzy CUST-10074 ACC-20074
Customer 095 erp_only CUST-10094
Customer 102 crm_only ACC-20101
Customer 107 fuzzy CUST-10106 ACC-20106
Customer 116 erp_only CUST-10115
Customer 121 fuzzy CUST-10120 ACC-20120
Customer 122 crm_only ACC-20121
Customer 126 crm_only ACC-20125
Customer 133 erp_only CUST-10132
Customer 137 crm_only ACC-20136
Customer 138 fuzzy CUST-10137 ACC-20137
Customer 156 erp_only CUST-10155
Customer 158 crm_only ACC-20157
Customer 163 fuzzy CUST-10162 ACC-20162
Customer 164 fuzzy CUST-10163 ACC-20163
Customer 168 fuzzy CUST-10167 ACC-20167
Customer 171 fuzzy CUST-10170 ACC-20170
Customer 173 fuzzy CUST-10172 ACC-20172
Customer 179 erp_only CUST-10178
Customer 190 erp_only CUST-10189
Customer 198 erp_only CUST-10197
Customer 208 erp_only CUST-10207
Customer 210 crm_only ACC-20209
Customer 217 fuzzy CUST-10216 ACC-20216
Customer 218 fuzzy CUST-10217 ACC-20217

Illustrative pattern · synthetic data

How to present this

  • Variance between pipeline and invoices is expected in an open operating cycle.
  • erp_only / crm_only rows are intentional demo orphans for reconciliation walkthroughs.
  • Select an orphan row to highlight it; the SQL demo exports the full unmatched list as CSV.

Illustrative pattern · synthetic data

What the AI insight is told about this platform

Platform lens: operational reconciliation. Emphasize ERP/CRM/invoice variance and match-status orphans.

The model also receives the synthetic KPIs, the current invoice slice and the semantic model summary.