A Business Analyst Workflow From Requirement to Dashboard

Turning a SQL query into a Power BI report fails most often before anyone opens Power BI. The metric is undefined, the query returns the wrong grain, or the audience sees data they should not. This article gives a business analyst workflow from requirement to published dashboard, with the SQL, the DAX, the tests, and the tickets I use.

I have built and tested reports on Epic Clarity, revenue cycle, and finance data. I have also reviewed reports that looked polished and carried wrong numbers. The steps below separate what belongs in SQL, in the model, and in the visuals, so a defect has one obvious home.

SQL Query to Power BI Report: The Workflow in One Table

Think of the path as a chain of handoffs. Each link has an owner, an output, and a check. A report is correct only when every link is correct.

Notice how many steps happen before the connection in step 6. Analysts who open Power BI first spend the project fixing, in DAX, problems that a better query would have prevented.

RequirementMetric + spec
SQL viewGrain + filters
Semantic modelStar schema
DAX + visualsMeasures, pages
Published reportSecurity + refresh
Owner: BA
Owner: data analyst
Owner: report developer
Owner: security + dev
QA reconciles every stage to the one before it: SQL to source, report to SQL
Defects have one home. A wrong grain is a SQL defect. A wrong ratio is a DAX defect. A wrong audience is a security defect.

Steps 1 to 3: Requirement, Metric Definition, and Report Spec

Power BI is a delivery tool. The business analyst’s main contribution happens in three documents that exist before the first query. They answer who decides what, using which numbers.

Metric sheet

Write one row per metric. The numerator, denominator, exclusions, grain, and owner prevent the disputes that follow a published dashboard. The which tool to master first article and the earlier role comparison use the same template.

Report specification

Get the stakeholder to sign the spec. A signed “out of scope” list does more to protect a schedule than any estimate.

Steps 4 and 5: From SQL Query to Power BI Report Starts With a Governed View

Profile the source before you write the final query. The profile tells you which fields are populated, what values exist, and what the grain is. It also shows data gaps while a fix is still cheap.

-- Profile 1: row count, date range, and nulls in the fields the report needs
SELECT  COUNT(*)                                              AS encounters,
        MIN(HOSP_DISCH_TIME)                                  AS first_discharge,
        MAX(HOSP_DISCH_TIME)                                  AS last_discharge,
        SUM(CASE WHEN HOSP_DISCH_TIME IS NULL THEN 1 ELSE 0 END)  AS no_discharge_time,
        SUM(CASE WHEN DEPARTMENT_ID  IS NULL THEN 1 ELSE 0 END)   AS no_department
FROM    PAT_ENC_HSP
WHERE   HOSP_ADMSN_TIME >= '2025-01-01';

-- Profile 2: is the intended key unique?
SELECT  PAT_ENC_CSN_ID, COUNT(*) AS copies
FROM    PAT_ENC_HSP
GROUP BY PAT_ENC_CSN_ID
HAVING  COUNT(*) > 1;

Then build the view. A view gives you one named, reviewable, testable object. Power BI reads it, and so does anyone who needs to check the numbers. The Epic Clarity SQL guide covers the tables behind the example.

CREATE VIEW rpt.vw_discharge AS
SELECT  h.PAT_ENC_CSN_ID                                    AS encounter_csn,
        CAST(h.HOSP_DISCH_TIME AS date)                     AS discharge_date,
        h.DEPARTMENT_ID                                     AS department_id,
        DATEDIFF(day, h.HOSP_ADMSN_TIME, h.HOSP_DISCH_TIME) AS los_days
FROM    PAT_ENC_HSP h
WHERE   h.HOSP_DISCH_TIME IS NOT NULL
  AND   h.ADT_PAT_CLASS_C = 1;          -- inpatient; confirm the code in your data dictionary

CREATE VIEW rpt.vw_department AS
SELECT  d.DEPARTMENT_ID AS department_id, d.DEPARTMENT_NAME AS department_name, m.service_line
FROM    CLARITY_DEP d
LEFT JOIN ref.unit_service_line m ON m.department_id = d.DEPARTMENT_ID;

Notice what the view leaves out. No patient identifier, no name, no date of birth. The report asks about volume and length of stay, so minimum necessary says the model needs none of them. If a stakeholder later asks for patient lists, treat it as a new requirement with its own approval.

Tests for the view before Power BI sees it

-- Grain: one row per encounter
SELECT encounter_csn, COUNT(*) FROM rpt.vw_discharge GROUP BY encounter_csn HAVING COUNT(*) > 1;   -- expect 0 rows

