Build an Insurance BI Control Center

Build a reconciled insurance portfolio dashboard with SQL Server and Power BI.

Introduction

30 Second Summary

A polished dashboard can win trust in seconds. If its numbers count the same claim twice, that trust can steer an expensive decision.

In this project, you will build a local insurance brokerage control center backed by Microsoft SQL Server. You will turn its reconciled data into an interactive Power BI dashboard plus an executive evidence pack.

What You'll Build

Filter your finished control center by branch or product to see performance metrics beside proof that every claim reconciles to its source.

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

  • An interactive Executive Portfolio where you can compare written premium, commission revenue, renewals, loss ratio, claim frequency, trends, branches, and products.
  • A Claims and BI Reliability page that shows deduplicated claims beside refresh status, data freshness, and zero-variance reconciliation controls.
  • A portfolio-ready evidence pack that supports each dashboard conclusion with statistical results, KPI definitions, briefing slides, and an executive update.
  • Secret Mission: answer a last-minute branch-risk request by ranking the top two qualifying branches without rebuilding the governed model.

Are there any prerequisites?

You need intermediate SQL and Power BI skills on a Windows computer with administrator rights and at least 4.2 GB of free disk space.

Microsoft Excel needs Analysis ToolPak support, with an included fallback when RANDARRAY is unavailable.

Before We Start

This step is your moment to define the insurance brokerage control center before any tools are configured. Your objective will connect accurate premium, claim, and refresh data to the decisions made by brokerage executives and operational leaders.

Set Up the Local BI Lab

Your documented control-center objective now needs a dependable relational source. Microsoft Power BI Desktop relies on that source to trace each portfolio result back to governed data.

Microsoft SQL Server 2025 Express stores the insurance brokerage data locally. SQL Server Management Studio gives you the workspace for building the database. You will use Express version 17.0.1000.9 with SSMS version 22.11.0.

In this step, get ready to:
  • Install SQL Server 2025 Express as the local database engine.
  • Install SSMS 22 with a trusted local connection.
  • Build the seeded InsuranceBrokerBI database with validated row counts.
Install SQL Server 2025 Express

The database engine holds the source tables behind your control center. Its named instance gives Power BI a consistent local address later in the project.

Why SQL Server Express?

SQL Server Express is free for learning plus small local applications. It supports the T-SQL workflow required by this project.

Express keeps this portfolio focused on data modeling plus reporting. The database stays within the free local lab.

Choose the tab that matches the SQL Server state on your computer. Each path finishes with version 17.0.1000.9 running as the named instance SQLEXPRESS.

✔️ I have version 17.0.1000.9

Your database engine is already at the project version. Confirm that its named instance is SQLEXPRESS before continuing.

  • Keep the existing SQL Server 2025 Express installation.

ⓧ I have an older version

Removing an older instance can remove its local databases. Back up any database you need before continuing.

  • Open the official SQL Server removal guide.
  • Open Settings from the Windows Start menu.
  • Select Apps.
  • Search for sql.
  • Select the older Microsoft SQL Server installation.
  • Select Uninstall.
  • Select Remove to launch the SQL Server installation wizard.
  • Remove the old SQLEXPRESS instance by following the wizard.

The old instance is now clear of the required name. You can install the project version without an instance-name conflict.

  • Download SQL2025-SSEI-Expr.exe from the official SQL Server 2025 Express page.
  • Run SQL2025-SSEI-Expr.exe with administrator permissions.
  • Select the Database Engine feature in setup.
  • Keep SQLEXPRESS as the named instance.
  • Select Windows Authentication.
  • Complete the SQL Server installation.

ⓧ SQL Server is not installed

The setup wizard asks for the database feature plus the instance name. It also asks for an authentication mode.

  • Download SQL2025-SSEI-Expr.exe from the official SQL Server 2025 Express page.
  • Run SQL2025-SSEI-Expr.exe with administrator permissions.
  • Select the Database Engine feature in setup.
  • Keep SQLEXPRESS as the named instance.
  • Select Windows Authentication.
  • Complete the SQL Server installation.

Expect setup to take several minutes while Windows installs the database engine. The completed setup confirms that the local instance is ready.

That establishes the storage layer for your portfolio. The named instance is now available at localhost\SQLEXPRESS.

SQL Server setup did not finish?

  • Confirm that you ran SQL2025-SSEI-Expr.exe with administrator permissions.
  • Confirm that Windows has at least 4.2 GB of free disk space.
  • Confirm that your existing SQLEXPRESS instance was removed before reusing that name.

Still blocked? Help me diagnose my SQL Server 2025 Express installation failure.

Install SSMS and connect locally

The database engine runs in the background. SSMS gives you an editor plus a connected server tree for managing it.

Choose the SSMS state that matches your computer. The installer uses vs_SSMS.exe to open Visual Studio Installer.

✔️ I have SSMS 22.11.0

Your management tool already matches the project version. Continue to the connection instructions below.

ⓧ I have an older SSMS version

  • Open the official SSMS installation guide.
  • Download vs_SSMS.exe.
  • Run vs_SSMS.exe with administrator permissions.
  • Complete the installation through Visual Studio Installer.
  • Confirm 22.11.0 in the SSMS about dialog.

The installer can take several minutes while it updates the management components. Keep Visual Studio Installer open until it confirms completion.

ⓧ SSMS is not installed

  • Open the official SSMS installation guide.
  • Download vs_SSMS.exe.
  • Run vs_SSMS.exe with administrator permissions.
  • Complete the installation through Visual Studio Installer.
  • Confirm 22.11.0 in the SSMS about dialog.

The installer can take several minutes while it adds the management components. Keep Visual Studio Installer open until it confirms completion.

SSMS needs the server name plus the authentication method before it can manage your local instance. The first connection keeps certificate validation enabled.

  • Press the Windows key to open Windows search.
  • Type SQL Server Management Studio 22 into Windows search.
  • Press Enter to open SSMS.
  • Enter localhost\SQLEXPRESS in Server name.
  • Select Windows Authentication in Authentication.
  • Keep Mandatory in Encryption.
  • Keep Trust server certificate unchecked for the first attempt.
  • Select Connect.

A successful connection shows localhost\SQLEXPRESS at the top of the connected server tree. Your Windows account now manages the local engine.

Certificate blocked the local connection?

A local self-signed certificate can block the first connection. Trusting this named local server preserves mandatory encryption for the connection.

  • Return to the SSMS connection settings.
  • Check Trust server certificate.
  • Select Connect again.

Still blocked? Help me troubleshoot my encrypted SSMS connection to localhost\SQLEXPRESS.

Build and verify InsuranceBrokerBI

The supplied script creates a local star schema for policy plus claim analysis. It also creates the audit objects that make later refreshes traceable.

  • Drag the supplied insurance_bi.sql file into the open SSMS editor.
  • Confirm that the editor is connected to localhost\SQLEXPRESS.
  • Execute insurance_bi.sql by pressing F5.

The script creates InsuranceBrokerBI plus the five schemas. The final result sets report the seeded policy count plus the claim controls.

What did the script build?

  • The stg schema holds the duplicated claim-event feed.
  • The dw schema holds the dimensions plus policy and claim facts.
  • The etl schema holds the repeatable claim-mart refresh procedure.
  • The audit schema records refresh outcomes.
  • The rpt schema exposes reporting views for Power BI plus Excel.

The last two queries are your source controls. Find this exact block at the bottom of insurance_bi.sql.

SELECT COUNT(*) AS PolicyRows
FROM dw.FactPolicy;

SELECT
    COUNT(*) AS ClaimEventRows,
    COUNT(DISTINCT ClaimNumber) AS DistinctClaims
FROM stg.ClaimFeed;

What do these controls prove?

The first query checks the policy fact at its intended grain. One row represents one policy.

The second query compares event rows with distinct business claim numbers. Their mismatch creates the data-quality problem you investigate next.

Before you run the controls again, do you expect the number of claim events to match the number of distinct claims?

  • Highlight the two control queries at the bottom of insurance_bi.sql.
  • Run the highlighted controls by pressing F5.

You will see 480 under PolicyRows. You will also see 144 under ClaimEventRows plus 96 under DistinctClaims.

That is the local lab working end to end. The database contains the complete seeded source plus the intentional duplicate-event condition.

Did the database script stop?

  • Confirm that the editor connection shows localhost\SQLEXPRESS.
  • Confirm that you executed the complete insurance_bi.sql file from its first line.
  • Remove the partially created InsuranceBrokerBI database before retrying a failed first build.

Need help locating the failed batch? Help me diagnose where insurance_bi.sql stopped in SSMS.

✔️ Awesome, I've got everything!

Your local InsuranceBrokerBI database is seeded. Keep SSMS open with the database available in the connected server tree.

ⓧ I'd like to double check the full code

Compare the supplied insurance_bi.sql file with this complete reference. The final control queries must return the seeded counts shown above.

USE master;
GO

CREATE DATABASE InsuranceBrokerBI;
GO

USE InsuranceBrokerBI;
GO

CREATE SCHEMA stg;
GO
CREATE SCHEMA dw;
GO
CREATE SCHEMA etl;
GO
CREATE SCHEMA audit;
GO
CREATE SCHEMA rpt;
GO

CREATE TABLE dw.DimDate
(
    DateKey int NOT NULL PRIMARY KEY,
    FullDate date NOT NULL UNIQUE,
    YearNumber smallint NOT NULL,
    MonthNumber tinyint NOT NULL,
    MonthName varchar(20) NOT NULL,
    YearMonth char(7) NOT NULL
);

CREATE TABLE dw.DimBranch
(
    BranchKey int NOT NULL PRIMARY KEY,
    BranchName varchar(50) NOT NULL UNIQUE,
    StateCode char(2) NOT NULL
);

