Technical Guide

Deploying LLMs for BI Automation

Integrating large language models with Power BI and Tableau to automate report narratives, anomaly triage, and natural-language queries — without letting the model touch your numbers.

1. Why LLMs belong next to your BI stack, not inside it

Most organizations that run Power BI or Tableau have the same three gaps. Reports get refreshed but nobody reads them until something breaks. Alerts fire but a human has to figure out whether they matter. And the people who most need answers from the data cannot write DAX, SQL, or a calculated field, so they file a ticket and wait.

Large language models are genuinely good at all three gaps — summarizing structured results into prose, triaging alert payloads against context, and translating plain English into query languages. They are genuinely bad at one thing that BI is entirely about: arithmetic. An LLM asked to sum a column will produce something that looks like a sum. Sometimes it is even correct. That is not a property you can build a reporting pipeline on.

The design principle that runs through this entire guide is therefore simple: the model never computes; it narrates, classifies, and translates. Your warehouse and your BI engine do the math. The LLM receives already-computed aggregates and does language work on top of them. Every architecture below is a variation on enforcing that boundary.

A second principle worth stating up front: treat the LLM as an unreliable junior analyst with excellent prose. You would not let that analyst push numbers to the CFO unreviewed, and you should not let the model do so either — at least not until you have run in shadow mode long enough to trust the specific pipeline, with the specific data shapes, you actually have.

2. Three architecture patterns

Pattern A — LLM between the warehouse and the BI layer

The model sits in your ETL/ELT flow. After the warehouse (Fabric/Synapse, Snowflake, BigQuery, SQL Server) finishes its transformations, a job queries computed aggregates, sends them to the LLM, and writes the model's output — narrative text, classifications, entity tags — back into a table. Power BI or Tableau then displays that column like any other.

Pattern B — LLM consuming BI REST APIs

The model (orchestrated by an Azure Function, n8n, Power Automate, or a small service) calls the BI platform's own APIs: Power BI's ExecuteQueries endpoint to run DAX against a published dataset, or Tableau's REST API to pull view data as CSV. The LLM works on the results and the output goes to email, Teams, Slack, or a ticketing system.

Pattern C — Scheduled narrative-generation pipelines

A cron-style pipeline (nightly, weekly) that packages a fixed set of KPIs and comparisons into a JSON payload, has the LLM draft a structured narrative, validates the output, and distributes it. This is Pattern A or B plus scheduling, templating, and a validation gate — and it is where most organizations should start, because the scope is bounded and the failure mode is "the email did not go out," not "a user got a wrong answer interactively."

All three patterns share one property: the LLM is a stateless component behind your orchestrator. You can swap providers (Azure OpenAI, Anthropic Claude, a locally hosted model) without touching the BI layer, because the contract is just "JSON in, text out."

3. Walkthrough: nightly narrative summaries from Power BI

Goal: every morning at 6:30, stakeholders receive a short written summary of yesterday's numbers — what moved, by how much, versus which baseline — generated after the overnight dataset refresh completes, and only from figures the dataset itself computed.

3.1 The trigger

Do not run on a fixed clock time and hope the refresh finished. Poll the refresh history, or better, have the refresh pipeline call you. With the Power BI REST API, a service principal can check refresh status directly:

GET https://api.powerbi.com/v1.0/myorg/groups/{workspaceId}/datasets/{datasetId}/refreshes?$top=1
Authorization: Bearer {token}

# Response (abridged)
{
  "value": [{
    "refreshType": "Scheduled",
    "startTime": "2026-08-02T09:00:14Z",
    "endTime":   "2026-08-02T09:07:41Z",
    "status": "Completed"
  }]
}

An n8n version of this flow looks like: Schedule trigger (every 15 min, 6:00–8:00) → HTTP Request node (refresh history, OAuth2 client-credentials against Entra ID) → IF node (status equals Completed and endTime is after last run) → HTTP Request node (ExecuteQueries, below) → HTTP Request node (LLM API) → validation Code node → Send Email / Teams node. Persist the last processed endTime in a static-data field or a small table so re-runs are idempotent.

3.2 Pull computed aggregates with ExecuteQueries

