Healthcare BA Portfolio Case Study

Build a senior BA portfolio with SQL, Power BI dashboards, and process maps.

Introduction

30 Second Summary

Every time a doctor orders an MRI or a specialist referral, someone at a health insurer has to approve it. When that approval process is slow or inconsistent, patients wait longer for care and the insurer loses revenue.

In this project, you will build a portfolio-ready case study for a mid-sized health insurer struggling with long prior authorization turnaround times, inconsistent quality reporting, and limited visibility into high-risk members. You will generate synthetic data, analyze it with SQL, build two executive dashboards in Power BI, and publish everything on a shareable Canva site with interactive dashboards hosted on NovyPro.

What You'll Build

Picture sharing a single URL with a hiring manager and watching them click through interactive dashboards, scan the process improvements you designed, and download your Power BI file to explore on their own machine.

By the end of this project, you'll have:

  • Two interactive Power BI dashboards covering prior authorization performance and population health, hosted on NovyPro so a hiring manager can filter and explore the data themselves.
  • A complete set of BA deliverables including a BRD infographic, an As-Is / To-Be process map, and a SQL analysis snippet that showcase the full BA lifecycle from requirements through analysis.
  • A shareable Canva portfolio site URL you can put on your resume and LinkedIn, linking your dashboards, deliverables, and narrative into one polished page.
  • Secret Mission: Create a stakeholder recommendations deck that turns your dashboard findings into executive-ready action items.

Are there any prerequisites?

You will need a Windows machine with Power BI Desktop installed. Basic familiarity with SQL queries and Excel will help you follow along.

Before We Start

Before diving into data and dashboards, let's lock in what you're building and why it matters.

Defining your target role and the single takeaway you want a hiring manager to walk away with will shape every decision in this project.

Set Up Your Tools and Accounts

You have defined your target role and the key takeaway your portfolio needs to deliver. Now it is time to get the workspace ready so you can start building.

Your portfolio case study depends on five tools working together: a SQL environment for data analysis, Power BI Desktop for dashboards, NovyPro for hosting interactive dashboards online, Lucidchart (or draw.io) for process mapping, and Canva for the portfolio site. This step gets each one installed and connected.

In this step, get ready to:
  • Set up your SQL environment and create the HealthPlanAnalytics database.
  • Create your free NovyPro, Lucidchart (or draw.io), and Canva accounts.
  • Confirm all tools are accessible and connected.
Set up your SQL environment

Every query you write in later steps runs against a local SQL database. You need a SQL tool that lets you write queries and view results, plus a dedicated database to hold the healthcare tables.

First, confirm whether you already have a SQL tool installed. SQL Server Management Studio (SSMS) and MySQL Workbench are the two most common free options on Windows.

✔️ I already have a SQL tool installed

Open your SQL tool and confirm you can connect to your local database server. If the connection succeeds, you are ready to create the database.

ⓧ I don't have a SQL tool yet

Pick one of the two free options below and install it. Both work for this project.

Which option should I choose?

MySQL Installer is a single download that bundles everything you need. SQL Server Express requires two separate downloads (the server and SSMS). If you have no preference, MySQL is the faster path to get started.

After installation, open your SQL tool and confirm you can connect to your local database server before moving on.

  • Create the project database by running this command in a new query window:
CREATE DATABASE HealthPlanAnalytics;

What does this command do?

This creates an empty database called HealthPlanAnalytics on your local server. All five healthcare tables you generate later will live inside this database.

  • Confirm the database was created by running the verification query for your SQL tool:

SQL Server

SELECT name FROM sys.databases WHERE name = 'HealthPlanAnalytics';

You should see one row with the value HealthPlanAnalytics.

MySQL

SHOW DATABASES LIKE 'HealthPlanAnalytics';

You should see one row with the value HealthPlanAnalytics.

Don't see the database?

  • Make sure you clicked Execute (or pressed F5 in SSMS, or the lightning bolt icon in MySQL Workbench) to run the CREATE DATABASE command before running the verification query.
  • Check for a typo in the database name. SQL is case-sensitive depending on your server's collation settings.

Help me troubleshoot my SQL database creation

Create your free accounts

Three web platforms power different parts of your portfolio. NovyPro hosts your interactive dashboards. Lucidchart (or draw.io) handles process mapping. Canva holds the final portfolio site.

  • Open your browser and go to novypro.com.
  • Sign up for a free account and complete the registration.

You should see the NovyPro dashboard after signing up.

NovyPro also provides the Power BI credentials you need to publish dashboards online. Set these up now.

  • Click your profile icon in the top-right corner of NovyPro.
  • Select Power BI from the dropdown.
  • Follow the on-screen credential setup to create your Power BI account through NovyPro.
  • Note the Power BI email and password that NovyPro provides. You will use these credentials to sign into Power BI Desktop shortly.

Why NovyPro for Power BI?

NovyPro gives you a free Power BI account with Publish to Web access. Without NovyPro, you would need a paid Power BI Pro license to host interactive dashboards online.

The tradeoff is that dashboards published through NovyPro are publicly accessible. This is fine for your portfolio because all data is synthetic.

  • Go to lucidchart.com and sign up for a free account. Alternatively, go to app.diagrams.net (draw.io) if you prefer a fully free, no-account-required option.

You should see a blank canvas or template picker after signing up.

  • Go to canva.com and sign up for a free account if you do not already have one.

You should see the Canva homepage with template options after signing in.

Confirm all tools are accessible

Each tool is installed or signed up individually. This final check makes sure everything works together before you start generating data.

  • Press the Windows key to open your search bar.
  • Type Power BI Desktop and press Enter to open it.
  • Click Sign in and enter the Power BI email and password that NovyPro provided during credential setup.

After signing in, Power BI Desktop shows your account name in the top-right corner.

  • Switch to your SQL tool and confirm you can still connect to the HealthPlanAnalytics database.
  • Open Lucidchart (or draw.io) in your browser and confirm you can create a new blank diagram.

Before you move on, take a moment to predict: what should you see across all four tools if everything is set up correctly?

Here is your checklist. Power BI Desktop shows your signed-in account name. Your SQL tool lists HealthPlanAnalytics in the database explorer. Lucidchart (or draw.io) opens a blank diagram canvas. Canva is accessible in your browser.

Power BI Desktop won't sign in?

  • Double-check that you are using the email and password from NovyPro's Power BI credential setup, not a personal Microsoft account.
  • If you see a message about your account not being recognized, return to NovyPro, click your profile icon, select Power BI, and verify the credentials were created successfully.

Help me sign into Power BI Desktop with NovyPro credentials

Your workspace is locked and loaded. Next up, you will generate realistic healthcare data and load it into your HealthPlanAnalytics database so you have something to analyze.

Generate and Load Mock Data

Your dashboards and SQL analysis need realistic data behind them. Without data that mirrors real healthcare operations, your portfolio piece is just a pretty layout with nothing to prove.

Real patient data is off-limits for a public portfolio. In this step, you will generate synthetic healthcare data that mirrors the patterns a real health insurer would see. The data will include built-in operational problems so your later analysis has something meaningful to surface.

In this step, get ready to:
  • Generate five CSV files with realistic synthetic healthcare data using an AI tool.
  • Validate the data in Excel for consistency and built-in problems.
  • Load all five CSVs into your HealthPlanAnalytics database and confirm the row counts.
Generate the Members CSV