CREATE TABLE dw.DimProduct
(
    ProductKey int NOT NULL PRIMARY KEY,
    ProductName varchar(60) NOT NULL UNIQUE,
    ProductGroup varchar(40) NOT NULL
);

CREATE TABLE dw.DimBroker
(
    BrokerKey int NOT NULL PRIMARY KEY,
    BrokerName varchar(60) NOT NULL,
    BrokerChannel varchar(30) NOT NULL
);

CREATE TABLE dw.FactPolicy
(
    PolicyKey int NOT NULL PRIMARY KEY,
    PolicyNumber varchar(12) NOT NULL UNIQUE,
    SaleDateKey int NOT NULL,
    SaleDate date NOT NULL,
    BranchKey int NOT NULL,
    ProductKey int NOT NULL,
    BrokerKey int NOT NULL,
    MemberSegment varchar(30) NOT NULL,
    WrittenPremium decimal(12,2) NOT NULL,
    CommissionAmount decimal(12,2) NOT NULL,
    InsuredAmount decimal(14,2) NOT NULL,
    IsRenewal bit NOT NULL,
    PolicyStatus varchar(20) NOT NULL,
    CONSTRAINT FK_FactPolicy_Date FOREIGN KEY (SaleDateKey) REFERENCES dw.DimDate(DateKey),
    CONSTRAINT FK_FactPolicy_Branch FOREIGN KEY (BranchKey) REFERENCES dw.DimBranch(BranchKey),
    CONSTRAINT FK_FactPolicy_Product FOREIGN KEY (ProductKey) REFERENCES dw.DimProduct(ProductKey),
    CONSTRAINT FK_FactPolicy_Broker FOREIGN KEY (BrokerKey) REFERENCES dw.DimBroker(BrokerKey)
);

CREATE TABLE stg.ClaimFeed
(
    ClaimEventKey int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    ClaimNumber varchar(12) NOT NULL,
    PolicyNumber varchar(12) NOT NULL,
    EventUpdatedAt datetime2(0) NOT NULL,
    ClaimStatus varchar(20) NOT NULL,
    IncurredAmount decimal(12,2) NOT NULL,
    PaidAmount decimal(12,2) NOT NULL,
    OccurrenceDate date NOT NULL,
    SettlementDate date NULL
);

CREATE TABLE dw.FactClaim
(
    ClaimKey int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    SourceClaimEventKey int NOT NULL,
    ClaimNumber varchar(12) NOT NULL UNIQUE,
    PolicyKey int NOT NULL,
    OccurrenceDateKey int NOT NULL,
    OccurrenceDate date NOT NULL,
    SettlementDate date NULL,
    SettlementDays int NULL,
    BranchKey int NOT NULL,
    ProductKey int NOT NULL,
    BrokerKey int NOT NULL,
    IncurredAmount decimal(12,2) NOT NULL,
    PaidAmount decimal(12,2) NOT NULL,
    ClaimStatus varchar(20) NOT NULL,
    CONSTRAINT FK_FactClaim_Date FOREIGN KEY (OccurrenceDateKey) REFERENCES dw.DimDate(DateKey),
    CONSTRAINT FK_FactClaim_Branch FOREIGN KEY (BranchKey) REFERENCES dw.DimBranch(BranchKey),
    CONSTRAINT FK_FactClaim_Product FOREIGN KEY (ProductKey) REFERENCES dw.DimProduct(ProductKey),
    CONSTRAINT FK_FactClaim_Broker FOREIGN KEY (BrokerKey) REFERENCES dw.DimBroker(BrokerKey)
);

CREATE TABLE audit.RefreshRun
(
    RunID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    StartedAt datetime2(0) NOT NULL CONSTRAINT DF_RefreshRun_StartedAt DEFAULT SYSDATETIME(),
    FinishedAt datetime2(0) NULL,
    Status varchar(20) NOT NULL,
    SourceRows int NULL,
    TargetRows int NULL,
    SourcePaidAmount decimal(14,2) NULL,
    TargetPaidAmount decimal(14,2) NULL,
    ErrorMessage nvarchar(2048) NULL
);
GO

WITH Digits AS
(
    SELECT d
    FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS v(d)
),
Numbers AS
(
    SELECT ones.d + (10 * tens.d) + (100 * hundreds.d) AS n
    FROM Digits AS ones
    CROSS JOIN Digits AS tens
    CROSS JOIN Digits AS hundreds
)
INSERT INTO dw.DimDate
(
    DateKey, FullDate, YearNumber, MonthNumber, MonthName, YearMonth
)
SELECT
    CONVERT(int, CONVERT(char(8), DATEADD(day, n, CAST('2025-01-01' AS date)), 112)),
    DATEADD(day, n, CAST('2025-01-01' AS date)),
    YEAR(DATEADD(day, n, CAST('2025-01-01' AS date))),
    MONTH(DATEADD(day, n, CAST('2025-01-01' AS date))),
    DATENAME(month, DATEADD(day, n, CAST('2025-01-01' AS date))),
    CONVERT(char(7), DATEADD(day, n, CAST('2025-01-01' AS date)), 126)
FROM Numbers
WHERE n <= DATEDIFF(day, CAST('2025-01-01' AS date), CAST('2026-12-31' AS date));

INSERT INTO dw.DimBranch (BranchKey, BranchName, StateCode)
VALUES
    (1, 'Curitiba', 'PR'),
    (2, 'Florianopolis', 'SC'),
    (3, 'Porto Alegre', 'RS'),
    (4, 'Sao Paulo', 'SP');

INSERT INTO dw.DimProduct (ProductKey, ProductName, ProductGroup)
VALUES
    (1, 'Professional Liability', 'Professional Protection'),
    (2, 'Life Protection', 'People Protection'),
    (3, 'Auto', 'Property and Casualty'),
    (4, 'Business Property', 'Property and Casualty');

INSERT INTO dw.DimBroker (BrokerKey, BrokerName, BrokerChannel)
VALUES
    (1, 'Ana Costa', 'Branch'),
    (2, 'Bruno Lima', 'Branch'),
    (3, 'Carla Mendes', 'Digital'),
    (4, 'Diego Alves', 'Partner'),
    (5, 'Elisa Rocha', 'Branch'),
    (6, 'Fabio Martins', 'Digital'),
    (7, 'Gabriela Souza', 'Partner'),
    (8, 'Henrique Silva', 'Branch');
GO

WITH Digits AS
(
    SELECT d
    FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS v(d)
),
Numbers AS
(
    SELECT TOP (480)
        ROW_NUMBER() OVER (ORDER BY hundreds.d, tens.d, ones.d) AS n
    FROM Digits AS ones
    CROSS JOIN Digits AS tens
    CROSS JOIN Digits AS hundreds
)
INSERT INTO dw.FactPolicy
(
    PolicyKey, PolicyNumber, SaleDateKey, SaleDate, BranchKey, ProductKey,
    BrokerKey, MemberSegment, WrittenPremium, CommissionAmount,
    InsuredAmount, IsRenewal, PolicyStatus
)
SELECT
    n.n,
    'POL-' + RIGHT('000000' + CAST(n.n AS varchar(6)), 6),
    CONVERT(int, CONVERT(char(8), d.SaleDate, 112)),
    d.SaleDate,
    k.BranchKey,
    k.ProductKey,
    k.BrokerKey,
    CASE n.n % 4
        WHEN 0 THEN 'Physician'
        WHEN 1 THEN 'Dentist'
        WHEN 2 THEN 'Clinic'
        ELSE 'Other Healthcare'
    END,
    p.WrittenPremium,
    CAST(p.WrittenPremium *
        CASE k.ProductKey
            WHEN 1 THEN 0.18
            WHEN 2 THEN 0.14
            WHEN 3 THEN 0.12
            ELSE 0.16
        END AS decimal(12,2)),
    CAST(50000 + ((n.n % 25) * 15000) + (k.ProductKey * 50000) AS decimal(14,2)),
    CASE WHEN n.n % 3 = 0 THEN 1 ELSE 0 END,
    CASE WHEN n.n % 11 = 0 THEN 'Lapsed' ELSE 'Active' END
FROM Numbers AS n
CROSS APPLY
(
    VALUES
    (
        (((n.n - 1) / 4) % 4) + 1,
        ((n.n - 1) % 4) + 1,
        (((n.n - 1) / 16) % 8) + 1
    )
) AS k(BranchKey, ProductKey, BrokerKey)
CROSS APPLY
(
    VALUES (DATEADD(day, (n.n - 1) % 450, CAST('2025-01-01' AS date)))
) AS d(SaleDate)
CROSS APPLY
(
    VALUES (CAST(500 + ((n.n % 17) * 85) + (k.ProductKey * 120) AS decimal(12,2)))
) AS p(WrittenPremium);
GO

INSERT INTO stg.ClaimFeed
(
    ClaimNumber, PolicyNumber, EventUpdatedAt, ClaimStatus,
    IncurredAmount, PaidAmount, OccurrenceDate, SettlementDate
)
SELECT
    'CLM-' + RIGHT('000000' + CAST(p.PolicyKey AS varchar(6)), 6),
    p.PolicyNumber,
    CAST(DATEADD(day, CASE WHEN p.PolicyKey % 10 = 0 THEN 5 ELSE 46 END, x.OccurrenceDate) AS datetime2(0)),
    CASE WHEN p.PolicyKey % 10 = 0 THEN 'Open' ELSE 'Paid' END,
    x.IncurredAmount,
    CAST(x.IncurredAmount * CASE WHEN p.PolicyKey % 10 = 0 THEN 0.35 ELSE 0.88 END AS decimal(12,2)),
    x.OccurrenceDate,
    CASE
        WHEN p.PolicyKey % 10 = 0 THEN NULL
        ELSE DATEADD(day, 35 + (p.PolicyKey % 10), x.OccurrenceDate)
    END