The ExecuteQueries endpoint runs DAX against the published dataset, so all measures, RLS-independent logic, and time intelligence come from the semantic model — not from anything the LLM does:

POST https://api.powerbi.com/v1.0/myorg/groups/{workspaceId}/datasets/{datasetId}/executeQueries
Content-Type: application/json

{
  "queries": [{
    "query": "EVALUATE ROW(
        \"RevenueYesterday\", CALCULATE([Total Revenue], PREVIOUSDAY('Date'[Date])),
        \"RevenueSameDayLastWeek\", CALCULATE([Total Revenue], DATEADD('Date'[Date], -8, DAY)),
        \"OrdersYesterday\", CALCULATE([Order Count], PREVIOUSDAY('Date'[Date])),
        \"AvgOrderValueYesterday\", CALCULATE([AOV], PREVIOUSDAY('Date'[Date]))
      )"
  }],
  "serializerSettings": { "includeNulls": true }
}

Note the limits: ExecuteQueries requires the dataset to be accessible to the caller (service principal added to the workspace), is subject to per-user request throttling, and returns at most a bounded number of rows per query — all fine for aggregate pulls, wrong for bulk export. Compute deltas and percentages in your orchestration code (Python, or an n8n Code node), never in the prompt:

# Azure Function (Python) — assemble the payload the LLM will narrate
def build_facts(row: dict) -> dict:
    rev, rev_lw = row["[RevenueYesterday]"], row["[RevenueSameDayLastWeek]"]
    return {
        "period": "2026-08-01",
        "comparison": "same weekday, prior week",
        "metrics": [
            {"name": "Revenue", "value": rev, "baseline": rev_lw,
             "delta_pct": round((rev - rev_lw) / rev_lw * 100, 1)},
            {"name": "Orders", "value": row["[OrdersYesterday]"]},
            {"name": "Avg order value", "value": row["[AvgOrderValueYesterday]"]},
        ],
    }

3.3 The LLM call

POST /v1/messages   (Anthropic API; Azure OpenAI chat/completions is analogous)
{
  "model": "claude-sonnet-latest",
  "max_tokens": 700,
  "system": "<system prompt from section 6>",
  "messages": [{
    "role": "user",
    "content": "Write the daily summary from these facts only:\n{\"period\":\"2026-08-01\",...}"
  }]
}

The response is a draft, not a deliverable. It passes through the validation gate in section 7 before anything is sent. Tableau note: the same pipeline works with Tableau as the source by replacing step 3.2 with a REST call to /api/{version}/sites/{site-id}/views/{view-id}/data (CSV of a summary view) or, on recent Tableau Cloud/Server releases, the VizQL Data Service for headless queries against a published data source. The rest of the pipeline is unchanged — which is precisely the benefit of keeping the LLM behind an orchestrator.

4. Walkthrough: anomaly detection with LLM triage

Two separations matter here. First, detection is statistics, not language modeling. Use whatever you already have — Power BI data alerts, Tableau-driven threshold checks, or a warehouse job running a z-score/EWMA/seasonal-decomposition test. Asking an LLM "is this number anomalous?" is asking it to do math; it will answer confidently either way. Second, triage is where the LLM earns its keep: given an alert payload plus context, decide severity, probable cause category, and routing — the work a human on-call currently does at 7 a.m.

A workable flow: the detector fires and posts a payload to a webhook (Azure Function or n8n). The orchestrator enriches it — pulls the metric's last 30 daily values from the warehouse, checks a calendar table for holidays/promos, checks the refresh log for late upstream loads. The LLM then classifies:

{
  "alert": {
    "metric": "Daily New Signups",
    "value": 41, "expected_range": [180, 260],
    "detector": "seasonal_esd", "fired_at": "2026-08-02T07:05:00Z"
  },
  "context": {
    "last_30_days": [212, 198, ..., 41],
    "calendar_flags": [],
    "upstream_refresh": {"status": "Completed", "rows_loaded": 312,
                          "typical_rows": 48000},
    "recent_changes": ["Signup form deployment 2026-08-01 22:14 UTC"]
  }
}