You will use ChatGPT (or a similar AI tool with code execution) to generate each CSV file one table at a time. The Members table is the foundation because every other table references Member_ID.

  • Open ChatGPT (or your preferred AI tool) in your browser.
  • Create a new folder on your Desktop called HealthPlanData to store all five CSV files.
  • Paste the following prompt into ChatGPT to generate the Members table:
Generate a CSV file named Members.csv with exactly 5,000 rows.

Columns: Member_ID (M0001 to M5000), First_Name, Last_Name,
Date_of_Birth (1930-2008), Age, Gender (M/F),
County (mix of 10 US counties across urban and rural areas), State,
Plan_Type (HMO/PPO/Medicare Advantage/Medicaid),
Risk_Score (decimal, 0.5 to 5.0),
Chronic_Conditions (one or more of: Diabetes, Hypertension, CHF,
COPD, Asthma, CKD, Depression, or None - separate multiples with
semicolons), PCP_ID (PCP001 to PCP050),
Enrollment_Date (2020-01-01 to 2025-12-31).

Rules:
- 30% of members must have Risk_Score above 3.0
- Members with Risk_Score above 3.0 must have 2+ chronic conditions
- Medicare Advantage members should skew older (65+)
- Medicaid members should skew younger (18-40)
- Mix: 25% HMO, 25% PPO, 30% Medicare Advantage, 20% Medicaid

Provide the file for download.

What does this prompt do?

This prompt creates your member population with intentional patterns. The 30% high-risk threshold ensures your later dashboards have enough high-risk members to analyze.

The plan type distribution and age skews mimic a real health insurer's member mix. Medicare Advantage members trend older, while Medicaid members trend younger.

  • Download the generated Members.csv file and save it to your HealthPlanData folder.

ChatGPT not generating 5,000 rows?

Some AI tools may cap output size. If the file has fewer rows than expected, ask the tool to "continue generating rows and append them to the same file" or to "use code execution to generate the full 5,000 rows and provide the file for download."

Help me get ChatGPT to generate a large CSV file with 5,000 rows of healthcare member data.

Generate the remaining four CSV files

With your Members table as the foundation, you will now generate the four tables that reference it. Each prompt includes specific built-in problems that your dashboards will later expose.

  • Generate the Nurse_Reviewers table by pasting this prompt into ChatGPT:
Generate a CSV file named Nurse_Reviewers.csv with exactly 15 rows.

Columns: Reviewer_ID (NR001 to NR015), First_Name, Last_Name,
Specialty (mix of: Internal Medicine, Cardiology, Orthopedics,
Oncology, Behavioral Health, Pulmonology),
Hire_Date (2018-01-01 to 2024-06-30),
Department (Utilization Management for all rows).

Provide the file for download.
  • Download Nurse_Reviewers.csv and save it to your HealthPlanData folder.

The Authorization_Requests table is the most important one for your Utilization Management dashboard. It includes built-in compliance failures and caseload imbalances.

  • Generate the Authorization_Requests table by pasting this prompt:
Generate a CSV file named Authorization_Requests.csv with exactly
2,500 rows.

Columns: Auth_ID (PA0001 to PA2500),
Member_ID (randomly from M0001-M5000),
Reviewer_ID (from NR001-NR015),
Service_Type (Imaging/Surgery/DME/Home Health/Specialty Referral/
Physical Therapy/Inpatient Admission),
Urgency (80% Standard, 20% Expedited),
Submit_Date (2025-01-01 to 2025-12-31), Decision_Date,
Decision (Approved/Denied/Partially Approved/Pended),
Denial_Reason (only when Denied: Missing Documentation/
Medical Necessity Not Met/Out of Network/Service Not Covered/
Incomplete Clinical Information/Duplicate Request),
Line_of_Business (Commercial/Medicare Advantage/Medicaid),
Diagnosis_Code (ICD-10 codes matching the Service_Type).

Built-in problems:
- Assign 40% of all cases to just 3 nurses: NR002, NR007, NR012
- 15-20% of Standard requests: Decision_Date > 7 days after Submit_Date
- 10-15% of Expedited requests: Decision_Date > 3 days after Submit_Date
- Overall denial rate: 18-22%
- Top denial reasons: Medical Necessity Not Met, Missing Documentation

Provide the file for download.

Why plant these problems in the data?

Under CMS-0057-F (effective January 1, 2026), health insurers must decide standard prior authorizations within 7 calendar days and expedited requests within 72 hours. Your data intentionally includes requests that exceed these targets so your dashboards can flag compliance gaps.

The caseload imbalance (40% of cases going to just 3 of 15 nurses) simulates a real problem where a few experienced reviewers get overloaded while others are underutilized.

  • Download Authorization_Requests.csv and save it to your HealthPlanData folder.
  • Generate the Care_Gaps table by pasting this prompt:
Generate a CSV file named Care_Gaps.csv with exactly 10,000 rows.

Columns: Gap_ID (CG00001 to CG10000),
Member_ID (randomly from M0001-M5000, multiple gaps per member OK),
Measure_Name (HbA1c Testing/Breast Cancer Screening/
Colorectal Cancer Screening/Blood Pressure Control/
Medication Adherence - Diabetes/Medication Adherence - Hypertension/
Annual Wellness Visit/Depression Screening),
Measure_Category (HEDIS or STAR),
Status (Open/Closed),
Due_Date (2025-01-01 to 2025-12-31),
Closure_Date (only if Closed, before Due_Date),
Outreach_Count (0 to 5),
Last_Outreach_Date (only if Outreach_Count > 0).

Built-in problems:
- 45% of gaps should have Status = Open
- 30% of Open gaps should have Outreach_Count >= 2
- Members with Risk_Score above 3.0 should have more open gaps
- Breast Cancer Screening and Colorectal Cancer Screening: lowest
  closure rates

Provide the file for download.
  • Download Care_Gaps.csv and save it to your HealthPlanData folder.
  • Generate the final table, Claims, by pasting this prompt:
Generate a CSV file named Claims.csv with exactly 20,000 rows.

Columns: Claim_ID (CL00001 to CL20000),
Member_ID (randomly from M0001-M5000),
Service_Date (2025-01-01 to 2025-12-31),
Claim_Type (ER/Inpatient/Outpatient/Professional),
Diagnosis_Code (ICD-10: E11 Diabetes, I10 Hypertension, I50 CHF,
J44 COPD, J45 Asthma, N18 CKD, F32 Depression, plus general codes),
Procedure_Code (CPT codes appropriate for Claim_Type),
Billed_Amount (decimal), Paid_Amount (decimal, 60-90% of Billed),
Provider_ID (PRV001 to PRV100).

Built-in problems:
- Members with Risk_Score above 3.0: 3-5x more claims than low-risk
- High-risk members: disproportionately more ER and Inpatient claims
- ER Billed_Amount: $500-$5,000
- Inpatient Billed_Amount: $5,000-$50,000
- Outpatient and Professional: $100-$2,000

Provide the file for download.
  • Download Claims.csv and save it to your HealthPlanData folder.

Confirm that all five files are saved in your HealthPlanData folder: Members.csv, Nurse_Reviewers.csv, Authorization_Requests.csv, Care_Gaps.csv, and Claims.csv.

Why generate one table at a time?

The tables have referential relationships. Authorization_Requests references both Member_ID from Members and Reviewer_ID from Nurse_Reviewers. Generating Members first ensures every foreign key in later tables points to a real member.

Review and validate the data in Excel

