Build a Trade Surveillance Risk Mart

Build a SQL Server trade risk mart with lineage, quality checks, and Power BI.

Introduction

30 Second Summary

A risk report can look convincing even when one missing record changes the total. Without a clear trail back to the original records, nobody can prove where the number came from or whether anything was lost.

In this project, you will build a reproducible risk-data pipeline in SQL Server plus an interactive Power BI Desktop control room. You will combine normalized trade records with CSV reference data plus JSON surveillance alerts in an auditable dimensional model.

What You'll Build

You will refresh a Risk and Surveillance Control Room that turns the finished mart into filterable exposure metrics backed by visible quality checks.

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

  • A reproducible risk-data pipeline that loads normalized trades plus two secondary feeds into a local risk mart.
  • Auditable controls that keep four deliberate quality failures visible. Data lineage shows every source-to-target path. Reconciliation confirms six source trades match six fact rows with a 0.00 notional difference.
  • A filterable control room for challenging exposure, alerts, and exceptions. A completed Jira sprint maps the delivered work to the pipeline stages.
  • Secret Mission: Add a synthetic counterparty concentration rule that feeds the shared exception framework and raises the exception count after refresh.

Are there any prerequisites?

You should be comfortable with SQL Server and Power BI on a Windows setup that includes SQL Server major version 13 or later, SSMS with Import Flat File Wizard, Power BI Desktop, and Jira.

The script account needs bulk-operation permission, while the SQL Server service needs read access to the project JSON file.

Before We Start

Before the hands-on work begins, lock in the risk this project exposes: broken source linkage and inconsistent identifiers can undermine reporting. You will also define why data lineage and reconciliation make risk results auditable and trustworthy.

Prepare the Risk Data Sprint

Risk evidence falls apart when source files, tool compatibility, or delivery records cannot be reproduced. This step creates a traceable foundation before any data moves.

You will assemble the project files for SQL Server. You will also confirm compatibility through SQL Server Management Studio, open Power BI Desktop, and record the delivery plan in Jira.

In this step, get ready to:
  • Create the project folders and save the six supplied artifacts.
  • Confirm that SQL Server meets the project compatibility requirements.
  • Create four Jira work items inside a sprint backlog.
Create the Project Folders and Save the Supplied Files

A reproducible pipeline starts with stable source artifacts. The CSV extract represents reference data. The JSON file preserves the surveillance alerts in their supplied structure.

  • Press the Windows key to open your search bar.
  • Type File Explorer.
  • Press Enter to open File Explorer.
  • Enter C:\Users\Public in the address bar.
  • Create a folder named RiskDataPortfolio inside C:\Users\Public.
  • Open the RiskDataPortfolio folder.
  • Create a folder named data inside RiskDataPortfolio.
  • Create a folder named sql inside RiskDataPortfolio.

Why Separate Data and SQL?

The data folder holds incoming source extracts. The sql folder holds the repeatable pipeline logic that processes those extracts.

  • Press the Windows key to return to your search bar.
  • Type Notepad.
  • Press Enter to open Notepad.

Preserve the Supplied Contents

Each artifact must match the supplied Complete Code exactly. Preserving the file names also keeps every later source path reproducible.

  • Populate C:\Users\Public\RiskDataPortfolio\data\instrument_risk.csv with the supplied data\instrument_risk.csv contents.
  • Populate C:\Users\Public\RiskDataPortfolio\data\surveillance_alerts.json with the supplied data\surveillance_alerts.json contents.
  • Populate C:\Users\Public\RiskDataPortfolio\sql\01_setup_sources.sql with the supplied sql\01_setup_sources.sql contents.
  • Populate C:\Users\Public\RiskDataPortfolio\sql\02_profile_quality.sql with the supplied sql\02_profile_quality.sql contents.
  • Populate C:\Users\Public\RiskDataPortfolio\sql\03_build_mart.sql with the supplied sql\03_build_mart.sql contents.
  • Populate C:\Users\Public\RiskDataPortfolio\sql\04_secret_mission.sql with the supplied sql\04_secret_mission.sql contents.

✔️ Awesome, I've Got Everything

Your two source files and four SQL scripts are saved in their matching folders. Keep the supplied contents unchanged so later checks produce the designed results.

ⓧ I'd Like to Double Check the Full Code

Compare C:\Users\Public\RiskDataPortfolio\data\instrument_risk.csv with this reference:

InstrumentCode,AssetClass,RiskWeight
BOND-UK-01,Government Bond,0.05
EQ-GB-01,Equity,0.25
FX-EURUSD,Foreign Exchange,0.10
BOND-CORP-01,Corporate Bond,0.20
eq-gb-01,equity,0.30

What Does This File Supply?

This reference extract supplies four standardized instruments plus a case-variant duplicate. That duplicate creates a deliberate quality failure after normalization.

Compare C:\Users\Public\RiskDataPortfolio\data\surveillance_alerts.json with this reference:

[
  {
    "alertId": "A-1001",
    "tradeId": "T-1001",
    "alertType": "SELF_MATCH",
    "severity": "HIGH",
    "alertTimestamp": "2026-09-15T10:15:00"
  },
  {
    "alertId": "A-1002",
    "tradeId": "T-1003",
    "alertType": "RAPID_CANCEL",
    "severity": "MEDIUM",
    "alertTimestamp": "2026-09-16T11:30:00"
  },
  {
    "alertId": "A-1003",
    "tradeId": "T-1006",
    "alertType": "PRICE_SPIKE",
    "severity": "LOW",
    "alertTimestamp": "2026-09-17T14:05:00"
  },
  {
    "alertId": "A-1004",
    "tradeId": "T-9999",
    "alertType": "ORPHAN_ALERT",
    "severity": "HIGH",
    "alertTimestamp": "2026-09-17T15:00:00"
  }
]

What Does This File Supply?

This array supplies four surveillance alerts. The final alert points to the absent trade T-9999, which creates the designed orphan condition.

Compare C:\Users\Public\RiskDataPortfolio\sql\01_setup_sources.sql with this reference:

USE master;
GO

SELECT SERVERPROPERTY('ProductMajorVersion') AS ProductMajorVersion,
       SERVERPROPERTY('ServerName') AS ServerName,
       SERVERPROPERTY('Edition') AS Edition,
       SERVERPROPERTY('ProductVersion') AS ProductVersion;
GO

IF DB_ID(N'RiskPortfolio') IS NULL
BEGIN
    EXEC(N'CREATE DATABASE RiskPortfolio');
END;
GO

USE RiskPortfolio;
GO

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'src')
    EXEC(N'CREATE SCHEMA src');
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'stg')
    EXEC(N'CREATE SCHEMA stg');
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'dw')
    EXEC(N'CREATE SCHEMA dw');
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'audit')
    EXEC(N'CREATE SCHEMA audit');
GO

DROP TABLE IF EXISTS dw.FactTradeRisk;
DROP TABLE IF EXISTS dw.DimInstrument;
DROP TABLE IF EXISTS dw.DimCounterparty;
DROP TABLE IF EXISTS dw.DimDesk;
DROP TABLE IF EXISTS dw.DimDate;
DROP TABLE IF EXISTS audit.ReconciliationCheck;
DROP TABLE IF EXISTS audit.DataLineage;
DROP TABLE IF EXISTS audit.DataQualityException;
DROP TABLE IF EXISTS stg.InstrumentRiskClean;
DROP TABLE IF EXISTS stg.SurveillanceAlert;
DROP TABLE IF EXISTS stg.RawJson;
DROP TABLE IF EXISTS src.Trade;
DROP TABLE IF EXISTS src.TradingDesk;
DROP TABLE IF EXISTS src.Counterparty;
DROP TABLE IF EXISTS dbo.InstrumentRiskRaw;
GO

CREATE TABLE src.Counterparty
(
    CounterpartyID nvarchar(20) NOT NULL PRIMARY KEY,
    CounterpartyName nvarchar(100) NOT NULL,
    CountryCode nchar(2) NOT NULL,
    RiskRating nvarchar(10) NOT NULL,
    ExternalEntityID nvarchar(30) NULL
);

CREATE TABLE src.TradingDesk
(
    DeskCode nvarchar(20) NOT NULL PRIMARY KEY,
    DeskName nvarchar(100) NOT NULL
);

CREATE TABLE src.Trade
(
    TradeID nvarchar(20) NOT NULL PRIMARY KEY,
    TradeDate date NOT NULL,
    DeskCode nvarchar(20) NOT NULL,
    CounterpartyID nvarchar(20) NOT NULL,
    InstrumentCode nvarchar(30) NOT NULL,
    BuySell char(1) NOT NULL,
    Quantity decimal(19,4) NOT NULL,
    PriceUSD decimal(19,4) NOT NULL,
    CONSTRAINT FK_Trade_Desk FOREIGN KEY (DeskCode)
        REFERENCES src.TradingDesk(DeskCode),
    CONSTRAINT FK_Trade_Counterparty FOREIGN KEY (CounterpartyID)
        REFERENCES src.Counterparty(CounterpartyID)
);

INSERT INTO src.Counterparty
    (CounterpartyID, CounterpartyName, CountryCode, RiskRating, ExternalEntityID)
VALUES
    (N'CP-001', N'Northstar Asset Management', N'GB', N'LOW', N'EXT-GB-001'),
    (N'CP-002', N'Meridian Capital Partners', N'US', N'HIGH', N'EXT-US-002'),
    (N'CP-003', N'Harbour Securities', N'SG', N'HIGH', NULL);

INSERT INTO src.TradingDesk (DeskCode, DeskName)
VALUES
    (N'DESK-RATES', N'Rates Trading'),
    (N'DESK-EQUITIES', N'Equities Trading'),
    (N'DESK-FX', N'Foreign Exchange Trading');