FROM dw.FactPolicy AS p
CROSS APPLY
(
    VALUES
    (
        DATEADD(day, 20 + (p.PolicyKey % 45), p.SaleDate),
        CAST(2000 + ((p.PolicyKey % 19) * 800) + (p.ProductKey * 1500) AS decimal(12,2))
    )
) AS x(OccurrenceDate, IncurredAmount)
WHERE p.PolicyKey % 5 = 0;

INSERT INTO stg.ClaimFeed
(
    ClaimNumber, PolicyNumber, EventUpdatedAt, ClaimStatus,
    IncurredAmount, PaidAmount, OccurrenceDate, SettlementDate
)
SELECT
    'CLM-' + RIGHT('000000' + CAST(p.PolicyKey AS varchar(6)), 6),
    p.PolicyNumber,
    CAST(DATEADD(day, 60, x.OccurrenceDate) AS datetime2(0)),
    'Paid',
    CAST(x.IncurredAmount + 500 AS decimal(12,2)),
    CAST((x.IncurredAmount + 500) * 0.90 AS decimal(12,2)),
    x.OccurrenceDate,
    DATEADD(day, 45, x.OccurrenceDate)
FROM dw.FactPolicy AS p
CROSS APPLY
(
    VALUES
    (
        DATEADD(day, 20 + (p.PolicyKey % 45), p.SaleDate),
        CAST(2000 + ((p.PolicyKey % 19) * 800) + (p.ProductKey * 1500) AS decimal(12,2))
    )
) AS x(OccurrenceDate, IncurredAmount)
WHERE p.PolicyKey % 10 = 0;
GO

CREATE OR ALTER PROCEDURE etl.usp_refresh_claim_mart
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @RunID int;

    INSERT INTO audit.RefreshRun (Status)
    VALUES ('Running');

    SET @RunID = CONVERT(int, SCOPE_IDENTITY());

    BEGIN TRY
        BEGIN TRANSACTION;

        DELETE FROM dw.FactClaim;

        WITH RankedClaims AS
        (
            SELECT
                cf.ClaimEventKey,
                cf.ClaimNumber,
                cf.PolicyNumber,
                cf.ClaimStatus,
                cf.IncurredAmount,
                cf.PaidAmount,
                cf.OccurrenceDate,
                cf.SettlementDate,
                ROW_NUMBER() OVER
                (
                    PARTITION BY cf.ClaimNumber
                    ORDER BY cf.EventUpdatedAt DESC, cf.ClaimEventKey DESC
                ) AS EventRank
            FROM stg.ClaimFeed AS cf
        )
        INSERT INTO dw.FactClaim
        (
            SourceClaimEventKey, ClaimNumber, PolicyKey, OccurrenceDateKey,
            OccurrenceDate, SettlementDate, SettlementDays, BranchKey,
            ProductKey, BrokerKey, IncurredAmount, PaidAmount, ClaimStatus
        )
        SELECT
            r.ClaimEventKey,
            r.ClaimNumber,
            p.PolicyKey,
            CONVERT(int, CONVERT(char(8), r.OccurrenceDate, 112)),
            r.OccurrenceDate,
            r.SettlementDate,
            CASE
                WHEN r.SettlementDate IS NULL THEN NULL
                ELSE DATEDIFF(day, r.OccurrenceDate, r.SettlementDate)
            END,
            p.BranchKey,
            p.ProductKey,
            p.BrokerKey,
            r.IncurredAmount,
            r.PaidAmount,
            r.ClaimStatus
        FROM RankedClaims AS r
        INNER JOIN dw.FactPolicy AS p
            ON p.PolicyNumber = r.PolicyNumber
        WHERE r.EventRank = 1;

        UPDATE audit.RefreshRun
        SET
            FinishedAt = SYSDATETIME(),
            Status = 'Succeeded',
            SourceRows =
            (
                SELECT COUNT(*)
                FROM
                (
                    SELECT ClaimNumber
                    FROM stg.ClaimFeed
                    GROUP BY ClaimNumber
                ) AS DistinctClaims
            ),
            TargetRows = (SELECT COUNT(*) FROM dw.FactClaim),
            SourcePaidAmount =
            (
                SELECT SUM(Latest.PaidAmount)
                FROM
                (
                    SELECT
                        PaidAmount,
                        ROW_NUMBER() OVER
                        (
                            PARTITION BY ClaimNumber
                            ORDER BY EventUpdatedAt DESC, ClaimEventKey DESC
                        ) AS EventRank
                    FROM stg.ClaimFeed
                ) AS Latest
                WHERE Latest.EventRank = 1
            ),
            TargetPaidAmount = (SELECT SUM(PaidAmount) FROM dw.FactClaim)
        WHERE RunID = @RunID;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        UPDATE audit.RefreshRun
        SET
            FinishedAt = SYSDATETIME(),
            Status = 'Failed',
            ErrorMessage = ERROR_MESSAGE()
        WHERE RunID = @RunID;

        THROW;
    END CATCH;
END;
GO

CREATE OR ALTER VIEW rpt.vw_ClaimFeedNaive
AS
SELECT
    cf.ClaimEventKey,
    cf.ClaimNumber,
    cf.PolicyNumber,
    cf.EventUpdatedAt,
    cf.ClaimStatus,
    cf.IncurredAmount,
    cf.PaidAmount,
    cf.OccurrenceDate,
    cf.SettlementDate,
    b.BranchName,
    pr.ProductName,
    br.BrokerName
FROM stg.ClaimFeed AS cf
INNER JOIN dw.FactPolicy AS p
    ON p.PolicyNumber = cf.PolicyNumber
INNER JOIN dw.DimBranch AS b
    ON b.BranchKey = p.BranchKey
INNER JOIN dw.DimProduct AS pr
    ON pr.ProductKey = p.ProductKey
INNER JOIN dw.DimBroker AS br
    ON br.BrokerKey = p.BrokerKey;
GO

CREATE OR ALTER VIEW rpt.vw_RefreshHealth
AS
SELECT
    RunID,
    StartedAt,
    FinishedAt,
    Status,
    SourceRows,
    TargetRows,
    SourceRows - TargetRows AS RowVariance,
    SourcePaidAmount,
    TargetPaidAmount,
    SourcePaidAmount - TargetPaidAmount AS AmountVariance,
    ErrorMessage
FROM audit.RefreshRun;
GO

CREATE OR ALTER VIEW rpt.vw_PolicyStats
AS
SELECT
    p.PolicyNumber,
    pr.ProductName,
    b.BranchName,
    p.MemberSegment,
    p.WrittenPremium,
    p.CommissionAmount,
    CAST(p.CommissionAmount / p.WrittenPremium AS decimal(10,6)) AS CommissionRate,
    p.InsuredAmount,
    p.IsRenewal
FROM dw.FactPolicy AS p
INNER JOIN dw.DimProduct AS pr
    ON pr.ProductKey = p.ProductKey
INNER JOIN dw.DimBranch AS b
    ON b.BranchKey = p.BranchKey;
GO

CREATE OR ALTER VIEW rpt.vw_ClaimSeverity
AS
SELECT
    c.PaidAmount,
    p.WrittenPremium,
    p.InsuredAmount,
    pr.ProductName,
    b.BranchName,
    c.SettlementDays
FROM dw.FactClaim AS c
INNER JOIN dw.FactPolicy AS p
    ON p.PolicyKey = c.PolicyKey
INNER JOIN dw.DimProduct AS pr
    ON pr.ProductKey = c.ProductKey
INNER JOIN dw.DimBranch AS b
    ON b.BranchKey = c.BranchKey;
GO

CREATE OR ALTER VIEW rpt.vw_AutoClaims
AS
SELECT c.PaidAmount
FROM dw.FactClaim AS c
INNER JOIN dw.DimProduct AS p
    ON p.ProductKey = c.ProductKey
WHERE p.ProductName = 'Auto';
GO

CREATE OR ALTER VIEW rpt.vw_BusinessPropertyClaims
AS
SELECT c.PaidAmount
FROM dw.FactClaim AS c
INNER JOIN dw.DimProduct AS p
    ON p.ProductKey = c.ProductKey
WHERE p.ProductName = 'Business Property';
GO

SELECT COUNT(*) AS PolicyRows
FROM dw.FactPolicy;

SELECT
    COUNT(*) AS ClaimEventRows,
    COUNT(DISTINCT ClaimNumber) AS DistinctClaims
FROM stg.ClaimFeed;
GO

Your local BI lab is ready. Next, you will use those duplicate claim events to build a dashboard that looks convincing while overstating the truth.

Build the Dashboard That Lies

Your local SQL Server database now holds the seeded insurance portfolio. Power BI Desktop can turn those rows into a report that looks ready for decisions.

Speed creates a risk because a polished dashboard can overstate operational results. In this step, you will build the fast version first so you can test its claim count against the source.

In this step, get ready to:
  • Import the insurance brokerage tables into Power BI Desktop.
  • Create the dimension-to-fact relationships and a visible starter report.
  • Run duplicate-control queries and document the mismatch.
Connect Power BI to the local database

Import mode copies data from the local database into the Power BI model. This gives the report a stable snapshot that you can validate against the SQL source.

  • Press the Windows key to open Windows search.
  • Type Power BI Desktop into the search box.
  • Press Enter to open Power BI Desktop.

Power BI Desktop opens with a blank report canvas. You are ready to connect that report to the local database.

  • Select Get data on the Home ribbon.
  • Select SQL Server database as the data source.
  • Enter localhost\SQLEXPRESS in the Server field.
  • Enter InsuranceBrokerBI in the Database field.
  • Select Import as the data connectivity mode.
  • Select OK to start the connection.

