Service Operations Control Tower

Build a reconciled SQL Server and Power BI control tower with Python checks.

Introduction

30 Second Summary

Support managers often find ticket counts scattered across systems while service risk keeps growing. A convincing total can still be wrong when status history duplicates each ticket.

In this project, you will build a two-page support-operations control tower in Power BI Desktop using local SQL Server data. Every headline number stays traceable to a reconciled reporting layer.

What You'll Build

Your finished control tower lets a manager spot an SLA exception on the overview before opening the full ticket detail through drillthrough.

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

  • An Operations Overview that exposes ticket workload through four prominent cards. Team charts reveal where service pressure sits. Priority charts highlight SLA risk.
  • A reconciled star schema that preserves one row per ticket. Explicit DAX measures match the SQL baselines.
  • A Ticket Detail drillthrough that opens the evidence behind one exception. A lineage note traces the path from source data to report. A Performance Analyzer result records visual responsiveness.
  • Secret Mission: Turn manual reconciliation into a Python quality gate. The script reruns five integrity checks before writing quality_report.csv.

Are there any prerequisites?

You need administrator access on a Windows PC for the local SQL Server plus Python setup. Your starting toolkit includes Power BI Desktop, SQL Server Management Studio, plus Visual Studio Code with no Power BI Service account required.

Before We Start

This is your moment to lock in who the control tower serves and which operational decision it should improve before any hands-on work begins.

Prepare the Local Analytics Stack

SQL Server Management Studio is a management client. It cannot store project data without a running SQL Server database engine.

The automation layer needs Python 3.15.0 in an isolated environment. The environment also pins mssql-python at version 1.16.0 for repeatable database connections.

In this step, get ready to:
  • Verify the local SQL Server engine.
  • Create an isolated Python environment.
  • Prepare the portfolio workspace in Visual Studio Code.
Verify or install SQL Server 2025 Express

The named service SQL Server (SQLEXPRESS) hosts the databases used throughout the project. A green running indicator proves the engine is available before SSMS tries to connect.

  • Press the Windows key to open Windows search.
  • Type SQL Server Configuration Manager.
  • Press Enter to open SQL Server Configuration Manager.
  • Select SQL Server Services in the left pane.

✔️ SQL Server (SQLEXPRESS) is running

Good news. The green indicator confirms that your local database engine is already running.

  • Leave SQL Server (SQLEXPRESS) running.

ⓧ SQL Server (SQLEXPRESS) is stopped

The service exists without a green indicator. Starting it makes the existing engine available to SSMS.

  • Open the context menu for SQL Server (SQLEXPRESS).
  • Start the service from its context menu.

You should now see a green running indicator beside SQL Server (SQLEXPRESS).

ⓧ SQL Server (SQLEXPRESS) is absent

The installation can take several minutes while Windows configures the database engine. Leave the installer running until it reports completion.

  • Visit the official SQL Server 2025 Express download page.
  • Download SQL2025-SSEI-Expr.exe.
  • Run SQL2025-SSEI-Expr.exe with administrator access.
  • Approve the Windows administrator prompt.
  • Follow the Express installation flow.
  • Keep SQLEXPRESS as the named instance.
  • Complete the installation.
  • Return to SQL Server Services in SQL Server Configuration Manager.

You should now see SQL Server (SQLEXPRESS) with a green running indicator.

The SQL Server Service Is Still Missing?

Confirm that the Express installer reached its completion screen. Restart Windows if the installer requested it.

Ask for targeted help with diagnosing the missing SQL Server service.

A successful SSMS connection proves that your Windows identity can reach the running engine. It also gives every later SQL script one predictable server name.

  • Press the Windows key to open Windows search.
  • Type SQL Server Management Studio.
  • Press Enter to open SSMS.
  • Enter localhost\SQLEXPRESS as the server name.
  • Choose Windows Authentication as the authentication method.
  • Connect to the instance.

That is the database boundary crossed. Object Explorer should show the connected localhost\SQLEXPRESS instance.

SSMS Cannot Connect?

Confirm that the server name contains one backslash. Check that SQL Server Configuration Manager still shows a green running indicator.

Ask for help with troubleshooting the local SSMS connection.

Install Python 3.15 and isolate the project

A virtual environment keeps this project's packages separate from other Python work. The Python launcher can target 3.15 directly without replacing another installed runtime.

  • Press the Windows key to open Windows search.
  • Type PowerShell.
  • Press Enter to open PowerShell.
  • Check for Python 3.15.0 by running this command:
py -V:3.15 -V

What Does This Command Do?

The version selector asks the Python launcher for its registered 3.15 runtime. The final version flag prints the exact release.

✔️ I see Python 3.15.0

Python 3.15.0 is ready for the project environment. Keep this PowerShell window open.

ⓧ I see an older version

The required runtime can sit beside your existing Python installation. The launcher selects it through the 3.15 runtime tag.

  • Install Python 3.15 by running this command:
py install 3.15

What Does This Command Do?

Python Install Manager downloads the requested 3.15 runtime. The launcher registers it for version-specific commands.

The installation should finish with Python 3.15.0 available through the launcher.

ⓧ Command not found

The Python launcher is unavailable in this PowerShell session. Install Python Install Manager before adding the required runtime.

py install 3.15

What Does This Command Do?

Python Install Manager downloads the requested runtime. It registers Python 3.15 with the launcher.

The installation should finish with Python 3.15.0 available through the launcher.

Python 3.15 Is Still Unavailable?

Close PowerShell after the installation completes. Open a fresh PowerShell session so the launcher registration is available.

Ask for help with diagnosing the Python launcher.

The project environment belongs inside one named workspace. Placing that workspace on your Desktop makes it easy to find from File Explorer and Visual Studio Code.

  • Press the Windows key to open Windows search.
  • Type File Explorer.
  • Press Enter to open File Explorer.
  • Select Desktop in the navigation pane.
  • Create a folder named service-operations-control-tower.

The new service-operations-control-tower folder should now be visible on your Desktop.

  • Press the Windows key to open Windows search.
  • Type Visual Studio Code.
  • Press Enter to open Visual Studio Code.
  • Select File from the top menu.
  • Select Open Folder....
  • Select the service-operations-control-tower folder from your Desktop.
  • Confirm the folder selection.

Visual Studio Code should show service-operations-control-tower at the top of the Explorer sidebar.

  • Select Terminal from the top menu.
  • Select New Terminal.
  • Create .venv with Python 3.15 by running this command:
py -V:3.15 -m venv .venv

What Does This Command Do?

The launcher selects Python 3.15. The environment module creates an isolated interpreter inside .venv.

The .venv folder should now appear inside the workspace in VS Code.

The Virtual Environment Was Not Created?

Confirm that VS Code opened the service-operations-control-tower folder. Rerun the runtime check if the launcher cannot select Python 3.15.

Ask for help with creating the virtual environment.

The first package installation can take a few minutes while dependencies download. Keep the terminal open until the installation completes.

  • Install mssql-python==1.16.0 inside .venv by running this command:
.\.venv\Scripts\python.exe -m pip install "mssql-python==1.16.0"

What Does This Command Do?

The command invokes the Python interpreter stored inside .venv. The exact version pin keeps the project on the tested 1.16.0 release.

The installation output should finish without an error. The installed package is now isolated inside .venv.

The Package Installation Failed?

Confirm that the command begins with the interpreter inside .venv. Check the internet connection before retrying the download.

Ask for help with diagnosing the package installation.

Prepare the portfolio workspace

Separate folders keep database scripts, report files, documentation, and automation evidence easy to trace. This structure becomes the local home for the complete control tower.

  • Select service-operations-control-tower in the VS Code Explorer sidebar.
  • Create the sql folder with the New Folder action.
  • Create the powerbi folder with the New Folder action.
  • Create the docs folder with the New Folder action.
  • Create the python folder with the New Folder action.

The Explorer sidebar should now show sql, powerbi, docs, and python inside service-operations-control-tower.

Power BI Desktop stays open for the reporting work that follows. SSMS and VS Code already hold the two other parts of the workflow.

  • Press the Windows key to open Windows search.
  • Type Power BI Desktop.
  • Press Enter to open Power BI Desktop.
  • Leave Power BI Desktop open.
  • Switch back to SSMS.
  • Confirm that Object Explorer shows localhost\SQLEXPRESS.
  • Return to the PowerShell terminal in VS Code.

Before the final check, which exact Python release do you expect the launcher to report?

  • Verify the registered Python runtime by running this command:
py -V:3.15 -V

What Does This Command Confirm?

The command bypasses other registered Python releases. It prints the exact runtime selected for this project.

PowerShell should print Python 3.15.0. Together with the connected SSMS instance and the four workspace folders, this confirms the local analytics stack is ready.

Your database engine, Python environment, and portfolio workspace are ready. Next, you will load the operational source and expose the double-counting trap.

Expose the Double-Counting Trap

Your local analytics stack is ready. The connection in SQL Server Management Studio can now host the operational source for your control tower.

A ticket can accumulate several status changes during its lifetime. A naive one-to-many join can multiply those ticket rows without making the problem obvious.

In this step, get ready to:
  • Create the operational database with deterministic ticket data.
  • Confirm that the ticket source contains one row per ticket.
  • Run a naive history join that exposes row inflation.
Create the operational source

The source represents a small support operation with teams, priorities, tickets, and status changes. Fixed sample data gives every later reconciliation the same baseline.

  • In SSMS from earlier, create a blank query using the existing connection to localhost\SQLEXPRESS.
  • Save the blank query as 01_source_and_naive.sql inside the workspace's sql folder.

The saved file now belongs to your service-operations-control-tower workspace.

  • Add the database and reporting schema foundation to sql\01_source_and_naive.sql by copying this code:
USE master;
GO

CREATE DATABASE ServiceOpsPortfolio;
GO

USE ServiceOpsPortfolio;
GO

CREATE SCHEMA reporting;
GO

What Does This SQL Establish?

  • The first batch switches to master so SQL Server can create the new database.
  • The second batch switches the query context to ServiceOpsPortfolio.
  • The final batch creates the reporting schema that later holds the reporting views.
  • Save sql\01_source_and_naive.sql.
  • Select only the SQL batch you just added.
  • Execute the selected batch in SSMS.

The batch completes without an error. The ServiceOpsPortfolio database now contains the reporting schema.

Database Setup Batch Failed?

  • Confirm that the query is connected to localhost\SQLEXPRESS with Windows Authentication.
  • Check whether an earlier attempt already created ServiceOpsPortfolio.
  • Ask for targeted help with diagnosing the database setup batch.
  • Append the team table definition to sql\01_source_and_naive.sql by copying this code:
CREATE TABLE dbo.Team
(
    TeamID INT NOT NULL PRIMARY KEY,
    TeamName NVARCHAR(50) NOT NULL,
    ManagerName NVARCHAR(80) NOT NULL,
    Region NVARCHAR(30) NOT NULL
);
GO

What Does the Team Table Store?

  • Each TeamID identifies one support team.
  • The descriptive columns hold the team's name, manager, and region.
  • The primary key prevents two team rows from sharing the same identifier.
  • Save sql\01_source_and_naive.sql.
  • Select only the new CREATE TABLE dbo.Team batch.
  • Execute the selected batch in SSMS.

SSMS completes the batch without an error. The database now has a table for four support teams.

Team Table Was Not Created?

  • Confirm that the current database context is ServiceOpsPortfolio.
  • Check that the selected text includes the closing parenthesis and final GO.
  • Ask for help with diagnosing the team table batch.
  • Append the priority table definition by copying this code below the team table:
CREATE TABLE dbo.Priority
(
    PriorityID INT NOT NULL PRIMARY KEY,
    PriorityName NVARCHAR(20) NOT NULL,
    SLATargetHours INT NOT NULL
);
GO

What Does the Priority Table Store?

  • Each PriorityID identifies one priority level.
  • The SLATargetHours value defines the resolution target for that level.
  • These targets later support consistent SLA calculations.
  • Save sql\01_source_and_naive.sql.
  • Select only the new CREATE TABLE dbo.Priority batch.
  • Execute the selected batch in SSMS.

The priority table is now ready to hold four SLA levels.

Priority Table Batch Failed?

  • Confirm that dbo.Priority has not already been created by an earlier execution.
  • Check that every column definition ends with the punctuation shown above.
  • Ask for help with diagnosing the priority table batch.
  • Append the ticket table definition by copying this code below the priority table:
CREATE TABLE dbo.Ticket
(
    TicketID INT NOT NULL PRIMARY KEY,
    TeamID INT NOT NULL,
    PriorityID INT NOT NULL,
    Title NVARCHAR(120) NOT NULL,
    Category NVARCHAR(40) NOT NULL,
    Channel NVARCHAR(20) NOT NULL,
    OpenedAt DATETIME2(0) NOT NULL,
    ClosedAt DATETIME2(0) NULL
);
GO

What Does the Ticket Table Represent?

  • Each TicketID identifies one support ticket.
  • The team and priority identifiers connect each ticket to its operational owners.
  • The timestamps capture when the ticket opened and when it closed.
  • Save sql\01_source_and_naive.sql.
  • Select only the new CREATE TABLE dbo.Ticket batch.
  • Execute the selected batch in SSMS.

The ticket table is ready. Its primary key enforces one stored row for each ticket identifier.

Ticket Table Batch Failed?

  • Confirm that the query remains in the ServiceOpsPortfolio database.
  • Check the commas between the ticket column definitions.
  • Ask for help with diagnosing the ticket table batch.
  • Append the status-history table definition by copying this code below the ticket table:
CREATE TABLE dbo.TicketStatusHistory
(
    StatusHistoryID INT NOT NULL PRIMARY KEY,
    TicketID INT NOT NULL,
    StatusName NVARCHAR(30) NOT NULL,
    StatusChangedAt DATETIME2(0) NOT NULL
);
GO

Why Keep Status History Separate?

  • Each row records one status change for a ticket.
  • One TicketID can therefore appear in several history rows.
  • The timestamp preserves the order of those changes.
  • Save sql\01_source_and_naive.sql.
  • Select only the new CREATE TABLE dbo.TicketStatusHistory batch.
  • Execute the selected batch in SSMS.

The four source tables now exist. The ticket table and history table preserve two different row meanings.

History Table Batch Failed?

  • Check that StatusHistoryID is the primary key.
  • Confirm that StatusChangedAt uses the complete data type shown above.
  • Ask for help with diagnosing the history table batch.
  • Append the four team rows by copying this code below the table definitions:
INSERT INTO dbo.Team (TeamID, TeamName, ManagerName, Region)
VALUES
    (1, N'Payments', N'Aisha Khan', N'Central'),
    (2, N'Identity', N'Lewis Grant', N'North'),
    (3, N'Data Platform', N'Mairi Campbell', N'East'),
    (4, N'Workplace', N'Ross Stewart', N'West');
GO

What Does This Data Add?

  • The rows create four support teams with stable identifiers.
  • The manager and region values provide descriptive fields for later grouping.
  • Fixed values keep the project results reproducible.
  • Save sql\01_source_and_naive.sql.
  • Select only the new INSERT INTO dbo.Team batch.
  • Execute the selected batch in SSMS.

The batch completes successfully. Four team rows now support the ticket data.

Team Rows Were Not Inserted?

  • Confirm that dbo.Team was created before this insert.
  • Check whether the same four primary-key values were inserted during an earlier attempt.
  • Ask for help with diagnosing the team insert.
  • Append the four priority rows by copying this code below the team insert:
INSERT INTO dbo.Priority (PriorityID, PriorityName, SLATargetHours)
VALUES
    (1, N'Critical', 4),
    (2, N'High', 8),
    (3, N'Medium', 24),
    (4, N'Low', 48);
GO

What Do the Priority Rows Define?

  • The rows define four operational priority levels.
  • Each level carries a fixed SLA target measured in hours.
  • The targets range from critical work to low-priority work.
  • Save sql\01_source_and_naive.sql.
  • Select only the new INSERT INTO dbo.Priority batch.
  • Execute the selected batch in SSMS.

Four priority rows now provide the SLA targets used by the ticket source.

Priority Rows Were Not Inserted?

  • Confirm that dbo.Priority exists in ServiceOpsPortfolio.
  • Check whether an earlier execution already inserted the same identifiers.
  • Ask for help with diagnosing the priority insert.
  • Append the twelve operational tickets by copying this code below the priority insert:
INSERT INTO dbo.Ticket
    (TicketID, TeamID, PriorityID, Title, Category, Channel, OpenedAt, ClosedAt)