AI-generated data can have subtle issues: wrong column names, missing values, or inconsistent IDs. A quick spot-check in Excel catches these before you load anything into SQL.

  • Open Members.csv in Excel.
  • Confirm the header row contains the column names you specified (e.g. Member_ID, Plan_Type, Risk_Score, Chronic_Conditions).
  • Scroll to the bottom and confirm the row count is close to 5,000 (check the status bar at the bottom of Excel or press Ctrl+End to jump to the last row).
  • Open Authorization_Requests.csv in Excel.
  • Spot-check a few rows: pick a Member_ID value and confirm it falls in the M0001 to M5000 range from your Members table.
  • Check that rows with Decision set to Denied have a value in the Denial_Reason column, and that approved rows have it blank.

You should see denial reasons like Medical Necessity Not Met and Missing Documentation appearing most frequently among the denied rows.

  • Open Care_Gaps.csv in Excel and filter the Status column to show only Open rows.
  • Confirm that some open gaps have Outreach_Count of 2 or more. These represent members who were contacted but still have not closed their care gap.
  • Repeat a quick header and row-count check for Nurse_Reviewers.csv (15 rows) and Claims.csv (approximately 20,000 rows).

Fix issues now, not later

If any file has wrong column names, missing data, or significantly fewer rows than expected, re-run that specific prompt in ChatGPT. It is much easier to regenerate a CSV now than to debug a SQL import failure later.

Load all five CSVs into your SQL database

Your validated CSV files need to live in the HealthPlanAnalytics database as tables so you can query them with SQL in the next step. The import process depends on which SQL tool you are using.

SQL Server (SSMS)

SQL Server Management Studio has a built-in import wizard that handles CSV files.

  • In SSMS, expand your HealthPlanAnalytics database in the Object Explorer.
  • Right-click on Tables and select Import Data.
  • Choose Flat File Source as the data source.
  • Browse to your HealthPlanData folder and select Members.csv.
  • Confirm the column preview looks correct, then click Next through the remaining wizard pages and click Finish.
  • Repeat the import process for Nurse_Reviewers.csv, Authorization_Requests.csv, Care_Gaps.csv, and Claims.csv.

MySQL Workbench

MySQL Workbench has a Table Data Import Wizard for loading CSV files.

  • In MySQL Workbench, make sure your HealthPlanAnalytics database is selected in the Schemas panel.
  • Right-click on Tables under the database and select Table Data Import Wizard.
  • Browse to your HealthPlanData folder and select Members.csv.
  • Select Create new table and name it Members.
  • Review the column mappings, then click Next and Finish to import.
  • Repeat the import process for Nurse_Reviewers.csv, Authorization_Requests.csv, Care_Gaps.csv, and Claims.csv.

Import failing or column types look wrong?

If the import wizard shows errors, check that your CSV files do not have extra blank rows at the end. Open the file in Excel, delete any empty rows after your data, and save before re-importing.

If date columns are importing as text, you can leave them for now. Your SQL queries in the next step will handle any needed type casting.

Help me troubleshoot a CSV import issue in my SQL environment.

Before you run the verification query, think about what row counts you expect to see for each table. Do you remember the targets from the prompts?

  • Verify all five tables loaded correctly by running this query in your SQL environment:
-- Check row counts for all five tables
SELECT 'Authorization_Requests' AS Table_Name, COUNT(*) AS Row_Count
FROM Authorization_Requests
UNION ALL
SELECT 'Nurse_Reviewers', COUNT(*) FROM Nurse_Reviewers
UNION ALL
SELECT 'Members', COUNT(*) FROM Members
UNION ALL
SELECT 'Care_Gaps', COUNT(*) FROM Care_Gaps
UNION ALL
SELECT 'Claims', COUNT(*) FROM Claims;

What should I see?

Your results should show five rows with these approximate counts:

  • Authorization_Requests: approximately 2,500 rows.
  • Nurse_Reviewers: 15 rows.
  • Members: approximately 5,000 rows.
  • Care_Gaps: approximately 10,000 rows.
  • Claims: approximately 20,000 rows.

Seeing 0 rows or a missing table?

If a table shows 0 rows, the import may have created the table structure but failed to load the data. Re-run the import wizard for that specific CSV file. If a table is missing entirely, check that you selected the HealthPlanAnalytics database (not a default system database) before importing.

Help me figure out why my SQL table import shows 0 rows or a missing table.

Your database is loaded and ready for analysis. Next up, you will write SQL queries that surface the operational problems hidden in this data. Those patterns will drive everything in your dashboards.

Analyze Data with SQL

Your HealthPlanAnalytics database is loaded with five tables of realistic healthcare data. Now it is time to make that data talk.

Raw numbers sitting in tables do not tell a story on their own. In this step, you will write SQL queries that surface prior authorization bottlenecks, nurse workload imbalances, care gap closure rates, and high-risk member profiles. These findings drive everything you build in the dashboards next.

In this step, get ready to:
  • Write utilization management queries covering PA turnaround, CMS compliance, denial reasons, and nurse caseload.
  • Write population health queries covering care gap closure, high-risk members, and utilization patterns.
  • Capture the high-risk member query as a portfolio-ready SQL code snippet visual.
Write the utilization management queries

Utilization management tracks how a health plan reviews and approves care requests. Your first set of queries focuses on prior authorization turnaround time, CMS compliance, denial patterns, and nurse reviewer workload.

Using MySQL instead of SQL Server?

The queries below use SQL Server (T-SQL) syntax. If you are using MySQL, replace DATEDIFF(DAY, start, end) with DATEDIFF(end, start) for day differences. For hour differences, replace DATEDIFF(HOUR, start, end) with TIMESTAMPDIFF(HOUR, start, end).

The window function SUM(COUNT(*)) OVER() works in both SQL Server and MySQL 8.0+.

  • Calculate PA turnaround time and flag CMS compliance by running this query in your SQL environment:
-- Calculate turnaround and flag CMS-0057-F compliance
SELECT
    Request_ID,
    Member_ID,
    Urgency,
    Service_Type,
    Submit_Date,
    Decision_Date,
    DATEDIFF(DAY, Submit_Date, Decision_Date) AS Turnaround_Days,
    -- 7 days for Standard, 72 hours for Expedited
    CASE
        WHEN Urgency = 'Standard'
            AND DATEDIFF(DAY, Submit_Date, Decision_Date) > 7
            THEN 'Non-Compliant'
        WHEN Urgency = 'Expedited'
            AND DATEDIFF(HOUR, Submit_Date, Decision_Date) > 72
            THEN 'Non-Compliant'
        ELSE 'Compliant'
    END AS CMS_Compliance_Flag
FROM Authorization_Requests;

What does this query do?

  • DATEDIFF(DAY, Submit_Date, Decision_Date) calculates the number of calendar days between submission and decision for each PA request.
  • The CASE statement flags each request against CMS-0057-F targets: 7 calendar days for Standard requests and 72 hours for Expedited requests.
  • Any request exceeding its target is marked Non-Compliant.
  • Check your results. You should see every authorization request with a Turnaround_Days value and a CMS_Compliance_Flag of either Compliant or Non-Compliant. Some rows should show Non-Compliant because the mock data includes built-in turnaround problems.
  • Calculate the compliance rate broken down by urgency and service type by running this query:
-- Compliance rate by urgency and service type
SELECT
    Urgency,
    Service_Type,
    COUNT(*) AS Total_Requests,
    SUM(CASE
        WHEN Urgency = 'Standard'
            AND DATEDIFF(DAY, Submit_Date, Decision_Date) <= 7 THEN 1
        WHEN Urgency = 'Expedited'
            AND DATEDIFF(HOUR, Submit_Date, Decision_Date) <= 72 THEN 1
        ELSE 0
    END) AS Compliant_Count,
    ROUND(100.0 * SUM(CASE
        WHEN Urgency = 'Standard'
            AND DATEDIFF(DAY, Submit_Date, Decision_Date) <= 7 THEN 1
        WHEN Urgency = 'Expedited'
            AND DATEDIFF(HOUR, Submit_Date, Decision_Date) <= 72 THEN 1
        ELSE 0
    END) / COUNT(*), 1) AS Compliance_Rate_Pct
FROM Authorization_Requests
GROUP BY Urgency, Service_Type
ORDER BY Compliance_Rate_Pct ASC;

What does this query do?

  • Groups all authorization requests by Urgency and Service_Type so you can see which combinations are falling behind.
  • Calculates Compliance_Rate_Pct as the percentage of requests meeting CMS targets within each group.
  • Sorting ascending puts the worst-performing combinations at the top, which is exactly what a dashboard user needs to see first.
  • Check your results. Each row shows a unique urgency and service type combination with its compliance rate. Look for any rows with compliance below 80%. Those are the areas your dashboard will highlight.
  • Break down denial reasons by count and percentage by running this query:
-- Denial reason breakdown with percentages
SELECT
    Denial_Reason,
    COUNT(*) AS Denial_Count,
    ROUND(100.0 * COUNT(*)
        / SUM(COUNT(*)) OVER(), 1) AS Denial_Pct
FROM Authorization_Requests
WHERE Decision = 'Denied'
GROUP BY Denial_Reason
ORDER BY Denial_Count DESC;

What does this query do?

  • Filters to denied requests only, then counts how many times each denial reason appears.
  • SUM(COUNT(*)) OVER() is a window function that calculates the total denial count across all reasons, letting you compute each reason's percentage without a subquery.
  • Check your results. You should see each denial reason ranked by frequency. The top reason tells you where the biggest operational bottleneck sits.
  • Analyze nurse caseload distribution and average turnaround per nurse by running this query:
-- Nurse caseload and average turnaround
SELECT
    nr.Nurse_ID,
    nr.Nurse_Name,
    COUNT(ar.Request_ID) AS Total_Cases,
    ROUND(AVG(DATEDIFF(DAY, ar.Submit_Date,
        ar.Decision_Date)), 1) AS Avg_Turnaround_Days
FROM Authorization_Requests ar
JOIN Nurse_Reviewers nr
    ON ar.Nurse_ID = nr.Nurse_ID
GROUP BY nr.Nurse_ID, nr.Nurse_Name
ORDER BY Total_Cases DESC;

What does this query do?

  • Joins Authorization_Requests with Nurse_Reviewers to show each nurse's total caseload alongside their average turnaround time.
  • Nurses with high caseloads and long turnaround times are likely overloaded. This is the data point that will drive the caseload bar chart in your dashboard.
  • Check your results. You should see 15 rows (one per nurse). Some nurses should have noticeably higher case counts and longer turnaround times, confirming the workload imbalance built into the mock data.

Getting a SQL error?

  • If you see a message about an invalid column name, double-check that your column names match the table schema exactly. Column names are case-sensitive in some SQL environments.
  • If DATEDIFF throws an error, confirm whether you are using SQL Server or MySQL. MySQL uses DATEDIFF(end, start) with only two arguments for day differences.
  • If your JOIN returns zero rows, verify that the Nurse_ID values in Authorization_Requests match those in Nurse_Reviewers.

Still stuck? Help me troubleshoot my SQL query errors.

Write the population health queries

Population health analysis shifts the focus from individual authorizations to member outcomes across the entire plan. These queries surface care gap closure rates, identify high-risk members, and reveal utilization patterns by chronic condition.

  • Calculate care gap closure rates by measure name and category by running this query:
-- Care gap closure rates by measure and category
SELECT
    Measure_Name,
    Measure_Category,
    COUNT(*) AS Total_Gaps,
    SUM(CASE WHEN Gap_Status = 'Closed'
        THEN 1 ELSE 0 END) AS Closed_Gaps,
    ROUND(100.0 * SUM(CASE WHEN Gap_Status = 'Closed'
        THEN 1 ELSE 0 END)
        / COUNT(*), 1) AS Closure_Rate_Pct
FROM Care_Gaps
GROUP BY Measure_Name, Measure_Category
ORDER BY Closure_Rate_Pct ASC;

What does this query do?

  • Groups all care gaps by Measure_Name (e.g., HbA1c screening, breast cancer screening) and Measure_Category to show which HEDIS and STAR measures have the lowest closure rates.
  • Low closure rates despite outreach indicate a process gap, which is exactly what your population health dashboard will visualize.
  • Check your results. Each row shows a measure with its total gaps, closed gaps, and closure percentage. Measures with closure rates below 50% need the most attention.

The next query is the centerpiece of your portfolio. Before you run it, think about what you expect to see. Do you think high-risk members will cluster around certain chronic conditions or plan types?

  • Identify high-risk members by joining Members, Claims, and Care_Gaps by running this query:
-- High-risk member identification
SELECT
    m.Member_ID,
    m.Age,
    m.Risk_Score,
    m.Chronic_Conditions,
    SUM(CASE WHEN c.Claim_Type = 'ER'
        THEN 1 ELSE 0 END) AS ER_Visits,
    SUM(CASE WHEN c.Claim_Type = 'Inpatient'
        THEN 1 ELSE 0 END) AS Inpatient_Visits,
    COUNT(CASE WHEN cg.Gap_Status = 'Open'
        THEN 1 END) AS Open_Gaps,
    m.Plan_Type
FROM Members m
LEFT JOIN Claims c ON m.Member_ID = c.Member_ID
LEFT JOIN Care_Gaps cg ON m.Member_ID = cg.Member_ID
WHERE m.Risk_Score >= 2.5
GROUP BY m.Member_ID, m.Age, m.Risk_Score,
    m.Chronic_Conditions, m.Plan_Type
ORDER BY m.Risk_Score DESC;

Why is this the portfolio query?

This single query demonstrates three-table joins, conditional aggregation, and domain knowledge of population health risk stratification. It is the kind of query a hiring manager wants to see because it connects clinical data (chronic conditions, care gaps) with operational data (claims, utilization).

The LEFT JOIN pattern ensures members without claims or care gaps still appear in results. The WHERE m.Risk_Score >= 2.5 filter focuses on the population that drives the most cost and clinical concern.

Your results should be sorted by Risk_Score descending, showing Member_ID, Age, Risk_Score, Chronic_Conditions, ER_Visits, Inpatient_Visits, Open_Gaps, and Plan_Type. The top results should show members with risk scores above 3.0 and multiple ER visits.

  • Calculate ER and inpatient visit frequency by chronic condition by running this query:
-- ER and inpatient utilization by chronic condition
SELECT
    m.Chronic_Conditions,
    SUM(CASE WHEN c.Claim_Type = 'ER'
        THEN 1 ELSE 0 END) AS ER_Visits,
    SUM(CASE WHEN c.Claim_Type = 'Inpatient'
        THEN 1 ELSE 0 END) AS Inpatient_Visits,
    COUNT(DISTINCT m.Member_ID) AS Member_Count
FROM Members m
JOIN Claims c ON m.Member_ID = c.Member_ID
WHERE c.Claim_Type IN ('ER', 'Inpatient')
GROUP BY m.Chronic_Conditions
ORDER BY ER_Visits DESC;

