From SQL Query to Power BI Dashboard: The Simplest Pipeline That Actually Works

🎯 Situation

A new analyst joined a client's team and asked a straightforward question: "How do I get data from our database into a Power BI report that refreshes automatically?"

Most answers to that question online jump straight to connecting Power BI to raw tables and writing transformations in Power Query. That works at small scale. At production scale — with multiple users, scheduled refreshes, and business logic that needs to be consistent — it creates fragile reports that break when the source data changes.

Here's that pipeline, step by step.

⚠️ Challenge: Two Wrong Approaches

Before the right approach, it's worth naming the two common mistakes — because both are taught as standard practice in introductory tutorials.

Both approaches work for prototypes and personal reports. Neither scales cleanly to team-level, production reporting where multiple people share dashboards built on consistent, governed data.

🔍 The 4-Step Pipeline

Step 1 — Write the business logic in SQL, not Power Query

Create a SQL view in your central database that handles all joins, filters, aggregations, and metric definitions. This is the single source of truth for that KPI — the same view we discussed in the article on KPI definitions.

-- Step 1: Create a clean view in your database
-- This is what Power BI will connect to — not the raw tables

CREATE VIEW vw_monthly_sales AS
SELECT
    DATE_TRUNC('month', o.order_date)   AS month,
    p.category                          AS product_category,
    r.name                              AS region,
    COUNT(DISTINCT o.order_id)          AS order_count,
    SUM(ol.quantity)                    AS units_sold,
    SUM(ol.quantity * ol.unit_price)    AS gross_revenue,
    SUM(ol.quantity * ol.unit_price
        - ol.quantity * p.cost_price)   AS gross_margin
FROM orders o
JOIN order_lines ol  ON o.order_id  = ol.order_id
JOIN products    p   ON ol.product_id = p.product_id
JOIN regions     r   ON o.region_id = r.region_id
WHERE o.status = 'completed'
GROUP BY 1, 2, 3;

This view joins four tables, filters to completed orders, and produces a clean monthly summary. Any tool — Power BI, Excel, Python, Looker — can query it and get the same numbers.

Step 2 — Connect Power BI to the view, not to raw tables

In Power BI Desktop: Get Data → SQL Server (or your database of choice) → enter the server and database → choose the view (vw_monthly_sales), not the underlying tables. Import mode or DirectQuery depending on volume and refresh needs. For most SMBs: Import mode with daily refresh is the right choice.

Step 3 — Keep Power Query minimal

Once the view is clean, Power Query should do almost nothing: rename columns if needed, set data types, nothing else. All transformation logic stays in SQL. If you find yourself writing complex M code in Power Query, that's a signal the SQL view needs work.

Step 4 — Build the semantic model and schedule the refresh

In Power BI Service: publish the report → go to dataset settings → configure scheduled refresh (daily at 6 AM is standard) → set up a gateway if your database is on-premises. Done. The report updates automatically every morning without anyone touching it.

✓️ Why This Architecture Lasts

The reason this pattern works at scale is the separation of concerns — each layer has one job:

• Database (SQL) — stores raw data, defines business logic via views, enforces consistency across all consumers
• Power BI dataset — imports clean, pre-aggregated data, defines the semantic model (relationships, hierarchies, DAX measures), handles user-facing calculations
• Power BI report — visualizes the semantic model, handles layout and interactivity, zero business logic here

When the business changes — a new region, a redefined KPI, a new product category — you update the SQL view. Every report built on that view updates automatically at the next refresh. You don't touch the .pbix file.

When someone asks "where is the revenue calculation?" — the answer is always the same: vw_monthly_sales, line 12. Not "somewhere in Power Query" or "I think it's in the DAX measure but also maybe in the M query."