VALUES
    (101, 1, 1, N'Payment gateway timeout', N'Payments', N'Portal', '2026-10-15T08:00:00', '2026-10-15T15:00:00'),
    (102, 2, 2, N'Account locked', N'Access', N'Phone', '2026-10-15T09:00:00', '2026-10-15T13:00:00'),
    (103, 3, 3, N'Export job running slowly', N'Reporting', N'Portal', '2026-10-16T08:00:00', NULL),
    (104, 1, 2, N'Duplicate invoice created', N'Billing', N'Email', '2026-10-16T10:00:00', '2026-10-16T15:00:00'),
    (105, 4, 1, N'Mobile application crash', N'Endpoint', N'Phone', '2026-10-18T07:00:00', NULL),
    (106, 2, 4, N'Password reset request', N'Access', N'Portal', '2026-10-17T08:00:00', '2026-10-17T09:00:00'),
    (107, 3, 2, N'Data synchronisation delay', N'Integration', N'Email', '2026-10-18T09:00:00', NULL),
    (108, 1, 3, N'Refund not received', N'Payments', N'Phone', '2026-10-17T11:00:00', '2026-10-18T15:00:00'),
    (109, 3, 1, N'Operations dashboard blank', N'Reporting', N'Portal', '2026-10-19T08:00:00', NULL),
    (110, 2, 2, N'New user access request', N'Access', N'Email', '2026-10-19T09:00:00', NULL),
    (111, 4, 3, N'API returns rate-limit errors', N'Integration', N'Portal', '2026-10-18T12:00:00', NULL),
    (112, 4, 4, N'Printer setup assistance', N'Endpoint', N'Phone', '2026-10-19T10:00:00', NULL);
GO

What Does the Ticket Data Cover?

  • The data creates twelve tickets across all four teams.
  • Some tickets have closing timestamps.
  • The remaining tickets use NULL to represent work that is still open.
  • Save sql\01_source_and_naive.sql.
  • Select only the new INSERT INTO dbo.Ticket batch.
  • Execute the selected batch in SSMS.

The batch completes successfully. The operational source now contains twelve distinct tickets.

Ticket Rows Were Not Inserted?

  • Check that every ticket row contains eight values.
  • Confirm that the date values retain their single quotation marks.
  • Ask for help with diagnosing the ticket insert.
  • Append the status history by copying this code below the ticket insert:
INSERT INTO dbo.TicketStatusHistory
    (StatusHistoryID, TicketID, StatusName, StatusChangedAt)
VALUES
    (1, 101, N'Open', '2026-10-15T08:00:00'),
    (2, 101, N'In Progress', '2026-10-15T08:15:00'),
    (3, 101, N'Closed', '2026-10-15T15:00:00'),
    (4, 102, N'Open', '2026-10-15T09:00:00'),
    (5, 102, N'In Progress', '2026-10-15T09:20:00'),
    (6, 102, N'Resolved', '2026-10-15T13:00:00'),
    (7, 103, N'Open', '2026-10-16T08:00:00'),
    (8, 103, N'In Progress', '2026-10-16T09:00:00'),
    (9, 104, N'Open', '2026-10-16T10:00:00'),
    (10, 104, N'Closed', '2026-10-16T15:00:00'),
    (11, 105, N'Open', '2026-10-18T07:00:00'),
    (12, 106, N'Open', '2026-10-17T08:00:00'),
    (13, 106, N'In Progress', '2026-10-17T08:15:00'),
    (14, 106, N'Closed', '2026-10-17T09:00:00'),
    (15, 107, N'Open', '2026-10-18T09:00:00'),
    (16, 107, N'Pending', '2026-10-18T10:00:00'),
    (17, 108, N'Open', '2026-10-17T11:00:00'),
    (18, 108, N'In Progress', '2026-10-17T13:00:00'),
    (19, 108, N'Resolved', '2026-10-18T15:00:00'),
    (20, 109, N'Open', '2026-10-19T08:00:00'),
    (21, 109, N'In Progress', '2026-10-19T08:30:00'),
    (22, 110, N'Open', '2026-10-19T09:00:00'),
    (23, 111, N'Open', '2026-10-18T12:00:00'),
    (24, 112, N'Open', '2026-10-19T10:00:00');
GO

What Does the History Data Reveal?

  • The data creates twenty-four recorded status changes.
  • Several tickets have more than one history row.
  • The history table therefore has a finer grain than the ticket table.
  • Save sql\01_source_and_naive.sql.
  • Select only the new INSERT INTO dbo.TicketStatusHistory batch.
  • Execute the selected batch in SSMS.

The final insert completes successfully. Your source now contains the ticket events needed to expose the reporting trap.

History Rows Were Not Inserted?

  • Check that every history row contains four values.
  • Confirm that dbo.TicketStatusHistory exists before the insert runs.
  • Ask for help with diagnosing the history insert.
Establish the source grain

An operational data grain states what one row represents. The grain of dbo.Ticket is one row per support ticket.

  • Append the source ticket count to sql\01_source_and_naive.sql by copying this query:
SELECT COUNT(*) AS SourceTicketRows
FROM dbo.Ticket;

What Does This Query Check?

  • The query counts the physical rows in dbo.Ticket.
  • The alias SourceTicketRows labels the baseline clearly.
  • This baseline becomes the trusted ticket total for the join comparison.
  • Save sql\01_source_and_naive.sql.
  • Select only the new source-count query.

Before you execute the query, how many rows should one-row-per-ticket data return?

  • Execute the selected source-count query in SSMS.

Your result shows 12 under SourceTicketRows. This confirms that dbo.Ticket contains one row for each of the twelve tickets.

Source Count Is Not 12?

  • Confirm that the selected query reads from dbo.Ticket.
  • Check that the ticket insert completed exactly once.
  • Ask for help with reconciling the source ticket count.
Run the naive join

The ticket identifier connects each ticket to its status changes. The join returns one result row for every matching ticket-history pair.

  • Append the naive join checks by copying these queries below the source count:
SELECT COUNT(*) AS NaiveJoinedRows
FROM dbo.Ticket AS t
INNER JOIN dbo.TicketStatusHistory AS h
    ON h.TicketID = t.TicketID;

SELECT
    t.TicketID,
    COUNT(*) AS JoinedRowsPerTicket
FROM dbo.Ticket AS t
INNER JOIN dbo.TicketStatusHistory AS h
    ON h.TicketID = t.TicketID
GROUP BY t.TicketID
HAVING COUNT(*) > 1
ORDER BY t.TicketID;
GO

What Do These Queries Test?

  • The first query counts every row produced by the ticket-to-history join.
  • The second query groups the result by TicketID.
  • The HAVING condition keeps tickets that produced more than one joined row.
  • Save sql\01_source_and_naive.sql.
  • Select only the two new naive join queries.

Before you execute these queries, do you expect the joined count to stay aligned with the source count?

  • Execute the selected naive join queries in SSMS.

The first result set shows 24 under NaiveJoinedRows. The grouped result lists the ticket identifiers that produced multiple joined rows.

Why Did the Ticket Count Double?

Each row in dbo.Ticket represents one ticket. Each matching row in dbo.TicketStatusHistory represents one status change.

The join repeats a ticket once for every matching change. COUNT(*) therefore measures joined ticket-history pairs, which makes it invalid as a ticket measure.

Naive Join Count Is Not 24?

  • Confirm that the join connects h.TicketID to t.TicketID.
  • Check that all twenty-four history rows were inserted exactly once.
  • Ask for help with diagnosing the naive join result.

✔️ Awesome, I've got everything!

Great work. Save sql\01_source_and_naive.sql so the complete source setup and both validation checks stay together.

ⓧ I'd like to double check the full code

USE master;
GO

CREATE DATABASE ServiceOpsPortfolio;
GO

USE ServiceOpsPortfolio;
GO

CREATE SCHEMA reporting;
GO

CREATE TABLE dbo.Team
(
    TeamID INT NOT NULL PRIMARY KEY,
    TeamName NVARCHAR(50) NOT NULL,
    ManagerName NVARCHAR(80) NOT NULL,
    Region NVARCHAR(30) NOT NULL
);
GO

CREATE TABLE dbo.Priority
(
    PriorityID INT NOT NULL PRIMARY KEY,
    PriorityName NVARCHAR(20) NOT NULL,
    SLATargetHours INT NOT NULL
);
GO

CREATE TABLE dbo.Ticket
(
    TicketID INT NOT NULL PRIMARY KEY,
    TeamID INT NOT NULL,
    PriorityID INT NOT NULL,
    Title NVARCHAR(120) NOT NULL,
    Category NVARCHAR(40) NOT NULL,
    Channel NVARCHAR(20) NOT NULL,
    OpenedAt DATETIME2(0) NOT NULL,
    ClosedAt DATETIME2(0) NULL
);
GO

