1.3 Structuring Enterprise Business Data for AI System Interoperability

Structuring Enterprise Business Data for AI System Interoperability

Quick Answer: True agentic interoperability requires structuring enterprise data across Dataverse, Microsoft Fabric, Azure SQL, and SharePoint using unified metadata taxonomies, security-trimmed indexing pipelines, and standardized API contracts. Azure AI Search Hybrid Search (dense vector + sparse BM25 + semantic reranker) provides the foundational retrieval tier, while Microsoft Entra ID OAuth 2.0 On-Behalf-Of (OBO) delegation ensures data access controls are strictly enforced at runtime.

In modern enterprises, business knowledge does not reside in a single monolithic database. It is distributed across transactional CRM systems (Dataverse), cloud data lakes (Microsoft Fabric OneLake), high-throughput relational databases (Azure SQL), and unstructured collaboration portals (SharePoint Online).

An AB-100 Solution Architect must design data architectures that allow multiple autonomous agents and Copilots to securely discover, index, query, and interoperate across these heterogeneous repositories without data duplication or security breaches.

---

1. Organizing Enterprise Data Stores for Cross-System AI Indexing

Each enterprise data store serves a distinct architectural purpose and requires tailored indexing and integration patterns:

+-----------------------------------------------------------------------------------------+
|                                ENTERPRISE DATA ESTATE                                   |
+-----------------------------------------------------------------------------------------+
        |                                   |                                |            
        v                                   v                                v            
  [ DATAVERSE ]                    [ MICROSOFT FABRIC ]             [ SHAREPOINT / O365 ] 
  - Dynamics 365 CRM/ERP           - OneLake Delta Parquet          - Word, PDF, Excel    
  - Virtual Tables                 - Direct Lake Semantic Models    - Document Libraries  
  - Copilot Search / Synapse Link  - Fabric Copilot Grounding       - Graph Connectors    
        |                                   |                                |            
        +-----------------------------------+--------------------------------+            
                                            |                                             
                                            v                                             
                         [ AZURE AI SEARCH HYBRID INDEX ]                                 
                         - Dense Vector (text-embedding-3)                                
                         - Sparse BM25 (Lexical Keywords)                                 
                         - Semantic Reranker (Cross-Encoder)                              
                         - Entra ID Security Filters (ACLs)                               

Dataverse

• Role: The core business application data store for Power Apps, Dynamics 365 Customer Engagement, and Copilot Studio.
• AI Grounding Mechanisms:
  • Dataverse Copilot Search: Out-of-the-box semantic search across Dataverse tables and note attachments, natively respecting Dataverse role-based security (Business Units, Security Roles, Row-Level Sharing).
  • Azure Synapse Link for Dataverse: Continuously exports Dataverse tables into Azure Data Lake Storage Gen2 / Microsoft Fabric OneLake in Delta Parquet format for large-scale analytical AI indexing.
  • Virtual Tables: Allows external data (e.g., Azure SQL, SAP) to appear natively within Dataverse, enabling Copilot Studio agents to query external systems using standard Dataverse connectors.

Microsoft Fabric OneLake

• Role: The unified enterprise data lakehouse supporting analytics, machine learning, and BI workloads.
• AI Grounding Mechanisms:
  • OneLake Shortcuts: Virtualizes data from external clouds (Amazon S3, Google Cloud Storage, Azure Data Lake) into Fabric without data movement.
  • Direct Lake Mode: Enables Power BI semantic models and Fabric Copilots to query billions of rows of Delta Parquet data in memory with sub-second response times, avoiding expensive data imports.
  • Fabric Copilot & AI Skills: Exposes structured tabular data to agents via natural language semantic models with built-in business metric definitions.

Azure SQL Database & Azure Cosmos DB