What does this query do?

  • Aggregates ER and inpatient visits by each chronic condition, revealing which conditions drive the most acute utilization.
  • COUNT(DISTINCT m.Member_ID) shows how many unique members contribute to each condition's utilization, so you can distinguish between many members with a few visits versus a few members with many visits.
  • Check your results. Conditions like diabetes and COPD should show high ER visit counts. This data feeds the utilization bar chart in your population health dashboard.

Getting unexpected results?

  • If your high-risk member query returns zero rows, check that your Members table has a Risk_Score column and that some values are at or above 2.5.
  • If ER_Visits or Inpatient_Visits show unexpectedly large numbers, check whether the LEFT JOIN is creating duplicate rows. Adding COUNT(DISTINCT c.Claim_ID) instead of a SUM(CASE...) can help if you have a Claim_ID column.

Need help? Help me debug my population health SQL queries.

Capture the SQL code snippet for your portfolio

The high-risk member identification query is the one you will showcase on your Canva portfolio site. You need a clean, visually appealing image of the query with syntax highlighting on a dark background.

  • Copy your high-risk member identification query (the one that joins Members, Claims, and Care_Gaps).
  • Open ChatGPT or a similar AI tool and paste the following prompt, replacing the placeholder with your actual query:
Create an SVG image of a SQL code snippet for a professional portfolio.