INSERT INTO src.Trade
    (TradeID, TradeDate, DeskCode, CounterpartyID, InstrumentCode, BuySell, Quantity, PriceUSD)
VALUES
    (N'T-1001', '2026-09-15', N'DESK-EQUITIES', N'CP-001', N'EQ-GB-01', 'B', 500.0000, 120.0000),
    (N'T-1002', '2026-09-15', N'DESK-RATES', N'CP-002', N'BOND-UK-01', 'B', 200.0000, 980.0000),
    (N'T-1003', '2026-09-16', N'DESK-FX', N'CP-002', N'FX-EURUSD', 'S', 100000.0000, 1.0800),
    (N'T-1004', '2026-09-16', N'DESK-RATES', N'CP-003', N'BOND-CORP-01', 'B', 300.0000, 105.0000),
    (N'T-1005', '2026-09-17', N'DESK-EQUITIES', N'CP-003', N'CRYPTO-X', 'B', 10.0000, 25000.0000),
    (N'T-1006', '2026-09-17', N'DESK-EQUITIES', N'CP-002', N'EQ-GB-01', 'S', 1000.0000, 55.0000);

CREATE TABLE stg.RawJson
(
    RawJsonID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    SourceFile nvarchar(260) NOT NULL,
    Payload nvarchar(max) NOT NULL,
    LoadedAt datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME()
);

INSERT INTO stg.RawJson (SourceFile, Payload)
SELECT N'surveillance_alerts.json',
       CONVERT(nvarchar(max), JsonFile.Payload)
FROM OPENROWSET(
    BULK N'C:\Users\Public\RiskDataPortfolio\data\surveillance_alerts.json',
    SINGLE_CLOB
) AS JsonFile(Payload);

SELECT COUNT(*) AS TradeRows
FROM src.Trade;

SELECT COUNT(*) AS AlertRows
FROM OPENJSON((SELECT TOP (1) Payload FROM stg.RawJson));
GO

What Does This Script Prepare?

This script contains the compatibility check plus the reproducible operational source setup. A later step runs the complete file to create the database and land the raw alert payload.

Compare C:\Users\Public\RiskDataPortfolio\sql\02_profile_quality.sql with this reference:

USE RiskPortfolio;
GO

DROP TABLE IF EXISTS stg.SurveillanceAlert;
DROP TABLE IF EXISTS audit.DataQualityException;
GO

CREATE TABLE stg.SurveillanceAlert
(
    AlertID nvarchar(20) NOT NULL PRIMARY KEY,
    TradeID nvarchar(20) NOT NULL,
    AlertType nvarchar(40) NOT NULL,
    Severity nvarchar(10) NOT NULL,
    AlertTimestamp datetime2(0) NOT NULL
);

INSERT INTO stg.SurveillanceAlert
    (AlertID, TradeID, AlertType, Severity, AlertTimestamp)
SELECT AlertID,
       TradeID,
       UPPER(AlertType),
       UPPER(Severity),
       AlertTimestamp
FROM OPENJSON((SELECT TOP (1) Payload FROM stg.RawJson))
WITH
(
    AlertID nvarchar(20) '$.alertId',
    TradeID nvarchar(20) '$.tradeId',
    AlertType nvarchar(40) '$.alertType',
    Severity nvarchar(10) '$.severity',
    AlertTimestamp datetime2(0) '$.alertTimestamp'
) AS ParsedAlerts;

CREATE TABLE audit.DataQualityException
(
    ExceptionID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    RuleCode nvarchar(40) NOT NULL,
    SourceName nvarchar(100) NOT NULL,
    BusinessKey nvarchar(100) NOT NULL,
    IssueDescription nvarchar(300) NOT NULL,
    DetectedAt datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME()
);

INSERT INTO audit.DataQualityException
    (RuleCode, SourceName, BusinessKey, IssueDescription)
SELECT N'DUPLICATE_INSTRUMENT',
       N'instrument_risk.csv',
       UPPER(LTRIM(RTRIM(InstrumentCode))),
       N'Multiple source rows collapse to the same standardized instrument code.'
FROM dbo.InstrumentRiskRaw
GROUP BY UPPER(LTRIM(RTRIM(InstrumentCode)))
HAVING COUNT(*) > 1;

INSERT INTO audit.DataQualityException
    (RuleCode, SourceName, BusinessKey, IssueDescription)
SELECT DISTINCT
       N'MISSING_INSTRUMENT',
       N'src.Trade',
       t.InstrumentCode,
       N'Trade instrument has no row in the standardized reference extract.'
FROM src.Trade AS t
LEFT JOIN
(
    SELECT DISTINCT UPPER(LTRIM(RTRIM(InstrumentCode))) AS InstrumentCode
    FROM dbo.InstrumentRiskRaw
) AS r
    ON UPPER(LTRIM(RTRIM(t.InstrumentCode))) = r.InstrumentCode
WHERE r.InstrumentCode IS NULL;

INSERT INTO audit.DataQualityException
    (RuleCode, SourceName, BusinessKey, IssueDescription)
SELECT N'ORPHAN_ALERT',
       N'surveillance_alerts.json',
       a.TradeID,
       N'Alert references a trade that is absent from the operational source.'
FROM stg.SurveillanceAlert AS a
LEFT JOIN src.Trade AS t
    ON a.TradeID = t.TradeID
WHERE t.TradeID IS NULL;

INSERT INTO audit.DataQualityException
    (RuleCode, SourceName, BusinessKey, IssueDescription)
SELECT N'MISSING_EXTERNAL_ID',
       N'src.Counterparty',
       CounterpartyID,
       N'Counterparty is missing the external entity identifier used for linkage.'
FROM src.Counterparty
WHERE ExternalEntityID IS NULL;

SELECT RuleCode,
       SourceName,
       BusinessKey,
       IssueDescription
FROM audit.DataQualityException
ORDER BY ExceptionID;
GO

What Does This Script Prepare?

This script parses the alert array into relational columns. It also records the four designed quality exceptions without hiding them from later reporting.

Compare C:\Users\Public\RiskDataPortfolio\sql\03_build_mart.sql with this reference:

USE RiskPortfolio;
GO

DROP TABLE IF EXISTS dw.FactTradeRisk;
DROP TABLE IF EXISTS dw.DimInstrument;
DROP TABLE IF EXISTS dw.DimCounterparty;
DROP TABLE IF EXISTS dw.DimDesk;
DROP TABLE IF EXISTS dw.DimDate;
DROP TABLE IF EXISTS audit.ReconciliationCheck;
DROP TABLE IF EXISTS audit.DataLineage;
DROP TABLE IF EXISTS stg.InstrumentRiskClean;
GO

CREATE TABLE stg.InstrumentRiskClean
(
    InstrumentCode nvarchar(30) NOT NULL PRIMARY KEY,
    AssetClass nvarchar(50) NOT NULL,
    RiskWeight decimal(5,2) NOT NULL
);

INSERT INTO stg.InstrumentRiskClean
    (InstrumentCode, AssetClass, RiskWeight)
SELECT UPPER(LTRIM(RTRIM(InstrumentCode))),
       MAX(UPPER(LTRIM(RTRIM(AssetClass)))),
       MAX(TRY_CONVERT(decimal(5,2), RiskWeight))
FROM dbo.InstrumentRiskRaw
WHERE TRY_CONVERT(decimal(5,2), RiskWeight) IS NOT NULL
GROUP BY UPPER(LTRIM(RTRIM(InstrumentCode)));

CREATE TABLE dw.DimDate
(
    DateKey int NOT NULL PRIMARY KEY,
    FullDate date NOT NULL UNIQUE,
    CalendarYear int NOT NULL,
    CalendarMonth int NOT NULL,
    CalendarQuarter int NOT NULL
);

CREATE TABLE dw.DimDesk
(
    DeskKey int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    DeskCode nvarchar(20) NOT NULL UNIQUE,
    DeskName nvarchar(100) NOT NULL
);

CREATE TABLE dw.DimCounterparty
(
    CounterpartyKey int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CounterpartyID nvarchar(20) NOT NULL UNIQUE,
    CounterpartyName nvarchar(100) NOT NULL,
    CountryCode nchar(2) NOT NULL,
    RiskRating nvarchar(10) NOT NULL,
    ExternalEntityID nvarchar(30) NULL
);

CREATE TABLE dw.DimInstrument
(
    InstrumentKey int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    InstrumentCode nvarchar(30) NOT NULL UNIQUE,
    AssetClass nvarchar(50) NOT NULL,
    RiskWeight decimal(5,2) NOT NULL
);

INSERT INTO dw.DimDate
    (DateKey, FullDate, CalendarYear, CalendarMonth, CalendarQuarter)
SELECT DISTINCT
       YEAR(TradeDate) * 10000 + MONTH(TradeDate) * 100 + DAY(TradeDate),
       TradeDate,
       YEAR(TradeDate),
       MONTH(TradeDate),
       DATEPART(QUARTER, TradeDate)
FROM src.Trade;

INSERT INTO dw.DimDesk (DeskCode, DeskName)
SELECT DeskCode, DeskName
FROM src.TradingDesk;

INSERT INTO dw.DimCounterparty
    (CounterpartyID, CounterpartyName, CountryCode, RiskRating, ExternalEntityID)
SELECT CounterpartyID,
       CounterpartyName,
       CountryCode,
       UPPER(RiskRating),
       ExternalEntityID
FROM src.Counterparty;

SET IDENTITY_INSERT dw.DimInstrument ON;
INSERT INTO dw.DimInstrument
    (InstrumentKey, InstrumentCode, AssetClass, RiskWeight)