Why Use Import Mode?

Import mode stores a copy of the source rows inside the report file. Each refresh creates a clear comparison point between the Power BI result and the SQL control totals.

  • Authenticate with your Windows account if Power BI requests database credentials.
  • Select dw.DimDate, dw.DimBranch, dw.DimProduct, dw.DimBroker, dw.FactPolicy, dw.FactClaim, rpt.vw_ClaimFeedNaive, and rpt.vw_RefreshHealth in the Navigator.
  • Select Load to import the eight objects.

The Data pane now lists four dimensions, two facts, and two reporting views. The model has the source objects needed for the starter report.

Database Connection Blocked?

Confirm that the server value includes the named instance. Use localhost\SQLEXPRESS exactly.

If a certificate warning blocks the import, follow Microsoft's SQL Server connector guidance for trusted local servers. Keep connection encryption enabled.

Still blocked? Help me troubleshoot a Power BI connection to my local SQL Server Express instance.

Create relationships and the starter page

A star schema lets shared dimensions filter each fact table. The model needs active one-to-many relationships so branch, product, broker, and date filters behave consistently.

  • Select the Model view icon on the left side of Power BI Desktop.
  • Inspect the relationship lines that Power BI detected during import.

Power BI can detect some relationships from the SQL foreign keys. Each required relationship still needs a visible solid line in the model.

  • Create a missing policy-date relationship by dragging DateKey from DimDate onto SaleDateKey in FactPolicy.
  • Create a missing policy-branch relationship by dragging BranchKey from DimBranch onto BranchKey in FactPolicy.
  • Create a missing policy-product relationship by dragging ProductKey from DimProduct onto ProductKey in FactPolicy.
  • Create a missing policy-broker relationship by dragging BrokerKey from DimBroker onto BrokerKey in FactPolicy.

What Do the Policy Links Establish?

Each dimension holds one row per key. FactPolicy can hold many rows for that key.

The one-to-many lines let a selected date, branch, product, or broker filter the related policy rows.

  • Create a missing claim-date relationship by dragging DateKey from DimDate onto OccurrenceDateKey in FactClaim.
  • Create a missing claim-branch relationship by dragging BranchKey from DimBranch onto BranchKey in FactClaim.
  • Create a missing claim-product relationship by dragging ProductKey from DimProduct onto ProductKey in FactClaim.
  • Create a missing claim-broker relationship by dragging BrokerKey from DimBroker onto BrokerKey in FactClaim.
  • Confirm that every relationship shows 1 beside the dimension and * beside the fact.

The solid relationship lines confirm that the shared dimensions can filter both fact tables. Your model now has the structure needed for consistent analysis.

Relationship Line Missing?

Check that you dragged the dimension key onto the matching fact key. A date relationship uses SaleDateKey for policies and OccurrenceDateKey for claims.

If Power BI reports that a relationship already exists, keep the existing line. Help me check my insurance star-schema relationships in Power BI.

The starter page will use vw_ClaimFeedNaive, where each row represents a claim event snapshot. This data grain determines what an ordinary count means.

  • Select the Report view icon on the left side of Power BI Desktop.
  • Select a blank area of the report canvas.
  • Select Card visual from the Visualizations pane.

Before you add the field, ask yourself whether the card will count business claims or event snapshots.

  • Drag ClaimEventKey from vw_ClaimFeedNaive into the Values field well.
  • Set the ClaimEventKey aggregation to Count (Not Blanks).

The card shows 144. That is the intended shortfall because the visual has counted event rows.

  • Select a blank area of the report canvas.
  • Select another Card visual.
  • Drag PaidAmount from vw_ClaimFeedNaive into the Values field well.
  • Set the PaidAmount aggregation to Sum.

Why Does the Grain Matter?

Both cards aggregate the rows supplied by vw_ClaimFeedNaive. An event snapshot contributes to the count and the paid amount whenever it appears as a separate row.

  • Select a blank area of the report canvas.
  • Select Clustered column chart from the Visualizations pane.
  • Drag BranchName from vw_ClaimFeedNaive into the X-axis field well.
  • Drag PaidAmount from vw_ClaimFeedNaive into the Y-axis field well.
  • Select another blank area of the report canvas.
  • Select another Clustered column chart.
  • Drag ProductName from vw_ClaimFeedNaive into the X-axis field well.
  • Drag PaidAmount from vw_ClaimFeedNaive into the Y-axis field well.

You now have two cards plus branch and product comparisons. Every visual uses the same event-level reporting view.

  • Select File in Power BI Desktop.
  • Select Save as.
  • Select your Desktop as the local save location.
  • Enter Insurance Brokerage Control Center.pbix as the file name.
  • Select Save.

Card Does Not Show 144?

Confirm that the card uses ClaimEventKey from vw_ClaimFeedNaive. A similarly named field from another table changes the result.

Check that the aggregation is Count (Not Blanks). Help me debug a Power BI card that should count claim-event rows.

Prove the dashboard is overstating claims

A source-to-report check compares a dashboard metric with a separately calculated control. You will use T-SQL in SSMS to separate event rows from distinct business claims.

  • Switch back to SSMS from earlier.
  • Create a new query tab from the SSMS toolbar.
  • Paste the duplicate-control queries below into the new tab:
USE InsuranceBrokerBI;
GO

SELECT
    COUNT(*) AS ClaimEventRows,
    COUNT(DISTINCT ClaimNumber) AS DistinctClaims,
    SUM(PaidAmount) AS NaivePaidAmount
FROM stg.ClaimFeed;

SELECT
    ClaimNumber,
    COUNT(*) AS EventRows,
    MIN(EventUpdatedAt) AS FirstEventAt,
    MAX(EventUpdatedAt) AS LatestEventAt
FROM stg.ClaimFeed
GROUP BY ClaimNumber
HAVING COUNT(*) > 1
ORDER BY ClaimNumber;

What Do These Controls Check?

  • The first query compares all claim-event rows with distinct claim numbers. It also calculates the paid amount produced by the event-level feed.
  • The second query lists claim numbers that appear more than once. Its timestamps show the earlier snapshot and the latest snapshot.
  • Press Ctrl+S to save the query.
  • Select your Desktop as the save location.
  • Enter analysis_queries.sql as the file name.
  • Select Save.

Before you run these controls, ask yourself whether the event-row count will match the distinct-claim count.

  • Highlight the SQL from USE InsuranceBrokerBI through the duplicate-group query.
  • Select Execute in the SSMS toolbar.

The first result set shows 144 under ClaimEventRows and 96 under DistinctClaims. The next result set lists the duplicated ClaimNumber groups with their event timestamps.

You found the lie. The dashboard is polished, but its claim count describes snapshots instead of business claims.

Control Results Look Different?

Confirm that the query window is connected to localhost\SQLEXPRESS. The status bar should identify the local Express instance.

Confirm that the first line switches to InsuranceBrokerBI. Help me diagnose unexpected duplicate-control results in SQL Server.

The portfolio control script also contains the prepared refresh checks. Keeping the controls together creates one repeatable audit script.

  • Place the cursor after the duplicate-group query.
  • Add one blank line below that query.
  • Paste the remaining control section below the blank line:
EXEC etl.usp_refresh_claim_mart;

SELECT *
FROM rpt.vw_RefreshHealth
ORDER BY RunID DESC;

SELECT
    COUNT(*) AS CleanClaimRows,
    COUNT(DISTINCT ClaimNumber) AS CleanDistinctClaims,
    SUM(PaidAmount) AS CleanPaidAmount
FROM dw.FactClaim;
GO

Why Keep the Remaining Checks?

The lower section calls the repeatable refresh procedure. Its queries then inspect the audit result and the clean claim target.

Keeping this section in the same file preserves the supplied control sequence. Only the two duplicate-control queries are selected during this step.

  • Press Ctrl+S to save the completed analysis_queries.sql file.
  • Keep the lower refresh section unselected during this step.
  • Confirm that the final line of the saved file is GO.

✔️ Awesome, I've got everything!

Great. Your analysis_queries.sql file is complete. You executed only the two duplicate-control queries in this step.

ⓧ I'd like to double check the full code

Compare your complete analysis_queries.sql file with this reference.

USE InsuranceBrokerBI;
GO

SELECT
    COUNT(*) AS ClaimEventRows,
    COUNT(DISTINCT ClaimNumber) AS DistinctClaims,
    SUM(PaidAmount) AS NaivePaidAmount
FROM stg.ClaimFeed;

SELECT
    ClaimNumber,
    COUNT(*) AS EventRows,
    MIN(EventUpdatedAt) AS FirstEventAt,
    MAX(EventUpdatedAt) AS LatestEventAt
FROM stg.ClaimFeed
GROUP BY ClaimNumber
HAVING COUNT(*) > 1
ORDER BY ClaimNumber;

EXEC etl.usp_refresh_claim_mart;

SELECT *
FROM rpt.vw_RefreshHealth
ORDER BY RunID DESC;

SELECT
    COUNT(*) AS CleanClaimRows,
    COUNT(DISTINCT ClaimNumber) AS CleanDistinctClaims,
    SUM(PaidAmount) AS CleanPaidAmount
FROM dw.FactClaim;
GO

Before you refresh, predict whether the dashboard count will stay aligned with the SQL business-claim count.

  • Return to the Power BI report from earlier.
  • Select Refresh on the Home ribbon.
  • Switch back to the saved analysis_queries.sql tab in SSMS.
  • Highlight only the two duplicate-control queries at the top of the file.
  • Select Execute.

The refreshed Power BI card still shows 144 claim events. The SQL control still shows only 96 distinct claim numbers.

