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 Power BI extension
      INSTALL pbix FROM community;
      LOAD pbix;
    properties:
      pbix_file: './models/sales_model.pbix'

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
host: 0.0.0.0
port: 8080

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/
template-source: sales_summary.sql
connection:
  - sales-model
cache-template: sales_cache.sql
cache-schedule: "0 */6 * * *"  # Refresh every 6 hours

parameters:
  year:
    type: integer
    description: Filter by year
    minimum: 2020
    maximum: 2030
  quarter:
    type: integer
    description: Filter by quarter (1-4)
    minimum: 1
    maximum: 4

Example 2: Customer Segmentation

Leverage Power BI's customer segments:

sqls/customer_segments.sql

SELECT
  c.CustomerID,
  c.CustomerName,
  c.Segment,  -- Calculated in Power BI
  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
{{#params.limit}}
LIMIT {{{params.limit}}}
{{/params.limit}}

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 >= DATE_SUB(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-template: powerbi_cache.sql
cache-schedule: "0 */6 * * *"  # Refresh every 6 hours

sqls/powerbi_cache.sql

-- Load entire model into DuckDB cache
SELECT * FROM pbix_scan('{{{conn.pbix_file}}}', 'Sales')
UNION ALL
SELECT * FROM pbix_scan('{{{conn.pbix_file}}}', 'Customers')
UNION ALL
SELECT * FROM pbix_scan('{{{conn.pbix_file}}}', 'Products')

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 (100x 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

-- Reimplement YoY growth 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. Monitor File Changes

# Re-scan when .pbix changes
watch:
  files:
    - '/data/models/*.pbix'
  action: refresh-cache

4. Secure Model Files

# Restrict file permissions
properties:
  pbix_file: '/secure/models/sales.pbix'
  read_only: true

# Add authentication
authentication:
  type: api-key
  keys:
    - name: dashboard-app
      key: ${DASHBOARD_API_KEY}

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. Add indexes on frequently queried columns
4. 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;

Phase 2: Add Caching

cache-template: powerbi_cache.sql
cache-schedule: "0 */6 * * *"

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!