-- Completeness: view count equals an independent source count
SELECT (SELECT COUNT(*) FROM rpt.vw_discharge WHERE discharge_date >= '2026-09-01' AND discharge_date < '2026-10-01') AS view_rows,
       (SELECT COUNT(*) FROM PAT_ENC_HSP WHERE HOSP_DISCH_TIME >= '2026-09-01' AND HOSP_DISCH_TIME < '2026-10-01'
                                          AND ADT_PAT_CLASS_C = 1) AS source_rows;

-- Orphans: every department in the fact exists in the dimension
SELECT DISTINCT f.department_id FROM rpt.vw_discharge f
LEFT JOIN rpt.vw_department d ON d.department_id = f.department_id WHERE d.department_id IS NULL;   -- expect 0 rows

These are the same families the ETL validation SQL article uses for warehouse loads. A report view is a small load, and it deserves the same tests.

Step 6: Connect Power BI and Choose Import or DirectQuery

Power BI Desktop connects to SQL Server and many other sources. Connect to the view, not to a pasted query. A pasted query hides logic inside the report file and can stop Power BI from pushing later transformations back to the database.

let
    Source    = Sql.Database("sqlserver01", "Reporting"),
    Discharge = Source{[Schema = "rpt", Item = "vw_discharge"]}[Data]
in
    Discharge

Then choose how Power BI stores the data. The decision affects speed, freshness, and which features work. Record it in the spec.

Import fits most hospital and finance dashboards because yesterday’s data is enough and users expect speed. Choose DirectQuery only when freshness or size forces it, and tune the source first. The Microsoft Power BI documentation explains composite models, which mix both modes in one model.

Steps 7 and 8: Model the Data and Write DAX Measures

Shape the model as a star: one fact table in the middle, dimension tables around it. Relationships run from each dimension to the fact table, one to many, with a single filter direction.

Discharges      = COUNTROWS ( fact_discharge )
Total LOS Days  = SUM ( fact_discharge[los_days] )
Avg LOS         = DIVIDE ( [Total LOS Days], [Discharges] )

Discharges PM   = CALCULATE ( [Discharges], DATEADD ( dim_date[Date], -1, MONTH ) )
Discharges MoM% = DIVIDE ( [Discharges] - [Discharges PM], [Discharges PM] )

Build ratios from totals, as Avg LOS does. Never average an averaged column. Scenario one below shows how that mistake produces a believable and wrong number. The DAX reference lists each function. The window functions article covers the SQL equivalents of period comparison.

The rule I follow is simple. Put row-level logic as low as possible and aggregate logic as high as possible. That keeps one definition per rule and makes defects easy to find.

Steps 9 to 12: Visuals, Security, Refresh, and Sign-Off

Step 9: Design pages around questions

Give each page one question and put the answer first. A card for discharges and average LOS, a trend, and a ranked bar usually answer a unit manager. Put the metric definition in a tooltip or a small info panel, so a viewer who doubts a number can read the rule.

Step 10: Secure the data

Row-level security limits which rows a viewer sees. You define roles in Power BI Desktop with DAX filters and assign people to them in the service. A common pattern maps each manager’s sign-in to the departments they own.

-- Role: UnitManagers   Table: dim_department   Filter expression:
[manager_email] = USERPRINCIPALNAME ()

Filters flow from dimension to fact through relationships, so this one filter also restricts the discharge rows. Test each role with the “View as” feature before publishing. Then test it again in the service.

Step 11: Publish and schedule refresh

Publish from Desktop to a workspace. If the SQL server sits inside your network, the service reaches it through an on-premises data gateway. Use a service account for credentials, not a personal sign-in that expires when someone changes jobs.

Step 12: Test, reconcile, and sign off

Sign-off records who tested what and on which version. A report with no test record cannot defend its numbers in a quality meeting.

Scenario: A Dashboard That Averaged the Averages

A hospital operations team published a length-of-stay dashboard. A director compared the service line card to a query result and found a gap. The dashboard was higher by more than a day. Nobody had changed the data.

I was the business analyst on the report. I traced the gap in an hour. The report averaged a column of unit-level averages.

-- Before: measure over a precomputed average column
Avg LOS = AVERAGE ( fact_unit_month[avg_los] )

-- After: the view carries additive columns, the measure divides sums
Avg LOS = DIVIDE ( SUM ( fact_unit_month[total_los_days] ), SUM ( fact_unit_month[discharges] ) )