VALUES
    (0, N'UNKNOWN', N'UNKNOWN', 0.00);
SET IDENTITY_INSERT dw.DimInstrument OFF;

INSERT INTO dw.DimInstrument
    (InstrumentCode, AssetClass, RiskWeight)
SELECT InstrumentCode, AssetClass, RiskWeight
FROM stg.InstrumentRiskClean;

CREATE TABLE dw.FactTradeRisk
(
    TradeRiskKey int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    TradeID nvarchar(20) NOT NULL UNIQUE,
    DateKey int NOT NULL,
    DeskKey int NOT NULL,
    CounterpartyKey int NOT NULL,
    InstrumentKey int NOT NULL,
    NotionalUSD decimal(19,2) NOT NULL,
    WeightedExposureUSD decimal(19,2) NOT NULL,
    AlertCount int NOT NULL,
    HighestAlertSeverity nvarchar(10) NOT NULL,
    CONSTRAINT FK_FactTradeRisk_Date FOREIGN KEY (DateKey)
        REFERENCES dw.DimDate(DateKey),
    CONSTRAINT FK_FactTradeRisk_Desk FOREIGN KEY (DeskKey)
        REFERENCES dw.DimDesk(DeskKey),
    CONSTRAINT FK_FactTradeRisk_Counterparty FOREIGN KEY (CounterpartyKey)
        REFERENCES dw.DimCounterparty(CounterpartyKey),
    CONSTRAINT FK_FactTradeRisk_Instrument FOREIGN KEY (InstrumentKey)
        REFERENCES dw.DimInstrument(InstrumentKey)
);

WITH AlertSummary AS
(
    SELECT TradeID,
           COUNT(*) AS AlertCount,
           MAX
           (
               CASE Severity
                   WHEN N'HIGH' THEN 3
                   WHEN N'MEDIUM' THEN 2
                   WHEN N'LOW' THEN 1
                   ELSE 0
               END
           ) AS SeverityRank
    FROM stg.SurveillanceAlert
    GROUP BY TradeID
)
INSERT INTO dw.FactTradeRisk
    (TradeID, DateKey, DeskKey, CounterpartyKey, InstrumentKey,
     NotionalUSD, WeightedExposureUSD, AlertCount, HighestAlertSeverity)
SELECT t.TradeID,
       YEAR(t.TradeDate) * 10000 + MONTH(t.TradeDate) * 100 + DAY(t.TradeDate),
       d.DeskKey,
       c.CounterpartyKey,
       COALESCE(i.InstrumentKey, 0),
       CONVERT(decimal(19,2), ABS(t.Quantity * t.PriceUSD)),
       CONVERT
       (
           decimal(19,2),
           ABS(t.Quantity * t.PriceUSD) * COALESCE(i.RiskWeight, 0.00)
       ),
       COALESCE(a.AlertCount, 0),
       CASE COALESCE(a.SeverityRank, 0)
           WHEN 3 THEN N'HIGH'
           WHEN 2 THEN N'MEDIUM'
           WHEN 1 THEN N'LOW'
           ELSE N'NONE'
       END
FROM src.Trade AS t
INNER JOIN dw.DimDesk AS d
    ON t.DeskCode = d.DeskCode
INNER JOIN dw.DimCounterparty AS c
    ON t.CounterpartyID = c.CounterpartyID
LEFT JOIN dw.DimInstrument AS i
    ON UPPER(LTRIM(RTRIM(t.InstrumentCode))) = i.InstrumentCode
LEFT JOIN AlertSummary AS a
    ON t.TradeID = a.TradeID;

CREATE TABLE audit.DataLineage
(
    LineageID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    SourceObject nvarchar(150) NOT NULL,
    LandingObject nvarchar(150) NOT NULL,
    TargetObject nvarchar(150) NOT NULL,
    TransformationSummary nvarchar(300) NOT NULL
);

INSERT INTO audit.DataLineage
    (SourceObject, LandingObject, TargetObject, TransformationSummary)
VALUES
    (N'src.Trade + src.TradingDesk + src.Counterparty', N'src schema', N'dw.FactTradeRisk + dimensions', N'Join normalized entities, derive notional, and replace operational keys with surrogate keys.'),
    (N'instrument_risk.csv', N'dbo.InstrumentRiskRaw', N'stg.InstrumentRiskClean + dw.DimInstrument', N'Standardize text, detect normalized duplicates, retain the larger synthetic risk weight, and add an unknown member.'),
    (N'surveillance_alerts.json', N'stg.RawJson + stg.SurveillanceAlert', N'dw.FactTradeRisk', N'Preserve the raw payload, parse the array, validate trade linkage, and aggregate alerts to trade grain.');

CREATE TABLE audit.ReconciliationCheck
(
    ReconciliationID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CheckName nvarchar(100) NOT NULL,
    SourceValue decimal(19,2) NOT NULL,
    TargetValue decimal(19,2) NOT NULL,
    Difference decimal(19,2) NOT NULL,
    CheckedAt datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME()
);

INSERT INTO audit.ReconciliationCheck
    (CheckName, SourceValue, TargetValue, Difference)
SELECT N'Trade row count',
       s.SourceValue,
       t.TargetValue,
       s.SourceValue - t.TargetValue
FROM
    (SELECT CONVERT(decimal(19,2), COUNT(*)) AS SourceValue FROM src.Trade) AS s
CROSS JOIN
    (SELECT CONVERT(decimal(19,2), COUNT(*)) AS TargetValue FROM dw.FactTradeRisk) AS t
UNION ALL
SELECT N'Notional total',
       s.SourceValue,
       t.TargetValue,
       s.SourceValue - t.TargetValue
FROM
    (SELECT CONVERT(decimal(19,2), SUM(ABS(Quantity * PriceUSD))) AS SourceValue FROM src.Trade) AS s
CROSS JOIN
    (SELECT CONVERT(decimal(19,2), SUM(NotionalUSD)) AS TargetValue FROM dw.FactTradeRisk) AS t;

SELECT CheckName, SourceValue, TargetValue, Difference
FROM audit.ReconciliationCheck
ORDER BY ReconciliationID;

SELECT TradeID,
       DateKey,
       DeskKey,
       CounterpartyKey,
       InstrumentKey,
       NotionalUSD,
       WeightedExposureUSD,
       AlertCount,
       HighestAlertSeverity
FROM dw.FactTradeRisk
ORDER BY TradeID;
GO

What Does This Script Prepare?

This script standardizes the reference data before loading the dimensional mart. It also creates source-to-target lineage plus row-count and notional reconciliation evidence.

Compare C:\Users\Public\RiskDataPortfolio\sql\04_secret_mission.sql with this reference:

USE RiskPortfolio;
GO

DELETE FROM audit.DataQualityException
WHERE RuleCode = N'CP_CONCENTRATION';

INSERT INTO audit.DataQualityException
    (RuleCode, SourceName, BusinessKey, IssueDescription)
SELECT N'CP_CONCENTRATION',
       N'dw.FactTradeRisk',
       c.CounterpartyID,
       N'Weighted exposure exceeds the synthetic portfolio threshold of 25000.00.'
FROM dw.FactTradeRisk AS f
INNER JOIN dw.DimCounterparty AS c
    ON f.CounterpartyKey = c.CounterpartyKey
GROUP BY c.CounterpartyID
HAVING SUM(f.WeightedExposureUSD) > 25000.00;

SELECT RuleCode,
       SourceName,
       BusinessKey,
       IssueDescription
FROM audit.DataQualityException
WHERE RuleCode = N'CP_CONCENTRATION';
GO

What Does This Script Prepare?

This optional extension reuses the shared exception framework. Its threshold is a synthetic demonstration value rather than a regulatory or production risk limit.

  • Return to the open File Explorer window.
  • Confirm that data contains two files.
  • Confirm that sql contains four files.

You should see instrument_risk.csv plus surveillance_alerts.json in data. You should also see scripts 01 through 04 in sql.

Do the Files End in .txt?

Notepad may append .txt when the intended extension is missing from the saved name. Rename each affected file so its final extension matches .csv, .json, or .sql.

Ask for help checking the saved artifact names:

Verify SQL Server Compatibility and Open the Tools

The alert pipeline depends on OPENJSON. This project requires SQL Server 2016 major version 13 or later plus database compatibility level 130 or greater.

  • Press the Windows key to open your search bar.
  • Type SQL Server Management Studio.
  • Press Enter to open SSMS.
  • Connect SSMS to the SQL Server Database Engine you will use for this project.
  • Open C:\Users\Public\RiskDataPortfolio\sql\01_setup_sources.sql in SSMS.
  • Highlight only this opening version query:
SELECT SERVERPROPERTY('ProductMajorVersion') AS ProductMajorVersion,
       SERVERPROPERTY('ServerName') AS ServerName,
       SERVERPROPERTY('Edition') AS Edition,
       SERVERPROPERTY('ProductVersion') AS ProductVersion;
GO

What Does This Query Check?

  • The ProductMajorVersion result determines whether the server supports this project's JSON parsing path.
  • The ServerName result identifies the instance that Power BI uses later.
  • The edition and full product version provide useful environment evidence.

Before you run the highlighted query, do you expect this SQL Server instance to meet the minimum major version?

  • Run only the highlighted version query with the SSMS query execution control.

You will see one result row containing the server's major version, server name, edition, and full product version.

✔️ My Server Is Compatible

A ProductMajorVersion value of 13 or higher confirms that the server meets the version requirement. That compatibility gate is cleared for the JSON landing work.

The database created later must also use compatibility level 130 or greater for OPENJSON.

ⓧ My Server Is Too Old