Given that payload, a competent model will notice that rows_loaded is two orders of magnitude below typical and classify this as a probable data-pipeline issue rather than a business collapse — which is exactly the judgment call that pages the wrong team when it is missed. Constrain the output to a schema (use the provider's structured-output/tool-call mode rather than "please reply in JSON"):

{
  "severity": "high",
  "category": "data_quality",        // enum: data_quality | business | seasonal | unknown
  "confidence": "high",
  "rationale": "Upstream load wrote 312 rows vs ~48k typical; metric drop
                coincides with incomplete load, not user behavior.",
  "route_to": "data-engineering",
  "suggested_check": "Re-run signups staging load; compare row counts."
}

Two honest caveats. The model's confidence field is self-reported and weakly calibrated — use it for sorting, not gating. And keep a deterministic fallback: if the LLM call fails or returns invalid JSON, the alert goes out raw. An AI layer must never be able to swallow an alert.

5. Natural-language queries: NL-to-DAX/SQL with guardrails

This is the most requested capability and the easiest to ship irresponsibly. The failure modes are specific: the model invents column names, silently misreads intent ("top customers" by revenue or by orders?), or writes a query that is valid but wrong. If you are on Power BI, evaluate Copilot and Q&A first — if they satisfy your users, do not build. The custom route is justified when you need private deployment, cross-source queries, or tighter control than the packaged features give you.

If you build, these guardrails are the difference between a demo and a system:

1. Ground the schema explicitly. Feed the model the actual tables, columns, measure names, and — critically — short descriptions and value examples. For Power BI, export this from the semantic model (INFO functions or the XMLA endpoint); for SQL, generate it from information_schema plus a curated glossary. The model should be told: "Use only these objects. If the question cannot be answered from them, say so."
2. Generate against the semantic layer when possible. NL→DAX via ExecuteQueries means RLS still applies and measures carry your business logic. NL→raw-SQL bypasses both; if you go that way, run as a read-only role, on a replica, with row limits.
3. Validate before executing. Parse the generated query. Reject anything that is not a single read statement (for DAX, anything that is not EVALUATE ...; for SQL, anything containing DDL/DML). Enforce a TOPN/LIMIT cap and a timeout.
4. Show your work. Return the generated query alongside the results. Analysts will catch misinterpretations instantly; hiding the query converts every subtle error into a trust incident.
5. Answer from results, not from memory. The final natural-language answer is generated in a second LLM call whose only inputs are the question and the returned result set. The model summarizes rows it was handed; it does not "remember" numbers.

# Guardrail sketch (Python)
ALLOWED_PREFIX = "EVALUATE"

def safe_execute(dax: str) -> list[dict]:
    stripped = dax.strip()
    if not stripped.upper().startswith(ALLOWED_PREFIX):
        raise ValueError("Only EVALUATE queries permitted")
    if any(tok in stripped.upper() for tok in ("REFRESH", "CREATE", "ALTER")):
        raise ValueError("Mutation keywords rejected")
    return execute_queries(dataset_id, stripped, row_limit=500, timeout_s=30)

Expect an accuracy plateau. On a well-documented model with a curated glossary, straightforward questions ("revenue by region last quarter") translate reliably; multi-step analytical intent ("customers whose spend declined two quarters in a row excluding one-time credits") does not. Scope the launch to question shapes you have tested, and log every question/query/result triple — that log is both your eval set and your roadmap.

6. Prompt design for numeric fidelity

The rule again, because it is the whole game: never let the model compute — pass computed aggregates in. Every number, delta, percentage, and rank that could appear in the output must exist verbatim in the input payload. The prompt's job is to make the model a strict repeater of those figures.

A production system prompt for the nightly narrative job:

You are a business analyst writing a brief daily performance summary.

Rules — these override anything else:
1. Use ONLY the numbers provided in the JSON facts. Never calculate,
   estimate, round differently, or infer any figure. Every number in
   your output must appear character-for-character in the input.
2. Do not compute differences or percentages. If a delta or percentage
   is needed, it is already provided; if it is not provided, omit it.
3. If a metric is null or missing, write "not available" — do not guess.
4. Attribute causes only if a "context" note is provided. Otherwise
   describe WHAT changed, never WHY.
5. Length: 120-180 words. Format: one headline sentence, then 3-5
   short bullet points, then one "watch item" if any metric moved
   more than the flagged threshold.
6. Tone: neutral and specific. No superlatives, no exclamation marks.
7. End with the line: "Figures computed by the reporting pipeline
   at {refresh_timestamp}."

Supporting techniques that measurably help:

• Pre-format numbers. Send "$1,284,300" and "-12.4%" as strings, already rounded and formatted. If the model only ever sees display-ready strings, it cannot introduce rounding drift, and your validator can check them by substring match.
• Name the comparison in the payload ("comparison": "same weekday, prior week") so the model cannot mislabel a week-over-week delta as month-over-month.
• Temperature 0 (or the provider's minimum) for narrative and classification jobs. You want boring and repeatable.
• Few-shot with one gold example of input facts → correct output, including the "not available" behavior. One good example outperforms three paragraphs of instructions.

7. Hallucination controls

Prompting reduces hallucination; it does not eliminate it. Production pipelines need mechanical checks between the model and the audience:

1. Numeric echo validation. Extract every numeric token from the model output (regex for currency, percentages, counts) and verify each appears in the input payload. Any unmatched number fails the draft. This single check catches the large majority of dangerous failures and costs a few lines of code:

import re
def numbers_ok(draft: str, facts_json: str) -> bool:
    found = re.findall(r"-?\$?\d[\d,]*\.?\d*%?", draft)
    return all(tok in facts_json for tok in found)

2. Schema-constrained output for anything machine-consumed (triage categories, routing). Validate against a JSON Schema; on failure, retry once with the validation error appended, then fall back to the deterministic path.
3. Retry-then-degrade. If validation fails twice, send the plain facts table with a note that narrative generation was skipped. A missing paragraph is a non-event; a wrong number in an executive email is a project-ending event.
4. Shadow mode first. Run the pipeline for two to four weeks with output going only to the team that owns it. Count validation failures and "technically correct but misleading" drafts. Promote to stakeholders only when the review burden is genuinely low.
5. Log everything. Prompt, payload, raw output, validation verdict, final disposition — every run. When someone questions a summary from three weeks ago, you need the exact inputs, not a shrug.
6. Label the output. "Draft generated automatically from reporting data" is honest and lowers the temperature of the first inevitable error.

8. Cost model: token math for a nightly job

LLM costs for narrative BI work are small, and it is worth doing the arithmetic once to stop worrying about it. Rough sizing rule: one token is about four characters of English or JSON.

Take the nightly summary job: a system prompt (~450 tokens), a facts payload with 12 metrics, deltas, and 30 days of context values (~1,100 tokens), one gold-standard example (~400 tokens) — call it 2,000 input tokens — and a 160-word output with formatting, about 350 output tokens.

Line item	Tokens/run	Runs/month	Tokens/month
Input (prompt + facts + example)	2,000	30	60,000
Output (narrative draft)	350	30	10,500
Validation retries (~10% of runs)	2,350	3	~7,000

At representative mid-tier model pricing — on the order of $3 per million input tokens and $15 per million output — that is roughly $0.20 input + $0.16 output + $0.04 retries: well under $1 per month for the nightly job. Scale it up: fifty distinct daily summaries across departments is still a few dollars a month. Add an anomaly-triage pipeline at ~3,000 tokens per event and a hundred events monthly — under a dollar. Even an interactive NL-query assistant at 4,000 tokens per question and 1,000 questions a month lands in the tens of dollars.

The practical implications: model fees are noise; engineering time is the cost. Do not compromise the architecture to save tokens, do not bother with a cheaper model tier until volume is orders of magnitude higher, and when someone proposes fine-tuning to "save on prompt length," check the math first — at these volumes it never pays back. The one place cost does bite is if you let an interactive assistant stuff entire raw tables into context; the fix is the same as the fidelity fix — send aggregates, not rows. Where the same large system prompt and schema description are resent on every call, prompt caching (supported by the major providers) cuts input cost substantially; treat it as a free optimization once things work.

9. Security: service principals, data minimization, private deployment

9.1 Identity and least privilege

Automation should never run on a human account. For Power BI: register an Entra ID app, enable service-principal access in the tenant admin settings (scoped to a security group, not the whole tenant), add the principal to the specific workspace with the minimum role — Viewer suffices for ExecuteQueries; Contributor only if the pipeline also triggers refreshes. Store the client secret or, better, a certificate in Azure Key Vault or your orchestrator's credential store — never in n8n workflow JSON, environment files in a repo, or the prompt itself. For Tableau, the analogue is a Personal Access Token on a dedicated service account with a minimal site role, rotated on a schedule, because PATs expire and revoke cleanly.

9.2 Data minimization

The strongest security control in this architecture is one you get for free by following the fidelity rule: the LLM only ever sees aggregates. A payload of twelve KPIs and their deltas contains no customer names, no account numbers, no row-level anything. Design reviews should ask one question of every pipeline: what is the most sensitive thing in the payload? For narrative jobs, the honest answer should be "the company's own topline numbers." When a use case genuinely needs row-level text (triaging support-ticket contents, for instance), minimize deliberately: strip identifier columns, mask emails and phone numbers with a deterministic scrubber before the API call, and cap the number of rows. Also review your provider's data-use terms — the major API providers do not train on API traffic under standard commercial terms, but that is a contract fact to verify, not assume, and it may differ for consumer-tier products your staff might reach for informally.

9.3 Private deployment when data cannot leave

Some environments — government contractors, healthcare, firms under strict data-residency agreements — cannot send even aggregates to a public API endpoint. There is a spectrum of options, in increasing order of isolation:

• Cloud-tenant deployment: Azure OpenAI or AWS Bedrock inside your own tenant/VPC, with private endpoints and no public egress. Data stays within your cloud boundary and your compliance framework (including government-cloud variants where required). For most "data can't leave" requirements, this is the right answer and dramatically cheaper to operate than self-hosting.
• Self-hosted open-weight models: Llama-family or comparable open-weight models served on your own GPUs (vLLM or similar) for air-gapped or on-premises-only environments. Be honest about the trade: narrative quality for constrained, well-prompted tasks like the nightly summary is entirely adequate on mid-size open models; complex NL-to-DAX translation degrades more noticeably. The fidelity-first architecture helps here too — a model that only repeats provided numbers has less room to be worse.
• Hybrid routing: aggregates-only pipelines use a hosted API; anything touching row-level or regulated data routes to the private deployment. The orchestrator makes this a configuration choice, not a rebuild.

Private and hybrid deployment for exactly these scenarios is MBIC's core specialty; the architecture in this guide is deliberately deployment-agnostic so that decision stays reversible.

10. A practical build order

Sequenced so that each step ships value and de-risks the next:

1. Week 1 — plumbing. Service principal or PAT, credential vaulting, and a proven ExecuteQueries/view-data pull for one dataset. No LLM yet. This step surfaces every tenant-setting and permission problem early, and those are the delays.
2. Weeks 2–3 — one nightly narrative in shadow mode. One dataset, ~10 metrics, system prompt from section 6, numeric echo validation, output to the building team only. Iterate on the payload shape — that is where quality lives.
3. Week 4 — promote and instrument. Send to real stakeholders with the "generated automatically" label. Add run logging and a failure alert (degrade to plain facts, never silence).
4. Weeks 5–6 — anomaly triage. Wire the existing detector (or add a simple z-score job) to the enrich-then-classify flow. Route to a channel humans already watch; keep the raw-alert fallback.
5. Weeks 7–10 — NL queries, scoped. Curate the schema glossary, build the validation gate, launch to a pilot group of five to ten analysts with the generated query always visible. Log every interaction as future eval data.
6. Ongoing — the flywheel. Review logs monthly: validation failure rate, questions the NL layer refused or botched, triage classifications the on-call overrode. Each is a concrete prompt or glossary fix. Expand to new datasets by cloning the pipeline, not by widening one pipeline's scope.

Total elapsed time to a genuinely useful system is measured in weeks, and the LLM spend is trivial. The real investment is the unglamorous middle: payload design, validation, and the discipline of never letting the model do arithmetic. Teams that hold that line ship BI automation that survives contact with a skeptical CFO. Teams that do not usually get one bad number into one important email, and the project quietly dies. Build the boring version.

Want this wired into your stack? Book a 15-minute call → mbic.us/contact.html — or get a free AI & Automation Opportunity Audit → mbic.us/ai-audit.html.