CREATE TABLE dbo.TicketStatusHistory
(
    StatusHistoryID INT NOT NULL PRIMARY KEY,
    TicketID INT NOT NULL,
    StatusName NVARCHAR(30) NOT NULL,
    StatusChangedAt DATETIME2(0) NOT NULL
);
GO

INSERT INTO dbo.Team (TeamID, TeamName, ManagerName, Region)
VALUES
    (1, N'Payments', N'Aisha Khan', N'Central'),
    (2, N'Identity', N'Lewis Grant', N'North'),
    (3, N'Data Platform', N'Mairi Campbell', N'East'),
    (4, N'Workplace', N'Ross Stewart', N'West');
GO

INSERT INTO dbo.Priority (PriorityID, PriorityName, SLATargetHours)
VALUES
    (1, N'Critical', 4),
    (2, N'High', 8),
    (3, N'Medium', 24),
    (4, N'Low', 48);
GO

INSERT INTO dbo.Ticket
    (TicketID, TeamID, PriorityID, Title, Category, Channel, OpenedAt, ClosedAt)
VALUES
    (101, 1, 1, N'Payment gateway timeout', N'Payments', N'Portal', '2026-10-15T08:00:00', '2026-10-15T15:00:00'),
    (102, 2, 2, N'Account locked', N'Access', N'Phone', '2026-10-15T09:00:00', '2026-10-15T13:00:00'),
    (103, 3, 3, N'Export job running slowly', N'Reporting', N'Portal', '2026-10-16T08:00:00', NULL),
    (104, 1, 2, N'Duplicate invoice created', N'Billing', N'Email', '2026-10-16T10:00:00', '2026-10-16T15:00:00'),
    (105, 4, 1, N'Mobile application crash', N'Endpoint', N'Phone', '2026-10-18T07:00:00', NULL),
    (106, 2, 4, N'Password reset request', N'Access', N'Portal', '2026-10-17T08:00:00', '2026-10-17T09:00:00'),
    (107, 3, 2, N'Data synchronisation delay', N'Integration', N'Email', '2026-10-18T09:00:00', NULL),
    (108, 1, 3, N'Refund not received', N'Payments', N'Phone', '2026-10-17T11:00:00', '2026-10-18T15:00:00'),
    (109, 3, 1, N'Operations dashboard blank', N'Reporting', N'Portal', '2026-10-19T08:00:00', NULL),
    (110, 2, 2, N'New user access request', N'Access', N'Email', '2026-10-19T09:00:00', NULL),
    (111, 4, 3, N'API returns rate-limit errors', N'Integration', N'Portal', '2026-10-18T12:00:00', NULL),
    (112, 4, 4, N'Printer setup assistance', N'Endpoint', N'Phone', '2026-10-19T10:00:00', NULL);
GO

INSERT INTO dbo.TicketStatusHistory
    (StatusHistoryID, TicketID, StatusName, StatusChangedAt)
VALUES
    (1, 101, N'Open', '2026-10-15T08:00:00'),
    (2, 101, N'In Progress', '2026-10-15T08:15:00'),
    (3, 101, N'Closed', '2026-10-15T15:00:00'),
    (4, 102, N'Open', '2026-10-15T09:00:00'),
    (5, 102, N'In Progress', '2026-10-15T09:20:00'),
    (6, 102, N'Resolved', '2026-10-15T13:00:00'),
    (7, 103, N'Open', '2026-10-16T08:00:00'),
    (8, 103, N'In Progress', '2026-10-16T09:00:00'),
    (9, 104, N'Open', '2026-10-16T10:00:00'),
    (10, 104, N'Closed', '2026-10-16T15:00:00'),
    (11, 105, N'Open', '2026-10-18T07:00:00'),
    (12, 106, N'Open', '2026-10-17T08:00:00'),
    (13, 106, N'In Progress', '2026-10-17T08:15:00'),
    (14, 106, N'Closed', '2026-10-17T09:00:00'),
    (15, 107, N'Open', '2026-10-18T09:00:00'),
    (16, 107, N'Pending', '2026-10-18T10:00:00'),
    (17, 108, N'Open', '2026-10-17T11:00:00'),
    (18, 108, N'In Progress', '2026-10-17T13:00:00'),
    (19, 108, N'Resolved', '2026-10-18T15:00:00'),
    (20, 109, N'Open', '2026-10-19T08:00:00'),
    (21, 109, N'In Progress', '2026-10-19T08:30:00'),
    (22, 110, N'Open', '2026-10-19T09:00:00'),
    (23, 111, N'Open', '2026-10-18T12:00:00'),
    (24, 112, N'Open', '2026-10-19T10:00:00');
GO

SELECT COUNT(*) AS SourceTicketRows
FROM dbo.Ticket;

SELECT COUNT(*) AS NaiveJoinedRows
FROM dbo.Ticket AS t
INNER JOIN dbo.TicketStatusHistory AS h
    ON h.TicketID = t.TicketID;

SELECT
    t.TicketID,
    COUNT(*) AS JoinedRowsPerTicket
FROM dbo.Ticket AS t
INNER JOIN dbo.TicketStatusHistory AS h
    ON h.TicketID = t.TicketID
GROUP BY t.TicketID
HAVING COUNT(*) > 1
ORDER BY t.TicketID;
GO

You caught the inflation before it reached the dashboard. Next, you will create a stable one-row-per-ticket reporting layer that preserves trustworthy totals.

Build and Reconcile the Reporting Layer

Your previous SQL Server check exposed a broken ticket grain. That 24-row join cannot become the source for a trustworthy ticket count.

Power BI needs a stable one-row-per-ticket interface. A reporting layer provides that interface before any visuals or measures depend on it.

In this step, get ready to:
  • Create dimension views for teams and priorities.
  • Build a one-row-per-ticket fact view with the latest status and SLA fields.
  • Use reconciliation checks to prove the reporting layer is safe to model.
Create the dimension views

A dimension view presents one unique business entity for filtering or grouping. The team view keeps one row per team.

  • In the file sidebar of Visual Studio Code, select the existing sql folder.
  • Use the new-file control to create 02_reporting_layer.sql inside the sql folder.
  • Add the database context and team view by copying this code into sql\02_reporting_layer.sql:
USE ServiceOpsPortfolio;
GO

CREATE OR ALTER VIEW reporting.DimTeam
AS
SELECT
    TeamID,
    TeamName,
    ManagerName,
    Region
FROM dbo.Team;
GO

What Does This View Do?

  • The opening batch places the script in ServiceOpsPortfolio.
  • The reporting.DimTeam view exposes the descriptive fields used to group ticket results by team.
  • The source is dbo.Team, so the view retains one row per team.
  • Save sql\02_reporting_layer.sql.
  • Switch back to SQL Server Management Studio.
  • Load sql\02_reporting_layer.sql into the connected query editor.
  • Execute the loaded script by selecting Execute.

SSMS should report successful completion. The reporting.DimTeam view now exists in ServiceOpsPortfolio.

Team View Did Not Compile?

Confirm that your SSMS connection still targets localhost\SQLEXPRESS with Windows Authentication. Check that the existing reporting schema has not been renamed.

Ask for help with diagnosing the team view creation.

Priority is another filtering entity. Its SLA target also supplies the threshold used by the ticket fact view.

  • Return to sql\02_reporting_layer.sql in Visual Studio Code.
  • Place your cursor after the final GO line.
  • Add the priority view by copying this code:
CREATE OR ALTER VIEW reporting.DimPriority
AS
SELECT
    PriorityID,
    PriorityName,
    SLATargetHours
FROM dbo.Priority;
GO

What Does This View Do?

  • The reporting.DimPriority view exposes one row for each priority.
  • The SLATargetHours field provides the allowed response window for each priority.
  • A unique PriorityID keeps priority filtering predictable in the future model.
  • Save sql\02_reporting_layer.sql.
  • Switch back to SSMS.
  • Reload sql\02_reporting_layer.sql in the query editor.
  • Execute the updated script by selecting Execute.

SSMS should complete both view statements successfully. Your reporting layer now has one view for each filtering entity.

Priority View Did Not Compile?

Confirm that dbo.Priority contains the columns PriorityID, PriorityName, and SLATargetHours.

Ask for help with diagnosing the priority view creation.

Build the ticket fact view