A value below 13 cannot run this project's JSON parsing path.

  • Switch to a SQL Server 2016 or later Database Engine instance.
  • Rerun the saved version query against that instance.
  • Continue only after ProductMajorVersion reports 13 or higher.

ⓧ The Query Does Not Run

Confirm that SSMS is connected to the Database Engine instead of an unsupported server type. Confirm that only the opening version query is highlighted.

Still Unable to Run the Check?

Ask for help diagnosing the connection or query execution issue:

  • Press the Windows key to open your search bar.
  • Type Power BI Desktop.
  • Press Enter to open Power BI Desktop.
Create the Jira Sprint Backlog

A migration case study also needs delivery evidence. The sprint connects the source profiling, quality controls, dimensional model, and reporting work to a visible plan.

  • Return to Jira.
  • Select Create (+) in the top navigation.
  • Enter Profile risk sources in the Title field.
  • Complete the required fields for the current space.
  • Select Create.
  • Create Implement data quality controls through the same flow.
  • Create Build dimensional risk mart through the same flow.
  • Create Deliver Power BI control room through the same flow.

Why Track These Four Items?

Each work item maps to a major pipeline stage. The completed sprint later becomes evidence that the technical delivery followed a planned sequence.

  • Select Backlog in the Jira space.
  • Scroll to the Backlog section.
  • Select Create sprint.
  • Move the four new work items into the sprint.

Unable to Create the Sprint?

Sprint actions require the relevant Jira permissions. Adding work items to the sprint also requires permission to schedule and edit them.

Ask for help identifying the missing Jira permission:

Before the final check, do you expect both the technical gate and the delivery gate to pass?

  • Switch back to the open SSMS window.
  • Rerun the highlighted SERVERPROPERTY query.
  • Return to the Jira backlog.
  • Confirm that the sprint contains all four named work items.

You should see ProductMajorVersion report 13 or higher. You should also see all four delivery work items inside the sprint backlog.

Your project now has reproducible inputs, a compatible SQL Server instance, open reporting tools, and a traceable delivery plan. That foundation is ready to support the first landing-layer run.

Your risk data sprint is ready. Next, you will run the source setup and prove that the operational trades plus raw surveillance alerts have landed.

Land Operational Trades and JSON Alerts

Your compatibility check and sprint backlog are ready. The next proof point is a queryable landing layer where risk records can be inspected before any modelling begins.

The SQL Server database will keep operational trades in normalized tables. It will preserve the JSON alert feed as one raw payload.

In this step, get ready to:
  • Create the RiskPortfolio database and its source schemas from the saved SQL script.
  • Compare the normalized trade count with the parsed alert count.
Run the landing script

A landing layer gives each source a stable first stop. The relational records stay in src while the unchanged alert payload lands in stg.

  • Return to the open SSMS session from earlier.
  • Load C:\Users\Public\RiskDataPortfolio\sql\01_setup_sources.sql into a query editor tab with the SSMS file picker.

You should see a query editor tab named 01_setup_sources.sql.

  • Confirm the query editor is connected to the SQL Server instance that passed the earlier version check.

The saved script should match the supplied Complete Code. Use the second tab if you want to compare the entire file before running it.

✔️ Awesome, I've got everything!

Your saved 01_setup_sources.sql file is ready to run.

ⓧ I'd like to double check the full code

USE master;
GO

SELECT SERVERPROPERTY('ProductMajorVersion') AS ProductMajorVersion,
       SERVERPROPERTY('ServerName') AS ServerName,
       SERVERPROPERTY('Edition') AS Edition,
       SERVERPROPERTY('ProductVersion') AS ProductVersion;
GO

IF DB_ID(N'RiskPortfolio') IS NULL
BEGIN
    EXEC(N'CREATE DATABASE RiskPortfolio');
END;
GO

USE RiskPortfolio;
GO

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'src')
    EXEC(N'CREATE SCHEMA src');
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'stg')
    EXEC(N'CREATE SCHEMA stg');
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'dw')
    EXEC(N'CREATE SCHEMA dw');
IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = N'audit')
    EXEC(N'CREATE SCHEMA audit');
GO

DROP TABLE IF EXISTS dw.FactTradeRisk;
DROP TABLE IF EXISTS dw.DimInstrument;
DROP TABLE IF EXISTS dw.DimCounterparty;
DROP TABLE IF EXISTS dw.DimDesk;
DROP TABLE IF EXISTS dw.DimDate;
DROP TABLE IF EXISTS audit.ReconciliationCheck;
DROP TABLE IF EXISTS audit.DataLineage;
DROP TABLE IF EXISTS audit.DataQualityException;
DROP TABLE IF EXISTS stg.InstrumentRiskClean;
DROP TABLE IF EXISTS stg.SurveillanceAlert;
DROP TABLE IF EXISTS stg.RawJson;
DROP TABLE IF EXISTS src.Trade;
DROP TABLE IF EXISTS src.TradingDesk;
DROP TABLE IF EXISTS src.Counterparty;
DROP TABLE IF EXISTS dbo.InstrumentRiskRaw;
GO

CREATE TABLE src.Counterparty
(
    CounterpartyID nvarchar(20) NOT NULL PRIMARY KEY,
    CounterpartyName nvarchar(100) NOT NULL,
    CountryCode nchar(2) NOT NULL,
    RiskRating nvarchar(10) NOT NULL,
    ExternalEntityID nvarchar(30) NULL
);

CREATE TABLE src.TradingDesk
(
    DeskCode nvarchar(20) NOT NULL PRIMARY KEY,
    DeskName nvarchar(100) NOT NULL
);

CREATE TABLE src.Trade
(
    TradeID nvarchar(20) NOT NULL PRIMARY KEY,
    TradeDate date NOT NULL,
    DeskCode nvarchar(20) NOT NULL,
    CounterpartyID nvarchar(20) NOT NULL,
    InstrumentCode nvarchar(30) NOT NULL,
    BuySell char(1) NOT NULL,
    Quantity decimal(19,4) NOT NULL,
    PriceUSD decimal(19,4) NOT NULL,
    CONSTRAINT FK_Trade_Desk FOREIGN KEY (DeskCode)
        REFERENCES src.TradingDesk(DeskCode),
    CONSTRAINT FK_Trade_Counterparty FOREIGN KEY (CounterpartyID)
        REFERENCES src.Counterparty(CounterpartyID)
);

INSERT INTO src.Counterparty
    (CounterpartyID, CounterpartyName, CountryCode, RiskRating, ExternalEntityID)
VALUES
    (N'CP-001', N'Northstar Asset Management', N'GB', N'LOW', N'EXT-GB-001'),
    (N'CP-002', N'Meridian Capital Partners', N'US', N'HIGH', N'EXT-US-002'),
    (N'CP-003', N'Harbour Securities', N'SG', N'HIGH', NULL);

INSERT INTO src.TradingDesk (DeskCode, DeskName)
VALUES
    (N'DESK-RATES', N'Rates Trading'),
    (N'DESK-EQUITIES', N'Equities Trading'),
    (N'DESK-FX', N'Foreign Exchange Trading');

INSERT INTO src.Trade
    (TradeID, TradeDate, DeskCode, CounterpartyID, InstrumentCode, BuySell, Quantity, PriceUSD)
VALUES
    (N'T-1001', '2026-09-15', N'DESK-EQUITIES', N'CP-001', N'EQ-GB-01', 'B', 500.0000, 120.0000),
    (N'T-1002', '2026-09-15', N'DESK-RATES', N'CP-002', N'BOND-UK-01', 'B', 200.0000, 980.0000),
    (N'T-1003', '2026-09-16', N'DESK-FX', N'CP-002', N'FX-EURUSD', 'S', 100000.0000, 1.0800),
    (N'T-1004', '2026-09-16', N'DESK-RATES', N'CP-003', N'BOND-CORP-01', 'B', 300.0000, 105.0000),
    (N'T-1005', '2026-09-17', N'DESK-EQUITIES', N'CP-003', N'CRYPTO-X', 'B', 10.0000, 25000.0000),
    (N'T-1006', '2026-09-17', N'DESK-EQUITIES', N'CP-002', N'EQ-GB-01', 'S', 1000.0000, 55.0000);

CREATE TABLE stg.RawJson
(
    RawJsonID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    SourceFile nvarchar(260) NOT NULL,
    Payload nvarchar(max) NOT NULL,
    LoadedAt datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME()
);

INSERT INTO stg.RawJson (SourceFile, Payload)
SELECT N'surveillance_alerts.json',
       CONVERT(nvarchar(max), JsonFile.Payload)
FROM OPENROWSET(
    BULK N'C:\Users\Public\RiskDataPortfolio\data\surveillance_alerts.json',
    SINGLE_CLOB
) AS JsonFile(Payload);

SELECT COUNT(*) AS TradeRows
FROM src.Trade;

SELECT COUNT(*) AS AlertRows
FROM OPENJSON((SELECT TOP (1) Payload FROM stg.RawJson));
GO

What does this script create?

  • The opening query records the SQL Server version and instance details.
  • The database section creates RiskPortfolio with the src, stg, dw, and audit schemas.
  • The source section creates constrained tables for counterparties, desks, and trades.
  • The insert statements load three counterparties, three desks, and six trades.
  • The staging section uses OPENROWSET to retain the complete alert file in stg.RawJson.
  • The final checks use OPENJSON to count the top-level alert objects without replacing the retained payload.

Before you run the script, what counts do you expect the final two queries to return?

  • Run the complete 01_setup_sources.sql script in the SSMS query editor.

The script should complete without an error. You should see two result sets at the bottom of the query editor.

Did the Raw JSON Load Stop?