• Role: High-throughput transactional databases holding operational orders, billing transactions, and application telemetry.
• AI Grounding Mechanisms:
  • Native Vector Search in Azure SQL: Azure SQL supports vector storage and cosine distance calculations (VECTOR_DISTANCE) directly inside the database engine, enabling hybrid queries that join relational customer tables with vector embeddings in a single T-SQL query.
  • Change Data Capture (CDC): Streams database mutations in real time to Azure Event Hubs or Azure Functions to trigger downstream vector index updates.

SharePoint Online & OneDrive

• Role: Unstructured document repositories holding contracts, policy manuals, operating procedures, and product specifications.
• AI Grounding Mechanisms:
  • Microsoft Graph Connectors: Ingests external enterprise SaaS platforms (ServiceNow, Salesforce, Confluence) directly into the Microsoft 365 Semantic Index.
  • Sensitivity Label Inheritance: Microsoft Purview Information Protection labels (e.g., Confidential - Financial Data) assigned to SharePoint files automatically propagate through Copilot indexing, preventing unauthorized exposure.

---

2. Schema Design Principles for Semantic Search & RAG

Designing schemas for AI vector search differs fundamentally from traditional relational normalization. In vector retrieval, joins between normalized tables at query time are computationally expensive and degrade retrieval accuracy.

The Composite AI Document Schema

Every chunk indexed in Azure AI Search or a custom vector database should follow an enriched composite schema:

{
  "chunk_id": "doc_hr_policy_2026_v2_chunk_0042",
  "document_id": "doc_hr_policy_2026_v2",
  "title": "Enterprise Global Travel & Expense Policy 2026",
  "content": "Section 4.2: Lodging Allowances. Employees traveling to Tier 1 metropolitan areas (New York, London, Tokyo) are authorized for lodging reimbursement up to $350 USD per night before taxes...",
  "content_vector": [0.0124, -0.0451, 0.0892, "... 1536/3072 dimensional floats ..."],
  "metadata": {
    "source_system": "SharePoint_Corporate_HR",
    "source_url": "https://contoso.sharepoint.com/sites/hr/policies/travel-2026.pdf",
    "department": ["Finance", "All Employees"],
    "effective_start_date": "2026-01-01T00:00:00Z",
    "effective_end_date": "2026-12-31T23:59:59Z",
    "is_superseded": false,
    "taxonomy_tags": ["travel", "reimbursement", "lodging", "expense_limits"],
    "allowed_security_principals": ["group-all-employees-id", "group-finance-approvers-id"]
  }
}

Key Schema Design Rules

1. Denormalize Essential Metadata into Chunks: Embed document-level attributes (department, effective_date, security_principals) directly into each chunk record. This enables single-pass OData pre-filtering during search execution.
2. Isolate Text from Vector Representation: Maintain both the raw text (content) and its mathematical embedding (content_vector). The vector is used for mathematical similarity matching; the text is retrieved and injected into the LLM context prompt.
3. Include Strict Identity Access Control Lists (ACLs): Store an array of allowed Microsoft Entra ID user or group Object IDs (allowed_security_principals). When an agent queries the index on behalf of a user, it appends an OData filter matching the caller's Entra security tokens, ensuring zero data leakage across tenant or role boundaries.

---

3. Knowledge Indexing Technologies: Comparative Architectural Analysis

Microsoft provides three distinct enterprise knowledge indexing technologies. Architects must know precisely when to deploy each:

The Gold Standard: Azure AI Search Hybrid Search Architecture

For custom enterprise agents, standard vector search alone is insufficient. The Microsoft reference architecture implements Hybrid Search with Semantic Reranking:

1. Dense Vector Search: Uses embeddings generated by models such as text-embedding-3-large to capture conceptual semantics and intent ("How do I claim a hotel refund when working abroad?").
2. Sparse Lexical Search (BM25): Executes traditional inverted index keyword matching. This is indispensable for capturing exact alphanumeric strings, error codes, part numbers, and legal citations (e.g., SKU-7729-XB or Policy Ref #401.3) that dense vectors frequently misplace.
3. Reciprocal Rank Fusion (RRF): Merges the ranked candidate lists from the dense vector search and sparse keyword search into a single consolidated candidate list.
4. Semantic Reranking (L2 Cross-Encoder): Takes the top 50 candidates from RRF and processes them through a deep learning model trained to evaluate contextual coherence. It re-scores each candidate and outputs the top 3-5 hyper-relevant chunks for prompt grounding.

[User Query: "Replace seal for valve assembly SKU-9042-X"]
                         |
         +---------------+---------------+
         |                               |
         v                               v
  [Dense Vector Search]        [Sparse BM25 Keyword Search]
  (Matches "valve seal           (Matches exact "SKU-9042-X"
   maintenance procedures")       part number in parts table)
         |                               |
         +---------------+---------------+
                         |
                         v
           [Reciprocal Rank Fusion (RRF)]
                         |
                         v
             [Semantic Reranker (L2)]
       (Deep cross-encoder evaluates intent)
                         |
                         v
        [Top 3-5 Hyper-Grounded Chunks]

---

4. Structuring Unstructured Enterprise Documents

Enterprise repositories are filled with unstructured PDFs, scanned warranties, audio transcripts, and complex spreadsheets. Solution architects must implement an automated ingestion pipeline to convert these formats into AI-ready assets:

The Document Ingestion Pipeline

1. Extraction & Layout Analysis: Utilize Azure AI Document Intelligence with the prebuilt-layout model. This model extracts text while identifying visual layout geometry: titles, section headers, footnotes, and multi-column layouts.
2. Table Normalization: Converts complex nested and merged financial tables into structured Markdown tables or JSON arrays. Without layout analysis, PDF tables decompose into scrambled lines of disjointed numbers.
3. Noise Pruning: Strips recurring running headers, page numbers, legal boilerplates, and watermarks via regex or AI classification skills.
4. Semantic Hierarchy Chunking: Chunks text along document Markdown header boundaries (##, ###) rather than arbitrary character counts. Each chunk includes the ancestor header path (e.g., Travel Policy > International Expenses > Meals) as a metadata prefix to provide contextual grounding for the chunk.

---

5. Exposing Enterprise Data Safely to Agents via API Contracts

Agents interact with enterprise data through tools and actions. To guarantee interoperability and prevent security breaches, data endpoints must adhere to formal API contracts and authentication standards.

OpenAPI v3 Specification Standards

Copilot Studio plugins and AI actions utilize OpenAPI v3 definitions to understand tool capabilities. The architect must ensure:

• Semantic Descriptions: The description tags in the OpenAPI schema act as prompt instructions for the LLM's planner. Vague descriptions ("Gets data") cause agent tool-selection failures; descriptions must be precise: "Retrieves real-time warehouse inventory levels given a validated 10-digit product SKU and warehouse ID.".
• Strict Schema Typing: All parameters must have explicit types (string, integer), formats (uuid, date-time), and required properties to prevent malformed API calls.

Model Context Protocol (MCP)

For heterogeneous multi-agent environments spanning Azure AI Foundry, local runtimes, and external agent frameworks, architects leverage the Model Context Protocol (MCP). MCP standardizes how agents discover tools, inspect resource schemas, and stream responses across different runtime environments.

Security & Delegated Identity Architecture: OAuth 2.0 On-Behalf-Of (OBO)

Never allow an agent to query enterprise data endpoints using a single monolithic super-user service account. This bypasses all underlying row-level security and exposes confidential data across users.

• Delegated Identity (OAuth 2.0 OBO Flow):
  1. The user authenticates into the agent client (e.g., Teams or Web Chat) via Microsoft Entra ID, generating a User Bearer Token.
  2. When the agent invokes an external data API (e.g., Dynamics 365, Azure SQL, or custom REST endpoint), it exchanges the user token via the Entra ID On-Behalf-Of (OBO) flow.
  3. The downstream API receives an access token representing the specific calling user's identity and enforces native Row-Level Security (RLS) and permissions.
  4. If an employee lacks permission to view executive salaries in the ERP, the API returns HTTP 403 Forbidden, and the agent safely informs the user that they are unauthorized to access that data.