The ticket fact must preserve one row per TicketID. It also needs the latest status without multiplying each ticket by its full history.

Why Use OUTER APPLY?

The OUTER APPLY section evaluates a latest-status lookup for each ticket. TOP (1) limits that lookup to one history row.

The descending timestamps choose the newest status. The descending history ID resolves ties consistently.

The fact view is one SQL statement. Its first section selects ticket fields and defines the first two operational calculations.

  • Return to sql\02_reporting_layer.sql in Visual Studio Code.
  • Place your cursor after the reporting.DimPriority batch.
  • Paste the first section of reporting.FactTicket by copying this code:
CREATE OR ALTER VIEW reporting.FactTicket
AS
SELECT
    t.TicketID,
    t.TeamID,
    t.PriorityID,
    t.Title,
    t.Category,
    t.Channel,
    t.OpenedAt,
    t.ClosedAt,
    latest_status.StatusName AS CurrentStatus,
    p.SLATargetHours,
    CASE
        WHEN latest_status.StatusName IN (N'Closed', N'Resolved') THEN 0
        ELSE 1
    END AS IsOpen,
    CASE
        WHEN t.ClosedAt IS NULL THEN NULL
        ELSE DATEDIFF(hour, t.OpenedAt, t.ClosedAt)
    END AS ResolutionHours,

What Does This Section Calculate?

  • The selected ticket columns preserve the operational detail needed for analysis.
  • The CurrentStatus field receives the single status chosen later in the statement.
  • The IsOpen field identifies tickets whose latest status is outside the completed states.
  • The ResolutionHours field measures elapsed whole hours for closed tickets.

The second section calculates SLA risk and completes the latest-status lookup. Together, both sections form one view statement.

  • Continue directly after the ResolutionHours comma by copying this code:
    CASE
        WHEN DATEDIFF(
            hour,
            t.OpenedAt,
            CASE
                WHEN t.ClosedAt IS NULL THEN '2026-10-19T12:00:00'
                ELSE t.ClosedAt
            END
        ) > p.SLATargetHours THEN 1
        ELSE 0
    END AS IsSLABreached
FROM dbo.Ticket AS t
INNER JOIN dbo.Priority AS p
    ON p.PriorityID = t.PriorityID
OUTER APPLY
(
    SELECT TOP (1)
        h.StatusName
    FROM dbo.TicketStatusHistory AS h
    WHERE h.TicketID = t.TicketID
    ORDER BY h.StatusChangedAt DESC, h.StatusHistoryID DESC
) AS latest_status;
GO

How Does the View Stay at One Ticket?

  • The IsSLABreached field compares elapsed hours with the priority target.
  • The fixed analysis timestamp keeps open-ticket results deterministic for this sample.
  • The priority join adds one SLA target to each ticket because PriorityID is unique in dbo.Priority.
  • The latest-status lookup returns no more than one history row for each ticket.

Before you execute the view, do you expect its validation count to follow the 12 tickets or the 24 history rows?

  • Save sql\02_reporting_layer.sql.
  • Switch back to SSMS.
  • Reload sql\02_reporting_layer.sql in the query editor.
  • Execute the updated script by selecting Execute.

SSMS should report successful completion for all three views. The reporting fact is now ready for direct reconciliation against the source tickets.

Fact View Did Not Compile?

Check that the first section ends with the comma after ResolutionHours. Confirm that the second section begins with the final CASE expression.

Ask for help with diagnosing the fact view statement.

Reconcile the reporting layer

A successful view creation proves that the SQL is valid. Reconciliation proves that the reporting grain and relationship keys remain trustworthy.

  • Return to sql\02_reporting_layer.sql in Visual Studio Code.
  • Place your cursor after the final fact-view GO line.
  • Add the source-to-fact count check by copying this query:
SELECT
    (SELECT COUNT(*) FROM dbo.Ticket) AS SourceTicketRows,
    (SELECT COUNT(*) FROM reporting.FactTicket) AS ReportingFactRows;

What Does This Check Prove?

The query places the source ticket count beside the reporting fact count. Equal totals show that the latest-status logic preserved the ticket grain.

  • Save sql\02_reporting_layer.sql.
  • Switch back to SSMS.
  • Reload sql\02_reporting_layer.sql in the query editor.
  • Execute the updated script by selecting Execute.

The final result set should show 12 source ticket rows and 12 reporting fact rows. The reporting interface preserves every ticket exactly once by count.

Source and Fact Counts Differ?

Check that TOP (1) remains inside the status lookup. Confirm that the join to dbo.Priority uses PriorityID.

Ask for help with tracing the source-to-fact mismatch.

Matching totals can still hide duplicate keys. The next check tests the uniqueness of TicketID inside the fact view.

  • Return to the end of sql\02_reporting_layer.sql.
  • Add the duplicate-key check by copying this query:
SELECT COUNT(*) AS DuplicateTicketIDs
FROM
(
    SELECT TicketID
    FROM reporting.FactTicket
    GROUP BY TicketID
    HAVING COUNT(*) > 1
) AS duplicate_tickets;

What Does This Check Prove?

The inner query finds ticket IDs that occur more than once. The outer query counts how many duplicated IDs exist.

A result of 0 proves that the fact key is unique.

  • Save sql\02_reporting_layer.sql.
  • Switch back to SSMS.
  • Reload sql\02_reporting_layer.sql in the query editor.
  • Execute the updated script by selecting Execute.

The duplicate result should show 0. Each TicketID appears once in reporting.FactTicket.

Duplicate Ticket IDs Found?

Check the latest-status lookup for a missing TOP (1) clause. Confirm that the history table is referenced inside OUTER APPLY.

Ask for help with finding the duplicated fact rows.

The final check tests whether every fact-side team key has a matching dimension row. This protects team filtering from blank or disconnected groups.

  • Return to the end of sql\02_reporting_layer.sql.
  • Add the orphaned-team check by copying this query:
SELECT COUNT(*) AS OrphanedTeamKeys
FROM reporting.FactTicket AS f
LEFT OUTER JOIN reporting.DimTeam AS d
    ON d.TeamID = f.TeamID
WHERE d.TeamID IS NULL;
GO

What Does This Check Prove?

The left join retains every fact row before looking for missing team matches. The null filter isolates tickets whose TeamID has no dimension row.

A result of 0 proves that every ticket belongs to a valid team.

Your reporting script is now complete. The full-file reference below gives you one comparison point before the final run.

✔️ Awesome, I've got everything!

Your file now contains two dimension views, one ticket fact view, and three reconciliation checks.

ⓧ I'd like to double check the full code

USE ServiceOpsPortfolio;
GO

CREATE OR ALTER VIEW reporting.DimTeam
AS
SELECT
    TeamID,
    TeamName,
    ManagerName,
    Region
FROM dbo.Team;
GO

CREATE OR ALTER VIEW reporting.DimPriority
AS
SELECT
    PriorityID,
    PriorityName,
    SLATargetHours
FROM dbo.Priority;
GO

CREATE OR ALTER VIEW reporting.FactTicket
AS
SELECT
    t.TicketID,
    t.TeamID,
    t.PriorityID,
    t.Title,
    t.Category,
    t.Channel,
    t.OpenedAt,
    t.ClosedAt,
    latest_status.StatusName AS CurrentStatus,
    p.SLATargetHours,
    CASE
        WHEN latest_status.StatusName IN (N'Closed', N'Resolved') THEN 0
        ELSE 1
    END AS IsOpen,
    CASE
        WHEN t.ClosedAt IS NULL THEN NULL
        ELSE DATEDIFF(hour, t.OpenedAt, t.ClosedAt)
    END AS ResolutionHours,
    CASE
        WHEN DATEDIFF(
            hour,
            t.OpenedAt,
            CASE
                WHEN t.ClosedAt IS NULL THEN '2026-10-19T12:00:00'
                ELSE t.ClosedAt
            END
        ) > p.SLATargetHours THEN 1
        ELSE 0
    END AS IsSLABreached
FROM dbo.Ticket AS t
INNER JOIN dbo.Priority AS p
    ON p.PriorityID = t.PriorityID
OUTER APPLY
(
    SELECT TOP (1)
        h.StatusName
    FROM dbo.TicketStatusHistory AS h
    WHERE h.TicketID = t.TicketID
    ORDER BY h.StatusChangedAt DESC, h.StatusHistoryID DESC
) AS latest_status;
GO