Confirm that C:\Users\Public\RiskDataPortfolio\data\surveillance_alerts.json still exists. Confirm that the SQL Server service identity can read that file.

Bulk file access also requires ADMINISTER DATABASE BULK OPERATIONS or ADMINISTER BULK OPERATIONS permission.

Help me diagnose the failed raw JSON load.

Inspect the landed sources

The normalized source uses keys to protect relationships between trades, desks, and counterparties. The raw alert row preserves the original payload for replay and data lineage.

Before you rerun the checks, do you expect the relational source and the alert array to return the same count?

  • Run the final landing checks from the current query editor tab:
SELECT COUNT(*) AS TradeRows
FROM src.Trade;

SELECT COUNT(*) AS AlertRows
FROM OPENJSON((SELECT TOP (1) Payload FROM stg.RawJson));
GO

What Do These Checks Prove?

  • The first query counts normalized operational records in src.Trade.
  • The second query passes the retained payload to OPENJSON.
  • The two results prove that both source formats reached the landing layer.
  • Read the TradeRows value in the first result set.
  • Read the AlertRows value in the second result set.

The first result set should show TradeRows = 6. The second result set should show AlertRows = 4.

That is the first working slice of your risk pipeline. Both source styles now have a queryable landing point.

Seeing Different Row Counts?

A different TradeRows value means the trade insert section did not finish as supplied. Run the complete 01_setup_sources.sql script again.

A different AlertRows value means the retained payload does not contain the four supplied top-level alert objects. Restore surveillance_alerts.json from the Complete Code before rerunning the script.

Help me investigate unexpected landing counts.

Your operational trades and raw alerts are landed. Next, you will add the CSV reference source and expose the designed quality failures.

Import Reference Data and Expose Quality Failures

Your normalized trade tables are now queryable in SQL Server. The raw JSON alert payload has also landed intact.

The CSV reference extract supplies instrument classifications plus synthetic risk weights. Data quality profiling then reveals the broken linkages hidden across these isolated sources.

In this step, get ready to:
  • Import the instrument risk reference with controlled SQL data types.
  • Parse the surveillance alerts into structured rows.
  • Profile cross-source linkages to expose four quality failures.
Import the instrument risk reference

The Import Flat File Wizard in SSMS infers a table structure from the file. The Modify Columns stage lets you replace those guesses with the types required by the profiling script.

  • In your existing SSMS session, expand Databases.
  • Right-click RiskPortfolio.
  • Point to Tasks.
  • Select Import Flat File.
  • On Specify Input File, choose C:\Users\Public\RiskDataPortfolio\data\instrument_risk.csv.
  • Enter InstrumentRiskRaw as the destination table name.
  • Continue to Preview Data.

You'll see the three CSV columns plus five data rows. The case difference between EQ-GB-01 and eq-gb-01 remains visible in this raw landing table.

  • Continue to Modify Columns.

You'll see the wizard's inferred type beside each column. These settings control how SQL Server stores the imported values.

  • Set InstrumentCode to nvarchar(30).
  • Set AssetClass to nvarchar(50).
  • Confirm that both type selections display the required values.
  • Set RiskWeight to decimal(5,2).
  • Confirm that the RiskWeight type selection displays decimal(5,2).
  • Continue to Summary.
  • Select Finish.

You'll reach Results with a green success check. Your five-row reference extract is now available as dbo.InstrumentRiskRaw.

Import wizard does not complete?

Check that the selected file is C:\Users\Public\RiskDataPortfolio\data\instrument_risk.csv. Confirm that the destination table is named InstrumentRiskRaw.

Return to Modify Columns if the summary shows different data types. Help me troubleshoot an SSMS flat-file import

Run the quality profile

The 02_profile_quality.sql script turns the raw JSON payload into structured alert rows with an explicit OPENJSON schema. It then tests business keys across every source.

  • Load C:\Users\Public\RiskDataPortfolio\sql\02_profile_quality.sql into a query window in your existing SSMS session.

Before you run it, do you think the three source types will still agree once their business keys are standardized?

  • Run the complete 02_profile_quality.sql script in SSMS.

The final result set should contain exactly four rows.

  • A DUPLICATE_INSTRUMENT row appears for EQ-GB-01.
  • A MISSING_INSTRUMENT row appears for CRYPTO-X.
  • An ORPHAN_ALERT row appears for T-9999.
  • A MISSING_EXTERNAL_ID row appears for CP-003.

That is the profiling layer working. It has converted hidden cross-source inconsistencies into visible audit evidence.

Do you see fewer than four rows?

Confirm that dbo.InstrumentRiskRaw contains the five imported CSV rows. A different destination table name prevents the reference checks from running against the expected object.

Confirm that you ran the complete script through its final SELECT statement. Help me diagnose missing quality exceptions

  • Compare your saved script with the full-file reference below.

✔️ Awesome, I've got everything!

Your saved script is ready to use for the final review.

ⓧ I'd like to double check the full code

USE RiskPortfolio;
GO

DROP TABLE IF EXISTS stg.SurveillanceAlert;
DROP TABLE IF EXISTS audit.DataQualityException;
GO

CREATE TABLE stg.SurveillanceAlert
(
    AlertID nvarchar(20) NOT NULL PRIMARY KEY,
    TradeID nvarchar(20) NOT NULL,
    AlertType nvarchar(40) NOT NULL,
    Severity nvarchar(10) NOT NULL,
    AlertTimestamp datetime2(0) NOT NULL
);

INSERT INTO stg.SurveillanceAlert
    (AlertID, TradeID, AlertType, Severity, AlertTimestamp)
SELECT AlertID,
       TradeID,
       UPPER(AlertType),
       UPPER(Severity),
       AlertTimestamp
FROM OPENJSON((SELECT TOP (1) Payload FROM stg.RawJson))
WITH
(
    AlertID nvarchar(20) '$.alertId',
    TradeID nvarchar(20) '$.tradeId',
    AlertType nvarchar(40) '$.alertType',
    Severity nvarchar(10) '$.severity',
    AlertTimestamp datetime2(0) '$.alertTimestamp'
) AS ParsedAlerts;

CREATE TABLE audit.DataQualityException
(
    ExceptionID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    RuleCode nvarchar(40) NOT NULL,
    SourceName nvarchar(100) NOT NULL,
    BusinessKey nvarchar(100) NOT NULL,
    IssueDescription nvarchar(300) NOT NULL,
    DetectedAt datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME()
);

INSERT INTO audit.DataQualityException
    (RuleCode, SourceName, BusinessKey, IssueDescription)
SELECT N'DUPLICATE_INSTRUMENT',
       N'instrument_risk.csv',
       UPPER(LTRIM(RTRIM(InstrumentCode))),
       N'Multiple source rows collapse to the same standardized instrument code.'
FROM dbo.InstrumentRiskRaw
GROUP BY UPPER(LTRIM(RTRIM(InstrumentCode)))
HAVING COUNT(*) > 1;

INSERT INTO audit.DataQualityException
    (RuleCode, SourceName, BusinessKey, IssueDescription)
SELECT DISTINCT
       N'MISSING_INSTRUMENT',
       N'src.Trade',
       t.InstrumentCode,
       N'Trade instrument has no row in the standardized reference extract.'
FROM src.Trade AS t
LEFT JOIN
(
    SELECT DISTINCT UPPER(LTRIM(RTRIM(InstrumentCode))) AS InstrumentCode
    FROM dbo.InstrumentRiskRaw
) AS r
    ON UPPER(LTRIM(RTRIM(t.InstrumentCode))) = r.InstrumentCode
WHERE r.InstrumentCode IS NULL;

INSERT INTO audit.DataQualityException
    (RuleCode, SourceName, BusinessKey, IssueDescription)
SELECT N'ORPHAN_ALERT',
       N'surveillance_alerts.json',
       a.TradeID,
       N'Alert references a trade that is absent from the operational source.'
FROM stg.SurveillanceAlert AS a
LEFT JOIN src.Trade AS t
    ON a.TradeID = t.TradeID
WHERE t.TradeID IS NULL;

INSERT INTO audit.DataQualityException
    (RuleCode, SourceName, BusinessKey, IssueDescription)
SELECT N'MISSING_EXTERNAL_ID',
       N'src.Counterparty',
       CounterpartyID,
       N'Counterparty is missing the external entity identifier used for linkage.'
FROM src.Counterparty
WHERE ExternalEntityID IS NULL;

SELECT RuleCode,
       SourceName,
       BusinessKey,
       IssueDescription
FROM audit.DataQualityException
ORDER BY ExceptionID;
GO

How does this script expose risk?

  • The first section recreates stg.SurveillanceAlert so repeated runs produce the same structured landing table.
  • The OPENJSON schema maps each JSON property to a SQL column.
  • The four insert statements write failed controls into audit.DataQualityException.
  • The final query returns the rule code plus its source name plus its affected business key.
Review the four quality failures

Each exception preserves the source plus the affected business key. This makes the failure traceable before any cleansing rule changes the data.

  • Return to the final results grid from 02_profile_quality.sql.
  • Locate the DUPLICATE_INSTRUMENT row for EQ-GB-01.
  • Locate the MISSING_INSTRUMENT row for CRYPTO-X.
  • Locate the ORPHAN_ALERT row for T-9999.
  • Locate the MISSING_EXTERNAL_ID row for CP-003.
  • Confirm that the results grid contains exactly four rows.

What do the four failures prove?

  • The DUPLICATE_INSTRUMENT rule shows that EQ-GB-01 plus eq-gb-01 collapse to one key after standardisation.
  • The MISSING_INSTRUMENT rule shows that CRYPTO-X has no matching reference row.
  • The ORPHAN_ALERT rule shows that T-9999 has no matching operational trade.
  • The MISSING_EXTERNAL_ID rule shows that CP-003 lacks the identifier required for external linkage.