That mismatch proves the starter dashboard overstates claims at the event grain. You now have visible report evidence plus a source-level control result.

Your starter dashboard has exposed a real data-quality failure. Next, you will retain the latest event per claim and prove that the corrected mart reconciles to its source.

Reconcile and Repair the Claims Feed

Your starter report exposed the exact risk this step solves. Duplicate snapshots made 96 business claims look like 144 claim events.

Now you'll repair the claim mart in SQL Server Management Studio (SSMS). A common table expression ranks each snapshot so the newest event wins.

Source-to-report reconciliation then compares the corrected target with the source control. Once the database proves those totals, Power BI can display the 96 business claims.

In this step, get ready to:
  • Inspect how the ranking selects the latest claim event.
  • Run the claim mart refresh with reconciliation controls.
  • Replace the naive report fields with deduplicated claim fields.
Trace the latest claim event

A claim number represents one business claim. Its latest EventUpdatedAt value represents the current snapshot that belongs in the claim mart.

Before you run this, which part of the result should prove that one business claim has multiple snapshots?

  • Inspect the duplicate counts and timestamps by running this query in the analysis_queries.sql tab from earlier:
SELECT
    ClaimNumber,
    COUNT(*) AS EventRows,
    MIN(EventUpdatedAt) AS FirstEventAt,
    MAX(EventUpdatedAt) AS LatestEventAt
FROM stg.ClaimFeed
GROUP BY ClaimNumber
HAVING COUNT(*) > 1
ORDER BY ClaimNumber;

What does this query prove?

  • The grouping creates one result row for each duplicated ClaimNumber.
  • EventRows shows how many snapshots exist for that claim.
  • FirstEventAt identifies the earlier snapshot timestamp.
  • LatestEventAt identifies the timestamp that should win the ranking.

You'll see 48 duplicate claim numbers. Each row shows EventRows as 2.

The FirstEventAt timestamp is earlier than the corresponding LatestEventAt timestamp. This confirms that the feed contains snapshots from different points in time.

  • Return to insurance_bi.sql in SSMS.
  • Find the WITH RankedClaims AS section inside etl.usp_refresh_claim_mart.
  • Compare the ranking logic with this inspection reference:

Use this snippet only as a reference for the ranking logic inside the procedure.

        WITH RankedClaims AS
        (
            SELECT
                cf.ClaimEventKey,
                cf.ClaimNumber,
                cf.PolicyNumber,
                cf.ClaimStatus,
                cf.IncurredAmount,
                cf.PaidAmount,
                cf.OccurrenceDate,
                cf.SettlementDate,
                ROW_NUMBER() OVER
                (
                    PARTITION BY cf.ClaimNumber
                    ORDER BY cf.EventUpdatedAt DESC, cf.ClaimEventKey DESC
                ) AS EventRank
            FROM stg.ClaimFeed AS cf
        )

How does the ranking choose one event?

  • PARTITION BY cf.ClaimNumber restarts the ranking for every business claim.
  • ORDER BY cf.EventUpdatedAt DESC puts the newest snapshot first.
  • cf.ClaimEventKey DESC resolves a tie when two snapshots share the same update time.
  • EventRank stores the resulting position for each snapshot.
  • Locate WHERE r.EventRank = 1 lower in the same procedure.

That filter keeps only the newest ranked event for each claim. The target therefore receives one row per ClaimNumber.

Refresh and reconcile the claim mart

The ranking fixes the grain of the data. The refresh procedure also records source totals so the repaired target can prove that no current claims or paid amounts were lost.

  • Refresh the deduplicated claim mart by running this statement in analysis_queries.sql:
EXEC etl.usp_refresh_claim_mart;

What does the procedure do?

  • The procedure creates a new audit record with a running status.
  • The transaction replaces the existing FactClaim rows with the latest ranked events.
  • The audit update records source row totals beside target row totals.
  • The audit update also records source paid amounts beside target paid amounts.

SSMS should complete the procedure without reporting an error. The latest ranked events are now loaded into FactClaim.

Procedure did not complete?

  • Check the query tab's database selector for InsuranceBrokerBI.
  • Run the procedure again after selecting the correct database.
  • Return to insurance_bi.sql from earlier if SSMS reports that the procedure is missing.

Still stuck? Help me diagnose why the claim mart refresh procedure failed.

Before you run the control query, do you expect the source totals to match the target totals after the latest-event filter?

  • Inspect the refresh audit by running this query:
SELECT *
FROM rpt.vw_RefreshHealth
ORDER BY RunID DESC;

What does the health view check?

  • Status confirms whether the transactional refresh completed.
  • RowVariance compares the latest-event source count with the target count.
  • AmountVariance compares the latest-event source paid amount with the target paid amount.
  • RunID identifies each refresh attempt.

The first row shows Status as Succeeded. It shows both SourceRows and TargetRows as 96.

RowVariance is 0. AmountVariance is 0.00.

  • Confirm the clean target grain by running this query:
SELECT
    COUNT(*) AS CleanClaimRows,
    COUNT(DISTINCT ClaimNumber) AS CleanDistinctClaims,
    SUM(PaidAmount) AS CleanPaidAmount
FROM dw.FactClaim;
GO

What does the target check prove?

  • CleanClaimRows counts every row loaded into the clean target.
  • CleanDistinctClaims counts the business claims represented in that target.
  • Matching row counts prove that FactClaim contains one row per claim.
  • CleanPaidAmount provides the corrected paid total for audit comparison.

Both clean claim counts show 96. You have closed the duplicate-event gap with a refresh that proves its own result.

✔️ Awesome, I've got everything!

Your SQL checks now prove that FactClaim contains 96 deduplicated claims with zero row variance and zero amount variance.

ⓧ I'd like to double check the full code

  • Compare your existing analysis_queries.sql with this complete reference:
USE InsuranceBrokerBI;
GO

SELECT
    COUNT(*) AS ClaimEventRows,
    COUNT(DISTINCT ClaimNumber) AS DistinctClaims,
    SUM(PaidAmount) AS NaivePaidAmount
FROM stg.ClaimFeed;

SELECT
    ClaimNumber,
    COUNT(*) AS EventRows,
    MIN(EventUpdatedAt) AS FirstEventAt,
    MAX(EventUpdatedAt) AS LatestEventAt
FROM stg.ClaimFeed
GROUP BY ClaimNumber
HAVING COUNT(*) > 1
ORDER BY ClaimNumber;

EXEC etl.usp_refresh_claim_mart;

SELECT *
FROM rpt.vw_RefreshHealth
ORDER BY RunID DESC;

SELECT
    COUNT(*) AS CleanClaimRows,
    COUNT(DISTINCT ClaimNumber) AS CleanDistinctClaims,
    SUM(PaidAmount) AS CleanPaidAmount
FROM dw.FactClaim;
GO

How is this file organized?

  • The first query captures the naive event totals.
  • The second query exposes the duplicate groups.
  • The procedure refreshes the clean claim mart.
  • The health query tests the reconciliation result.
  • The final query checks the target grain.
Replace the naive report fields

An Import model stores a snapshot of the database inside the report. Refreshing the model brings the repaired FactClaim rows into the visuals.

  • Switch back to the starter report page in Power BI Desktop.
  • Select Refresh from the top ribbon.
  • Replace the current event field in the claim-event count visual with ClaimNumber from FactClaim.
  • Configure the new ClaimNumber value to count unique claim numbers.
  • Replace the current field in the paid amount visual with PaidAmount from FactClaim.
  • Configure the new PaidAmount value to add the paid amounts.
  • Replace the branch visual's category with BranchName from DimBranch.
  • Replace the branch visual's event value with ClaimNumber from FactClaim.

Why use dimension fields on the axes?

DimBranch and DimProduct filter FactClaim through the existing one-to-many relationships. This keeps descriptive labels separate from claim transactions.

The vw_ClaimFeedNaive table remains in the model for audit comparison. The corrected report visuals use the clean fact.

The claim total now reads 96. The paid amount also reflects the latest event for each claim.

  • Configure the branch visual's ClaimNumber value to count unique claim numbers.
  • Replace the product visual's category with ProductName from DimProduct.
  • Replace the product visual's event value with ClaimNumber from FactClaim.
  • Configure the product visual's ClaimNumber value to count unique claim numbers.
  • Add a table visual to the starter report page.
  • Add RunID from vw_RefreshHealth to the table.
  • Add Status from vw_RefreshHealth to the table.
  • Add SourceRows from vw_RefreshHealth to the table.

The table now identifies each recorded refresh. The remaining fields expose whether that refresh reconciled.

  • Add TargetRows from vw_RefreshHealth to the table.
  • Add RowVariance from vw_RefreshHealth to the table.
  • Add AmountVariance from vw_RefreshHealth to the table.
  • Sort the table so the highest RunID appears first.
  • Save Insurance Brokerage Control Center.pbix.

Before you check the finished page, do you expect the corrected claim total to agree with the latest refresh row?

  • Check the claim total displayed on the starter report page.
  • Check the first row of the refresh-health table.
  • Confirm that vw_ClaimFeedNaive remains available in the model for audit comparison.

You'll see 96 claims on the report page. The latest refresh row shows Succeeded with SourceRows and TargetRows at 96.

RowVariance is 0. AmountVariance is 0.00.

Still seeing 144 claims?

  • Inspect the claim total's field well for a field from vw_ClaimFeedNaive.
  • Replace any remaining naive event value with ClaimNumber from FactClaim.
  • Select Refresh again if the refresh-health table is blank.

Need another pair of eyes? Help me find why my Power BI report still shows 144 claim events after refreshing FactClaim.

Your report now uses a reconciled claim mart with evidence that the source and target agree. Next, you'll turn these trusted rows into governed KPIs across two executive report pages.

Build the Executive Control Center

