How AI Can Answer ‘What Were Our Top 10 Selling Items Last Year?’

First, define the question

Resolve ambiguity before calculating the answer

An AI agent should clarify ambiguous terms before calculating the answer:

• Does ‘top’ mean quantity, revenue or gross profit?
• Does ‘last year’ mean the calendar or financial year?
• Should returns and credit notes reduce the total?
• Should sales include or exclude GST?
• Are only posted invoices included?

What were the top 10 items by invoiced quantity between 1 January and 31 December 2025, using posted sales and treating returns as negative quantities?

These definitions become part of the organisation’s semantic layer: an approved catalogue explaining the meaning of sales, quantity, margin, customer, reporting periods and other business terms.

The roles

Give each layer one clear responsibility

• The ERP provides operational data.
• A scheduled synchroniser copies selected data into a reporting database.
• SQL performs filtering, joins and aggregation.
• The MCP server gives AI assistants controlled access to the reporting data.
• The AI interprets the question and selects a validated, allowlisted read-only query or template before explaining the result.

This design avoids downloading thousands of ERP records through an API every time somebody asks a question.

How the question is answered

Turn the question into a controlled calculation

1. The agent identifies the requested year and ranking metric.
2. It checks the approved metric definitions.
3. It inspects the available reporting schema.
4. It selects a suitable read-only query from the approved, allowlisted query surface.
5. The MCP server validates the query and its parameters.
6. The database calculates the result across the complete dataset.
7. The agent presents the 10 items and reports the data-freshness timestamp.

A representative query might be:

SELECT TOP (10)
    i.item_code,
    i.item_name,
    SUM(s.quantity) AS quantity_sold,
    SUM(s.net_sales) AS net_sales
FROM analytics.fact_sales_line AS s
INNER JOIN analytics.dim_item AS i
    ON i.item_key = s.item_key
WHERE s.posting_date >= '2025-01-01'
  AND s.posting_date <  '2026-01-01'
  AND s.document_status = 'Posted'
GROUP BY
    i.item_code,
    i.item_name
ORDER BY
    quantity_sold DESC;

Why not call the ERP API?

ERP APIs transport data; they are not always analytical engines

A complex question might require the agent to:

• Retrieve many pages of transactions.
• Call several different endpoints.
• Join customers, items and invoice lines.
• Fit a large result into the AI context.
• Repeat the same processing for every conversation.

A scheduled synchronisation performs that work once. SQL can then answer new, ad hoc questions efficiently without requiring a separately programmed API endpoint for every question.

Supporting NAV now and Odoo later

Keep the AI-facing contract independent of the ERP

Odoo uses PostgreSQL internally, but direct database access might not be available with a cloud deployment. Odoo 19’s external JSON-2 API is currently available with its Custom plan. Read the official Odoo JSON-2 API reference for the current integration requirements.

Business-oriented MCP tools

The MCP tools should describe business concepts rather than a particular ERP. The execute_readonly_sql tool, if enabled, should accept only validated, allowlisted query patterns—not arbitrary database access.

Stable reporting structures

Both ERP adapters can produce the same reporting fields, so the MCP tools and assistants do not need to change when the source system changes.

What about updating the ERP?

Keep analytical access read-only

Operations such as creating customers, quotes or sales orders should be separate, explicitly named MCP tools that call the ERP’s supported application API. They should not execute direct SQL updates.

This is particularly important for Odoo because raw SQL bypasses its ORM, access rules and business logic. See the official Odoo ORM guidance.

Security controls

Enforce read-only access at every layer

• Separate least-privilege identities for source reading, reporting loads and MCP reporting access.
• No public inbound route to NAV or the reporting SQL database.
• A database account with SELECT permission only.
• Access restricted to the reporting schema.
• One validated, read-only SQL statement per request.
• Approved tables, columns and query patterns only.
• Query timeouts and result-size limits.
• Logging of every executed query template and its normalised parameters.
• Exclusion of payroll, credentials and unnecessary personal information.
• A visible ‘data current as of’ timestamp in every answer.

Practical first stage

Begin with a small, reconciled proof of concept

• Customers, items and posted sales lines.
• Calendar and Australian financial-year definitions.
• Quantity, net sales and gross-profit measures.
• Ten agreed business questions.
• Reconciliation against existing ERP reports.
• A read-only MCP connection for an approved group of users.

Once the top-10 calculation and other control totals agree with the ERP, further datasets such as inventory, purchasing and customer retention can be added.