Modern Finance Intelligence: MCP Meets Fabric, RAG, and Real‑World Governance

Building a multi‑source reasoning engine for enterprise copilots

Most Copilot implementations today stop at Retrieval-Augmented Generation (RAG) — retrieving documents and generating answers.

But real enterprise finance scenarios demand more:
1. reasoning across structured data (financial tables, KPIs),
2. unstructured knowledge (PDFs, policies, procedures),
3. real‑time signals (FX rates, market data),
4. strong governance & traceability

In this guide, I’ll walk through how I built a custom MCP (Model Context Protocol) server that integrates:
• SharePoint PDFs (unstructured data)
• Microsoft Fabric Lakehouse tables (structured data)
• External REST APIs (real-time signals)
• Azure AI Search (semantic retrieval)
• Azure App Service (deployment)
• Microsoft Copilot Studio (MCP integration)

The outcome is a grounded, multi-source finance intelligence layer any Copilot can reason over.

Architecture Overview

SharePoint → Logic Apps → Azure Blob
↓
Azure AI Search (RAG)
↓
Fabric Lakehouse (ABFS) → MCP Server (FastAPI) ← External APIs
↓
Copilot Studio

Step 1 — Ingest SharePoint PDFs into Blob

Goal: Keep your knowledge base continuously updated.

How:
1. Create a Logic App
2. Add trigger:
• “When a file is created in SharePoint”
3. Add action:
• Upload file to Azure Blob Storage

Outputs

✔ PDFs automatically synced
✔ Stored in Blob for AI Search indexing
✔ Maintains governance traceability

Tip: Add a filter so only PDFs in a “Finance Documents” library trigger ingestion.

Step 2 — Create Azure AI Search Index

Goal: Add semantic + vector retrieval on unstructured content.

Steps:
1. Create Azure AI Search service
2. Create Data Source → Blob container
3. Create Index:
• content
• metadata
4. Enable vector search using embeddings from Microsoft Foundry

Step 3 — Setup Fabric Lakehouse

Goal: Query structured financial data

Steps:
1. Go to Microsoft Fabric
2. Create Lakehouse
3. Upload Excel → convert to table
4. Access via ABFS path: abfss://<container>@onelake.dfs.fabric.microsoft.com/<workspace>/<lakehouse>/Tables/<table>

Benefits

✔ Query with Pandas, PySpark, or SQL
✔ Well-governed OneLake storage
✔ Perfect for financial models requiring structured reasoning

Step 4 — Integrate External API (Real-Time Signals)

Create a PYTHON function to call the REST API

# extrernal_api.py
 import requests
 def get_exchange_rate():
 url = "https://api.exchangerate-api.com/v4/latest/USD"
 return requests.get(url).json()

Step 5 — Build MCP Server (FastAPI)

This is the core intelligence layer.
Your MCP server orchestrates:

• Azure Search (RAG)
• Fabric Lakehouse (structured data)
• External APIs (real‑time)
• Business logic (reasoning)
• Citation generation

Step 6 — Add RAG Layer (Azure AI Search)

Example callable function:

def search_docs(query):
    # Replace with actual Azure Search client call
    return [
        {
            "title": "Cashflow Report",
            "url": "https://blob-url",
            "source": "Azure Blob Storage"
        }
    ]

Later improvements can include:
✔ cosine similarity scoring
✔ hybrid search (keyword + vector)
✔ chunk-level citations

Step 7 — Deploy using GitHub Actions

Push code to GitHub and connect to Azure App Service

name: Deploy MCP App

on:
  push:
    branches:
      - main

jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      - run: pip install -r requirements.txt
      - uses: azure/webapps-deploy@v2
        with:
          app-name: cashflow-mcp
          publish-profile: ${{ secrets.AZURE_WEBAPP_PUBLISH_PROFILE }}

Your pipeline now gives you:
✔ Automatic deployment
✔ Versioning & rollback
✔ CI/CD governance

GitHub Repository

https://github.com/RichaPandit/cashflow-assistant-mcp-citations.git

Step 8 — Connect to Copilot Studio

In Microsoft Copilot Studio:
1. Go to Tools
2. Create new MCP tool
3. Add:
• Endpoint: /cashflow
• Method: POST
• Input: query
• Output: structured JSON

Final Outcome

We now have:

✔ Multi-source MCP server
✔ Real-time + structured + unstructured integration
✔ Grounded responses with citations
✔ Deployable Copilot extension

Key Takeaways

This architecture shifts the pattern from “RAG-based answers” to “Multi-source reasoning with MCP”. As an extension to this use-case, we can consider the following:
• Add authentication (App Registration)
• Improve citation UX
• Add tool selection logic in Copilot
• Expand to multi-agent workflows