Your repaired claim mart now gives Power BI a trustworthy set of 96 business claims. The zero-variance refresh proves that the report matches its source.

Correct rows still need a governed semantic model before leaders can use them. In this step, you will add reusable DAX measures to answer executive questions across two report pages.

In this step, get ready to:
  • Govern the model names, technical fields, and filter paths.
  • Implement the KPI measures from Measures.dax.
  • Build the Executive Portfolio and Claims and BI Reliability pages.
Govern the semantic model

A governed model gives report authors clear business names while keeping relationship fields out of sight. Its one-to-many relationships also give every filter a predictable route from dimensions to facts.

Use these governed names

  • Rename DimDate to Date.
  • Rename DimBranch to Branch.
  • Rename DimProduct to Product.
  • Rename DimBroker to Broker.
  • Keep FactPolicy as FactPolicy.
  • Keep FactClaim as FactClaim.
  • Rename vw_RefreshHealth to RefreshHealth.
  • Switch back to Insurance Brokerage Control Center.pbix in Power BI Desktop.
  • Select the Model icon on the left side of the window.
  • Rename each model table using the governed-name map above.
  • Right-click vw_ClaimFeedNaive in the model.
  • Select Hide in report view to preserve the audit table without offering it to report authors.

Which technical keys should be hidden?

  • Date contains DateKey.
  • Branch contains BranchKey.
  • Product contains ProductKey.
  • Broker contains BrokerKey.
  • FactPolicy contains PolicyKey, SaleDateKey, BranchKey, ProductKey, and BrokerKey.
  • FactClaim contains ClaimKey, SourceClaimEventKey, PolicyKey, OccurrenceDateKey, BranchKey, ProductKey, and BrokerKey.
  • RefreshHealth contains RunID.
  • Hide every field in the technical-key list by selecting Hide in report view from its context menu.

Check the filter paths

  • Date filters FactPolicy through DateKey and SaleDateKey.
  • Date filters FactClaim through DateKey and OccurrenceDateKey.
  • Branch filters FactPolicy through BranchKey.
  • Branch filters FactClaim through BranchKey.
  • Product filters FactPolicy through ProductKey.
  • Product filters FactClaim through ProductKey.
  • Broker filters FactPolicy through BrokerKey.
  • Broker filters FactClaim through BrokerKey.
  • Double-click each relationship line in the map above.
  • Set Cardinality to one-to-many with the dimension on the one side.
  • Set Cross-filter direction to single from the dimension toward the fact.
  • Select OK to keep each relationship.
  • Return to the repaired report page using the Report icon.

You should still see 96 claims with zero reconciliation variance. That is the model contract in place: cleaner names now sit above the same trusted data.

Did a visual show an error?

A renamed table can expose a visual that still points to an older field. Re-add the matching field from the governed table if a visual shows an error.

If a filter produces unexpected totals, reopen the relationship and confirm that the dimension sits on the one side. Help me diagnose a Power BI relationship that filters in the wrong direction.

Create the governed KPI measures

Measures centralize business rules so every card or chart uses the same calculation. You will store the definitions in Measures.dax while implementing each measure under FactPolicy.

  • Press the Windows key to open Windows search.
  • Type Notepad and press Enter.
  • Begin Measures.dax by pasting these base measures into the blank document:
Policy Count =
DISTINCTCOUNT('FactPolicy'[PolicyNumber])

Written Premium =
SUM('FactPolicy'[WrittenPremium])

Commission Revenue =
SUM('FactPolicy'[CommissionAmount])

Claim Count =
DISTINCTCOUNT('FactClaim'[ClaimNumber])

Claims Paid =
SUM('FactClaim'[PaidAmount])

What do these measures calculate?

  • Policy Count counts unique policy numbers so duplicate rows cannot inflate the portfolio size.
  • Written Premium totals the premium carried by the current filter context.
  • Commission Revenue totals the brokerage revenue carried by the current filter context.
  • Claim Count counts unique claim numbers from the repaired claim fact.
  • Claims Paid totals the latest paid amount retained for each claim.
  • Save the new file as Measures.dax in the same folder as Insurance Brokerage Control Center.pbix.
  • Return to Power BI Desktop.
  • Right-click FactPolicy in the Data pane.
  • Select New measure.
  • Create each base measure by pasting one complete definition into the formula bar.
  • Press Enter after each definition.
  • Add the five base measures to a table visual on the current report page.

The table should show 480 policies and 96 claims. It should also show totals for premium, commission, and paid claims.

Is a base measure failing?

Check that the table names match FactPolicy and FactClaim exactly. Confirm that every column name matches the formula before pressing Enter.

Still stuck? Help me fix a failing base measure in Power BI.

The next measures turn those totals into decision-ready rates. Each ratio reuses a governed measure so its numerator and denominator stay consistent across visuals.

  • Append these rate measures below the existing definitions in Measures.dax.
  • Create each rate measure under FactPolicy using New measure.
Loss Ratio =
DIVIDE([Claims Paid], [Written Premium], 0)

Claim Frequency =
DIVIDE([Claim Count], [Policy Count], 0)

Renewal Policies =
CALCULATE(
    [Policy Count],
    'FactPolicy'[IsRenewal] = 1
)

Renewal Rate =
DIVIDE([Renewal Policies], [Policy Count], 0)

How do the rate measures work?

  • Loss Ratio divides paid claims by written premium.
  • Claim Frequency divides distinct claims by distinct policies.
  • Renewal Policies modifies the filter context to retain rows where IsRenewal equals 1.
  • Renewal Rate divides renewal policies by all policies in the current context.
  • DIVIDE returns the supplied alternate result when a denominator is zero.
  • Save Measures.dax.
  • Save Insurance Brokerage Control Center.pbix.
  • Add the four rate measures to the existing table visual.

You should see populated values for loss ratio, claim frequency, renewal policies, and renewal rate. These values now respond to the same branch or product context as the base totals.

Are the ratios blank or incorrect?

Confirm that the base measures return values before checking the ratios. A misspelled measure reference can prevent a dependent measure from validating.

If Renewal Policies matches Policy Count, confirm that IsRenewal uses the numeric value 1 in the filter. Help me debug an unexpected DAX ratio.

Operational controls give the report a second purpose. They show whether claims settle efficiently and whether the latest refresh still reconciles.

  • Append these operational measures below the rate measures in Measures.dax.
  • Create each operational measure under FactPolicy using New measure.
Average Settlement Days =
AVERAGE('FactClaim'[SettlementDays])

Reconciliation Variance =
MAX('RefreshHealth'[AmountVariance])

Freshness Hours =
DATEDIFF(
    MAX('RefreshHealth'[FinishedAt]),
    NOW(),
    HOUR
)

What do the control measures reveal?

  • Average Settlement Days summarizes the elapsed days for settled claims.
  • Reconciliation Variance surfaces the largest recorded amount variance in the imported refresh data.
  • Freshness Hours measures the elapsed hours between the recorded finish time and the model refresh time.
  • Save Measures.dax.
  • Add the three operational measures to the existing table visual.
  • Confirm that Reconciliation Variance displays zero.
  • Confirm that Freshness Hours displays a whole-hour value.

Your measure check now covers portfolio scale, claims performance, renewals, settlement speed, and refresh integrity. The zero variance proves that the control layer still agrees with the repaired mart.

Is Freshness Hours blank?

Confirm that RefreshHealth contains a populated FinishedAt value for the succeeded run. Refresh the model if the latest audit row is missing from Power BI.

If Reconciliation Variance is blank, confirm that AmountVariance is present under RefreshHealth. Help me debug the refresh-health measures.

  • Select Written Premium, Commission Revenue, Claims Paid, and Reconciliation Variance while holding Ctrl.
  • Set Format to currency for the selected measures.
  • Select Loss Ratio, Claim Frequency, and Renewal Rate while holding Ctrl.
  • Set Format to percentage with two decimal places.
  • Select all thirteen measures while holding Ctrl.
  • Enter KPI in the Display folder property.
  • Save Insurance Brokerage Control Center.pbix.

The Data pane should now show a KPI folder under FactPolicy. The currency and percentage measures should use their governed formats in the table visual.

Are the formats or folder missing?

Select model measures with calculator icons instead of source columns. The measure properties become available after Power BI validates each DAX expression.

Still stuck? Help me organize and format my Power BI measures.

✔️ Awesome, I've got everything!

Your thirteen measures are saved in Measures.dax and implemented in the governed model.

ⓧ I'd like to double check the full code

Compare your complete Measures.dax file with this reference.

Policy Count =
DISTINCTCOUNT('FactPolicy'[PolicyNumber])

Written Premium =
SUM('FactPolicy'[WrittenPremium])

Commission Revenue =
SUM('FactPolicy'[CommissionAmount])

Claim Count =
DISTINCTCOUNT('FactClaim'[ClaimNumber])

Claims Paid =
SUM('FactClaim'[PaidAmount])

Loss Ratio =
DIVIDE([Claims Paid], [Written Premium], 0)

Claim Frequency =
DIVIDE([Claim Count], [Policy Count], 0)

Renewal Policies =
CALCULATE(
    [Policy Count],
    'FactPolicy'[IsRenewal] = 1
)

Renewal Rate =
DIVIDE([Renewal Policies], [Policy Count], 0)

Average Settlement Days =
AVERAGE('FactClaim'[SettlementDays])

Reconciliation Variance =
MAX('RefreshHealth'[AmountVariance])

Freshness Hours =
DATEDIFF(
    MAX('RefreshHealth'[FinishedAt]),
    NOW(),
    HOUR
)
Build the two report pages

