User (Intranet)

↓

AI Agent (Local LLM - Qwen/LLaMA)

↓

Intent Detection + Query Understanding

↓

Common Database (SQLite / DuckDB)

↓

Power BI (Connected to same DB)

↓

Dashboard

↓

Embedded in AI Agent UI

AI Agent + Power BI Integration (Intranet Setup)

🔷 Overview

This system connects an AI Agent (Python + Docker + DuckDB + Parquet + SQLite) with a dashboard created in Power BI.

👉 Core Idea:

AI Agent processes & stores data
Power BI reads same data
Dashboard is embedded back into AI Agent UI

🔷 Architecture

User Query
↓
AI Agent (Local Model – Qwen/LLaMA)
↓
Intent Detection + Data Processing
↓
Common Database (DuckDB / Parquet / SQLite)
↓
Power BI (Connected to same data)
↓
Dashboard
↓
Embedded in AI Agent UI

🔷 Technologies Used

AI Model: /
Backend: Python
Storage: DuckDB, SQLite, Parquet
Visualization:
Embedding:
API (optional):
Containerization: Docker

🔷 Step-by-Step Implementation

✅ Step 1: Setup AI Agent (Already Done)

Accept user query
Extract data from Excel/PDF
Convert into structured format

✅ Step 2: Data Processing

data = extract_data(file)

✅ Step 3: Store Data in Common Database

import duckdb

con = duckdb.connect("data.db")
con.execute("CREATE TABLE IF NOT EXISTS sales AS SELECT * FROM data")

data.to_parquet("data/sales.parquet")

✅ Step 4: Connect Power BI to Data

Option A: Parquet

Power BI → Get Data → Parquet

Option B: DuckDB (ODBC)

Install DuckDB ODBC
Power BI → Get Data → ODBC

✅ Step 5: Create Dashboard (One-Time)

Create charts
Save report
Publish (optional)

✅ Step 6: Handle User Query

if "dashboard" in query:
    intent = "dashboard"

✅ Step 7: Update Data

save_to_duckdb(data)
save_to_parquet(data)

✅ Step 8: Refresh Power BI

Auto refresh (recommended)
OR

trigger_refresh(dataset_id)

✅ Step 9: Get Embed URL

embed_url = "https://powerbi/report/..."

✅ Step 10: Show Dashboard in UI

<iframe src="embed_url" width="100%" height="600"></iframe>

🔷 🔥 PSEUDO CODE (IMPORTANT)

🔹 Main Flow

function handle_user_query(query):

    # Step 1: Detect intent using local model
    intent = detect_intent(query)

    if intent == "dashboard":

        # Step 2: Fetch or extract data
        data = get_uploaded_data()

        # Step 3: Process data
        cleaned_data = process_data(data)

        # Step 4: Store in database
        store_in_duckdb(cleaned_data)
        store_in_parquet(cleaned_data)

        # Step 5: Refresh Power BI dataset
        refresh_powerbi()

        # Step 6: Return dashboard लिंक
        return {
            "type": "dashboard",
            "url": EMBED_URL
        }

    else:
        return normal_ai_response(query)

🔹 Intent Detection (LLM)

function detect_intent(query):

    if "dashboard" in query:
        return "dashboard"

    elif "report" in query:
        return "dashboard"

    else:
        return "normal"

🔹 Data Storage

function store_in_duckdb(data):

    connect to duckdb
    create table if not exists
    insert data

function store_in_parquet(data):

    save file as parquet

🔹 Power BI Refresh

function refresh_powerbi():

    # Option 1: Do nothing (auto refresh enabled)
    return True

    # Option 2: API call
    call Power BI API to refresh dataset

🔹 UI Handling

function render_response(response):

    if response.type == "dashboard":
        show iframe(response.url)
    else:
        show text(response.message)

🔷 Full Logic Flow

User Query
↓
AI Agent (Intent Detection using local model)
↓
Process Data
↓
Store in DuckDB / Parquet
↓
Power BI reads same data
↓
Dashboard refresh
↓
Return embed URL
↓
Display in UI

🔷 Docker Setup

volumes:
  - ./data:/data

🔷 Installation Requirements

1. Python Libraries

pip install duckdb pandas pyarrow

2. Power BI

Install Power BI Desktop

3. DuckDB ODBC (Optional)

Install DuckDB ODBC driver

4. Docker

Install Docker

5. Local LLM (Ollama)

ollama run qwen

🔷 Key Design Points

No direct AI → Power BI data transfer
Use common database
Power BI reads same data
Use iframe for embedding
Works fully in intranet