Requirements:
- Dark background (dark navy #1e1e2e or charcoal #282c34)
- Syntax highlighting: blue for SQL keywords, green for strings,
  gray for comments, white for identifiers and operators
- Subtle rounded corners and a thin border
- Size: approximately 400x250 pixels (1/6 of a standard page)
- Clean monospace font

Here is the SQL code to display:

[Paste your high-risk member identification query here]

Why SVG format?

SVG images scale perfectly at any screen size without becoming blurry. This matters for a portfolio that will be viewed on phones, laptops, and large monitors. The 1/6 page size keeps the snippet compact so it does not dominate your Canva layout.

  • Save the generated SVG file to your local machine.
  • Open the SVG in your browser to confirm the syntax highlighting is clear and the text is readable at the intended thumbnail size.

Your SQL analysis is complete. You now have the data patterns that will power both dashboards: PA turnaround bottlenecks, nurse workload imbalances, denial root causes, care gap closure rates, and a high-risk member profile. Next up, you will turn these findings into visual stories that a hospital executive can absorb in five minutes.

Build Power BI Dashboards

Your SQL queries surfaced the patterns hiding in the raw data. Turnaround bottlenecks, denial root causes, nurse overload, high-risk members with open care gaps. Now it is time to turn those patterns into visuals that a healthcare executive can absorb in under five minutes.

In this step, you will build two Power BI dashboards. The first covers Utilization Management: prior authorization turnaround, denial reasons, and nurse caseloads. The second covers Population Health and Quality: care gap closure, high-risk members, and ER/inpatient utilization.

In this step, get ready to:
  • Build the Utilization Management dashboard with KPI cards, charts, and slicers.
  • Build the Population Health and Quality dashboard with KPI cards, a high-risk member table, and slicers.
  • Capture high-resolution screenshots of both dashboards for your portfolio.
Build the Utilization Management dashboard

Before you can build visuals, Power BI needs your data. You will load the five CSV files, apply a custom healthcare blue theme, create the DAX measures that power your KPI cards, and then assemble the visuals.

Using a Fiverr expert for dashboards?

If you plan to hand the dashboard build off to a Fiverr Power BI expert, complete the theme and data-loading steps below, then provide the expert with your five CSV files, the dashboard specifications from this step, and the healthcare-blue-theme.json file. Skip ahead to the screenshot substep once you receive the completed .pbix file.

  • Load your CSV data into Power BI by clicking Get Data on the Home ribbon.
  • Select Text/CSV from the list.
  • Browse to your first CSV file (Members.csv) and click Open.
  • In the preview dialog, click Load to import the data.
  • Repeat for Nurse_Reviewers.csv, Authorization_Requests.csv, Care_Gaps.csv, and Claims.csv.

You should now see all five tables listed in the Fields pane on the right side of the screen.

Don't see all five tables?

Make sure you clicked Load (not Transform Data) in the preview dialog. If a table is missing, go to Get Data again and import the missing CSV.

Help me load CSV data into Power BI Desktop

Now create and apply the healthcare blue theme so both dashboards share a consistent, professional palette.

  • Open a text editor (Notepad or VS Code) and create a new file called healthcare-blue-theme.json.
  • Paste the following theme definition into the file:
{
    "name": "Healthcare Blue",
    "dataColors": [
        "#0078D4",
        "#00B7C3",
        "#005A9E",
        "#50E6FF",
        "#002050",
        "#78D6F0"
    ],
    "background": "#FFFFFF",
    "foreground": "#002050",
    "tableAccent": "#0078D4"
}

What does this theme file do?

The dataColors array sets the colors Power BI cycles through for chart series. The foreground and tableAccent values control text and accent colors across all visuals. This palette uses shades of blue commonly associated with healthcare brands.

  • Save the file to a location you can find easily (your Desktop works).
  • In Power BI Desktop, click the View ribbon and toggle on the Theme pane.
  • Expand Theme settings in the Theme pane.
  • Click Import theme and select your healthcare-blue-theme.json file.

Power BI shows a confirmation dialog when the import succeeds. Your canvas background stays white, but every new visual you add will use the healthcare blue palette.

Next, you need DAX measures for the KPI cards. These calculated values do not exist as raw columns in your data.

  • In the Fields pane, right-click the Authorization_Requests table.
  • Select New measure from the context menu.
  • In the formula bar at the top of the canvas, paste this DAX formula and press Enter:
Avg Turnaround Days =
AVERAGE(
    DATEDIFF(
        Authorization_Requests[Submit_Date],
        Authorization_Requests[Decision_Date],
        DAY
    )
)

What does this measure calculate?

DATEDIFF counts the calendar days between each request's Submit_Date and Decision_Date. AVERAGE then gives you the mean turnaround across all requests. This single number tells leadership how fast the plan resolves PAs on average.

You should see Avg Turnaround Days appear under the Authorization_Requests table in the Fields pane.

  • Right-click Authorization_Requests again and select New measure. Paste this formula for the compliance rate:
Compliance Rate =
VAR _Total = COUNTROWS(Authorization_Requests)
VAR _Compliant = COUNTROWS(
    FILTER(
        Authorization_Requests,
        (Authorization_Requests[Urgency] = "Standard"
            && DATEDIFF(Authorization_Requests[Submit_Date],
                Authorization_Requests[Decision_Date], DAY) <= 7)
        || (Authorization_Requests[Urgency] = "Expedited"
            && DATEDIFF(Authorization_Requests[Submit_Date],
                Authorization_Requests[Decision_Date], DAY) <= 3)
    )
)
RETURN DIVIDE(_Compliant, _Total)

Where do the 7-day and 3-day thresholds come from?

Under CMS-0057-F (effective January 1, 2026), health plans must decide standard prior authorizations within 7 calendar days and expedited PAs within 72 hours. The measure uses 3 days as the day-level equivalent of 72 hours, which is a reasonable approximation for synthetic data at calendar-day granularity.

  • Create one more measure on the same table for denial rate:
Denial Rate =
DIVIDE(
    COUNTROWS(
        FILTER(
            Authorization_Requests,
            Authorization_Requests[Decision] = "Denied"
        )
    ),
    COUNTROWS(Authorization_Requests)
)

You now have three custom measures. Time to verify they work before building the full dashboard.

  • Click on a blank area of the report canvas.
  • In the Visualizations pane on the right, click the Card visual (the icon that looks like a single number).
  • Drag Avg Turnaround Days from the Fields pane into the card's Fields well.

You should see a single number appear on the canvas representing the average turnaround in days. This confirms your measure is working.

  • Create three more Card visuals on the canvas for Total PA Requests (use the count of Request_ID), Compliance Rate, and Denial Rate.
  • Arrange all four KPI cards in a row across the top of the canvas.

Format percentage measures

If Compliance Rate or Denial Rate shows a decimal instead of a percentage, click the measure in the Fields pane. In the Measure tools tab that appears, change the Format dropdown to Percentage.

Now add the three charts below the KPI cards.

  • Click a blank area of the canvas and select the Clustered bar chart visual from the Visualizations pane.
  • Drag Service_Type to the Y-axis well and Avg Turnaround Days to the X-axis well.

You should see a horizontal bar chart comparing average turnaround time across different service types (imaging, surgery, medication, etc.).

  • Add a Donut chart visual. Drag Denial_Reason to the Legend well and the count of Request_ID to the Values well.
  • Add another Clustered bar chart visual. Drag the nurse name field from Nurse_Reviewers to the Y-axis well and the count of cases to the X-axis well. This shows nurse caseload distribution.

Finally, add the slicers that let viewers filter the entire page.

  • Click a blank area and select the Slicer visual from the Visualizations pane.
  • Drag Line_of_Business from Authorization_Requests into the slicer's Field well.
  • Create a second slicer for Urgency and a third slicer for Submit_Date (this one will become a date range slicer).
  • Position the slicers along the top or side of the dashboard where they do not overlap the charts.

Click one of the slicer values. All charts and KPI cards should update to reflect the filtered selection. This cross-filtering is what makes dashboards powerful for executives.

  • Rename this page by double-clicking the page tab at the bottom and typing Utilization Management.
  • Save the file as HealthPlanAnalytics.pbix by pressing Ctrl+S.

Charts showing blank or zero?

Check that your column names in the DAX formulas match the actual column headers in your CSV data. Column names are case-sensitive in DAX. Open the Data view (left sidebar icons) to inspect the table and confirm the exact column names.

Help me fix blank visuals in my Power BI dashboard

Build the Population Health and Quality dashboard

The second dashboard focuses on the other side of the health plan's operations: HEDIS/STAR quality measures and population health management.

  • At the bottom of the Power BI canvas, click the + icon next to the Utilization Management tab to add a new page.
  • Double-click the new page tab and rename it Population Health.

The healthcare blue theme you imported earlier applies automatically to this new page. Now create the DAX measures this dashboard needs.

  • Right-click the Care_Gaps table in the Fields pane and select New measure. Paste this formula:
Care Gap Closure Rate =
DIVIDE(
    COUNTROWS(
        FILTER(Care_Gaps, Care_Gaps[Status] = "Closed")
    ),
    COUNTROWS(Care_Gaps)
)
  • Create another measure on the same table:
Open Gaps Remaining =
COUNTROWS(
    FILTER(Care_Gaps, Care_Gaps[Status] = "Open")
)
  • Right-click the Members table and create this measure:
High-Risk Members =
COUNTROWS(
    FILTER(Members, Members[Risk_Score] >= 3.0)
)

Why 3.0 as the risk threshold?

A risk score of 3.0 or above typically indicates a member with multiple chronic conditions and high predicted cost. This threshold aligns with the high-risk member identification query you wrote in the previous step. Adjust this value if your synthetic data uses a different scale.

Verify the measures work by adding a quick Card visual. Drag Care Gap Closure Rate into a new Card. You should see a decimal or percentage value appear.

  • Build the four KPI cards across the top: Total Members (count of Member_ID), High-Risk Members, Care Gap Closure Rate, and Open Gaps Remaining.

Now add the charts and table below the KPI row.

  • Add a Clustered bar chart. Drag Measure_Name to the Y-axis and Care Gap Closure Rate to the X-axis. This shows closure rate by individual HEDIS measure.
  • Add a Table visual. Drag these fields into the Columns well: Member_ID, Age, Risk_Score, and Plan_Type from the Members table. This is your high-risk member list.
  • Add another Clustered bar chart showing ER and inpatient utilization by chronic condition. Drag the chronic condition field to the Y-axis and the count of ER/inpatient claims to the X-axis.
  • Add three slicers for Plan_Type, County, and a Risk Score Tier field.
  • Save the file by pressing Ctrl+S.

Click through the slicers on this page. Each chart, table, and KPI card should update to reflect the selected filter. If a slicer selects a single county, the care gap closure rate should shift to reflect just that county's members.

Why two separate dashboard pages?

Utilization Management and Population Health serve different stakeholders with different questions. A VP of Medical Affairs cares about PA turnaround and denial patterns. A VP of Quality cares about HEDIS scores and high-risk member outreach. Separating these into distinct pages keeps each audience focused on their priorities.

Capture dashboard screenshots

Your Canva portfolio site will display static images of these dashboards (with links to the interactive versions on NovyPro). Crisp, high-resolution screenshots are essential for a professional portfolio.

  • Open Settings on your Windows machine by pressing the Windows key and typing Settings.
  • Go to System, then Display.
  • Under Scale, set the display scaling to 200%.

Why 200% scaling?

Screenshots captured at 200% scaling produce images with double the pixel density. When displayed at 1/3 page size on your Canva site, the extra resolution keeps text and chart labels sharp and readable. Screenshots at 100% scaling look blurry when embedded in a portfolio.

  • Switch back to Power BI Desktop and navigate to the Utilization Management page tab.
  • Open the Windows Snipping Tool by pressing the Windows key and typing Snipping Tool.
  • Click New and select the full dashboard area on the canvas.
  • Save the screenshot as Dashboard1_UtilizationManagement.png.
  • Switch to the Population Health page tab and repeat the process.
  • Save the second screenshot as Dashboard2_PopulationHealth.png.

Before you move on, take a moment to predict what you expect to see when you open one of these PNG files. Do you think the text on the KPI cards will be readable at a glance?

  • Open Dashboard1_UtilizationManagement.png in your image viewer to confirm it is crisp and readable.
  • Open Dashboard2_PopulationHealth.png to confirm the same.

Both images should show sharp text on KPI cards, clear bar and donut chart labels, and fully visible slicers. These are your 1/3 page portfolio visuals.

Screenshots look blurry?

Verify that your display scaling is set to 200% in Windows Settings. If you changed the scaling while Power BI was open, close and reopen Power BI Desktop so the new scaling takes effect. Then retake the screenshots.

Help me capture crisp Power BI screenshots

  • If you changed your display scaling from its original setting, set it back to your preferred value now.
  • Save your .pbix file one final time by pressing Ctrl+S.

Your dashboards are built, themed, and captured. Next up, you will assemble all of your deliverables into a polished Canva portfolio site and publish the interactive dashboards on NovyPro.

Assemble and Publish Your Portfolio

Your dashboards are built, your SQL analysis is complete, and you have crisp screenshots ready to go. Right now, though, these deliverables live as scattered files on your machine. No hiring manager will ever see them there.

In this step, you will create your remaining BA deliverables, assemble everything into a polished Canva portfolio site, and publish interactive dashboards on NovyPro so recruiters can filter and click through your work live.

In this step, get ready to:
  • Create your BA deliverable visuals (BRD infographic and process map).
  • Build and publish your Canva portfolio site with all deliverables.
  • Publish interactive dashboards to NovyPro and link them to your portfolio.
Create your BA deliverable visuals

You have two remaining visuals to produce before assembling the portfolio. A BRD (Business Requirements Document) infographic thumbnail summarizes the scope of your requirements work. An As-Is / To-Be process map shows what you are recommending the insurer change.

  • Generate the BRD infographic by pasting the following prompt into ChatGPT (or a similar AI tool):
Create an SVG infographic thumbnail for a Business Requirements Document (BRD).
This is for a healthcare analytics project at a mid-sized health insurer.
Use a compact layout sized approximately 400x300 pixels.

Include these sections as visual blocks:
- Project Scope: Prior Authorization Optimization, HEDIS/STAR Reporting,
  Nurse Workflow Efficiency, Population Health Management
- Key Stakeholders: Medical Director, VP of Operations, Nursing Manager,
  IT Director
- Compliance: CMS-0057-F (PA turnaround standards)
- Deliverables: Executive Dashboards, Process Maps, SQL Analysis,
  Stakeholder Recommendations

Use this color palette: dark navy for headers, teal for section backgrounds,
light blue for accents, white text on dark backgrounds.

Keep text minimal. Use icons or shapes to represent each section.
The overall look should be clean and professional.

Output the raw SVG code only.

What does this prompt do?

The prompt asks the AI to produce a compact SVG graphic that visually summarizes your project's requirements scope. It covers the four operational areas, key stakeholders, compliance context, and deliverables in a single thumbnail-sized image.

  • Copy the SVG code from the AI response and save it as a file called brd-infographic.svg on your Desktop.
  • Open the file in your browser to confirm it displays a clean, compact infographic with all four sections visible.

Iterate if needed

If the output does not look right, ask the AI to adjust specific sections. You can request different sizing, a different layout arrangement, or bolder section dividers. Two or three rounds of refinement usually produces a polished result.

Now create the As-Is / To-Be process map. This is a single diagram with the current manual workflow on the left and the improved automated workflow on the right.

  • Switch to your Lucidchart or draw.io tab in your browser.
  • Create a new blank diagram.
  • Add a vertical divider or label down the center. Title the left half As-Is (Current State) and the right half To-Be (Future State).

On the left side, map the current manual flow for both prior authorization and care gap processes:

  • Provider submits PA request via fax or portal.
  • Request enters an unstructured queue with no load balancing.
  • Nurse manually picks up the next case and looks up clinical criteria.
  • Nurse reviews and makes a decision (long turnaround time).
  • Manual notification sent to provider.
  • For care gaps: manual chart review, phone-based member outreach, paper-based tracking of gap closure.

On the right side, map the improved automated flow:

  • Provider submits PA electronically through a standardized system.
  • Auto-approval engine handles low-risk requests instantly.
  • Remaining requests routed to nurses via balanced assignment algorithm.
  • Integrated clinical decision support at point of review.
  • Decisions within CMS turnaround targets (7 days standard, 72 hours expedited).
  • Automated provider notification.
  • For care gaps: automated gap identification from claims data, risk-stratified digital outreach, real-time tracking dashboard.
  • Connect each step with arrows showing the process flow on both sides.
  • Export the finished diagram as an SVG file. In Lucidchart, click File then Export then SVG. In draw.io, click File then Export as then SVG.
  • Save the exported file as process-map.svg on your Desktop.

Open process-map.svg in your browser. You should see both the As-Is and To-Be flows side by side with clear labels and connected steps.

Process map looks cramped or overlapping?

Widen your diagram canvas before exporting. In Lucidchart, drag the edges of the canvas. In draw.io, the canvas auto-expands as you move shapes outward. Make sure each process step box has enough padding that text does not overflow.

Help me lay out my As-Is / To-Be process map so both sides are readable.

Build the Canva site

Before building the site, you need a shareable download link for your .pbix file so visitors can explore your dashboards locally.

  • Upload your .pbix file to GitHub or Google Drive.
  • If using GitHub: create a new public repository, upload the file, and copy the link to the file page.
  • If using Google Drive: upload the file, right-click it, select Share, change access to Anyone with the link, and copy the link.
  • Open the link in a new browser tab to confirm the file is accessible and downloadable.

Now assemble the portfolio site. Canva's free plan lets you publish up to 5 sites on a my.canva.site domain.

  • In your browser, go to canva.com and sign into your existing Canva account.
  • On the Canva homepage, search for Website in the search bar.
  • Select Create a blank Website to start with an empty single-page layout.

Why a single-page site?

A one-page scrolling layout lets a recruiter see your entire case study without navigating between pages. Each section of your portfolio becomes a scroll stop on the same page.

Add the narrative text sections first. Use Canva's text and heading tools to create the following sections in order, scrolling down the page. Click Add section at the bottom of the current section to extend the page as you go.

  • Add a Project Title heading at the top (e.g. "Healthcare Utilization Management & Population Health Analytics").
  • Add a Context section describing the mid-sized health insurer scenario and the operational areas you analyzed.
  • Add a Business Problem section listing the four problem areas: long PA turnaround times, inconsistent HEDIS/STAR reporting, overwhelmed nurse case managers, and limited visibility into high-risk populations.
  • Add a How Problems Were Identified section describing stakeholder interviews, data analysis, and process observation.
  • Add an Approach section summarizing the BA lifecycle you followed: requirements gathering, data analysis, dashboard design, and recommendations.

Preview the page so far by clicking the Preview button in the top menu. You should see all five text sections stacked in a scrollable layout.

Now add your visual deliverables. Upload each file and place it in the correct position on the page.

  • Click Uploads in the left sidebar and upload brd-infographic.svg, process-map.svg, and your SQL code snippet SVG file.
  • Drag the BRD infographic onto the page below the Approach section. Size it to approximately 1/6 of the page width for a compact thumbnail.
  • Drag the As-Is / To-Be process map below the BRD infographic.
  • Drag the SQL code snippet below the process map. Size it to approximately 1/6 of the page width.
  • Upload Dashboard1_UtilizationManagement.png and Dashboard2_PopulationHealth.png.
  • Drag Dashboard 1 below the SQL snippet. Size it to approximately 1/3 of the page width.
  • Drag Dashboard 2 below Dashboard 1. Size it to approximately 1/3 of the page width.

Finally, add the closing text sections below the dashboard screenshots.

  • Add a Key Deliverables section listing: BRD, As-Is / To-Be process map, SQL analysis, Utilization Management dashboard, Population Health dashboard.
  • Add a Skills Demonstrated section listing: healthcare data analysis, SQL, Power BI, process mapping, requirements documentation, CMS compliance, population health analytics.
  • Add a text element with a download link to your hosted .pbix file. Select the text, click the chain/link icon in the toolbar, and paste your GitHub or Google Drive download URL.

Click Preview again. You should see a complete scrolling portfolio with all text sections, all visual assets, and the download link at the bottom.

Images not displaying in preview?

Make sure your SVG files uploaded successfully. If an SVG appears blank, try re-exporting it from Lucidchart or draw.io. Some SVG files with embedded fonts may not render properly. In that case, export as PNG instead and re-upload.

Help me fix images that are not displaying on my Canva site.

Do not publish the Canva site yet. You will add the NovyPro dashboard link in the next substep and then publish everything together.

Publish dashboards to NovyPro

NovyPro hosts interactive Power BI dashboards that visitors can filter and explore without needing a Power BI account. Publishing requires two stages: first push from Power BI Desktop to the Power BI Service, then embed the public link in NovyPro.

  • Switch back to Power BI Desktop with your .pbix file open.
  • Make sure you are signed in with your NovyPro-provided Power BI credentials (the ones from Step 1).
  • Click Publish on the Home tab in the top ribbon.
  • Select your workspace when prompted, then click Select.
  • Wait for the publish confirmation. Click the link to open your report in Power BI Service.

What is Power BI Service?

Power BI Service is the cloud-hosted version of Power BI that runs in your browser at app.powerbi.com. Publishing pushes your local .pbix file to the cloud so it can be embedded and shared publicly.

Now generate the public embed link from Power BI Service.

  • In Power BI Service (the browser tab that opened after publishing), click File in the top menu.
  • Hover over Embed report.
  • Select Publish to web (public).
  • Click Continue on the confirmation dialog, then click Publish.
  • Copy the first link (the one for email sharing). This is the public embed URL you will paste into NovyPro.

Publish to web option is greyed out?

This option requires NovyPro-provided credentials that have Publish to web enabled. If you are signed in with a different Microsoft account, sign out and sign back in with the credentials from your NovyPro email.

Help me enable the Publish to web option in Power BI Service.

Now create your NovyPro project with the embed link.

  • In your browser, go to novypro.com and sign into your existing NovyPro account.
  • Click the + icon near your profile at the top of the page.
  • Select Power BI as the tool.
  • Paste the embed link you copied from Power BI Service into the Report Link field.
  • Enter a descriptive title (e.g. "Healthcare UM & Population Health Dashboards").
  • Upload one of your dashboard screenshots as the Cover Photo.
  • Confirm all required fields show green status indicators, then click Publish.
  • Toggle the project status to On if prompted.

Copy the URL of your published NovyPro project page. Open it in a new tab and confirm the dashboards load with interactive slicers and filters.

Now add the NovyPro link to your Canva site.

  • Switch back to your Canva site editor tab.
  • Below each dashboard screenshot, add a text element that says "View interactive dashboard on NovyPro" (or similar).
  • Select the text, click the chain/link icon in the toolbar, and paste your NovyPro project URL.

Now publish the Canva site.

  • Click Publish website in the top-right corner of the Canva editor.
  • Review the Site address (URL) and customize the subpath to something professional (e.g. healthcare-ba-portfolio).
  • Click Settings and turn on Resize on mobile so the site looks good on phones.
  • Click Continue to publish, then click Publish.

Canva will generate your live URL on the free my.canva.site domain. Copy this URL.

Before you check, do you think all the links and visuals will work correctly on the first try? What might be missing?

  • Open a new incognito browser window (Ctrl+Shift+N on Windows, Cmd+Shift+N on macOS).
  • Paste your Canva site URL and press Enter.
  • Scroll through the entire page. All visuals (BRD infographic, process map, SQL snippet, dashboard screenshots) should load clearly.
  • Click the NovyPro link. It should open the interactive dashboards where you can click slicers and filter data.
  • Click the .pbix download link. The file should begin downloading.

If all three checks pass, your portfolio is live. This is the URL you will add to your resume and LinkedIn profile.

NovyPro link not loading dashboards?

Make sure you toggled the project status to On in NovyPro. Also confirm you pasted the Publish to web link (the first link from Power BI Service), not the iframe embed code.

Help me troubleshoot my NovyPro dashboard link.

Secret mission

Create a Stakeholder Recommendations Deck

Senior BAs present findings and drive decisions. In this secret mission, you'll create a 3-5 slide executive recommendations deck that translates your dashboard data into specific, quantified action items for health plan leadership. This is the deliverable that separates analysts from advisors.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely on free tools and services, so there are no ongoing costs to worry about. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • SQL database: HealthPlanAnalytics with 5 tables (Members, Nurse_Reviewers, Authorization_Requests, Care_Gaps, Claims).
  • Local files: 5 CSV data files, dashboard screenshots (PNG), SVG graphics (BRD infographic, SQL code snippet, process map), .pbix file, PowerPoint deck, and PDF export.
  • Power BI Service: published report and semantic model in your workspace.
  • NovyPro: published dashboard project with public embed link.
  • Canva: published portfolio site.
  • Lucidchart or draw.io: As-Is / To-Be process map diagram.

Keep everything running

No action needed. This is a portfolio project designed to stay live for hiring managers and recruiters to view. Choose this option if you are actively job searching or plan to share the Canva site link on your resume and LinkedIn.

  • Your Canva portfolio site remains publicly accessible.
  • Your NovyPro dashboards stay interactive for anyone with the link.
  • Your local .pbix file and SQL database remain on your machine for future updates.
  • Your Lucidchart or draw.io process map remains in your account for editing.

Pause - I'll come back to this later

Take your portfolio offline temporarily while keeping all local files so you can republish later.

  • Unpublish your Canva site: open the site design in Canva, select the arrow next to Publish Changes, select Settings, then select Unpublish. Confirm by selecting Unpublish website.
  • In NovyPro, click your profile picture at the top right, select My Reports, and use the edit options next to your project to remove it from your public portfolio.
  • In Power BI Service, open your workspace, select More options (...) next to your published report, and choose Delete. Your local .pbix file is unaffected.
  • Keep all local files (CSVs, screenshots, SVGs, .pbix, PowerPoint, PDF) on your machine for when you are ready to republish.
  • Keep your HealthPlanAnalytics database intact so your queries are ready to re-run.

Delete - I don't want to use this again

Remove all project resources completely. Follow each step below.

  • Unpublish and delete your Canva site: open the site design in the Canva editor, select the arrow next to Publish Changes, select Settings, then select Unpublish. Confirm with Unpublish website. Then return to the Canva homepage, hover over the design thumbnail, click the three-dot menu, and select Move to trash.
  • Remove your NovyPro project: click your profile picture at the top right, select My Reports, click the arrow near Edit, then select the pencil icon next to the project you want to remove.
  • Delete your Power BI Service report: open your workspace in Power BI Service, select More options (...) next to the report, choose Delete, and confirm.
  • Delete your Lucidchart or draw.io process map: open the diagram in your account and delete it from your files list.
  • Drop your SQL database by running this command in your SQL environment:
DROP DATABASE HealthPlanAnalytics;
  • Delete your local project files (CSVs, screenshots, SVGs, .pbix, PowerPoint, and PDF). Move them to your Recycle Bin and empty it.
  • If you uploaded your .pbix file to GitHub or Google Drive for the download link, delete it from that service as well.

Nice Work!

Nice Work!

You just built a complete Senior Business Analyst portfolio case study for a healthcare payer. This is a shareable, interactive body of work that proves you can identify operational problems, analyze healthcare data, and deliver actionable insights.

You learned how to:

  • Generate realistic synthetic healthcare data across five relational tables and write SQL queries to surface prior authorization bottlenecks, denial root causes, nurse caseload imbalances, and high-risk member populations.
  • Build two executive Power BI dashboards covering Utilization Management and Population Health, apply a professional healthcare blue theme, and publish them as interactive dashboards on NovyPro.
  • Assemble a shareable portfolio site on Canva with BA deliverables (BRD infographic, As-Is / To-Be process map, SQL code snippet, dashboard screenshots) and live interactive dashboard links.
  • Secret Mission: Completed an optional challenge to push your portfolio and presentation skills even further.

Ready to quiz yourself?