Your three sources now have a shared quality record that keeps every broken linkage visible. Next, you'll apply deterministic cleansing rules and build the dimensional risk mart.

Build the Curated Risk Star Schema

Your profiling work exposed four quality failures across the operational data, reference extract, and surveillance alerts. Those failures make direct risk reporting unsafe.

This step applies deterministic cleansing before building a star schema in SQL Server. Surrogate keys separate operational identifiers from the analytics model.

An UNKNOWN instrument preserves trades that lack a reference mapping. Reconciliation then proves that the curated mart retains the source row count plus notional value.

In this step, get ready to:
  • Standardize the instrument reference data before loading the risk mart.
  • Build the dimensions plus trade fact table with an unknown-member path.
  • Capture auditable lineage plus zero-difference reconciliation controls.
Load the cleansed risk mart

The script standardizes the raw instrument codes before selecting the larger synthetic risk weight for each normalized code. It keeps every source trade by routing an unmatched instrument through the UNKNOWN dimension member.

✔️ Awesome, I've got everything!

Your saved mart script already matches the project source of truth.

ⓧ I'd like to double check the full code

USE RiskPortfolio;
GO

DROP TABLE IF EXISTS dw.FactTradeRisk;
DROP TABLE IF EXISTS dw.DimInstrument;
DROP TABLE IF EXISTS dw.DimCounterparty;
DROP TABLE IF EXISTS dw.DimDesk;
DROP TABLE IF EXISTS dw.DimDate;
DROP TABLE IF EXISTS audit.ReconciliationCheck;
DROP TABLE IF EXISTS audit.DataLineage;
DROP TABLE IF EXISTS stg.InstrumentRiskClean;
GO

CREATE TABLE stg.InstrumentRiskClean
(
    InstrumentCode nvarchar(30) NOT NULL PRIMARY KEY,
    AssetClass nvarchar(50) NOT NULL,
    RiskWeight decimal(5,2) NOT NULL
);

INSERT INTO stg.InstrumentRiskClean
    (InstrumentCode, AssetClass, RiskWeight)
SELECT UPPER(LTRIM(RTRIM(InstrumentCode))),
       MAX(UPPER(LTRIM(RTRIM(AssetClass)))),
       MAX(TRY_CONVERT(decimal(5,2), RiskWeight))
FROM dbo.InstrumentRiskRaw
WHERE TRY_CONVERT(decimal(5,2), RiskWeight) IS NOT NULL
GROUP BY UPPER(LTRIM(RTRIM(InstrumentCode)));

CREATE TABLE dw.DimDate
(
    DateKey int NOT NULL PRIMARY KEY,
    FullDate date NOT NULL UNIQUE,
    CalendarYear int NOT NULL,
    CalendarMonth int NOT NULL,
    CalendarQuarter int NOT NULL
);

CREATE TABLE dw.DimDesk
(
    DeskKey int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    DeskCode nvarchar(20) NOT NULL UNIQUE,
    DeskName nvarchar(100) NOT NULL
);

CREATE TABLE dw.DimCounterparty
(
    CounterpartyKey int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CounterpartyID nvarchar(20) NOT NULL UNIQUE,
    CounterpartyName nvarchar(100) NOT NULL,
    CountryCode nchar(2) NOT NULL,
    RiskRating nvarchar(10) NOT NULL,
    ExternalEntityID nvarchar(30) NULL
);

CREATE TABLE dw.DimInstrument
(
    InstrumentKey int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    InstrumentCode nvarchar(30) NOT NULL UNIQUE,
    AssetClass nvarchar(50) NOT NULL,
    RiskWeight decimal(5,2) NOT NULL
);

INSERT INTO dw.DimDate
    (DateKey, FullDate, CalendarYear, CalendarMonth, CalendarQuarter)
SELECT DISTINCT
       YEAR(TradeDate) * 10000 + MONTH(TradeDate) * 100 + DAY(TradeDate),
       TradeDate,
       YEAR(TradeDate),
       MONTH(TradeDate),
       DATEPART(QUARTER, TradeDate)
FROM src.Trade;

INSERT INTO dw.DimDesk (DeskCode, DeskName)
SELECT DeskCode, DeskName
FROM src.TradingDesk;

INSERT INTO dw.DimCounterparty
    (CounterpartyID, CounterpartyName, CountryCode, RiskRating, ExternalEntityID)
SELECT CounterpartyID,
       CounterpartyName,
       CountryCode,
       UPPER(RiskRating),
       ExternalEntityID
FROM src.Counterparty;

SET IDENTITY_INSERT dw.DimInstrument ON;
INSERT INTO dw.DimInstrument
    (InstrumentKey, InstrumentCode, AssetClass, RiskWeight)
VALUES
    (0, N'UNKNOWN', N'UNKNOWN', 0.00);
SET IDENTITY_INSERT dw.DimInstrument OFF;

INSERT INTO dw.DimInstrument
    (InstrumentCode, AssetClass, RiskWeight)
SELECT InstrumentCode, AssetClass, RiskWeight
FROM stg.InstrumentRiskClean;

CREATE TABLE dw.FactTradeRisk
(
    TradeRiskKey int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    TradeID nvarchar(20) NOT NULL UNIQUE,
    DateKey int NOT NULL,
    DeskKey int NOT NULL,
    CounterpartyKey int NOT NULL,
    InstrumentKey int NOT NULL,
    NotionalUSD decimal(19,2) NOT NULL,
    WeightedExposureUSD decimal(19,2) NOT NULL,
    AlertCount int NOT NULL,
    HighestAlertSeverity nvarchar(10) NOT NULL,
    CONSTRAINT FK_FactTradeRisk_Date FOREIGN KEY (DateKey)
        REFERENCES dw.DimDate(DateKey),
    CONSTRAINT FK_FactTradeRisk_Desk FOREIGN KEY (DeskKey)
        REFERENCES dw.DimDesk(DeskKey),
    CONSTRAINT FK_FactTradeRisk_Counterparty FOREIGN KEY (CounterpartyKey)
        REFERENCES dw.DimCounterparty(CounterpartyKey),
    CONSTRAINT FK_FactTradeRisk_Instrument FOREIGN KEY (InstrumentKey)
        REFERENCES dw.DimInstrument(InstrumentKey)
);

WITH AlertSummary AS
(
    SELECT TradeID,
           COUNT(*) AS AlertCount,
           MAX
           (
               CASE Severity
                   WHEN N'HIGH' THEN 3
                   WHEN N'MEDIUM' THEN 2
                   WHEN N'LOW' THEN 1
                   ELSE 0
               END
           ) AS SeverityRank
    FROM stg.SurveillanceAlert
    GROUP BY TradeID
)
INSERT INTO dw.FactTradeRisk
    (TradeID, DateKey, DeskKey, CounterpartyKey, InstrumentKey,
     NotionalUSD, WeightedExposureUSD, AlertCount, HighestAlertSeverity)
SELECT t.TradeID,
       YEAR(t.TradeDate) * 10000 + MONTH(t.TradeDate) * 100 + DAY(t.TradeDate),
       d.DeskKey,
       c.CounterpartyKey,
       COALESCE(i.InstrumentKey, 0),
       CONVERT(decimal(19,2), ABS(t.Quantity * t.PriceUSD)),
       CONVERT
       (
           decimal(19,2),
           ABS(t.Quantity * t.PriceUSD) * COALESCE(i.RiskWeight, 0.00)
       ),
       COALESCE(a.AlertCount, 0),
       CASE COALESCE(a.SeverityRank, 0)
           WHEN 3 THEN N'HIGH'
           WHEN 2 THEN N'MEDIUM'
           WHEN 1 THEN N'LOW'
           ELSE N'NONE'
       END
FROM src.Trade AS t
INNER JOIN dw.DimDesk AS d
    ON t.DeskCode = d.DeskCode
INNER JOIN dw.DimCounterparty AS c
    ON t.CounterpartyID = c.CounterpartyID
LEFT JOIN dw.DimInstrument AS i
    ON UPPER(LTRIM(RTRIM(t.InstrumentCode))) = i.InstrumentCode
LEFT JOIN AlertSummary AS a
    ON t.TradeID = a.TradeID;

CREATE TABLE audit.DataLineage
(
    LineageID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    SourceObject nvarchar(150) NOT NULL,
    LandingObject nvarchar(150) NOT NULL,
    TargetObject nvarchar(150) NOT NULL,
    TransformationSummary nvarchar(300) NOT NULL
);

INSERT INTO audit.DataLineage
    (SourceObject, LandingObject, TargetObject, TransformationSummary)
VALUES
    (N'src.Trade + src.TradingDesk + src.Counterparty', N'src schema', N'dw.FactTradeRisk + dimensions', N'Join normalized entities, derive notional, and replace operational keys with surrogate keys.'),
    (N'instrument_risk.csv', N'dbo.InstrumentRiskRaw', N'stg.InstrumentRiskClean + dw.DimInstrument', N'Standardize text, detect normalized duplicates, retain the larger synthetic risk weight, and add an unknown member.'),
    (N'surveillance_alerts.json', N'stg.RawJson + stg.SurveillanceAlert', N'dw.FactTradeRisk', N'Preserve the raw payload, parse the array, validate trade linkage, and aggregate alerts to trade grain.');

CREATE TABLE audit.ReconciliationCheck
(
    ReconciliationID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CheckName nvarchar(100) NOT NULL,
    SourceValue decimal(19,2) NOT NULL,
    TargetValue decimal(19,2) NOT NULL,
    Difference decimal(19,2) NOT NULL,
    CheckedAt datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME()
);

