Why We Built Semantic Views for SyteLine: One Source of Truth for Open Orders
Every SyteLine site has lived this meeting: sales says there are 830 open orders, the operations report says 820, and twenty minutes evaporate arguing about whose number is right. Both reports are querying the same database, and both are correct by their own definition, because that is the actual problem: they have different definitions. One includes lines on credit hold, the other does not; one counts an order with any open line, the other counts open lines. When we built SyteRay we hit this wall immediately, because an AI system that answers questions over SyteLine inherits every definitional ambiguity in the database. Our answer was a semantic layer: a small set of namespaced SyteRay_ views that encode each business definition exactly once. This post explains the design and why we think it is the right pattern for any SyteLine analytics effort, AI or not.
The Anatomy of a Reporting Discrepancy
An open order sounds like a fact, but in SyteLine it is a judgment call layered over the co and coitem tables. Do you count orders or lines? Do statuses beyond ordered count? What about lines that are fully shipped but not invoiced, quantities that are backordered, orders on customer credit hold, or lines with a zero remaining quantity that were never formally closed? Each report author answers those questions implicitly, in WHERE clauses nobody else reads.
Multiply that by bookings, shipments, backlog, and on-time delivery, and a site ends up with dozens of quietly divergent definitions. The cost is not just meeting time; it is decision quality. When numbers are contested, people fall back on instinct, and the ERP's value as a shared reality erodes.
The Fix: Encode Each Definition Exactly Once
SyteRay's semantic layer is deliberately boring technology: SQL views, created at install time in your own database, under a clear SyteRay_ namespace. There are views for the core commercial concepts, open orders, bookings, shipments, and each one encodes the full business definition: the status filters, the quantity arithmetic, the joins, the site scoping.
Because they are plain views, they are transparent and reviewable. Your DBA or controller can open SyteRay_OpenSalesOrders and read, in ordinary SQL, precisely what counts. If your site's definition differs, the view is the one place to change it, and every consumer inherits the change at once. The definition stops being folklore distributed across report files and becomes an inspectable object with a name.
Everything Reads the Views
The layer only works if it is actually the single source. In SyteRay, the chat answering pipeline, the 13 curated dashboards, and Deep Research reports all resolve commercial concepts through the same views. When you ask how many orders are open, the generated SQL reads SyteRay_OpenSalesOrders; when you glance at the open-orders dashboard tile, it is the same view; when a Deep Research report cites backlog, same view again.
That is what ends the dueling-numbers meeting. Not because everyone suddenly agrees on the philosophy of order openness, but because there is now exactly one place where the answer to that philosophical question is written down, and every screen faithfully reflects it.
- Chat answers, dashboard tiles, and research reports resolve the same concept through the same view.
- Disagreements become a review of one view's SQL instead of a forensic comparison of report queries.
- Site-specific definition changes are made once and propagate everywhere.
- The views are queryable by any tool you already use, not just SyteRay.
Guarded by the Enforcer, Useful Beyond SyteRay
SyteRay's query enforcer, which validates all generated SQL against the schema, knows about the semantic views and admits them as first-class citizens. In practice the planner is steered toward views for definitional questions and toward base tables for everything else, which keeps generated SQL both correct and idiomatic.
A pleasant side effect: the views are just database objects, so your existing report writer, Excel connection, or BI tool can read them too. Several of the thorniest legacy-report discrepancies get fixed not by rewriting the reports in SyteRay, but by repointing them at the semantic views. The single source of truth is a property of the database now, not of any one front end.
Key Takeaways
- 1SyteLine reporting discrepancies are usually definitional, two WHERE clauses disagreeing, not bugs.
- 2SyteRay encodes each commercial definition once as a namespaced, human-readable SyteRay_ view in your own database.
- 3Chat, dashboards, and Deep Research all read the same views, so the same question yields the same number everywhere.
- 4The views are ordinary SQL objects, so existing reports and BI tools can adopt the same single source of truth.
Tired of reports that disagree? See how the SyteRay semantic layer works on your data. Contact ajay@netray.co for a demo.
More Insights
From Question to Governed SQL: How SyteRay's Evidence-First Answers Work
Generic AI chatbots fail on ERP data because they guess at schemas and invent numbers. SyteRay takes a different contract: every answer over your SyteLine database ships with the SQL that produced it and the tables it read. Here is the pipeline that makes that possible.
SyteRayHow SyteRay Classifies Every SyteLine Stored Procedure's Side Effects
A mature SyteLine site runs on thousands of stored procedures that nobody fully remembers. SyteRay crawls the whole catalog, 8,636 procedures on the reference deployment, and classifies what each one reads, writes, and calls, including transitive effects. Here is how and why.
SyteRayGoverned Record Editing in SyteLine: Preview, Confirm, Audit, Undo
Letting an AI read your ERP is easy to accept. Letting it write is where trust is decided. SyteRay's editing flow makes every change previewable before it happens, explicit to confirm, fully audited after, and reversible. Here is the design.