The SQL view needed no change except two additive columns. The fix lived in the model, and the test now sits in the UAT list. Any ratio built on a precomputed ratio deserves suspicion.

Scenario: A Revenue Dashboard Every Manager Could See in Full

A revenue cycle team built a denial dashboard with row-level security so each facility manager saw only their facility. During a pre-release check, a manager reported seeing totals for every facility.

Two lessons apply to any BI tool. Test security in the environment where users will meet it, and keep identifiers out of the model when the question does not need them. The second lesson limited the damage here.

Where Does the Logic Belong in a SQL Query to Power BI Report?

Defects hide when the same rule lives in three layers. Use the tree below to decide the home for each rule, then write it down in the spec.

What kind of rule is it?
Row-level businessrule or filter
Type fix or renamefor this report
Ratio or total thatresponds to filters
Who may seewhich rows
SQL viewTest grain and counts
Power QueryKeep steps minimal
DAX measureDivide sums, not averages
RLS + rolesTest in the service
Every branch: one home per rule, written in the spec

Performance and Effort: What Slows a Report and What It Costs

A slow report loses its audience faster than a wrong one, because people stop opening it. Most slowness comes from the model, not from Power BI itself. Check these items in order.

Query folding means Power Query turns its steps into SQL that runs at the source. When a step breaks folding, Power Query pulls the data into the service and transforms it there. The fix is nearly always to move that step into the SQL view.

Effort and naming conventions

Plan effort by step, not by page. In my projects the SQL view and the tests took as long as the visuals. Ask for time in the estimate for profiling, view tests, security, and UAT. Teams that estimate only “build the dashboard” overrun every time.

Conventions look like overhead until the person who built the report leaves. Then they are the only documentation, and the team that wrote them ships fixes in an afternoon instead of a week.

Templates a Business Analyst Can Reuse

Data source request

Send this to the data owner before you build. It surfaces access and privacy questions while there is still time to answer them.

UAT checklist

Jira-style story for the report build

Role Checklists for Power BI Report Projects

Common Mistakes Moving From SQL Query to Power BI Report

When I Would Use Power BI and When I Would Use Something Else

Your employer’s standard usually decides the tool. The workflow in this article holds for Tableau as well: define, query, test, model, secure, publish. The which tool to master first article compares the tools by task.

Edge Cases That Break Report Workflows

Healthcare data adds a standing constraint. The model may be small, yet it can hold identifiers that make it PHI. Classify the semantic model, limit the audience, and review exports with the privacy office.

Keeping the Report Correct After Release

A published report is a living product. Sources change, definitions change, and users find new questions. Treat the report like any other system with a support plan.

For more SQL patterns behind these views, return to the SQL for IT Analysts hub.

Questions Analysts Ask About Moving From SQL Query to Power BI Report

Can I paste my SQL query straight into Power BI?

Yes, Power BI accepts a SQL statement when you connect. For governed reports I prefer a view. The view is reviewable, testable, and reusable, and it keeps logic out of the report file.

Should the report calculate or should SQL calculate?

Let SQL apply row-level rules and return additive columns. Let DAX compute ratios and totals that respond to filters. Putting aggregates in SQL and then aggregating again in the report is how averages of averages happen.

Import or DirectQuery for a hospital dashboard?

Import fits most. Daily refresh is enough for staffing and quality views, and Import is faster. Choose DirectQuery when freshness or data size forces it, and tune the source first.

Does a business analyst need to learn DAX?

You need to read it. Knowing what a measure does lets you review the logic against your metric sheet. Writing complex DAX is a developer task on most teams.

Run This Before Your Next Report Build

Take one report in development. Pick one ratio card and two filter combinations. Compute the same ratio in SQL by dividing totals. If the card and the query disagree in either combination, fix the model before anyone sees the report.

Sources and Further Reading

• Microsoft Power BI documentation. Official guidance on storage modes, models, row-level security, gateways, and refresh.
• DAX reference. Official function reference for the measures used here.
• Microsoft Learn, Power BI row-level security and workspace roles articles (learn.microsoft.com). Confirm current role behavior for your tenant.
• U.S. Department of Health and Human Services, HIPAA minimum necessary guidance. Applies to the fields in views and models.

Ticket numbers and figures in the scenarios are illustrative composites. Table and column names follow standard Epic Clarity and vary by version. Power BI features and licensing change often, so confirm them against current documentation. Run every query in a non-production environment first.