The first page answers portfolio questions for an executive. The second page exposes claims operations and report reliability for the team that supports those decisions.

  • Select the Report icon on the left side of Power BI Desktop.
  • Rename the current report page to Executive Portfolio.
  • Remove the temporary measure-check table and the earlier starter visuals from the canvas.
  • Add a card visual using Written Premium.
  • Add a card visual using Commission Revenue.
  • Add a card visual using Policy Count.
  • Add a card visual using Renewal Rate.
  • Add a card visual using Loss Ratio.

What does the executive row answer?

The first five cards show portfolio scale, brokerage income, renewal strength, and claims pressure. Their shared measures keep the headline numbers consistent with every supporting chart.

  • Add a card visual using Claim Frequency.
  • Add a line chart using Date[YearMonth] on the horizontal axis.
  • Add Written Premium and Claims Paid as the line-chart values.
  • Add a branch comparison chart using Branch[BranchName] with Written Premium and Commission Revenue.
  • Add a product comparison chart using Product[ProductName] with Loss Ratio and Claim Frequency.
  • Add a slicer using Branch[BranchName].
  • Add a slicer using Product[ProductName].

You should now see six executive KPI cards above a trend view and two comparison charts. The branch and product slicers provide a visible route into the portfolio.

  • Add a new report page using the plus icon beside the page tabs.
  • Rename the new page to Claims and BI Reliability.
  • Add a card visual using Claim Count.
  • Add a card visual using Average Settlement Days.
  • Add a claim-severity chart using Product[ProductName] with FactClaim[PaidAmount].
  • Set the PaidAmount summarization to Average.
  • Add a table visual using RunID, Status, SourceRows, TargetRows, RowVariance, AmountVariance, and FinishedAt from RefreshHealth.
  • Sort the refresh table by RunID in descending order.

Why keep the refresh record visible?

The table places report evidence beside the claims analysis. A stakeholder can see the source count, target count, status, and variances without leaving the report.

  • Add a card visual using Freshness Hours.
  • Add a card visual using Reconciliation Variance.
  • Select Sync slicers from the View ribbon.
  • Select the branch slicer on the Executive Portfolio page.
  • Select both report pages in the Sync column.
  • Select both report pages in the Visible column.
  • Repeat the sync settings for the product slicer.
  • Save Insurance Brokerage Control Center.pbix.

Before you test the report, do you expect a branch and product selection to affect both fact tables while leaving the refresh audit unchanged?

  • Select Curitiba in the branch slicer.
  • Select Auto in the product slicer.
  • Review the KPI cards on the Executive Portfolio page.
  • Switch to the Claims and BI Reliability page.
  • Review the claim visuals and refresh-health controls.

Both pages should show the selected branch and product context. The refresh table should continue to show Succeeded with row variance 0 and amount variance 0.00.

That is the control center working as one governed report. Executives can narrow the portfolio while the reliability evidence stays visible and stable.

Is one page ignoring the slicers?

Select the affected slicer and reopen the Sync slicers pane. Confirm that both pages are selected in the Sync column.

If a policy or claim visual remains unchanged, inspect its dimension-to-fact relationship in Model view. Help me trace a Power BI slicer that does not update both report pages.

Your two-page control center now turns reconciled data into governed portfolio decisions. Next, you will add statistical evidence and package the findings for stakeholders.

Add Statistical Evidence and the Stakeholder Pack

The two-page Power BI control center now gives leaders governed portfolio metrics. Its claims results reconcile to the source with zero row variance and zero amount variance.

Senior analysts must also quantify uncertainty. This step turns the reconciled evidence into a statistical appendix plus a stakeholder handoff pack.

In this step, get ready to:
  • Build an Excel appendix containing descriptive analysis plus inferential evidence.
  • Document the governed KPI logic in a one-page Word dictionary.
  • Create an executive communication pack for the recommended decision.
Connect Excel and build the statistical appendix

The Excel appendix adds statistical evidence to the governed totals. Its source views come directly from the reconciled SQL Server database.

  • Press the Windows key to open Windows search.
  • Type Excel and press Enter.
  • Select Blank workbook from the Excel start screen.
  • Save the workbook as Insurance Statistical Appendix.xlsx in the folder containing Insurance Brokerage Control Center.pbix.

The Excel title bar now shows Insurance Statistical Appendix.xlsx. This gives the statistical evidence a permanent home before the analysis begins.

  • Select the Data ribbon tab.
  • Select Get Data.
  • Select From Database.
  • Select From SQL Server Database.
  • Enter localhost\SQLEXPRESS in the Server field.
  • Enter InsuranceBrokerBI in the Database field.
  • Select OK.

Why Connect Through Power Query?

Power Query keeps the workbook linked to the reporting views. A refresh can reproduce the appendix from the governed database instead of relying on copied values.

  • Select Windows as the authentication method.
  • Select Connect.
  • Select rpt.vw_PolicyStats in Navigator.
  • Select rpt.vw_ClaimSeverity in Navigator.
  • Select rpt.vw_AutoClaims in Navigator.
  • Select rpt.vw_BusinessPropertyClaims in Navigator.
  • Select Load to import the four views.

You should see four populated query sheets. The policy view contains 480 records while the claim-severity view contains 96 records.

Cannot Connect to SQL Server?

  • Confirm the local SQLEXPRESS service is running before retrying the connection.
  • Confirm the database name is exactly InsuranceBrokerBI.
  • Keep certificate trust scoped to this local server if the encrypted connection is blocked.

Still stuck? Help me troubleshoot Excel connecting to my local SQL Server Express database.

  • Rename the rpt.vw_PolicyStats sheet to PolicyStats.
  • Rename the rpt.vw_ClaimSeverity sheet to ClaimSeverity.
  • Rename the rpt.vw_AutoClaims sheet to AutoClaims.
  • Rename the rpt.vw_BusinessPropertyClaims sheet to BusinessPropertyClaims.
  • Move the CommissionRate column to column F on the PolicyStats sheet.

The sheet tabs now expose the four statistical datasets by purpose. Column F on PolicyStats contains the commission rate used by the bootstrap calculation.

The Analysis ToolPak supplies the descriptive statistics plus least-squares regression tools. Excel keeps it disabled until you activate the add-in.

  • Select File.
  • Select Options.
  • Select Add-Ins.
  • Set Manage to Excel Add-ins.
  • Select Go.
  • Check Analysis ToolPak.
  • Select OK.

You should now see Data Analysis on the Data ribbon. That confirms Excel can produce the required statistical outputs.

  • Add a worksheet named Descriptive Statistics.
  • Run Descriptive Statistics from Data Analysis using ClaimSeverity!$A$1:$A$97 as the input range.
  • Select the option indicating that the first row contains labels.
  • Select the option that produces summary statistics.
  • Place the output at Descriptive Statistics!$A$1.

The output should contain the count plus measures of center and spread for paid claim amounts. The count should be 96.

What Questions Do These Methods Answer?

Descriptive statistics establish the scale plus spread of claim severity. Empirical probability measures observed claims relative to policies.

Hypothesis testing compares claim-severity samples. Least-squares regression tests whether written premium plus insured amount explain paid claims.

  • Add a worksheet named Product Probability.
  • Enter ProductName in cell A1.
  • Enter PolicyCount in cell B1.
  • Enter ClaimCount in cell C1.
  • Enter EmpiricalClaimProbability in cell D1.
  • Enter the four product names from the connected views in cells A2:A5.
  • Calculate each product's policy count from the PolicyStats sheet.
  • Calculate each product's claim count from the ClaimSeverity sheet.
  • Divide each product's claim count by its policy count in column D.
  • Format the four probability results as percentages.
  • Add a worksheet named Hypothesis Test.
  • Use T.TEST with the paid amounts from AutoClaims as the first array.
  • Use the paid amounts from BusinessPropertyClaims as the second array.
  • Set the tails argument to 2.
  • Set the type argument to 3 for unequal variance.
  • Label the result as Two-tailed unequal-variance p-value.

The product table should show 120 policies plus 24 claims for each product. Each empirical probability should display as 20%.

  • Open Data Analysis from the Data ribbon.
  • Select Regression.
  • Set the input Y range to ClaimSeverity!$A$1:$A$97 for paid amount.
  • Set the input X range to ClaimSeverity!$B$1:$C$97 for written premium plus insured amount.
  • Select the option indicating that the first row contains labels.
  • Place the regression output on a new worksheet named Regression.
  • Run the regression analysis.

You should see coefficients plus regression statistics on the new worksheet. The output gives you evidence about explanatory relationships without claiming that those relationships are causal.

Quantify uncertainty with bootstrapping

A percentile bootstrap repeatedly resamples the observed commission rates with replacement. The spread of those sample means becomes a practical interval for uncertainty.

  • Add a worksheet named Bootstrap.
  • Enter BootstrapMean in cell B1.
  • Build the bootstrap sample plus percentile bounds by entering the formulas below:
=AVERAGE(INDEX(PolicyStats!$F$2:$F$481,RANDARRAY(ROWS(PolicyStats!$F$2:$F$481),1,1,ROWS(PolicyStats!$F$2:$F$481),TRUE)))
=PERCENTILE.INC(B2:B501,0.025)
=PERCENTILE.INC(B2:B501,0.975)

What Do These Formulas Do?

  • RANDARRAY creates random row indexes from the connected policy data.
  • INDEX maps each random index to a commission rate.
  • AVERAGE turns one resample into one bootstrap mean.
  • PERCENTILE.INC selects the lower plus upper endpoints of the central 95% interval.

Enter the first formula in B2. Use the result to choose the compatibility path that matches your Excel version.

✔️ RANDARRAY returns a mean

  • Fill the formula in B2 down through B501 to create 500 bootstrap means.
  • Enter the lower percentile formula in B503.
  • Enter the upper percentile formula in B504.
  • Label the two bounds as Lower 95% Bound plus Upper 95% Bound.

