OCCI
Copilot tested on live job cost data without any data leaving the network
OCCI had already built a Copilot agent over its reporting database. Working inside OCCI's own Microsoft tenant, we tested it on live job cost data, checked every answer against our own SQL, and showed where the database, not the model, must do the math.
Construction & IndustryEnterprise data assistant evaluationAI added to a live product
- Tested
- 20 questions, each checked against our own SQL
- Scope
- Job cost, across ten approved reporting views
- Data access
- Read only, least privilege, inside the network
- Decision
- Stay on Copilot, pilot the weekly labor report
The challenge
OCCI's leaders wanted plain language answers on live job cost data from the Copilot agent the company had built, but needed proof that its figures could be trusted.
What we did
We tested the existing agent inside OCCI's tenant over ten approved views, through a read only data tool, and checked each of 20 answers against our own SQL query.
The outcome
The agent held up on direct questions over its views. Its two limits share one fix in the database, and OCCI chose to stay on Copilot and pilot its weekly labor report first.
The starting point
OCCI is a U.S. industrial contractor with a mature Microsoft reporting stack: an ERP replicated to an on premises SQL Server, reporting views and hundreds of stored procedures, SSRS reports and Power BI dashboards. Before we started, it had already published a Copilot Studio agent with a detailed knowledge base over that data.
What had to change
Executives and department heads wanted to ask about contracts, profit on bid cost, labor overruns, receivables and equipment use in plain language instead of scanning static reports. They needed to know whether Copilot could be trusted on their own figures.
What could not break
- Data never leaves OCCI's Microsoft tenant and on premises network
- Access only through the client's VPN, a managed contractor machine and MFA
- Read only, least privilege access to an agreed set of views, with no write rights
- Every figure must match the existing reports exactly
- No outside model or API in the data path
The calls we made
We framed it as a trust question about the agent the client already had, not a reason to build a new one.
- 01
Keep the agent the client already secured
Copilot Studio already carried OCCI's access controls, security posture and connections to SQL and Power BI, so it stayed the reasoning layer. When OCCI asked about Claude or custom code, we advised staying, because custom code adds flexibility but also security overhead.
Ruled outA custom Claude based stack the client would have to secure all over again.
- 02
A data tool with no model of its own
The agent reaches the database only through a small deterministic tool that validates and runs approved read only queries against an agreed set of views, under a least privilege login.
Ruled outLetting the model write and run free form SQL against the database.
- 03
Test answers the knowledge base cannot hold
Most questions needed live data the knowledge base does not contain, so a correct answer proves the agent really queried the views. We wrote our own SQL for every question and compared the answers by hand.
Ruled outQuestions the knowledge base could answer without touching live data.
- 04
Put the math in the database
Totals computed in the model's reasoning drifted between runs, while direct column sums stayed identical, and large results overflowed its context. So we recommended pre-aggregating heavy and certified metrics in SQL views and stored procedures, with the model choosing which to call.
Ruled outLetting the model add up raw rows in its own reasoning.
- 05
One pilot before report parity
Instead of aiming for parity with every report at once, we proposed a crawl then walk plan that starts with the weekly labor report, reproduced from its existing stored procedure and validated against the current report.
Ruled outFull report parity in a single step.
How the system fits together
What we built
A review of the existing build
We reviewed OCCI's Copilot Studio agent, knowledge base, SQL tools and Power BI, so the proof tested what already existed instead of replacing it.
A scoped job cost domain
Ten approved reporting views captured in a data dictionary with grain, keys and approved joins, reached through read only, least privilege access that we verified.
A layered test set
Questions from five layers: the scope's worked multi-view example, one question per view, the client's own validated questions and business rules, a deliberate spread of question types, and real identifiers, each with a verifying SQL query.
A results report
Scope coverage, test evidence with a pass or fail for every question, findings and limits, a read only review of the data procedures, recommendations and a definition of done for the job cost domain.
A phased pilot plan
A pilot that starts with the weekly labor report, adds drill down to the phase codes with the largest labor overruns, and measures accuracy, consistency, response time and token use, with options for dashboards generated on request inside the Microsoft environment.
How it works
Ask in plain language
An executive asks a question about contracts, job cost or labor.
Choose the approved view
Copilot interprets the question and picks the approved views, procedures and parameters.
Run a read only query
The data tool validates the query and runs it on the on premises database through the existing gateway.
Answer with context
Copilot answers from the live result and cites the knowledge base for definitions and rules.
Check against SQL
In the proof, every answer was compared with our own direct SQL query on the same views.
A closer look
{
"question_id": "Q07",
"layer": "One targeted question per view",
"type": "list",
"question": "Which phase codes on job 2417 are over their estimated quantity?",
"view": "rpt_job_phase_quantities",
"verifying_sql": "SELECT phase_code FROM rpt_job_phase_quantities WHERE job_no = '2417' AND actual_qty > estimated_qty",
"sql_result": ["03-210", "05-120"],
"copilot_answer": ["03-210", "05-120"],
"result": "pass"
}What changed
The agent was reliable on direct questions over its views: lookups, filtered lists, work in progress figures and profit by year. It applied OCCI's governance rules, separated actual from projected costs and cited its knowledge base, and the one miss was documented. Its two limits, context overflow on large results and totals that drifted between runs, share one fix in the database.
Under the hood
- Datics role
- AI added to a live product
- Integrations
- Copilot Studio with SharePoint as the knowledge source, on premises SQL Server views through a Power Platform connector and the On-premises Data Gateway, and Power BI datasets, with SSRS reports as ground truth.
- Boundary
- The model chooses approved views, procedures and parameters. Calculations stay deterministic in the database, with SELECT only access and no write or DDL rights.
- Model
- The reasoning model was the Claude option built into Copilot Studio, so no outside model or API sat in the data path, and no Power BI report or semantic model was rebuilt.
- Deployment
- Everything ran in a Power Platform sandbox inside OCCI's tenant and network, reached over its VPN from a managed contractor machine with MFA. No data left its environment.
Data and retrieval
Also in the stack
How it shipped
Consultation and design
After an initial consultation, we shared the proof of concept design: Copilot Studio stays the reasoning agent, SharePoint gives business context, and a read only data tool reaches an agreed set of views.
Access inside the tenant
OCCI provisioned a Power Platform sandbox, SQL access, the data gateway, its VPN, a contractor machine and MFA, and its systems engineer fixed a gateway permission issue.
Connect and test
We connected Copilot to the SQL views, ran the 20 questions, checked each answer by hand against our own SQL, and confirmed the Power BI connection worked too.
Results report
The report set out scope coverage, evidence, findings, limits, recommendations and a definition of done for the job cost domain.
Review and decision
We walked the findings through with OCCI's lead and its finance and reporting owners. OCCI chose to stay on Copilot, and we followed with a written brief for a short roadmap and pilot scope.
Inside the client's own environment
Our engineers worked inside OCCI's Microsoft tenant, in a Power Platform sandbox reached through its VPN, a managed contractor machine and MFA. We asked for exactly the access we needed at the start.
Side by side with their systems engineer
OCCI's systems engineer provisioned the tenant, SQL login and gateway, and fixed a gateway permission issue with us, so the proof ran on the client's real setup, not a copy.
Reviewed with the people who own the reports
We walked the findings through with the finance and reporting owners, and agreed the next step together: OCCI's AI committee collects its top questions per domain for the pilot.
What this unlocked
The proposed next step is a weekly labor report pilot, reproduced from its existing stored procedure and validated against the current report, with drill down on the biggest overruns, once OCCI's AI committee sends its top questions per domain.
Related work
Have a product with the same kind of constraints?
Tell us what already exists and what you want it to do. We will tell you what it takes.