Power BI Integration

Extension Credit

This guide uses the pbix extension by the DuckDB community. Thanks to the contributors who made Power BI file integration possible!

Query Power BI data models, DAX measures, and tabular models programmatically via REST API. Reuse existing BI logic without rebuilding, perfect for mobile apps, custom dashboards, and automated reporting.

Why Use Power BI with flAPI?

"Your Power BI logic, now available everywhere."

Power BI contains valuable business logic:

• Reuse DAX measures - complex calculations already defined
• No rebuilding - leverage existing BI models
• Mobile/custom apps - access BI data outside Power BI
• Automated reports - scheduled data extraction
• API for dashboards - build custom visualizations

flAPI advantages:

• Read Power BI .pbix files directly
• Cache for performance (Power BI can be slow)
• Add custom authentication layers
• Mobile-friendly REST APIs
• MCP tools for AI agents

Real-World Use Cases

Mobile App with BI Data

Problem: Need sales metrics in mobile app, but Power BI Desktop only Solution: flAPI exposes Power BI model as REST API, app queries it

Automated Reporting

Problem: Need to email daily reports from Power BI Solution: Scheduled job queries flAPI, generates PDF reports

Custom Dashboards

Problem: Want custom UI, not Power BI's interface Solution: Build React/Vue dashboard using flAPI endpoints

AI with BI Context

Problem: AI agent needs company metrics for analysis Solution: Expose Power BI as MCP tools for Claude/GPT

Installation

Install PBIX Extension

flapi.yaml

connections:
  powerbi-model:
    init: |
      INSTALL pbix FROM community;
      LOAD pbix;
    properties:
      pbix_file: './models/sales_model.pbix'

The pbix_file property is referenced in templates as {{{ conn.pbix_file }}}. It is a DuckDB pbix extension property — see upstream docs for the full list of options.

Prerequisites

1. Power BI Desktop file (.pbix)
2. Extract to accessible location
3. Read permissions on file

Configuration

Basic Setup

flapi.yaml

project-name: powerbi-api

connections:
  sales-model:
    init: |
      INSTALL pbix FROM community;
      LOAD pbix;
    properties:
      pbix_file: '/data/models/sales_dashboard.pbix'

Multiple Models

connections:
  # Sales analytics
  sales-model:
    init: |
      INSTALL pbix FROM community;
      LOAD pbix;
    properties:
      pbix_file: '/data/sales_dashboard.pbix'

  # Financial reporting
  finance-model:
    init: |
      LOAD pbix;
    properties:
      pbix_file: '/data/finance_dashboard.pbix'

Example 1: Query Sales Data

Power BI Model Structure

Typical .pbix contains:

• Tables: Sales, Customers, Products, Dates
• Relationships: defined between tables
• DAX Measures: Total Revenue, YoY Growth, etc.
• Calculated Columns: Profit Margin, Customer Segment

SQL Query

sqls/sales_summary.sql

-- Query tables from Power BI model
SELECT
  d.Year,
  d.Month,
  d.Quarter,
  SUM(s.Amount) AS TotalSales,
  COUNT(DISTINCT s.CustomerID) AS UniqueCustomers,
  COUNT(DISTINCT s.OrderID) AS TotalOrders
FROM pbix_scan('{{{ conn.pbix_file }}}', 'Sales') s
JOIN pbix_scan('{{{ conn.pbix_file }}}', 'Dates') d
  ON s.DateKey = d.DateKey