ⓧ RANDARRAY is unavailable

Your Excel release needs the Analysis ToolPak fallback. The statistical method remains a 500-replicate percentile bootstrap.

  • Open Data Analysis from the Data ribbon.
  • Select Random Number Generation.
  • Generate 500 rows of 480 integer indexes.
  • Limit each generated index to the range 1 through 480.
  • Allow repeated indexes so the sampling is with replacement.
  • Map the generated indexes to PolicyStats!$F$2:$F$481 using INDEX.
  • Calculate the mean of each resampled row using AVERAGE.
  • Calculate the lower plus upper interval bounds from the 500 means using the two percentile formulas shown above.
  • Save Insurance Statistical Appendix.xlsx.
  • Confirm cells B2:B501 contain 500 bootstrap means.
  • Confirm the lower 95% bound does not exceed the upper bound.

The workbook now moves beyond point estimates. It shows how much the estimated commission rate can vary across repeated samples.

Bootstrap Results Look Wrong?

  • Confirm CommissionRate occupies column F on the PolicyStats sheet.
  • Confirm the bootstrap formulas reference rows 2 through 481.
  • Confirm the percentile formulas reference all 500 means in B2:B501.

Need help? Help me debug my 500-mean Excel percentile bootstrap interval.

Create the stakeholder handoff pack

The statistical outputs need business definitions plus a clear decision path. The final pack translates the reconciled report into documentation for analysts plus communication for executives.

The one-page Word dictionary governs interpretation. The PowerPoint briefing plus Outlook update carry the evidence into a decision conversation.

  • Press the Windows key to open Windows search.
  • Type Word and press Enter.
  • Create a blank document.
  • Add the title Insurance Brokerage KPI Dictionary.
  • Create a compact table with columns for KPI, business definition, calculation, source, refresh control, plus interpretation.
  • Document Policy Count, Written Premium, Commission Revenue, plus Renewal Rate using FactPolicy as the governed source.
  • Document Claim Count, Claims Paid, plus Average Settlement Days using the deduplicated FactClaim source.
  • Document Loss Ratio as paid claims divided by written premium.
  • Document Claim Frequency as distinct claims divided by distinct policies.
  • Document Reconciliation Variance plus Freshness Hours using RefreshHealth.
  • State that the current report contains 96 deduplicated claims with zero row variance plus zero amount variance.
  • Save the one-page document as KPI Dictionary.docx in the portfolio folder.

The finished dictionary should fit on one page. A reviewer should be able to trace every portfolio or claims KPI to its governed source.

  • Press the Windows key to open Windows search.
  • Type PowerPoint and press Enter.
  • Create a blank presentation.
  • Create slide one with the title Situation.
  • Summarize the portfolio decision plus the duplicate-event risk on slide one.
  • Create slide two with the title Reconciled Evidence and Uncertainty.
  • Add the 480 policy baseline plus the 96 deduplicated claim baseline to slide two.
  • Add the zero-variance refresh result plus the relevant statistical outputs to slide two.
  • State the sample-size caveat plus the distinction between statistical significance and practical significance on slide two.
  • Create slide three with the title Recommendation and Next Action.
  • State the management recommendation supported by the report on slide three.
  • Name the owner plus next review action on slide three.
  • Save the presentation as Insurance Brokerage Briefing.pptx in the portfolio folder.

The thumbnail pane should show exactly three slides. Their sequence should move from situation to evidence to action.

  • Press the Windows key to open Windows search.
  • Type Outlook and press Enter.
  • Create a new email draft.
  • Enter Insurance Brokerage Control Center: decision and next action as the subject.
  • State the decision requested in the opening paragraph.
  • Cite the reconciled report evidence in the second paragraph.
  • State the uncertainty or sample-size caveat in the third paragraph.
  • Name the next action plus owner in the closing paragraph.
  • Leave the recipient fields empty so the message remains a reusable draft.
  • Save the draft as Executive Update.msg in the portfolio folder.

Before you check the portfolio folder, how many stakeholder files should now sit beside the Power BI report?

  • Return to the folder containing Insurance Brokerage Control Center.pbix.
  • Confirm Insurance Statistical Appendix.xlsx appears in the folder.
  • Confirm KPI Dictionary.docx appears in the folder.
  • Confirm Insurance Brokerage Briefing.pptx appears in the folder.
  • Confirm Executive Update.msg appears in the folder.
  • Open each file once to confirm it is readable.

You should see the existing Power BI report plus four new stakeholder files. That is the complete evidence pack for the reconciled control center.

Strong finish. You now have a governed control center plus the evidence needed to defend its conclusions in an executive conversation.

Secret mission

Answer the Branch Risk Request

A last-minute branch-risk request tests whether your governed model can answer a new question without rebuilding the core dashboard. Rank the two eligible branches by loss ratio, reproduce the result in Power BI, and prepare a management recommendation with a sample-size caveat.

Clean Up Your Resources

Clean Up Your Resources

Every project resource stays on your Windows computer. The project has no ongoing service charges.

Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • The InsuranceBrokerBI database on your local SQL Server Express instance at localhost\SQLEXPRESS.
  • The database build script insurance_bi.sql.
  • The reconciliation script analysis_queries.sql.
  • The governed measure definitions in Measures.dax.
  • The branch-risk analysis in branch_risk_request.sql.
  • The Power BI report Insurance Brokerage Control Center.pbix.
  • The Excel workbook Insurance Statistical Appendix.xlsx.
  • The Word KPI dictionary KPI Dictionary.docx.
  • The PowerPoint briefing Insurance Brokerage Briefing.pptx.
  • The Outlook update Executive Update.msg.
  • The management recommendation Branch Risk Recommendation.txt.

Keep everything running

No action is needed. Choose this option if you plan to demonstrate the control center or continue the portfolio analysis.

  • Leave SQL Server (SQLEXPRESS) running for future report refreshes.
  • Retain the InsuranceBrokerBI database for future demonstrations.
  • Keep the project scripts in their current saved locations.
  • Keep the report files in their current saved locations.
  • Keep the stakeholder documents in their current saved locations.

Pause - I'll come back to this later

Stop the local database service to free system resources. Your database files remain available for your next demonstration.

  • Close Insurance Brokerage Control Center.pbix in Power BI Desktop.
  • Close Insurance Statistical Appendix.xlsx in Excel.
  • Press the Windows key to open Windows search.
  • Enter SQL Server Configuration Manager in the search field.
  • Select SQL Server (SQLEXPRESS) in SQL Server Configuration Manager.
  • Stop the selected service.

You should see that the database service is stopped. The saved InsuranceBrokerBI data remains untouched.

You're set for a break. Your portfolio remains ready for the next demonstration.

  • Start SQL Server (SQLEXPRESS) in SQL Server Configuration Manager before your next report refresh.

Delete - I don't want to use this again

Removing InsuranceBrokerBI is permanent. Your saved evidence files remain until you delete them separately.

  • Close Insurance Brokerage Control Center.pbix in Power BI Desktop.
  • Close Insurance Statistical Appendix.xlsx in Excel.
  • Switch back to the existing SQL Server Management Studio connection.
  • Select master as the query database context.
  • Remove the project database by running this command:
DROP DATABASE IF EXISTS InsuranceBrokerBI;

What Does This Remove?

This command removes the InsuranceBrokerBI database from your local SQL Server instance. Your saved portfolio files remain untouched.

  • Refresh the database list in SQL Server Management Studio.

You should no longer see InsuranceBrokerBI in the database list. The project database is now removed.

Database Still Listed?

  • Close any project file that still has a database connection.
  • Confirm that the cleanup query uses the master database context.
  • Help me troubleshoot this database cleanup.

That is the irreversible database cleanup handled. Your saved files remain available for the final cleanup actions.

  • Close any open project files.
  • Delete insurance_bi.sql from its saved location.
  • Delete analysis_queries.sql from its saved location.
  • Delete Measures.dax from its saved location.
  • Delete branch_risk_request.sql from its saved location.

The project scripts are cleared. Remove the report evidence next.

  • Delete Insurance Brokerage Control Center.pbix from its saved location.
  • Delete Insurance Statistical Appendix.xlsx from its saved location.
  • Delete KPI Dictionary.docx from its saved location.
  • Delete Insurance Brokerage Briefing.pptx from its saved location.
  • Delete Executive Update.msg from its saved location.
  • Delete Branch Risk Recommendation.txt from its saved location.
  • Search your Windows files for each filename listed in the resources checklist.

You should see no remaining matches for the project filenames. Your local portfolio resources are now removed.

SQL Server Express remains installed. SQL Server Management Studio remains installed for future local projects.

Nice Work!

Nice Work!

You made it! You built an auditable insurance brokerage control center that turns duplicate claim events into reconciled evidence for executive decisions.

You've learned how to:

  • Built a SQL Server data mart with 480 policies. Used ROW_NUMBER() to reduce 144 staged claim events to 96 business claims. Recorded a successful refresh. Proved zero row variance. Proved zero amount variance.
  • Created a governed two-page Power BI control center with reusable DAX measures. Verified the report under branch filters. Repeated the check under product filters. The reconciled KPI baseline remained consistent.
  • Built an Excel statistical appendix that tests portfolio patterns beyond dashboard totals. Packaged KPI definitions in a one-page Word dictionary. Presented the decision in a three-slide PowerPoint briefing. Prepared an Outlook-ready executive update.
  • Secret Mission: Answered the last-minute branch-risk request with an independently aggregated ranking. Returned the top two branches in descending loss-ratio order. Added the same ranking to Power BI. Applied the minimum-volume threshold of at least 20 policies. Wrote a two-sentence management recommendation with an explicit sample-size caveat.

Ready to quiz yourself?