INSERT INTO audit.ReconciliationCheck
    (CheckName, SourceValue, TargetValue, Difference)
SELECT N'Trade row count',
       s.SourceValue,
       t.TargetValue,
       s.SourceValue - t.TargetValue
FROM
    (SELECT CONVERT(decimal(19,2), COUNT(*)) AS SourceValue FROM src.Trade) AS s
CROSS JOIN
    (SELECT CONVERT(decimal(19,2), COUNT(*)) AS TargetValue FROM dw.FactTradeRisk) AS t
UNION ALL
SELECT N'Notional total',
       s.SourceValue,
       t.TargetValue,
       s.SourceValue - t.TargetValue
FROM
    (SELECT CONVERT(decimal(19,2), SUM(ABS(Quantity * PriceUSD))) AS SourceValue FROM src.Trade) AS s
CROSS JOIN
    (SELECT CONVERT(decimal(19,2), SUM(NotionalUSD)) AS TargetValue FROM dw.FactTradeRisk) AS t;

SELECT CheckName, SourceValue, TargetValue, Difference
FROM audit.ReconciliationCheck
ORDER BY ReconciliationID;

SELECT TradeID,
       DateKey,
       DeskKey,
       CounterpartyKey,
       InstrumentKey,
       NotionalUSD,
       WeightedExposureUSD,
       AlertCount,
       HighestAlertSeverity
FROM dw.FactTradeRisk
ORDER BY TradeID;
GO

What does this script build?

  • The stg.InstrumentRiskClean table standardizes instrument codes plus asset classes before retaining the larger synthetic risk weight.
  • The four dw dimensions replace source identifiers with analytics keys.
  • The dw.FactTradeRisk table calculates notional plus weighted exposure at trade grain.
  • The audit tables preserve source paths plus control totals for review.
  • Switch back to the sql\03_build_mart.sql query tab in the SSMS session from earlier.
  • Confirm the query tab uses the same SQL Server connection as the earlier source scripts.
  • Run the entire script from the query toolbar.

The core mart is now loaded. SSMS returns a reconciliation result grid followed by the trade-fact result grid.

Script fails during the load?

  • Confirm that RiskPortfolio appears in the connected SQL Server instance.
  • Confirm that dbo.InstrumentRiskRaw still contains the five imported CSV rows.
  • Confirm that stg.SurveillanceAlert still contains alerts A-1001 through A-1004.

Help me diagnose the failed risk mart load.

Inspect the audit controls

Source-to-target data lineage records how each source reaches its reporting target. Reconciliation compares the source controls with the corresponding mart controls.

  • Locate audit.DataLineage under RiskPortfolio in the SSMS Object Explorer from earlier.
  • Use the table context menu to display its rows.

You should see three lineage records. They cover the normalized operational sources, instrument_risk.csv, and surveillance_alerts.json through their landing objects plus targets.

  • Locate audit.DataQualityException under the same database.
  • Use the table context menu to display its rows.

The four original exceptions remain visible. The mart preserves them as control evidence after cleansing.

Before you inspect the final result grids, do you expect cleansing to change the source trade count or total notional?

  • Return to the first result grid produced by sql\03_build_mart.sql.
  • Confirm the Trade row count source value matches its target value.
  • Confirm the Notional total source value matches its target value.
  • Move to the second result grid produced by the script.
  • Count the rows from T-1001 through T-1006.
  • Locate T-1005 to confirm its InstrumentKey is 0.

The reconciliation grid shows Trade row count with source 6.00, target 6.00, and difference 0.00.

It also shows Notional total with source 700500.00, target 700500.00, and difference 0.00. The fact grid contains six rows.

Controls do not reconcile?

  • Rerun the complete sql\03_build_mart.sql script to rebuild the mart from the unchanged source tables.
  • Confirm that src.Trade still contains trades T-1001 through T-1006.
  • Check that the first result grid includes both reconciliation controls.

Help me investigate a reconciliation difference.

You now have a reconciled risk mart with visible lineage plus retained quality evidence. Next, you will connect Power BI to this model and turn the controls into an interactive risk view.

Deliver the Power BI Control Room

Your SQL Server risk mart now contains six reconciled trade facts. Its lineage records preserve the path back to every source.

Risk users still need a way to filter exposure, challenge flagged trades, and inspect the controls behind each result. A governed mart creates value when those checks are visible during analysis.

In this step, you will connect Power BI Desktop to the mart. You will turn its facts, dimensions, exceptions, and reconciliations into a Risk and Surveillance Control Room.

In this step, get ready to:
  • Connect Power BI Desktop to the risk mart in Import mode.
  • Create a semantic model with dimension relationships plus DAX measures.
  • Deliver the control room as completed Jira sprint evidence.
Connect Power BI to the risk mart

Import mode copies the mart data into the Power BI semantic model. This fits the small local dataset while keeping report interactions responsive.

  • Switch back to the Power BI Desktop session from earlier.
  • Select SQL Server database from the connector list.
  • Enter your SQL Server name in the Server field.
  • Enter RiskPortfolio in the Database field.
  • Select Import as the data connectivity mode.
  • Authenticate with the SQL Server credentials used by your existing SSMS session.
  • In Navigator, select dw.DimDate, dw.DimDesk, dw.DimCounterparty, dw.DimInstrument, dw.FactTradeRisk, audit.DataQualityException, plus audit.ReconciliationCheck.
  • Select Load.

The Data pane now lists the four dimensions, the trade fact, the exception table, and the reconciliation table. This confirms that the governed reporting layer has arrived in Power BI.

Can't connect to the risk mart?

Confirm that the server value matches the name returned by SERVERPROPERTY('ServerName') earlier. Confirm that the RiskPortfolio database remains available in SSMS.

If authentication fails, use the same authentication method as your working SSMS connection.

Help me troubleshoot my Power BI connection to the local RiskPortfolio database.

Create relationships and measures

The star schema gives every fact row a controlled path to its date, desk, counterparty, and instrument descriptions. Active single-direction relationships keep filters flowing from each dimension into the fact table.

  • Select the Modeling tab.
  • Select Manage relationships.
  • Select New.
  • Create an active one-to-many relationship from DimDate[DateKey] to FactTradeRisk[DateKey] with Single filter direction.
  • Create an active one-to-many relationship from DimDesk[DeskKey] to FactTradeRisk[DeskKey] with Single filter direction.
  • Create an active one-to-many relationship from DimCounterparty[CounterpartyKey] to FactTradeRisk[CounterpartyKey] with Single filter direction.
  • Create an active one-to-many relationship from DimInstrument[InstrumentKey] to FactTradeRisk[InstrumentKey] with Single filter direction.

You should see four relationship lines leading from the dimension tables to FactTradeRisk. Each line shows a 1 beside its dimension plus an asterisk beside the fact table.

Measures use DAX to calculate report values inside the current filter context. The first two measures aggregate the financial values stored at trade grain.

  • Select FactTradeRisk in the Data pane.
  • Select New measure from the Modeling tab.
  • Create the total-notional measure by entering this formula:
Total Notional = SUM(FactTradeRisk[NotionalUSD])

What does this measure do?

  • The SUM function adds every visible NotionalUSD value after report filters are applied.
  • Press Enter to validate the measure.
  • Confirm that Total Notional appears with a calculator icon in the Data pane.

Total Notional won't validate?

Confirm that the formula uses FactTradeRisk[NotionalUSD] exactly. A renamed table or column prevents Power BI from resolving the reference.

Help me fix my Total Notional DAX measure.

  • Select New measure again.
  • Create the weighted-exposure measure by entering this formula:
Weighted Exposure = SUM(FactTradeRisk[WeightedExposureUSD])

What does this measure do?

  • This measure totals the risk-weighted exposure that the SQL mart calculated for each trade.
  • Press Enter to validate the measure.
  • Confirm that Weighted Exposure appears with a calculator icon in the Data pane.

Weighted Exposure won't validate?

Check that the column reference is FactTradeRisk[WeightedExposureUSD]. The measure must point to the imported fact table.

Help me fix my Weighted Exposure DAX measure.

The next measure counts distinct trades with at least one surveillance alert. This prevents a trade from being counted twice if its alert count grows.

  • Select New measure.
  • Create the flagged-trade measure by entering this formula:
Flagged Trades =
CALCULATE(
    DISTINCTCOUNT(FactTradeRisk[TradeID]),
    FactTradeRisk[AlertCount] > 0
)

What does this measure do?

  • The CALCULATE function keeps fact rows whose AlertCount is above 0.
  • The DISTINCTCOUNT function returns the number of unique TradeID values in that filtered set.
  • Press Enter to validate the measure.
  • Confirm that Flagged Trades appears with a calculator icon in the Data pane.

Flagged Trades won't validate?

Check the closing parenthesis after the alert filter. Confirm that TradeID plus AlertCount both belong to FactTradeRisk.

Help me fix my Flagged Trades DAX measure.

The high-risk measure reuses the weighted-exposure logic under a counterparty filter. It gives you a reusable metric for future report extensions.

  • Select New measure.
  • Create the high-risk exposure measure by entering this formula:
High Risk Exposure =
CALCULATE(
    [Weighted Exposure],
    DimCounterparty[RiskRating] = "HIGH"
)

What does this measure do?

  • The measure recalculates Weighted Exposure for counterparties whose RiskRating is HIGH.
  • Press Enter to validate the measure.
  • Confirm that High Risk Exposure appears with a calculator icon in the Data pane.

High Risk Exposure won't validate?

Confirm that Weighted Exposure already exists. Check that the relationship from DimCounterparty to FactTradeRisk is active.

Help me fix my High Risk Exposure DAX measure.

