A shared data layer is not a shared database connection
Reports are expected to be stable, reproducible, and based on fixed definitions. AI Q&A must interpret natural language, resolve ambiguity, and combine data dynamically. Both can use the same underlying facts, but they should not consume raw operational tables without mediation. Connecting a BI tool and a language model to the same database does not resolve duplicate records, unclear columns, inconsistent joins, or uneven permissions. It only exposes those problems through two interfaces.
The useful shared asset is a governed data contract: what constitutes a customer, order, product, or location; how metrics are calculated; how dates, currencies, and statuses are interpreted; and which roles may see each scope. Reports can materialize predictable views from that contract, while AI builds query plans from the same semantics. Storage may span a warehouse, lakehouse, and operational services, but the definitions presented to users must remain consistent.
Define grain before defining metrics
Many disagreements begin with an undefined grain rather than a faulty model. Does one order row represent an order, a line item, or a shipment? Is a customer counted by account, legal entity, store, or contact? Without an explicit grain and stable keys, a dashboard and an AI assistant can produce different answers that both appear plausible.
Treat each core dataset as a versioned product and document at least the following:
- Row grain:the event or entity represented by each row, including permitted duplication.
- Relationship rules:primary keys, foreign keys, master-data mappings, and cross-system identity resolution.
- Time semantics:when created, booked, shipped, and last updated, and which timestamp applies to each analysis.
- Metric contracts:formulas, filters, tax and currency treatment, cancellation or return logic, and accountable owners.
Metrics should live in a semantic layer or controlled model views, not be repeated across dashboard SQL, spreadsheets, and prompts. When a definition changes, publish a new version and identify affected consumers. Silently changing a shared formula can make historical reports move and invalidate previously correct AI answers.
Separate ingestion, modeling, and serving
A practical pipeline separates source data, standardized data, and analysis-ready models. Raw records from ERP, CRM, LINE, IoT platforms, or external services should retain source fields and ingestion timestamps. Subsequent layers can correct types, remove duplicates, reconcile master data, and apply business rules before exposing curated datasets. This structure lets engineers trace an answer through its transformations instead of reverse-engineering a final table.
Dashboards generally benefit from pre-aggregated models with predictable performance. AI questions may require more flexible drill-down, but they should still pass through an approved view or query service. The two interfaces do not need identical SQL; they need the same metric contract and data version. Frequently requested measures can use aggregates or caches, while exploratory queries should have limits on scan size, execution time, and cost.
- Publish the refresh schedule, latest successful load, and expected latency for every dataset.
- Retain source batch or event identifiers so displayed figures and generated answers can be traced.
- Run uniqueness, completeness, referential integrity, and reasonable-range checks inside the pipeline.
- Expose delayed or failed data explicitly instead of allowing fluent AI text to conceal a gap.
Give AI governed semantics, not unrestricted SQL access
A language model should not hold a privileged credential that can query the production database arbitrarily. A safer pattern lets it retrieve approved dataset descriptions, relationships, dimensions, and metric definitions from a catalog. It then submits a structured query request to a service that validates the plan. The service enforces row and column security, allowed operations, and resource limits; the model interprets the question and presents the result.
- Resolve the user, department, tenant, and permitted data scope before planning a query.
- Map the question to approved metrics, dimensions, date ranges, and filters.
- Validate datasets, fields, joins, estimated work, and sensitive-data policies before execution.
- Return the metric definition, data timestamp, and applied filters with the result.
- Ask for clarification or decline to infer when data or definitions are insufficient.
Authorization must be enforced by the data and query layers, not merely described in a system prompt. Personal data, compensation, and commercially sensitive fields may also require masking, purpose restrictions, and audit records. Treat both user input and retrieved documents as untrusted content so embedded instructions cannot bypass established controls.
Roll out through reconciliation, not confidence alone
Do not begin by asking the assistant to answer every enterprise question. Start with domains that already have mature definitions, trusted reports, and clear operational value, such as order status, available inventory, or service-case trends. Use existing reports as reconciliation references and create acceptance questions covering synonyms, ambiguous dates, cross-dimensional comparisons, missing data, and unauthorized requests.
Production observability should connect the full path: the original question, interpreted metric and filters, data version, executed query, returned rows, and final response. This evidence helps distinguish semantic parsing errors from pipeline defects and presentation mistakes. Durable fixes belong in the data contract, semantic layer, or query policy; accumulating prompt exceptions creates behavior that is difficult to test and govern.
A mature shared data layer is an internal product with ownership, versions, quality gates, and a controlled change process. Reports and AI assistants may present information differently, but they should cite the same definitions, freshness, and authorization outcome. Once those foundations are consistent, conversational access becomes a dependable interface to enterprise data rather than an impressive but unverifiable demonstration.