WHERE 1=1
{{#params.year}}
  AND d.Year = {{ params.year }}
{{/params.year}}
{{#params.quarter}}
  AND d.Quarter = {{ params.quarter }}
{{/params.quarter}}
GROUP BY d.Year, d.Month, d.Quarter
ORDER BY d.Year DESC, d.Month DESC

sqls/sales_summary.yaml

url-path: /sales/summary/
method: GET
template-source: sales_summary.sql
connection:
  - sales-model

cache:
  enabled: true
  table: sales_summary_cache
  schedule: 6h
  template-file: sales_cache.sql

request:
  - field-name: year
    field-in: query
    description: Filter by year
    required: false
    validators:
      - type: int
        min: 2020
        max: 2030
  - field-name: quarter
    field-in: query
    description: Filter by quarter (1-4)
    required: false
    validators:
      - type: int
        min: 1
        max: 4

Example 2: Customer Segmentation

Leverage Power BI's customer segments:

sqls/customer_segments.sql

SELECT
  c.CustomerID,
  c.CustomerName,
  c.Segment,
  c.LifetimeValue,
  c.LastPurchaseDate,
  COUNT(DISTINCT o.OrderID) AS TotalOrders,
  SUM(o.Amount) AS TotalSpent
FROM pbix_scan('{{{ conn.pbix_file }}}', 'Customers') c
LEFT JOIN pbix_scan('{{{ conn.pbix_file }}}', 'Orders') o
  ON c.CustomerID = o.CustomerID
{{#params.segment}}
WHERE c.Segment = '{{{ params.segment }}}'
{{/params.segment}}
GROUP BY
  c.CustomerID,
  c.CustomerName,
  c.Segment,
  c.LifetimeValue,
  c.LastPurchaseDate
ORDER BY TotalSpent DESC

Example 3: DAX Measures (via Pre-computed Tables)

Challenge: DuckDB can't execute DAX directly

Solution: Power BI calculates measures → export to tables → query with flAPI

In Power BI

1. Create a "Measures Table" with pre-computed metrics
2. Refresh on schedule
3. Export .pbix

Query Measures

sqls/kpi_dashboard.sql

SELECT
  Period,
  TotalRevenue,
  RevenueYoY,
  GrossMargin,
  CustomerChurnRate,
  AverageOrderValue
FROM pbix_scan('{{{ conn.pbix_file }}}', 'KPI_Measures')
WHERE Period >= CURRENT_DATE - INTERVAL 12 MONTH
ORDER BY Period DESC

Performance Optimization

Challenge: Reading .pbix Files

Power BI files can be large (100s of MB) with complex models.

Solution 1: Cache Aggressively

cache:
  enabled: true
  table: powerbi_cache
  schedule: 6h
  template-file: powerbi_cache.sql

sqls/powerbi_cache.sql

-- Load model into DuckLake cache
INSERT INTO {{cache.catalog}}.{{cache.schema}}.{{cache.table}}
SELECT * FROM pbix_scan('{{{ conn.pbix_file }}}', 'Sales')

Solution 2: Export to Parquet

# One-time: export Power BI tables to Parquet
duckdb -c "
  INSTALL pbix FROM community;
  LOAD pbix;
  COPY (SELECT * FROM pbix_scan('model.pbix', 'Sales'))
  TO 'sales.parquet' (FORMAT PARQUET);
"

Then query Parquet (much faster):

SELECT * FROM 'sales.parquet'
WHERE Year = 2024

Performance Comparison

Approach	Query Time	Setup Effort
Direct .pbix	200-500ms	Low (just reference file)
Cached .pbix	1-10ms	Medium (configure caching)
Exported Parquet	1-5ms	High (export pipeline)

Recommendation: Start with caching, migrate to Parquet if needed.

Limitations

What Works

• Read all tables from .pbix
• Query relationships (via JOIN)
• Access calculated columns
• Filter and aggregate
• Mix with other data sources

What Doesn't Work

• Execute DAX directly - no DAX engine in DuckDB
• Write back to .pbix - read-only
• Real-time data - must refresh .pbix file
• Power BI Service - only works with .pbix files, not cloud datasets

Workaround for DAX

Option 1: Pre-compute measures in Power BI

Power BI DAX:
YoY Growth = CALCULATE(
  [Total Sales],
  DATEADD('Date'[Date], -1, YEAR)
)

→ Materialize as calculated column or separate table
→ Query with flAPI

Option 2: Reimplement in SQL

SELECT
  current_year.Month,
  current_year.Sales AS CurrentSales,
  prior_year.Sales AS PriorYearSales,
  ROUND(
    (current_year.Sales - prior_year.Sales) / prior_year.Sales * 100,
    2
  ) AS YoYGrowthPercent
FROM sales_current current_year
LEFT JOIN sales_prior prior_year
  ON current_year.Month = prior_year.Month

Best Practices

1. Version Control .pbix Files

# Store .pbix in version control or shared storage
/data/models/
├── sales_v2024.01.pbix
├── finance_v2024.01.pbix
└── hr_dashboard.pbix

2. Automate .pbix Refresh

#!/bin/bash
# refresh_powerbi.sh - run on schedule

# Refresh Power BI Desktop file (requires Windows + Power BI Desktop)
powershell -Command "
  & 'C:\Program Files\Microsoft Power BI Desktop\bin\PBIDesktop.exe' \
  -openFile 'C:\models\sales_dashboard.pbix' \
  -refresh
"

# Copy updated file to flAPI location
cp C:\models\sales_dashboard.pbix /data/models/

3. Secure Model Files

Restrict file permissions on the server, and protect the endpoint with one of flAPI's supported auth schemes (basic, jwt, bearer, oidc):

# sqls/sales_summary.yaml
auth:
  enabled: true
  type: bearer
  jwt-secret: '${POWERBI_JWT_SECRET}'

Troubleshooting

Issue: "Cannot open .pbix file"

Error: Failed to read Power BI file

Solutions:

1. Verify file path is correct
2. Check file permissions (flAPI must have read access)
3. Ensure .pbix is not open in Power BI Desktop
4. Validate file is not corrupted

Issue: Slow queries

Problem: Queries take > 500ms

Solutions:

1. Enable caching (essential)
2. Export to Parquet for large datasets
3. Use incremental refresh for large models

Issue: Tables not found

Error: Table 'Sales' not found in .pbix

# List all tables in .pbix
duckdb -c "
  INSTALL pbix FROM community;
  LOAD pbix;
  SELECT * FROM pbix_tables('model.pbix');
"

Issue: Outdated data

Problem: API returns stale data

Solution: Refresh .pbix file more frequently

# Automate with cron
0 */2 * * * /scripts/refresh_powerbi.sh

Migration Path

Phase 1: Direct .pbix Access

connections:
  powerbi:
    init: |
      INSTALL pbix FROM community;
      LOAD pbix;
    properties:
      pbix_file: '/data/sales_dashboard.pbix'

Phase 2: Add Caching

cache:
  enabled: true
  table: powerbi_cache
  schedule: 6h
  template-file: powerbi_cache.sql

Phase 3: Export to Parquet (for scale)

# Export high-traffic tables
duckdb export_to_parquet.sql

-- export_to_parquet.sql
COPY (SELECT * FROM pbix_scan('model.pbix', 'Sales'))
TO 'sales.parquet';

Next Steps

• BigQuery: Alternative BI data warehouse
• Snowflake: Cloud data warehouse
• Caching Setup: Optimize performance
• Examples: See similar patterns

Additional Resources

• DuckDB PBIX Extension: Official extension docs
• Power BI Documentation: Microsoft Power BI guide
• DAX Reference: DAX function reference

---

When to Use

Power BI integration is perfect when you have existing BI models with complex logic that you want to expose via API. Don't rebuild what already works - just expose it with flAPI!