SELECT
    (SELECT COUNT(*) FROM dbo.Ticket) AS SourceTicketRows,
    (SELECT COUNT(*) FROM reporting.FactTicket) AS ReportingFactRows;

SELECT COUNT(*) AS DuplicateTicketIDs
FROM
(
    SELECT TicketID
    FROM reporting.FactTicket
    GROUP BY TicketID
    HAVING COUNT(*) > 1
) AS duplicate_tickets;

SELECT COUNT(*) AS OrphanedTeamKeys
FROM reporting.FactTicket AS f
LEFT OUTER JOIN reporting.DimTeam AS d
    ON d.TeamID = f.TeamID
WHERE d.TeamID IS NULL;
GO
  • Save the completed sql\02_reporting_layer.sql file.
  • Switch back to SSMS.
  • Reload the completed script in the query editor.

Before the final execution, what four values should prove that the source grain, fact grain, fact key, and team key all reconcile?

  • Execute the completed script by selecting Execute.

You should see 12 source rows, 12 fact rows, 0 duplicate ticket IDs, and 0 orphaned team keys.

That is the trust gap closed. Your reporting layer now protects the dashboard from the double-counting trap you exposed earlier.

Final Checks Do Not Pass?

Use the failing result to isolate the problem. A count mismatch points to the fact view grain, duplicates point to the latest-status lookup, and orphaned teams point to the team key relationship.

Ask for help with tracing the failed reconciliation check.

  • Capture a screenshot of the SSMS results showing all four reconciliation values.
  • Save the screenshot with your portfolio evidence.

Your SQL reporting interface is reconciled and ready for analysis. Next, you will import these views into Power BI and turn them into a controlled semantic model.

Create the Power BI Semantic Model

The reporting views now reconcile to 12 tickets. Your next goal is a trusted Power BI semantic model that preserves those totals.

SQL reconciliation protects the reporting layer. Ambiguous relationships or uncontrolled aggregations can still change the results inside Power BI, so you will build a star schema with explicit DAX measures.

In this step, get ready to:
  • Import the three reporting views from SQL Server.
  • Create two single-direction one-to-many relationships.
  • Add five explicit measures that reproduce the SQL baselines.
Import the reporting views

Import mode stores a local copy of the reporting data inside the Power BI file. The three reporting views give the model one fact table plus two dimensions.

  • Switch back to Power BI Desktop from earlier.
  • Select Home on the ribbon.
  • Select Get data.
  • Select SQL Server.
  • Enter localhost\SQLEXPRESS in the Server field.
  • Enter ServiceOpsPortfolio in the Database field.
  • Select Import as the data connectivity mode.
  • Confirm the connection settings.

Why Import Mode?

Import mode keeps this small portfolio dataset inside the Power BI file. Report visuals can query the local model without waiting for SQL Server after every interaction.

DirectQuery sends queries back to the source during report use. Import mode keeps this project focused on grain, relationships, and reconciliation.

  • Select Windows in the authentication options.
  • Authenticate with your current Windows account.
  • Select reporting.DimTeam in the Navigator.
  • Select reporting.DimPriority in the Navigator.
  • Select reporting.FactTicket in the Navigator.
  • Select Load.

You should see all three reporting views in the Data pane. This confirms that Power BI can read the reconciled SQL layer.

  • Switch to Model view.
  • Rename reporting.DimTeam to Team through the Name field in the Properties pane.
  • Rename reporting.DimPriority to Priority through the Name field.
  • Rename reporting.FactTicket to Fact Ticket through the Name field.

Good progress. Your model diagram now contains Team, Priority, and Fact Ticket.

Cannot Load the Reporting Views?

Confirm that the server name is localhost\SQLEXPRESS. Confirm that the database name is ServiceOpsPortfolio.

Return to SSMS from earlier to confirm that the SQL Server instance is still connected. Expand the reporting schema to confirm that all three views remain available.

Ask for targeted help with diagnosing the Power BI SQL Server connection.

Build the star relationships

A star schema places the one-row-per-ticket fact at the centre. The dimension tables filter that fact through unique business keys.

Power BI may detect relationships during import. Compare the model diagram with the two relationships this project requires.

✔️ I see both relationships

Keep the relationship from Team[TeamID] to Fact Ticket[TeamID]. Keep the relationship from Priority[PriorityID] to Fact Ticket[PriorityID].

ⓧ A relationship is missing or incorrect

  • Remove any incorrect relationship from the model diagram.
  • Create the team relationship by dragging Team[TeamID] onto Fact Ticket[TeamID].
  • Create the priority relationship by dragging Priority[PriorityID] onto Fact Ticket[PriorityID].
  • Double-click the relationship between Team and Fact Ticket.
  • Set Cardinality to one-to-many with Team on the one side.
  • Set Cross filter direction to Single.
  • Confirm the relationship settings.
  • Apply the same settings to the relationship between Priority and Fact Ticket.
  • Right-click Fact Ticket[TeamID] in Model view.
  • Select Hide in report view.
  • Hide Fact Ticket[PriorityID] from report view in the same way.

Each relationship line should show 1 beside its dimension. It should show * beside Fact Ticket, with filtering directed toward the fact table.

Why Hide the Fact Keys?

The fact-side keys still maintain the relationships. Hiding them keeps report authors focused on descriptive fields from Team and Priority.

This reduces the chance of building a visual from an internal key.

Relationship Shows the Wrong Direction?

Confirm that Team[TeamID] and Priority[PriorityID] contain unique values. Each relationship should filter from its dimension toward Fact Ticket.

Ask for help with correcting the star-schema relationships.

Add explicit measures

Explicit measures lock each business calculation into a named formula. Cards can reuse those formulas without selecting a new aggregation each time.

  • Switch back to the service-operations-control-tower workspace in Visual Studio Code.
  • Create powerbi\measures.dax inside the powerbi folder through the Explorer sidebar.
  • Paste the ticket-count formulas into powerbi\measures.dax from the code below:
Total Tickets =
COUNTROWS('Fact Ticket')

Open Tickets =
CALCULATE(
    [Total Tickets],
    'Fact Ticket'[IsOpen] = 1
)

What Do These Measures Do?

  • The Total Tickets measure counts rows in the one-row-per-ticket fact table.
  • The Open Tickets measure evaluates that trusted count after filtering IsOpen to 1.
  • Save powerbi\measures.dax.
  • Switch back to Power BI Desktop.
  • Right-click Fact Ticket in the Data pane.
  • Select New measure.
  • Paste the Total Tickets formula from powerbi\measures.dax into the formula bar.
  • Press Enter to create the measure.

The Total Tickets measure should appear under Fact Ticket with a calculator icon.

  • Right-click Fact Ticket again.
  • Select New measure.
  • Paste the Open Tickets formula into the formula bar.
  • Press Enter to create the measure.
  • Switch to Report view.
  • Add a card visual to the report canvas.
  • Place Total Tickets in the first card.
  • Place Open Tickets in a second card.

The first two cards should show 12 Total Tickets and 7 Open Tickets.

The Ticket Measures Do Not Match?

Confirm that both measures belong to Fact Ticket. Check that the table name in each formula includes the space.

Ask for help with debugging the ticket-count measures.

  • Return to powerbi\measures.dax in Visual Studio Code.
  • Append the SLA breach measure from the code below:
SLA Breaches =
CALCULATE(
    [Total Tickets],
    'Fact Ticket'[IsSLABreached] = 1
)

What Does This Measure Do?

  • The SLA Breaches measure reuses the reconciled ticket count.
  • The filter keeps fact rows where IsSLABreached equals 1.
  • Save powerbi\measures.dax.
  • Switch back to Power BI Desktop.
  • Create a new measure under Fact Ticket.
  • Paste the SLA Breaches formula into the formula bar.
  • Press Enter to create the measure.
  • Add a third card visual to the report canvas.
  • Place SLA Breaches in the third card.

The third card should show 6 SLA Breaches.

The Breach Card Does Not Show Six?

Check that the measure filters IsSLABreached to 1. Confirm that the card uses the explicit measure instead of the source column.

Ask for help with checking the SLA breach measure.

  • Return to powerbi\measures.dax in Visual Studio Code.
  • Append the rate and resolution measures from the code below:
SLA Breach Rate =
DIVIDE(
    [SLA Breaches],
    [Total Tickets],
    0
)

Avg Resolution Hours =
AVERAGE('Fact Ticket'[ResolutionHours])