The final measure counts the exceptions retained by the quality framework. It keeps data-quality evidence visible beside the risk metrics.

  • Select DataQualityException in the Data pane.
  • Select New measure.
  • Create the exception-count measure by entering this formula:
DQ Exceptions = COUNTROWS(DataQualityException)

What does this measure do?

  • The COUNTROWS function returns the number of visible rows in DataQualityException.
  • Press Enter to validate the measure.
  • Confirm that DQ Exceptions appears with a calculator icon in the Data pane.

DQ Exceptions won't validate?

Confirm that DataQualityException is loaded into the model. Check that the table name has no schema prefix inside the DAX formula.

Help me fix my DQ Exceptions DAX measure.

✔️ Awesome, I've got everything!

Your model now has four active dimension relationships plus five reusable measures. The reporting logic is ready for the control room.

ⓧ I'd like to double check the full code

Total Notional = SUM(FactTradeRisk[NotionalUSD])

Weighted Exposure = SUM(FactTradeRisk[WeightedExposureUSD])

Flagged Trades =
CALCULATE(
    DISTINCTCOUNT(FactTradeRisk[TradeID]),
    FactTradeRisk[AlertCount] > 0
)

High Risk Exposure =
CALCULATE(
    [Weighted Exposure],
    DimCounterparty[RiskRating] = "HIGH"
)

DQ Exceptions = COUNTROWS(DataQualityException)

Compare each formula with its separate model measure. Each measure name plus expression should match this reference exactly.

Build the control room and close the sprint

The control room combines headline exposure with the evidence needed to challenge it. Cards show the portfolio state while tables preserve trade-level plus control-level detail.

  • Rename the report page tab to Risk and Surveillance Control Room.
  • Create a Card visual using Total Notional in Values.
  • Create a Card visual using Weighted Exposure in Values.
  • Create a Card visual using Flagged Trades in Values.
  • Create a Card visual using DQ Exceptions in Values.
  • Format the two financial cards with Display units set to None plus two decimal places.
  • Create a Clustered column chart using DimDesk[DeskName] on the X-axis plus Weighted Exposure on the Y-axis.
  • Create a Slicer using DimCounterparty[RiskRating].

What have you built so far?

The four cards provide the headline control view. The desk chart exposes where weighted exposure sits across the trading operation.

The risk-rating slicer now tests whether those visuals respond to dimension filters through your relationship model.

  • Create a second Slicer using DimInstrument[AssetClass].
  • Create a Table visual using FactTradeRisk[TradeID], FactTradeRisk[AlertCount], FactTradeRisk[HighestAlertSeverity], FactTradeRisk[NotionalUSD], plus FactTradeRisk[WeightedExposureUSD].
  • Apply a visual-level condition that keeps rows whose AlertCount is above 0.
  • Create a Table visual using DataQualityException[RuleCode], DataQualityException[SourceName], DataQualityException[BusinessKey], plus DataQualityException[IssueDescription].
  • Create a Table visual using ReconciliationCheck[CheckName], ReconciliationCheck[SourceValue], ReconciliationCheck[TargetValue], plus ReconciliationCheck[Difference].
  • Arrange the cards across the top of the report canvas.
  • Place the chart plus slicers below the cards.
  • Place the three evidence tables beneath the analytical visuals.

Before you refresh, what values do you expect the four cards to recover from the reconciled mart?

  • Refresh the imported semantic model by selecting Refresh on the Home ribbon.

What should I see?

  • The Total Notional card shows 700,500.00.
  • The Weighted Exposure card shows 61,400.00.
  • The Flagged Trades card shows 3.
  • The DQ Exceptions card shows 4.

The flagged-trade table shows T-1001, T-1003, and T-1006.

The exception table shows four retained quality failures. Both reconciliation rows show a Difference of 0.00.

Cards or tables show the wrong values?

Clear any active slicer selections before comparing the totals. Confirm that every relationship is active with filtering from the dimension into FactTradeRisk.

If an exception or reconciliation table is empty, confirm that its audit table was included during the import.

Help me diagnose incorrect values in my Power BI risk control room.

  • Save the report as a local Power BI Desktop report file.

The report proves the technical delivery. Your sprint record now needs to show that each planned work item reached completion.

  • Return to the Jira board from earlier.
  • Select Profile risk sources, Implement data quality controls, Build dimensional risk mart, plus Deliver Power BI control room.
  • Drag the four selected work items into the board's right-most completed column.

You should see all four delivery work items in the completed column. This closes the sprint record against the pipeline stages you delivered.

Strong finish. Your local risk mart now has an interactive control room plus Agile evidence that traces the delivery from source profiling to governed reporting.

Secret mission

Add a Counterparty Concentration Rule

Your control framework already captures exceptions supplied by the source systems. Extend it with a derived counterparty concentration rule, then prove that the existing Power BI report can absorb the new exception without structural changes.

Clean Up Your Resources

Clean Up Your Resources

The completed project adds no new ongoing cost. Choose whether to keep the finished work, pause active apps, or remove the project artifacts.

Resources you used:

  • A populated RiskPortfolio SQL Server database containing source, staging, dimensional, audit, and imported reference tables.
  • The C:\Users\Public\RiskDataPortfolio folder containing the source files and SQL scripts.
  • A saved local Power BI Desktop report connected to RiskPortfolio in Import mode.
  • Five completed Jira delivery work items with sprint history and the Secret Mission comment.

Keep everything running

No action needed. Choose this if you want the finished pipeline and control room available for future demonstrations.

  • Keep RiskPortfolio available for future queries and report refreshes.
  • Keep C:\Users\Public\RiskDataPortfolio as the reproducible source package.
  • Keep the Power BI Desktop report with its relationships and measures intact.
  • Keep the completed Jira work items as the delivery record.

Pause - I'll come back to this later

Shut down the active desktop apps to free memory. The database, files, report, and Jira history remain available without active processing.

  • Save the Power BI Desktop report from earlier.
  • Close Power BI Desktop.
  • Close SQL Server Management Studio.
  • Leave RiskPortfolio installed on your local SQL Server instance.
  • Leave the completed Jira sprint unchanged.

Delete - I don't want to use this again

Remove the local database, project files, saved report, and Jira delivery items if you no longer need this project. Your installed applications remain available for other work.

Deletion is permanent

Deleting this work removes the finished pipeline and its reporting evidence. Each action below targets one project artifact.

The database can keep a live connection while SSMS or Power BI Desktop is using it. Closing those connections makes database deletion predictable.

  • Close the Power BI Desktop report from earlier.
  • Close every SSMS query window connected to RiskPortfolio.
  • Return to SSMS.
  • Expand the database list in the left navigation tree.
  • Right-click RiskPortfolio.
  • Select Delete from the context menu.
  • Confirm the deletion in the dialog.
  • Refresh the database list to confirm RiskPortfolio is absent.

That's the database cleanup done. SQL Server stays installed for other projects.

The project folder contains the reusable CSV, JSON, and SQL artifacts. Removing it clears the complete local source package.

  • Press the Windows key to open system search.
  • Enter Command Prompt in the search field.
  • Press Enter to open the command shell.
  • Delete the entire project folder by running this command:
rmdir /s /q C:\Users\Public\RiskDataPortfolio

What does this command remove?

The command removes C:\Users\Public\RiskDataPortfolio with every file beneath it. This includes the CSV extract, JSON alert feed, and SQL scripts.

  • Confirm the folder no longer exists by running this command:
dir C:\Users\Public\RiskDataPortfolio

What should I see?

Windows reports that it cannot find the specified location. That result confirms the project folder is gone.

Folder still present?

  • Close any window that has a project file open.
  • Run the deletion command again from a Command Prompt with permission to modify the public user folder.
  • Ask for help diagnosing the locked folder.

A Power BI report saved outside C:\Users\Public\RiskDataPortfolio remains after the folder deletion. Remove that separate file from the location you chose when saving it.

  • Skip this part if the report was saved inside C:\Users\Public\RiskDataPortfolio.
  • Press the Windows key to open system search.
  • Enter File Explorer in the search field.
  • Press Enter to open File Explorer.
  • Navigate to the location where you saved the Power BI Desktop report.
  • Delete the saved report file.
  • Refresh the folder to confirm the report file is absent.

Your local project files are now removed. The installed database and reporting tools remain available.

The Jira work items hold the delivery record and Secret Mission comment. Delete those items only if they were created solely for this exercise.

  • Return to the completed Jira sprint from earlier.
  • Open each of the five completed delivery work items.
  • Delete each work item through its actions menu.
  • Refresh the sprint to confirm the five project work items no longer appear.

The project work items and their Secret Mission comment are now removed from Jira.

Can't delete the Jira items?

Your Jira administrator may restrict deletion permissions. Ask for help preparing a deletion request.

Nice Work!

Nice Work!

Outstanding work. You built a reconciled SQL Server risk mart with a Power BI control room plus a completed Jira sprint.

You've learned how to:

  • Build an auditable risk-data pipeline from normalized trade data. Land CSV instrument data plus retained JSON surveillance alerts in the same flow.
  • Expose four designed data-quality failures before reporting. Transform governed records into a Kimball-style star schema with explicit source-to-target lineage. Reconcile six source trades to six fact rows with a 0.00 difference.
  • Present total notional in a Power BI control room. Add weighted exposure to the same report. Surface flagged trades plus quality exceptions. Capture the completed work in a Jira sprint as Agile delivery evidence.
  • Secret Mission: Added a reusable counterparty concentration rule for CP-002. Refreshed the existing exception model to raise DQ Exceptions from 4 to 5. Documented 25000.00 as a demonstration threshold.

Ready to quiz yourself?