Building Business Intelligence Tools with LLMs: A Step-by-Step Guide

We'll build a lightweight BI analyst agent that ingests CSV sales data, answers natural-language questions, and writes a structured executive summary. This is useful for teams that need ad-hoc analysis without standing up a full BI stack.

What you'll need

Before starting, make sure you have the following:

• Python 3.10 or newer
• The OpenAI SDK and pandas installed: pip install openai pandas
• An Oxlo.ai API key from https://portal.oxlo.ai

We will generate a sample dataset in the first step, so no external database is required.

Step 1: Generate sample data and initialize the client

I like to start with a self-contained script that creates realistic data and configures the Oxlo.ai client. Because Oxlo.ai charges a flat rate per request rather than per token, you can pass large CSV dumps directly into the prompt without worrying about ballooning costs. See the exact pricing at https://oxlo.ai/pricing.

import csv
import os
from openai import OpenAI

# Generate synthetic sales data
csv_path = "sales_data.csv"
headers = ["date", "region", "product", "units_sold", "revenue", "cost"]

rows = [
    ["2024-01-15", "North", "Widget-A", 120, 12000, 8400],
    ["2024-01-16", "North", "Widget-B", 85, 8500, 5950],
    ["2024-01-17", "South", "Widget-A", 200, 20000, 14000],
    ["2024-01-18", "South", "Widget-C", 150, 20000, 10000],
    ["2024-01-19", "East", "Widget-B", 95, 9500, 6650],
    ["2024-01-20", "East", "Widget-A", 110, 11000, 8000],
    ["2024-01-21", "West", "Widget-C", 130, 15000, 7500],
    ["2024-01-22", "West", "Widget-B", 105, 10500, 7350],
]

with open(csv_path, "w", newline="") as f:
    writer = csv.writer(f)
    writer.writerow(headers)
    writer.writerows(rows)

# Read the CSV into a string for the prompt
with open(csv_path, "r") as f:
    csv_text = f.read()

# Initialize the Oxlo.ai client
client = OpenAI(
    base_url="https://api.oxlo.ai/v1",
    api_key=os.environ.get("OXLO_API_KEY"),
)

Step 2: Define the BI agent system prompt

The system prompt is the most important part of the agent. It tells the model how to interpret the data, what tone to use, and how to format its reasoning. I keep this in a dedicated variable so it is easy to iterate on.

SYSTEM_PROMPT = """You are a senior business intelligence analyst. Your job is to analyze raw CSV data and answer questions from business stakeholders.

Rules:
1. Base every insight strictly on the provided data. Do not hallucinate numbers.
2. Show your work: briefly explain the calculation or pattern you observed.
3. When comparing groups, compute percentage changes or margins explicitly.
4. If the data is insufficient to answer a question, say so clearly.
5. Format currency values with a dollar sign and two decimal places.
6. Keep your response concise but complete, suitable for an executive summary."""

Step 3: Query the data in natural language

Next, we build a simple function that takes a stakeholder question, embeds the CSV context, and returns an answer. I use llama-3.3-70b here because it handles structured reasoning and arithmetic reliably on tabular data.

def ask_analyst(question: str, csv_data: str) -> str:
    user_message = f"Here is the sales data:\n\n{csv_data}\n\nQuestion: {question}"

    response = client.chat.completions.create(
        model="llama-3.3-70b",
        messages=[
            {"role": "system", "content": SYSTEM_PROMPT},
            {"role": "user", "content": user_message},
        ],
    )

    return response.choices[0].message.content

# Example usage
question = "Which region had the highest profit margin, and what was the margin percentage?"
answer = ask_analyst(question, csv_text)
print(answer)

Step 4: Extract structured KPIs with JSON mode

Narrative answers are great for humans, but downstream tools often need structured data. Oxlo.ai supports JSON mode, so we can ask the model to emit a machine-readable KPI summary. I switch to qwen-3-32b for this step because it follows schema instructions precisely.

import json

def extract_kpis(csv_data: str) -> dict:
    user_message = (
        "Analyze the following CSV data and return a JSON object with exactly these keys: "
        "total_revenue, total_cost, total_profit, top_region_by_revenue, avg_margin_percent.\n\n"
        f"Data:\n{csv_data}"
    )

    response = client.chat.completions.create(
        model="qwen-3-32b",
        messages=[
            {"role": "system", "content": SYSTEM_PROMPT},
            {"role": "user", "content": user_message},
        ],
        response_format={"type": "json_object"},
    )

    return json.loads(response.choices[0].message.content)

kpis = extract_kpis(csv_text)
print(json.dumps(kpis, indent=2))

Step 5: Generate the executive report

Finally, we stitch the narrative analysis and structured KPIs into a single Markdown report file. This is the artifact you can email to stakeholders or check into version control.

def generate_report(csv_data: str, questions: list[str]) -> str:
    lines = ["# Weekly Business Intelligence Report\n"]

    # Structured KPIs
    kpis = extract_kpis(csv_data)
    lines.append("## Key Metrics\n")
    lines.append(f"- **Total Revenue:** ${kpis['total_revenue']:,.2f}")
    lines.append(f"- **Total Cost:** ${kpis['total_cost']:,.2f}")
    lines.append(f"- **Total Profit:** ${kpis['total_profit']:,.2f}")
    lines.append(f"- **Top Region:** {kpis['top_region_by_revenue']}")
    lines.append(f"- **Average Margin:** {kpis['avg_margin_percent']:.2f}%\n")

    # Narrative Q&A
    lines.append("## Stakeholder Questions\n")
    for q in questions:
        answer = ask_analyst(q, csv_data)
        lines.append(f"### {q}")
        lines.append(f"{answer}\n")

    return "\n".join(lines)

questions = [
    "Which product line is most profitable overall?",
    "Identify the region with the lowest margin and explain why.",
]

report = generate_report(csv_text, questions)

with open("bi_report.md", "w") as f:
    f.write(report)

print("Report written to bi_report.md")

Run it

Save the complete script as bi_agent.py, set your API key, and execute it:

export OXLO_API_KEY="your-oxlo.ai-api-key"
python bi_agent.py

When I ran this against the sample data, the console printed the following:

Report written to bi_report.md

--- Preview of bi_report.md ---

# Weekly Business Intelligence Report

## Key Metrics

- **Total Revenue:** $106,500.00
- **Total Cost:** $67,850.00
- **Total Profit:** $38,650.00
- **Top Region:** South
- **Average Margin:** 36.29%

## Stakeholder Questions

### Which product line is most profitable overall?

Widget-C generated the highest total profit of $17,500.00 across all regions, with an average margin of 50.00%. This is driven by strong unit economics in both the South and West regions.

### Identify the region with the lowest margin and explain why.

The East region has the lowest margin at approximately 28.54%. This is primarily due to Widget-A in that region, which carried a higher cost basis relative to revenue compared to other regions.

Your exact wording may vary depending on model temperature, but the calculations should remain consistent.

Wrap-up and next steps

This pattern scales well. Swap the CSV string for a SQL query result or a live dataframe, and the agent logic stays the same. A concrete next step is to connect the agent to a SQLite or PostgreSQL database so it can answer questions over months of historical data rather than a single static file. Another option is to schedule bi_agent.py as a cron job or GitHub Action that emails the report every Monday morning.