What Do These Measures Do?

  • The SLA Breach Rate measure divides breached tickets by all tickets. Its fallback returns 0 when the total has no rows.
  • The Avg Resolution Hours measure calculates the arithmetic mean of populated ResolutionHours values.
  • Save powerbi\measures.dax.
  • Switch back to Power BI Desktop.
  • Right-click Fact Ticket in the Data pane.
  • Select New measure.
  • Paste the SLA Breach Rate formula into the formula bar.
  • Press Enter to create the measure.

The SLA Breach Rate measure should now appear under Fact Ticket.

  • Right-click Fact Ticket again.
  • Select New measure.
  • Paste the Avg Resolution Hours formula into the formula bar.
  • Press Enter to create the measure.
  • Select SLA Breach Rate in the Data pane.
  • Format the measure as a percentage with two decimal places.
  • Select Avg Resolution Hours in the Data pane.
  • Format the measure with two decimal places.

All five measures should now appear under Fact Ticket with calculator icons.

  • Add a fourth card visual to the report canvas.
  • Place SLA Breach Rate in the fourth card.
  • Add a fifth card visual to the report canvas.
  • Place Avg Resolution Hours in the fifth card.

The Rate or Average Looks Wrong?

Confirm that SLA Breach Rate uses the two explicit count measures. Confirm that Avg Resolution Hours references the ResolutionHours column.

Check the measure formatting if the values appear as 0.5 or show too many decimal places.

Ask for help with debugging the rate and average measures.

✔️ Awesome, I've got everything!

Great. Double-check that powerbi\measures.dax is saved.

ⓧ I'd like to double check the full code

Total Tickets =
COUNTROWS('Fact Ticket')

Open Tickets =
CALCULATE(
    [Total Tickets],
    'Fact Ticket'[IsOpen] = 1
)

SLA Breaches =
CALCULATE(
    [Total Tickets],
    'Fact Ticket'[IsSLABreached] = 1
)

SLA Breach Rate =
DIVIDE(
    [SLA Breaches],
    [Total Tickets],
    0
)

Avg Resolution Hours =
AVERAGE('Fact Ticket'[ResolutionHours])

Before you compare the cards, which values do you expect the reconciled model to reproduce?

  • Read the five card values on the report canvas.

You should see 12 Total Tickets, 7 Open Tickets, 6 SLA Breaches, 50.00% SLA Breach Rate, and 9.00 Avg Resolution Hours.

Strong result. Your semantic model now reproduces the reconciled SQL baselines through controlled relationships and explicit measures.

Your trusted model is ready. Next, you will turn these measures into an operations overview with ticket-level detail.

Deliver the Control Tower Experience

Your Power BI semantic model already reproduces the reconciled SQL totals. The next goal is to turn those trusted numbers into a report that helps a manager act.

A correct model still falls short when managers cannot scan risk quickly. It also falls short when supporting detail feels hard to reach.

A responsive report needs performance evidence. This step brings the overview, ticket detail, lineage, and visual timings into one finished deliverable.

In this step, get ready to:
  • Build an executive overview for workload and SLA risk.
  • Add ticket-level detail through drillthrough.
  • Capture report-quality evidence.
Build the executive overview

The overview needs a clear reading order. Managers should see headline workload first before moving into team pressure and breached tickets.

  • Rename the current report page Operations Overview.
  • Add the page title Service Operations Control Tower at the top of the canvas.
  • Position the Total Tickets card first in the top row.
  • Position the Open Tickets card second in the top row.
  • Position the SLA Breach Rate card third in the top row.
  • Position the Avg Resolution Hours card fourth in the top row.
  • Resize the four cards to equal widths.
  • Apply high-contrast labels to the four cards.

You should see the four reconciled measures arranged as one headline row. Their values remain 12, 7, 50.00%, and 9.00.

  • Add a bar chart beneath the cards.
  • Add Team[TeamName] as the bar chart category.
  • Add Open Tickets as the bar chart value.
  • Add a column chart beside the bar chart.
  • Add Priority[PriorityName] as the column chart category.
  • Add SLA Breaches as the column chart value.

The bar chart should separate open workload by team. The column chart should expose which priorities carry the SLA breaches.

  • Add a table beneath the charts.
  • Add Fact Ticket[TicketID] to the table.
  • Add Fact Ticket[Title] to the table.
  • Add Fact Ticket[CurrentStatus] to the table.
  • Add Fact Ticket[ResolutionHours] to the table.
  • Add Fact Ticket[IsSLABreached] to the table's filter settings.
  • Set the table filter to 1.

The exception table should now show only breached tickets. Each row provides the ticket identifier that supports later drillthrough.

  • Add a slicer near the overview title.
  • Add Team[TeamName] to the slicer.
  • Arrange the cards before the charts in the page reading order.
  • Arrange the charts before the exception table in the page reading order.
  • Apply one alert colour to the SLA Breach Rate card.
  • Reuse the alert colour on the SLA Breaches chart.

The page should now move from headline measures to grouped workload before ending with ticket-level exceptions. Selecting a team in the slicer should filter the overview to that team.

Why This Layout Works

The top row answers how much work exists. The charts reveal where pressure is concentrated.

The exception table supplies the evidence behind the risk. One dedicated alert colour keeps SLA warnings easy to recognise.

Overview Values Look Wrong?

  • Confirm that each card uses the existing explicit measure with the matching name.
  • Clear any page filters that were added before the team slicer.
  • Check that the exception table filter uses Fact Ticket[IsSLABreached] with the value 1.

Targeted help is available for diagnosing incorrect overview visuals.

Add drillthrough and lineage

A drillthrough page carries the selected ticket into a focused detail view. A lineage record explains how that detail travels from SQL Server into the report.

  • Add a new report page.
  • Rename the new page Ticket Detail.
  • Place Fact Ticket[TicketID] in the Drillthrough filters well.

Power BI should add a back button to the detail page automatically. The page can now receive one selected ticket from another visual.

  • Keep the automatic back button in place.
  • Add a card to the page.
  • Add Fact Ticket[TicketID] to the card.
  • Add a table beneath the ticket card.
  • Add Fact Ticket[Title] to the detail table.
  • Add Fact Ticket[Category] to the detail table.
  • Add Fact Ticket[Channel] to the detail table.
  • Add Fact Ticket[CurrentStatus] to the detail table.

The page now identifies the selected ticket before showing its descriptive fields. The remaining fields complete its operational timeline and SLA outcome.

  • Add Fact Ticket[OpenedAt] to the detail table.
  • Add Fact Ticket[ClosedAt] to the detail table.
  • Add Fact Ticket[ResolutionHours] to the detail table.
  • Add Fact Ticket[IsSLABreached] to the detail table.

Before you test the path, do you expect the detail page to keep every ticket or only the ticket you select?

  • Return to the Operations Overview page.
  • Right-click one ticket in the exception table.
  • Select Drillthrough.
  • Select Ticket Detail.

You should land on the detail page with one ticket shown in the card and table. The back button should return you to the filtered overview.

Drillthrough Option Missing?

  • Confirm that Fact Ticket[TicketID] is in the detail page's drillthrough filters well.
  • Confirm that the exception table contains Fact Ticket[TicketID].
  • Right-click a populated ticket row instead of the table header.

Targeted help is available for fixing the missing drillthrough path.

The report now proves how a manager reaches supporting detail. The lineage file captures how every displayed value moves through the reporting layers.

  • Return to the Visual Studio Code workspace from earlier.
  • Select the docs folder in the Explorer sidebar.
  • Create lineage.md inside the docs folder with the new-file control.
  • Add the business purpose and data flow to docs\lineage.md by pasting this content:
# Service Operations Control Tower Lineage

## Business purpose

The report helps a support-operations manager identify workload pressure and SLA risk, then reach the ticket detail that explains each exception.

## Data flow

1. Operational source: `dbo.Team`, `dbo.Priority`, `dbo.Ticket`, `dbo.TicketStatusHistory`
2. Reporting interface: `reporting.DimTeam`, `reporting.DimPriority`, `reporting.FactTicket`
3. Power BI model: `Team`, `Priority`, `Fact Ticket`
4. Report pages: `Operations Overview`, `Ticket Detail`

What Does This Record Explain?

  • The business purpose connects the report to the manager's operational decision.
  • The data flow traces each layer from the source tables to the report pages.
  • Save docs\lineage.md.
  • View docs\lineage.md in the Visual Studio Code Markdown preview.

You should see a lineage title followed by the business purpose and a four-stage data flow. The report page names should match the Power BI file.

