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 self-hosted and cloud ERP deployments

Keep the AI-facing contract independent of the ERP

Integration options vary by product, edition and hosting model. For cloud ERP, use a vendor-supported API, export or managed replication service; do not assume direct database access is available.

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

Each approved source adapter 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 applies across ERP platforms. Direct SQL against an ERP database bypasses the application layer and can therefore bypass application permissions, validation and automated behaviour. It can also bind an integration to database structures that change during upgrades. Use supported application APIs for writes; where read-only extraction is explicitly supported, use a dedicated least-privilege identity and copy only approved data into the reporting layer.

Security controls

Enforce read-only access at every layer

• Separate least-privilege identities for source reading, reporting loads and MCP reporting access.
• No new public inbound route to a self-hosted ERP or the reporting SQL database; cloud access uses authenticated, vendor-supported interfaces.
• The MCP-facing reporting database account has 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.