Markdown Preview Looks Incomplete?

  • Confirm that lineage.md is inside the docs folder.
  • Check that each section heading begins with the same number of hash characters shown above.
  • Save the file before refreshing the preview.

Targeted help is available for fixing the lineage Markdown structure.

  • Place the cursor after 4. Report pages: `Operations Overview`, `Ticket Detail`.
  • Add the grains, validation baselines, and performance prompt by pasting this content:

## Grain

- `dbo.Ticket`: one row per ticket
- `dbo.TicketStatusHistory`: one row per ticket status change
- `reporting.FactTicket`: one row per ticket, with the latest status selected
- `reporting.DimTeam`: one row per team
- `reporting.DimPriority`: one row per priority

## Validation baseline

- Source tickets: 12
- Naive ticket-history join rows: 24
- Reporting fact rows: 12
- Duplicate fact ticket IDs: 0
- Orphaned team keys: 0
- Open tickets: 7
- SLA breaches: 6
- SLA breach rate: 50.00%
- Average resolution hours: 9.00

## Performance check

Record the slowest visual, its DAX query duration, its visual display duration, and any change you made after reviewing Performance Analyzer.

What Does This Evidence Capture?

  • The grain section states what one row represents in every SQL reporting object.
  • The validation baseline preserves the reconciled values used to check the Power BI measures.
  • The performance section provides a place for the final visual timing review.
  • Save docs\lineage.md.
  • Refresh the Visual Studio Code Markdown preview.

You should see five documented grains and nine validation baselines. The final section should prompt you to capture the slowest visual's timings.

Lineage Sections Run Together?

  • Confirm that one blank line separates each heading from its content.
  • Check that every grain and baseline begins with a hyphen.
  • Compare the file with the full reference below.

Targeted help is available for repairing the complete lineage file.

✔️ Awesome, I've got everything!

Great. Double-check that you saved docs\lineage.md.

ⓧ I'd like to double check the full code

# Service Operations Control Tower Lineage

## Business purpose

The report helps a support-operations manager identify workload pressure and SLA risk, then reach the ticket detail that explains each exception.

## Data flow

1. Operational source: `dbo.Team`, `dbo.Priority`, `dbo.Ticket`, `dbo.TicketStatusHistory`
2. Reporting interface: `reporting.DimTeam`, `reporting.DimPriority`, `reporting.FactTicket`
3. Power BI model: `Team`, `Priority`, `Fact Ticket`
4. Report pages: `Operations Overview`, `Ticket Detail`

## Grain

- `dbo.Ticket`: one row per ticket
- `dbo.TicketStatusHistory`: one row per ticket status change
- `reporting.FactTicket`: one row per ticket, with the latest status selected
- `reporting.DimTeam`: one row per team
- `reporting.DimPriority`: one row per priority

## Validation baseline

- Source tickets: 12
- Naive ticket-history join rows: 24
- Reporting fact rows: 12
- Duplicate fact ticket IDs: 0
- Orphaned team keys: 0
- Open tickets: 7
- SLA breaches: 6
- SLA breach rate: 50.00%
- Average resolution hours: 9.00

## Performance check

Record the slowest visual, its DAX query duration, its visual display duration, and any change you made after reviewing Performance Analyzer.
  • Return to Power BI Desktop.
  • Save the report inside the powerbi folder as Service Operations Control Tower.pbix.
  • Return to the Visual Studio Code workspace.
  • Expand the powerbi folder in the Explorer sidebar.

You should see Service Operations Control Tower.pbix inside the powerbi folder. The saved report now contains both report pages and the existing semantic model.

Record the performance check

Power BI's Performance Analyzer records how long each visual takes to complete. Its results separate the DAX query duration from the visual display duration.

  • Return to the Operations Overview page in Power BI Desktop.
  • Select Optimize.
  • Select Performance Analyzer.
  • Select Start recording.

Before you refresh, which overview visual do you expect to take the longest?

  • Refresh the overview visuals by selecting Refresh visuals.

Performance Analyzer should log every visual on the overview page. Each entry should provide timing details for that visual.

  • Expand the slowest visual in Performance Analyzer.
  • Record its name as your slowest visual.
  • Record its DAX query duration as your DAX query duration.
  • Record its visual display duration as your visual display duration.
  • Review the Performance check prompt in docs\lineage.md.
  • Save Service Operations Control Tower.pbix again.

That is the control tower complete. Managers can scan the overview, reach one ticket's evidence, and review recorded performance data.

No Visual Timings Recorded?

  • Confirm that Start recording was selected before the refresh.
  • Confirm that the Operations Overview page was active during the refresh.
  • Select Refresh visuals again while Performance Analyzer remains open.

Targeted help is available for diagnosing missing Performance Analyzer results.

Secret mission

Automate the Quality Gate with Python

Turn your manual reconciliation into a repeatable Python quality gate. Generate a CSV that proves five reporting-layer checks still pass after the data changes.

Clean Up Your Resources

Clean Up Your Resources

Your control tower runs entirely on your Windows PC at a cost of £0. Keeping its local resources creates no ongoing charges.

Resources you used:

  • A local ServiceOpsPortfolio database in SQL Server with four source tables and three reporting views.
  • The local Power BI report named Service Operations Control Tower.pbix.
  • The service-operations-control-tower folder with its sql, powerbi, docs, plus python folders.
  • The local .venv environment with mssql-python version 1.16.0.
  • The generated python\quality_report.csv evidence from your Python quality gate.

Keep everything running

No action needed. Choose this if you plan to demonstrate the control tower again or continue refining it.

  • Keep the ServiceOpsPortfolio database available for future report refreshes.
  • Keep Service Operations Control Tower.pbix as the report you can demonstrate.
  • Keep the service-operations-control-tower folder as your SQL-to-Power-BI portfolio evidence.
  • Keep python\quality_report.csv as evidence that all five quality checks passed.

Your database remains ready for refreshes. Your report and quality evidence remain ready for demonstrations.

Pause - I'll come back to this later

Stop the local SQL Server service to free up memory. Your database files and portfolio artifacts remain stored on your PC.

  • Save Service Operations Control Tower.pbix in Power BI Desktop.
  • Close Power BI Desktop.
  • Return to SQL Server Configuration Manager from earlier.
  • Select SQL Server Services.
  • Select SQL Server (SQLEXPRESS).
  • Stop the selected service.
  • Close SQL Server Management Studio.
  • Close Visual Studio Code.

The SQL Server service now shows a stopped state. Your database and portfolio files remain available for your next session.

Delete - I don't want to use this again

Deleting the database and project files can feel drastic. This option gives you a clean rebuild while leaving shared tools available for other projects.

  • Save Service Operations Control Tower.pbix if you want a final copy before deletion.
  • Close Power BI Desktop.
  • Return to SQL Server Management Studio from earlier.
  • Select the ServiceOpsPortfolio database in Object Explorer.
  • Right-click the selected database.
  • Select Delete.
  • Choose the dialog option that closes current database connections.
  • Confirm the database deletion.

That clears the project-specific database from your SQL Server instance. Its four source tables and three reporting views are removed with it.

  • Open File Explorer from the Windows taskbar.
  • Search your PC for Service Operations Control Tower.pbix.
  • Delete the matching report file.
  • Search your PC for the service-operations-control-tower folder.
  • Delete the matching project folder.

Your report and project folder no longer appear in File Explorer. The deletion also removes docs\lineage.md, python\qa_checks.py, plus python\quality_report.csv.

SQL Server 2025 Express and Python 3.15.0 can support other local projects. Remove them only when you no longer need either runtime.

  • Open Windows installed-app management if no other local project needs these runtimes.
  • Uninstall SQL Server 2025 Express.
  • Uninstall Python 3.15.0.

Nice Work!

Nice Work!

You did it! Your Service Operations Control Tower now turns operational ticket data into a trusted management report with traceable totals.

You've learned how to:

  • Expose one-to-many join inflation in SQL Server. Define a stable one-row-per-ticket grain through reconciled fact and dimension views.
  • Build a Power BI star schema with controlled relationships. Reproduce the SQL baselines through explicit DAX measures.
  • Deliver an operations control tower with workload visuals and SLA exceptions. Connect the overview to ticket-level drillthrough. Capture lineage and performance evidence in docs\lineage.md.
  • Secret Mission: Automate five reconciliation and integrity checks with Python. Generate python\quality_report.csv with five PASS results as repeatable evidence that the reporting layer remains trustworthy.

Ready to quiz yourself? Let's see what you remember.