Revenue Analytics Dashboard with Tableau

Model a star schema in SQL Server and build an interactive Tableau dashboard.

Introduction

30 Second Summary

Every business tracks revenue, but raw transaction data is useless without a way to spot trends, flag problems, and answer the question every finance team asks: "are we actually collecting what we're owed?"

In this project, you will build a complete revenue analytics pipeline from database to dashboard. You will model a star schema database in SQL Server, write analytical SQL views with window functions, and design an interactive Tableau dashboard with KPI cards, trend lines, and filters.

What You'll Build

A revenue analytics pipeline that takes raw financial data in a SQL Server database through an analytical SQL layer and into a polished, interactive Tableau dashboard published to your Tableau Public profile.

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

  • A star schema database in SQL Server with dimension and fact tables storing 2+ years of synthetic revenue data across toll revenue categories, collections, bad debt, and interest income.
  • An analytical SQL layer with views and a stored procedure that calculate month-over-month changes, year-over-year comparisons, and collection rates using CTEs and window functions.
  • A published Tableau dashboard with KPI cards, revenue trend lines, MoM % change charts, category breakdowns, and interactive filters.
  • Secret Mission: Add a cash flow waterfall chart and bad debt aging analysis to your dashboard.

Are there any prerequisites?

This project assumes you are comfortable with SQL basics like JOINs, GROUP BY, and aggregate functions. You should also have basic familiarity with Tableau (connecting to data, dragging fields onto shelves).

You will need Docker Desktop and Tableau Desktop Public Edition installed on macOS. Step 1 walks you through setting up SQL Server and your query editor.

Set Up SQL Server in Docker and Connect

Before you can build a revenue analytics pipeline, you need a database to store and query your financial data. SQL Server is the industry-standard database engine for enterprise BI workloads, and Docker lets you run it locally without a full Windows installation.

In this step, you will spin up a SQL Server 2022 instance inside a Docker container, install the MSSQL extension for VS Code to connect to it, and verify everything is working with a test query.

In this step, get ready to:
  • Enable Rosetta emulation and run a SQL Server 2022 container in Docker.
  • Install the MSSQL extension for VS Code and connect to the SQL Server instance.
  • Verify the connection by running a test query.
Pull and run the SQL Server 2022 Docker container

SQL Server's Docker image is built for x86-64 (Intel) processors. On Apple Silicon Macs, Docker needs Rosetta emulation to translate these instructions so the container can run.

  • Open Docker Desktop by clicking the Docker whale icon in your menu bar at the top of your screen.
  • Click the gear icon in the top-right corner to open Settings.
  • Select General from the left sidebar.
  • Check the box for Use Rosetta for x86_64/amd64 emulation on Apple Silicon.
  • Click Apply & restart to save your changes.

Don't see the Rosetta option?

If the Rosetta checkbox does not appear, you are likely on an Intel Mac. SQL Server's image runs natively on Intel processors, so you can skip this setting and move on to the Docker command below.

Now you are ready to start the SQL Server container.

  • Press Cmd+Space to open Spotlight search.
  • Type Terminal and press Enter to open it.
  • Pull and start the SQL Server 2022 container by running this command:
docker run \
  -e "ACCEPT_EULA=Y" \
  -e "MSSQL_SA_PASSWORD=NextWork#123" \
  -p 1433:1433 \
  -v sqlvolume:/var/opt/mssql \
  -d mcr.microsoft.com/mssql/server:2022-latest

What does this command do?

  • ACCEPT_EULA=Y accepts the SQL Server license agreement.
  • MSSQL_SA_PASSWORD sets the system administrator password for SQL Server. The password must be at least 8 characters with a mix of uppercase, lowercase, digits, and symbols.
  • -p 1433:1433 maps port 1433 on your Mac to port 1433 inside the container. This is SQL Server's default port.
  • -v sqlvolume:/var/opt/mssql creates a Docker volume so your database files persist even if the container stops.
  • -d runs the container in the background.
  • mcr.microsoft.com/mssql/server:2022-latest is the official SQL Server 2022 image from Microsoft. It runs the free Developer Edition by default.

Want to use your own password?

NextWork#123 is a sample password that meets SQL Server's complexity requirements. You can replace it with any password that has at least 8 characters and includes uppercase, lowercase, digits, and a symbol. Remember it for the next substep.

This may take a few minutes as Docker downloads the SQL Server image (approximately 1.5 GB).

  • Wait about 15 seconds after the download finishes for SQL Server to start up inside the container.
  • Confirm the container is running by running this command:
docker ps

You should see a row with mcr.microsoft.com/mssql/server:2022-latest in the IMAGE column and a STATUS that starts with Up.

Container not showing up?

  • Make sure Docker Desktop is running. Check for the whale icon in your menu bar at the top of your screen.
  • If you see a password-related error, verify your password has at least 8 characters with a mix of uppercase, lowercase, digits, and symbols.
  • If you see a platform or architecture error on Apple Silicon, confirm Rosetta emulation is enabled in Docker Desktop settings.
  • Docker requires at least 2 GB of RAM allocated. Check Settings > Resources in Docker Desktop to verify.
  • Help me troubleshoot my SQL Server Docker container
Install the MSSQL extension for VS Code and connect to SQL Server

You need a tool to write and run SQL queries against your new SQL Server instance. The MSSQL extension for VS Code gives you a connection dialog, query editor, and results pane all inside your editor.

  • Press Cmd+Space to open Spotlight search.
  • Type Visual Studio Code and press Enter to open it.
  • Open the Extensions view by pressing Cmd+Shift+X.
  • In the search bar, type mssql.
  • Find SQL Server (mssql) by Microsoft in the results and click Install.

You know the extension is installed when the MSSQL icon appears in the Activity Bar on the left side of VS Code.

Why VS Code instead of Azure Data Studio?

Azure Data Studio was retired by Microsoft in February 2026. The MSSQL extension for VS Code is its official replacement. It provides the same connection management, query editor, and results export features you need for this project.

Now connect the extension to your SQL Server container.

  • Click the MSSQL icon in the Activity Bar on the left side of VS Code to open the Connections view.
  • Click Add Connection (the plus icon) to open the connection dialog.
  • Set Input type to Parameters.
  • In the Server name field, enter localhost,1433.
  • Set Authentication Type to SQL Login.
  • In the User name field, enter sa.
  • In the Password field, enter NextWork#123 (or the password you chose in the Docker command).
  • Check the Trust Server Certificate checkbox.
  • Set Encrypt to Mandatory.
  • Check Save Password so you do not have to re-enter it each time.
  • Press Enter to connect.

You should see localhost,1433 appear in the Connections view with a green dot indicating an active connection.

What do these connection settings mean?

  • localhost,1433 tells VS Code to connect to port 1433 on your own machine, which Docker mapped to the SQL Server container.
  • sa is the built-in system administrator account for SQL Server.
  • The password matches the MSSQL_SA_PASSWORD you set when you ran the Docker container.

Connection failed?

  • Make sure the Docker container is still running. Run docker ps in your terminal to check.
  • Double-check that you entered localhost,1433 with a comma, not a colon. SQL Server uses a comma to separate the host and port.
  • Verify the password matches exactly what you used in the Docker run command.
  • If you still cannot connect, try using 127.0.0.1,1433 as the server name instead of localhost,1433.
  • Help me troubleshoot my SQL Server connection in VS Code
Verify the connection by running a test query

You are connected, but a quick test query confirms SQL Server is responding correctly and running the expected version.

  • Right-click your localhost,1433 connection in the Connections view and select New Query.
  • Copy and paste this query into the editor:
SELECT @@VERSION;
  • Click the Run button (the green play icon) in the toolbar at the top of the query editor to execute the query.

You should see a result row showing Microsoft SQL Server 2022 along with the Developer Edition label and Linux build information.

What is SELECT @@VERSION?

@@VERSION is a built-in SQL Server system variable that returns the full version string. Seeing "Developer Edition" confirms you are running the free development license, which includes all the features of Enterprise Edition.

Query not returning results?

  • Check the status bar at the bottom of VS Code. It should show your active connection to localhost,1433. If it shows "Disconnected," click the connection indicator and reconnect.
  • Make sure you clicked Run (the green play icon) and not the estimated plan button next to it.
  • Help me run a query in the VS Code MSSQL extension

Your SQL Server instance is running and you are connected. Next up, you will create the star schema database and load it with sample revenue data.

Create the Star Schema Database and Load Sample Data

Your SQL Server container is running and you've confirmed the connection. Now it's time to build the database that will power your revenue analytics.

In this step, you'll design a star schema database with dimension tables and a central fact table. Then you'll load 36 months of synthetic toll revenue data that includes seasonal patterns, growth trends, and varying payment statuses.

In this step, get ready to:
  • Create the RevenueAnalytics database with three dimension tables and one fact table.
  • Load synthetic revenue data spanning January 2023 through December 2025.
  • Verify your data model with row counts and sample queries.
Create the database and dimension tables

A star schema organizes data into two types of tables. Dimension tables describe the "who, what, when" context. A central fact table stores the numeric measurements you want to analyze.

Your database needs three dimensions: dates, revenue categories, and payment statuses. Each one gives the fact table a different axis to slice revenue data along.

What is a star schema?

Picture a star shape. The fact table sits in the center, and each dimension table radiates outward like a point on the star. Every row in the fact table links to one row in each dimension through foreign keys.

Star schemas are the industry standard for BI and analytical workloads. They make queries fast and intuitive because you can filter and group by any dimension without complex joins.

  • In Azure Data Studio, open a new query by clicking New Query in the toolbar (or press Cmd+N on macOS).
  • Create the database and dimension tables by pasting and running this SQL:
-- Create the database
CREATE DATABASE RevenueAnalytics;
GO

USE RevenueAnalytics;
GO

-- Dimension: Date
CREATE TABLE DimDate (
    DateKey INT PRIMARY KEY,
    FullDate DATE NOT NULL,
    MonthNum INT NOT NULL,
    MonthName VARCHAR(20) NOT NULL,
    Quarter INT NOT NULL,
    Year INT NOT NULL
);

-- Dimension: Revenue Category
CREATE TABLE DimRevenueCategory (
    CategoryKey INT PRIMARY KEY IDENTITY(1,1),
    CategoryName VARCHAR(50) NOT NULL
);

-- Dimension: Payment Status
CREATE TABLE DimPaymentStatus (
    StatusKey INT PRIMARY KEY IDENTITY(1,1),
    StatusName VARCHAR(50) NOT NULL
);
GO

What does this SQL do?

  • CREATE DATABASE RevenueAnalytics creates a fresh database to hold all your revenue tables.
  • DimDate stores one row per month. DateKey uses a format like 202301 (year + month) so you can sort and join on it easily.
  • DimRevenueCategory will hold your four toll revenue types: LPT, ExpressToll, InterestIncome, and LateFees.
  • DimPaymentStatus tracks whether revenue was Collected, Outstanding, BadDebt, or Unbillable.
  • IDENTITY(1,1) on the category and status tables tells SQL Server to auto-generate primary key values starting at 1.
  • Click Run in the toolbar (or press F5) to execute the query.
  • You should see Commands completed successfully in the Messages panel at the bottom.

Seeing a 'database already exists' error?

If you ran this script before, the RevenueAnalytics database already exists. Run DROP DATABASE RevenueAnalytics; first, then re-run the full script.

If the connection dropped, check that your Docker container is still running. Open Docker Desktop and confirm the SQL Server container shows a green "Running" status.

Help me fix my database creation error

✔️ Awesome, I've got everything!

Great. Make sure you see the success message before moving on.

ⓧ I'd like to double check the full code

-- Create the database
CREATE DATABASE RevenueAnalytics;
GO

USE RevenueAnalytics;
GO

-- Dimension: Date
CREATE TABLE DimDate (
    DateKey INT PRIMARY KEY,
    FullDate DATE NOT NULL,
    MonthNum INT NOT NULL,
    MonthName VARCHAR(20) NOT NULL,
    Quarter INT NOT NULL,
    Year INT NOT NULL
);

-- Dimension: Revenue Category
CREATE TABLE DimRevenueCategory (
    CategoryKey INT PRIMARY KEY IDENTITY(1,1),
    CategoryName VARCHAR(50) NOT NULL
);

-- Dimension: Payment Status
CREATE TABLE DimPaymentStatus (
    StatusKey INT PRIMARY KEY IDENTITY(1,1),
    StatusName VARCHAR(50) NOT NULL
);
GO
Create the fact table

The fact table is the center of your star schema. Each row records a single revenue measurement tied to a specific date, category, and payment status through foreign keys.

  • Open a new query tab in Azure Data Studio by clicking New Query again.
  • Create the fact table by pasting and running this SQL:
USE RevenueAnalytics;
GO

CREATE TABLE FactRevenue (
    RevenueKey INT PRIMARY KEY IDENTITY(1,1),
    DateKey INT NOT NULL FOREIGN KEY REFERENCES DimDate(DateKey),
    CategoryKey INT NOT NULL FOREIGN KEY REFERENCES DimRevenueCategory(CategoryKey),
    StatusKey INT NOT NULL FOREIGN KEY REFERENCES DimPaymentStatus(StatusKey),
    Amount DECIMAL(12,2) NOT NULL,
    TransactionCount INT NOT NULL
);
GO

What does this table do?

  • FactRevenue is the central measurement table. Each row represents revenue for one category and one payment status in one month.
  • The three FOREIGN KEY constraints link each row back to the dimension tables. This is what makes the star shape.
  • Amount stores the dollar value and TransactionCount stores how many individual transactions made up that amount.
  • Click Run to execute the query.
  • You should see Commands completed successfully in the Messages panel.

Seeing a 'reference to invalid table' error?

The foreign key constraints require the dimension tables to exist first. Make sure you ran the previous script (creating DimDate, DimRevenueCategory, and DimPaymentStatus) successfully before running this one.

Help me fix my fact table creation error

✔️ Awesome, I've got everything!

Your star schema structure is complete. Time to fill it with data.

ⓧ I'd like to double check the full code

USE RevenueAnalytics;
GO

CREATE TABLE FactRevenue (
    RevenueKey INT PRIMARY KEY IDENTITY(1,1),
    DateKey INT NOT NULL FOREIGN KEY REFERENCES DimDate(DateKey),
    CategoryKey INT NOT NULL FOREIGN KEY REFERENCES DimRevenueCategory(CategoryKey),
    StatusKey INT NOT NULL FOREIGN KEY REFERENCES DimPaymentStatus(StatusKey),
    Amount DECIMAL(12,2) NOT NULL,
    TransactionCount INT NOT NULL
);
GO
Populate the tables with synthetic data

Empty tables aren't useful for analysis. You'll load 36 months of date records, four revenue categories, four payment statuses, and then fill the fact table with realistic synthetic revenue data.

The dimension data goes in first because the fact table's foreign keys depend on those rows existing.

  • Open a new query tab in Azure Data Studio.
  • Populate the dimension tables by pasting and running this SQL:
USE RevenueAnalytics;
GO

-- Populate DimDate: January 2023 through December 2025
DECLARE @StartDate DATE = '2023-01-01';
DECLARE @EndDate DATE = '2025-12-01';
DECLARE @CurrentDate DATE = @StartDate;

WHILE @CurrentDate <= @EndDate
BEGIN
    INSERT INTO DimDate (DateKey, FullDate, MonthNum, MonthName, Quarter, Year)
    VALUES (
        YEAR(@CurrentDate) * 100 + MONTH(@CurrentDate),
        @CurrentDate,
        MONTH(@CurrentDate),
        DATENAME(MONTH, @CurrentDate),
        DATEPART(QUARTER, @CurrentDate),
        YEAR(@CurrentDate)
    );
    SET @CurrentDate = DATEADD(MONTH, 1, @CurrentDate);
END;

-- Populate DimRevenueCategory
INSERT INTO DimRevenueCategory (CategoryName) VALUES
('LPT'),
('ExpressToll'),
('InterestIncome'),
('LateFees');

-- Populate DimPaymentStatus
INSERT INTO DimPaymentStatus (StatusName) VALUES
('Collected'),
('Outstanding'),
('BadDebt'),
('Unbillable');
GO

What does this script do?

  • The WHILE loop generates one row in DimDate for every month from January 2023 through December 2025. That gives you 36 months of date records.
  • DateKey is calculated as YEAR * 100 + MONTH, so January 2023 becomes 202301. This format sorts chronologically and is easy to read.
  • The four revenue categories (LPT, ExpressToll, InterestIncome, LateFees) mirror toll road and transportation finance domains.
  • The four payment statuses (Collected, Outstanding, BadDebt, Unbillable) let you track where revenue ends up after billing.
  • Click Run to execute the script.
  • Verify the dimension data loaded correctly by opening a new query tab and running this:
USE RevenueAnalytics;
SELECT COUNT(*) AS DateRows FROM DimDate;
SELECT COUNT(*) AS CategoryRows FROM DimRevenueCategory;
SELECT COUNT(*) AS StatusRows FROM DimPaymentStatus;

You should see three result sets: 36 for DateRows, 4 for CategoryRows, and 4 for StatusRows.

Seeing 0 rows in DimDate?

Make sure you ran the populate script in the RevenueAnalytics database. Check that the USE RevenueAnalytics; line is at the top of your script. If you're in the wrong database, the inserts may have gone to master instead.

Help me fix my dimension table population

✔️ Awesome, I've got everything!

Your dimensions are loaded. Now for the big one: the fact table data.

ⓧ I'd like to double check the full code

USE RevenueAnalytics;
GO

-- Populate DimDate: January 2023 through December 2025
DECLARE @StartDate DATE = '2023-01-01';
DECLARE @EndDate DATE = '2025-12-01';
DECLARE @CurrentDate DATE = @StartDate;

WHILE @CurrentDate <= @EndDate
BEGIN
    INSERT INTO DimDate (DateKey, FullDate, MonthNum, MonthName, Quarter, Year)
    VALUES (
        YEAR(@CurrentDate) * 100 + MONTH(@CurrentDate),
        @CurrentDate,
        MONTH(@CurrentDate),
        DATENAME(MONTH, @CurrentDate),
        DATEPART(QUARTER, @CurrentDate),
        YEAR(@CurrentDate)
    );
    SET @CurrentDate = DATEADD(MONTH, 1, @CurrentDate);
END;

-- Populate DimRevenueCategory
INSERT INTO DimRevenueCategory (CategoryName) VALUES
('LPT'),
('ExpressToll'),
('InterestIncome'),
('LateFees');

-- Populate DimPaymentStatus
INSERT INTO DimPaymentStatus (StatusName) VALUES
('Collected'),
('Outstanding'),
('BadDebt'),
('Unbillable');
GO

The fact table needs realistic revenue data that behaves the way real financial data does. The next script uses a cursor to loop through every month in DimDate and generate revenue rows with built-in patterns.

  • Open a new query tab.
  • Populate the fact table with synthetic revenue data by pasting and running this SQL:
USE RevenueAnalytics;
GO

-- Populate FactRevenue with synthetic data
-- Uses a cursor over DimDate and nested loops for categories/statuses
-- with realistic patterns: growth trend, seasonal variation, varying ratios

DECLARE @DateKey INT, @Year INT, @MonthNum INT;
DECLARE @CatKey INT, @StatKey INT;
DECLARE @BaseAmount DECIMAL(12,2);
DECLARE @Amount DECIMAL(12,2);
DECLARE @TxnCount INT;
DECLARE @GrowthFactor DECIMAL(5,3);
DECLARE @SeasonalFactor DECIMAL(5,3);

DECLARE date_cursor CURSOR FOR
    SELECT DateKey, Year, MonthNum FROM DimDate ORDER BY DateKey;

OPEN date_cursor;
FETCH NEXT FROM date_cursor INTO @DateKey, @Year, @MonthNum;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Growth: 5% per year from 2023 baseline
    SET @GrowthFactor = 1.0 + ((@Year - 2023) * 0.05);

    -- Seasonal: higher in summer months (Jun-Aug), lower in winter (Dec-Feb)
    SET @SeasonalFactor = CASE
        WHEN @MonthNum IN (6, 7, 8) THEN 1.15
        WHEN @MonthNum IN (12, 1, 2) THEN 0.88
        WHEN @MonthNum IN (3, 4, 5, 9, 10, 11) THEN 1.0
    END;

    -- Loop through each category
    SET @CatKey = 1;
    WHILE @CatKey <= 4
    BEGIN
        -- Base amounts vary by category
        SET @BaseAmount = CASE @CatKey
            WHEN 1 THEN 850000.00  -- LPT (largest)
            WHEN 2 THEN 620000.00  -- ExpressToll
            WHEN 3 THEN 95000.00   -- InterestIncome
            WHEN 4 THEN 45000.00   -- LateFees
        END;

        -- Loop through each payment status
        SET @StatKey = 1;
        WHILE @StatKey <= 4
        BEGIN
            -- Distribution ratios by status
            SET @Amount = @BaseAmount * @GrowthFactor * @SeasonalFactor * CASE @StatKey
                WHEN 1 THEN 0.82   -- Collected: 82%
                WHEN 2 THEN 0.10   -- Outstanding: 10%
                WHEN 3 THEN 0.05   -- BadDebt: 5%
                WHEN 4 THEN 0.03   -- Unbillable: 3%
            END;

            -- Add some randomness using CHECKSUM and NEWID
            SET @Amount = @Amount * (0.95 + (ABS(CHECKSUM(NEWID())) % 11) * 0.01);

            SET @TxnCount = CAST(@Amount / 75.00 AS INT);

            INSERT INTO FactRevenue (DateKey, CategoryKey, StatusKey, Amount, TransactionCount)
            VALUES (@DateKey, @CatKey, @StatKey, ROUND(@Amount, 2), @TxnCount);

            SET @StatKey = @StatKey + 1;
        END;

        SET @CatKey = @CatKey + 1;
    END;

    FETCH NEXT FROM date_cursor INTO @DateKey, @Year, @MonthNum;
END;

CLOSE date_cursor;
DEALLOCATE date_cursor;
GO

What does this script do?

This script generates revenue data for every combination of month, category, and payment status. It uses three nested patterns to make the data feel realistic:

  • Growth trend: Revenue increases 5% per year from the 2023 baseline. This simulates organic business growth.
  • Seasonal variation: Summer months (June through August) get a 15% boost. Winter months (December through February) get an 12% dip. This mirrors toll road traffic patterns.
  • Payment status distribution: 82% of revenue is Collected, 10% Outstanding, 5% BadDebt, and 3% Unbillable. These ratios are applied to every category.
  • Randomness: CHECKSUM(NEWID()) adds slight variation so every month's numbers are unique, not perfectly identical.
  • Click Run to execute the script. This may take a few seconds to complete.
  • Verify the fact table loaded correctly by opening a new query tab and running this:
USE RevenueAnalytics;
SELECT COUNT(*) AS FactRows FROM FactRevenue;
SELECT TOP 5 * FROM FactRevenue;

You should see 576 in the FactRows result. That's 36 months x 4 categories x 4 statuses. The TOP 5 query shows sample rows so you can confirm the amounts and foreign keys look correct.

Seeing fewer than 576 rows?

If you get fewer rows, the cursor may have failed partway through. Run DELETE FROM FactRevenue; to clear the partial data, then re-run the populate script from the top.

If you see a cursor already open error, the previous run was interrupted before the cursor was cleaned up. Run CLOSE date_cursor; DEALLOCATE date_cursor; first, then try the full script again.

Help me fix my fact table data loading

✔️ Awesome, I've got everything!

Your database is fully loaded with 576 fact rows across 36 months of revenue data.

ⓧ I'd like to double check the full code

USE RevenueAnalytics;
GO

DECLARE @DateKey INT, @Year INT, @MonthNum INT;
DECLARE @CatKey INT, @StatKey INT;
DECLARE @BaseAmount DECIMAL(12,2);
DECLARE @Amount DECIMAL(12,2);
DECLARE @TxnCount INT;
DECLARE @GrowthFactor DECIMAL(5,3);
DECLARE @SeasonalFactor DECIMAL(5,3);

DECLARE date_cursor CURSOR FOR
    SELECT DateKey, Year, MonthNum FROM DimDate ORDER BY DateKey;

OPEN date_cursor;
FETCH NEXT FROM date_cursor INTO @DateKey, @Year, @MonthNum;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @GrowthFactor = 1.0 + ((@Year - 2023) * 0.05);

    SET @SeasonalFactor = CASE
        WHEN @MonthNum IN (6, 7, 8) THEN 1.15
        WHEN @MonthNum IN (12, 1, 2) THEN 0.88
        WHEN @MonthNum IN (3, 4, 5, 9, 10, 11) THEN 1.0
    END;

    SET @CatKey = 1;
    WHILE @CatKey <= 4
    BEGIN
        SET @BaseAmount = CASE @CatKey
            WHEN 1 THEN 850000.00
            WHEN 2 THEN 620000.00
            WHEN 3 THEN 95000.00
            WHEN 4 THEN 45000.00
        END;

        SET @StatKey = 1;
        WHILE @StatKey <= 4
        BEGIN
            SET @Amount = @BaseAmount * @GrowthFactor * @SeasonalFactor * CASE @StatKey
                WHEN 1 THEN 0.82
                WHEN 2 THEN 0.10
                WHEN 3 THEN 0.05
                WHEN 4 THEN 0.03
            END;

            SET @Amount = @Amount * (0.95 + (ABS(CHECKSUM(NEWID())) % 11) * 0.01);

            SET @TxnCount = CAST(@Amount / 75.00 AS INT);

            INSERT INTO FactRevenue (DateKey, CategoryKey, StatusKey, Amount, TransactionCount)
            VALUES (@DateKey, @CatKey, @StatKey, ROUND(@Amount, 2), @TxnCount);

            SET @StatKey = @StatKey + 1;
        END;

        SET @CatKey = @CatKey + 1;
    END;

    FETCH NEXT FROM date_cursor INTO @DateKey, @Year, @MonthNum;
END;

CLOSE date_cursor;
DEALLOCATE date_cursor;
GO

Your star schema is complete with 36 months of revenue data ready for analysis. Next up, you'll write SQL views that use CTEs and window functions to calculate month-over-month changes, year-over-year comparisons, and category breakdowns.

Build SQL Views for Revenue Analysis

Your database now has a solid foundation with dimension tables, a fact table, and 36 months of realistic revenue data. The raw numbers are in place, but they don't tell a story on their own.

In this step, you'll create SQL views that transform raw data into analytical insights. Each view uses a Common Table Expression (CTE) to organize the query logic. Two of the three views also use window functions to compare revenue across time periods.

In this step, get ready to:
  • Create a monthly revenue summary view with bad debt and collection rate metrics.
  • Create a MoM and YoY comparison view using window functions.
  • Create a revenue-by-category breakdown view with percentage-of-total calculations.
Create the monthly revenue summary view

The first view answers a fundamental question for financial stakeholders: how much revenue came in each month, and how much of it was actually collected? It also calculates the bad debt rate and collection rate as percentages.

  • In Azure Data Studio, open a new query tab by clicking New Query in the toolbar.
  • Create the first view by pasting and running this code:
USE RevenueAnalytics;
GO

-- View 1: Monthly Revenue Summary
CREATE OR ALTER VIEW vw_MonthlyRevenueSummary AS
WITH MonthlyAgg AS (
    SELECT
        d.Year,
        d.MonthNum,
        d.MonthName,
        d.FullDate,
        SUM(f.Amount) AS TotalRevenue,
        SUM(CASE WHEN ps.StatusName = 'Collected' THEN f.Amount ELSE 0 END) AS CollectedRevenue,
        SUM(CASE WHEN ps.StatusName = 'BadDebt' THEN f.Amount ELSE 0 END) AS BadDebtAmount,
        SUM(CASE WHEN ps.StatusName = 'Unbillable' THEN f.Amount ELSE 0 END) AS UnbillableAmount,
        SUM(f.TransactionCount) AS TotalTransactions
    FROM FactRevenue f
    INNER JOIN DimDate d ON f.DateKey = d.DateKey
    INNER JOIN DimPaymentStatus ps ON f.StatusKey = ps.StatusKey
    GROUP BY d.Year, d.MonthNum, d.MonthName, d.FullDate
)
SELECT
    Year,
    MonthNum,
    MonthName,
    FullDate,
    TotalRevenue,
    CollectedRevenue,
    BadDebtAmount,
    UnbillableAmount,
    TotalTransactions,
    ROUND(BadDebtAmount / NULLIF(TotalRevenue, 0) * 100, 2) AS BadDebtRate,
    ROUND(CollectedRevenue / NULLIF(TotalRevenue, 0) * 100, 2) AS CollectionRate
FROM MonthlyAgg;
GO

What does this code do?

  • The WITH MonthlyAgg AS block defines a CTE that joins FactRevenue with DimDate and DimPaymentStatus, then groups by month to aggregate revenue totals.
  • The CASE WHEN expressions act like IF statements inside the SUM. They split revenue into buckets by payment status: collected, bad debt, and unbillable.
  • The outer SELECT computes BadDebtRate and CollectionRate as percentages. NULLIF(TotalRevenue, 0) prevents divide-by-zero errors.

What is a CTE?

A Common Table Expression is a temporary result set that you define at the top of a query with the WITH keyword. It only exists for the duration of that query.

CTEs make complex queries easier to read by breaking them into named steps. The outer SELECT then references the CTE like a regular table.

  • Verify the view by querying it. Run this in a new query tab:
SELECT TOP 5 * FROM vw_MonthlyRevenueSummary ORDER BY Year, MonthNum;

You should see 5 rows with columns for Year, MonthNum, MonthName, TotalRevenue, CollectedRevenue, BadDebtAmount, UnbillableAmount, TotalTransactions, BadDebtRate, and CollectionRate.

Seeing an error when creating the view?

  • Check that you selected the correct database. The USE RevenueAnalytics; line at the top must run before the CREATE OR ALTER VIEW statement.
  • Make sure you highlight and run the entire block of code, including the GO statements. In Azure Data Studio, GO is a batch separator that tells the server to execute everything above it.
  • If you see Invalid object name 'FactRevenue', confirm that you ran the scripts from Step 2 to create and populate the tables.

Still stuck? Ask for help: Why is my vw_MonthlyRevenueSummary view failing to create in SQL Server?

Create the MoM and YoY comparison view

Stakeholders don't just want to see this month's revenue. They want to know if it went up or down compared to last month and the same month last year. This view uses LAG() to look back at previous rows and calculate those comparisons automatically.

  • Add the second view to your query file by pasting and running this code below the first view:
-- View 2: MoM and YoY Revenue Comparisons
CREATE OR ALTER VIEW vw_RevenueComparisons AS
WITH MonthlyTotals AS (
    SELECT
        d.Year,
        d.MonthNum,
        d.MonthName,
        d.FullDate,
        SUM(f.Amount) AS TotalRevenue
    FROM FactRevenue f
    INNER JOIN DimDate d ON f.DateKey = d.DateKey
    GROUP BY d.Year, d.MonthNum, d.MonthName, d.FullDate
)
SELECT
    Year,
    MonthNum,
    MonthName,
    FullDate,
    TotalRevenue,
    LAG(TotalRevenue, 1) OVER (ORDER BY Year, MonthNum) AS PriorMonthRevenue,
    TotalRevenue - LAG(TotalRevenue, 1) OVER (ORDER BY Year, MonthNum) AS MoM_Change,
    ROUND(
        (TotalRevenue - LAG(TotalRevenue, 1) OVER (ORDER BY Year, MonthNum))
        / NULLIF(LAG(TotalRevenue, 1) OVER (ORDER BY Year, MonthNum), 0) * 100,
    2) AS MoM_PctChange,
    LAG(TotalRevenue, 12) OVER (ORDER BY Year, MonthNum) AS PriorYearRevenue,
    TotalRevenue - LAG(TotalRevenue, 12) OVER (ORDER BY Year, MonthNum) AS YoY_Change,
    ROUND(
        (TotalRevenue - LAG(TotalRevenue, 12) OVER (ORDER BY Year, MonthNum))
        / NULLIF(LAG(TotalRevenue, 12) OVER (ORDER BY Year, MonthNum), 0) * 100,
    2) AS YoY_PctChange,
    SUM(TotalRevenue) OVER (
        PARTITION BY Year
        ORDER BY MonthNum
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RunningAnnualTotal
FROM MonthlyTotals;
GO

What does this code do?

  • LAG(TotalRevenue, 1) looks back 1 row (the previous month) to get last month's revenue. Subtracting it from the current month gives you the month-over-month change.
  • LAG(TotalRevenue, 12) looks back 12 rows (the same month in the prior year) for the year-over-year comparison.
  • SUM(TotalRevenue) OVER (PARTITION BY Year ...) calculates a running total that resets at the start of each calendar year. This is the cumulative annual revenue that builds month by month.

What is a window function?

A window function performs a calculation across a set of rows that are related to the current row. Unlike GROUP BY, which collapses rows into one, window functions keep every row and add computed columns alongside them.

The OVER() clause defines the "window" of rows to look at. ORDER BY controls the sequence, and PARTITION BY resets the calculation for each group.

  • Verify the view by running this query:
SELECT * FROM vw_RevenueComparisons ORDER BY Year, MonthNum;

You should see 36 rows of data. The first row (January 2023) has NULL values for PriorMonthRevenue and PriorYearRevenue because there is no earlier data to compare against. The YoY columns populate starting from January 2024 (12 months in).

All MoM or YoY columns showing NULL?

  • NULLs in the first row are expected. LAG() returns NULL when there is no prior row to look at.
  • If every row shows NULL, check that your ORDER BY Year, MonthNum is inside the OVER() clause. Without it, SQL Server cannot determine which row is "previous."

Need more help? Why are my LAG window function columns all returning NULL in SQL Server?

Create the revenue-by-category breakdown view

The final view breaks revenue down by category (LPT, ExpressToll, InterestIncome, LateFees) so you can see which revenue streams are growing and which are shrinking. It also calculates each category's share of total monthly revenue.

  • Add the third view to your query file by pasting and running this code below the second view:
-- View 3: Revenue by Category Summary
CREATE OR ALTER VIEW vw_RevenueByCategorySummary AS
WITH CategoryMonthly AS (
    SELECT
        d.Year,
        d.MonthNum,
        d.MonthName,
        d.FullDate,
        rc.CategoryName,
        SUM(f.Amount) AS CategoryRevenue,
        SUM(f.TransactionCount) AS CategoryTransactions
    FROM FactRevenue f
    INNER JOIN DimDate d ON f.DateKey = d.DateKey
    INNER JOIN DimRevenueCategory rc ON f.CategoryKey = rc.CategoryKey
    GROUP BY d.Year, d.MonthNum, d.MonthName, d.FullDate, rc.CategoryName
)
SELECT
    Year,
    MonthNum,
    MonthName,
    FullDate,
    CategoryName,
    CategoryRevenue,
    CategoryTransactions,
    ROUND(
        CategoryRevenue / NULLIF(SUM(CategoryRevenue) OVER (PARTITION BY Year, MonthNum), 0) * 100,
    2) AS PctOfMonthlyTotal,
    LAG(CategoryRevenue, 1) OVER (PARTITION BY CategoryName ORDER BY Year, MonthNum) AS PriorMonthCategoryRevenue,
    CategoryRevenue - LAG(CategoryRevenue, 1) OVER (PARTITION BY CategoryName ORDER BY Year, MonthNum) AS MoM_CategoryChange
FROM CategoryMonthly;
GO

What does this code do?

  • The CTE joins FactRevenue with DimDate and DimRevenueCategory, grouping by both month and category to get revenue per category per month.
  • SUM(CategoryRevenue) OVER (PARTITION BY Year, MonthNum) calculates the total monthly revenue across all categories. Dividing each category's revenue by this total gives the percentage share.
  • The LAG() here is partitioned by CategoryName. This means it compares each category to its own prior month, not the prior row in general.
  • Verify the view by running this query:
SELECT TOP 10 * FROM vw_RevenueByCategorySummary ORDER BY Year, MonthNum, CategoryName;

You should see rows grouped by month with four categories per month. The PctOfMonthlyTotal column shows each category's share, and LPT should be the largest contributor (around 52-53%).

Seeing unexpected percentage values?

  • Check that the PARTITION BY in the SUM() OVER() uses both Year, MonthNum. If you only partition by one, the total spans multiple months and the percentages will be wrong.
  • Make sure the four percentage values within the same month add up to roughly 100%. Small rounding differences are normal.

Need more help? Why are my category percentage-of-total values not adding up to 100% in SQL Server?

Why use views instead of running these queries directly?

Views save complex query logic so you can reuse it with a simple SELECT * FROM vw_ViewName. Anyone who connects to the database gets the same analytical calculations without rewriting the SQL.

In the next step, you'll export the output of these views as CSV files for Tableau. The views act as a clean analytical layer between the raw data and the dashboard.

✔️ Awesome, I've got everything!

Great. Double check you've saved your 05_create_views.sql file and that all three views return results when queried.

ⓧ I'd like to double check the full code

Here is the complete 05_create_views.sql file with all three views. Compare your code against this:

USE RevenueAnalytics;
GO

-- View 1: Monthly Revenue Summary
CREATE OR ALTER VIEW vw_MonthlyRevenueSummary AS
WITH MonthlyAgg AS (
    SELECT
        d.Year,
        d.MonthNum,
        d.MonthName,
        d.FullDate,
        SUM(f.Amount) AS TotalRevenue,
        SUM(CASE WHEN ps.StatusName = 'Collected' THEN f.Amount ELSE 0 END) AS CollectedRevenue,
        SUM(CASE WHEN ps.StatusName = 'BadDebt' THEN f.Amount ELSE 0 END) AS BadDebtAmount,
        SUM(CASE WHEN ps.StatusName = 'Unbillable' THEN f.Amount ELSE 0 END) AS UnbillableAmount,
        SUM(f.TransactionCount) AS TotalTransactions
    FROM FactRevenue f
    INNER JOIN DimDate d ON f.DateKey = d.DateKey
    INNER JOIN DimPaymentStatus ps ON f.StatusKey = ps.StatusKey
    GROUP BY d.Year, d.MonthNum, d.MonthName, d.FullDate
)
SELECT
    Year,
    MonthNum,
    MonthName,
    FullDate,
    TotalRevenue,
    CollectedRevenue,
    BadDebtAmount,
    UnbillableAmount,
    TotalTransactions,
    ROUND(BadDebtAmount / NULLIF(TotalRevenue, 0) * 100, 2) AS BadDebtRate,
    ROUND(CollectedRevenue / NULLIF(TotalRevenue, 0) * 100, 2) AS CollectionRate
FROM MonthlyAgg;
GO

-- View 2: MoM and YoY Revenue Comparisons
CREATE OR ALTER VIEW vw_RevenueComparisons AS
WITH MonthlyTotals AS (
    SELECT
        d.Year,
        d.MonthNum,
        d.MonthName,
        d.FullDate,
        SUM(f.Amount) AS TotalRevenue
    FROM FactRevenue f
    INNER JOIN DimDate d ON f.DateKey = d.DateKey
    GROUP BY d.Year, d.MonthNum, d.MonthName, d.FullDate
)
SELECT
    Year,
    MonthNum,
    MonthName,
    FullDate,
    TotalRevenue,
    LAG(TotalRevenue, 1) OVER (ORDER BY Year, MonthNum) AS PriorMonthRevenue,
    TotalRevenue - LAG(TotalRevenue, 1) OVER (ORDER BY Year, MonthNum) AS MoM_Change,
    ROUND(
        (TotalRevenue - LAG(TotalRevenue, 1) OVER (ORDER BY Year, MonthNum))
        / NULLIF(LAG(TotalRevenue, 1) OVER (ORDER BY Year, MonthNum), 0) * 100,
    2) AS MoM_PctChange,
    LAG(TotalRevenue, 12) OVER (ORDER BY Year, MonthNum) AS PriorYearRevenue,
    TotalRevenue - LAG(TotalRevenue, 12) OVER (ORDER BY Year, MonthNum) AS YoY_Change,
    ROUND(
        (TotalRevenue - LAG(TotalRevenue, 12) OVER (ORDER BY Year, MonthNum))
        / NULLIF(LAG(TotalRevenue, 12) OVER (ORDER BY Year, MonthNum), 0) * 100,
    2) AS YoY_PctChange,
    SUM(TotalRevenue) OVER (
        PARTITION BY Year
        ORDER BY MonthNum
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RunningAnnualTotal
FROM MonthlyTotals;
GO

-- View 3: Revenue by Category Summary
CREATE OR ALTER VIEW vw_RevenueByCategorySummary AS
WITH CategoryMonthly AS (
    SELECT
        d.Year,
        d.MonthNum,
        d.MonthName,
        d.FullDate,
        rc.CategoryName,
        SUM(f.Amount) AS CategoryRevenue,
        SUM(f.TransactionCount) AS CategoryTransactions
    FROM FactRevenue f
    INNER JOIN DimDate d ON f.DateKey = d.DateKey
    INNER JOIN DimRevenueCategory rc ON f.CategoryKey = rc.CategoryKey
    GROUP BY d.Year, d.MonthNum, d.MonthName, d.FullDate, rc.CategoryName
)
SELECT
    Year,
    MonthNum,
    MonthName,
    FullDate,
    CategoryName,
    CategoryRevenue,
    CategoryTransactions,
    ROUND(
        CategoryRevenue / NULLIF(SUM(CategoryRevenue) OVER (PARTITION BY Year, MonthNum), 0) * 100,
    2) AS PctOfMonthlyTotal,
    LAG(CategoryRevenue, 1) OVER (PARTITION BY CategoryName ORDER BY Year, MonthNum) AS PriorMonthCategoryRevenue,
    CategoryRevenue - LAG(CategoryRevenue, 1) OVER (PARTITION BY CategoryName ORDER BY Year, MonthNum) AS MoM_CategoryChange
FROM CategoryMonthly;
GO

Your analytical layer is now in place. Three views transform raw fact table data into insights that stakeholders actually care about. Next up, you'll create a stored procedure for flexible date-range reporting and then bring everything into Tableau.

Create a Parameterized Stored Procedure

Your three views now surface monthly summaries, period-over-period comparisons, and category breakdowns in a single query each.

Views always return the full dataset, though. When a stakeholder asks for revenue numbers for a specific quarter or fiscal year, you need a way to filter on the fly. In this step, you will create a stored procedure that accepts start and end date parameters and returns a filtered revenue report on demand.

In this step, get ready to:
  • Write a parameterized stored procedure that filters revenue data by date range.
  • Test the procedure with a single quarter and a full year of data.
Write the stored procedure for flexible revenue reporting

A stored procedure is a saved SQL script that lives inside the database and accepts input parameters. Instead of rewriting a query each time someone asks for a different date range, you call the procedure with the dates you need.

  • Open a new query tab in Azure Data Studio by clicking New Query in the toolbar.
  • Paste the following code to create the usp_GetRevenueReport stored procedure:
USE RevenueAnalytics;
GO

CREATE OR ALTER PROCEDURE usp_GetRevenueReport
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SET NOCOUNT ON;

    WITH FilteredRevenue AS (
        SELECT
            d.Year,
            d.MonthNum,
            d.MonthName,
            d.FullDate,
            ps.StatusName,
            f.Amount,
            f.TransactionCount
        FROM FactRevenue f
        INNER JOIN DimDate d ON f.DateKey = d.DateKey
        INNER JOIN DimPaymentStatus ps ON f.StatusKey = ps.StatusKey
        WHERE d.FullDate >= @StartDate AND d.FullDate <= @EndDate
    )
    SELECT
        Year,
        MonthNum,
        MonthName,
        FullDate,
        SUM(Amount) AS TotalRevenue,
        SUM(CASE WHEN StatusName = 'Collected' THEN Amount ELSE 0 END) AS CollectedRevenue,
        SUM(CASE WHEN StatusName = 'BadDebt' THEN Amount ELSE 0 END) AS BadDebtAmount,
        SUM(CASE WHEN StatusName = 'Unbillable' THEN Amount ELSE 0 END) AS UnbillableAmount,
        SUM(TransactionCount) AS TotalTransactions,
        ROUND(
            SUM(CASE WHEN StatusName = 'BadDebt' THEN Amount ELSE 0 END)
            / NULLIF(SUM(Amount), 0) * 100,
        2) AS BadDebtRate,
        ROUND(
            SUM(CASE WHEN StatusName = 'Collected' THEN Amount ELSE 0 END)
            / NULLIF(SUM(Amount), 0) * 100,
        2) AS CollectionRate
    FROM FilteredRevenue
    GROUP BY Year, MonthNum, MonthName, FullDate
    ORDER BY Year, MonthNum;
END;
GO

What does this code do?

  • CREATE OR ALTER PROCEDURE creates the procedure if it does not exist, or updates it if it does. This lets you re-run the script safely during development.
  • @StartDate and @EndDate are the input parameters. Whoever calls this procedure passes in the date range they want.
  • SET NOCOUNT ON suppresses the "rows affected" messages so the output stays clean.
  • The CTE FilteredRevenue joins FactRevenue with DimDate and DimPaymentStatus, then filters rows to only include dates within the parameter range.
  • The outer SELECT aggregates the filtered data by month, calculating the same metrics you see in vw_MonthlyRevenueSummary: total revenue, collected revenue, bad debt, unbillable, bad debt rate, and collection rate.
  • Click Run in the toolbar to execute the query.

You should see Commands completed successfully in the messages panel at the bottom. The procedure is now saved inside the RevenueAnalytics database.

Seeing an error instead of Commands completed?

  • Check that the first two lines are USE RevenueAnalytics; GO. Without these, SQL Server tries to create the procedure in the wrong database.
  • If you see Invalid object name 'FactRevenue', make sure you ran all the scripts from the previous steps (table creation and data population) in order.
  • help me debug my stored procedure creation error

Why a stored procedure instead of another view?

Views are great for repeatable analysis, but they cannot accept parameters. Every time you query a view, it returns the full dataset and you filter with a WHERE clause in the outer query.

A stored procedure pushes the filtering inside the database engine itself. Stakeholders (or a reporting tool) call it with the exact date range they need, and the database only processes the relevant rows. This is the standard pattern for parameterized reporting in production BI environments.

Test the stored procedure with different date ranges

The procedure is saved in the database and ready to call. You will test it with two different date ranges to confirm it filters correctly.

  • Add the following test queries below your CREATE OR ALTER PROCEDURE block in the same query tab:
-- Test with different date ranges
EXEC usp_GetRevenueReport @StartDate = '2025-01-01', @EndDate = '2025-03-31';
GO

EXEC usp_GetRevenueReport @StartDate = '2024-01-01', @EndDate = '2024-12-31';
GO

What do these test calls do?

  • The first EXEC calls the procedure for Q1 2025 (January through March). You should get 3 rows back, one per month.
  • The second EXEC calls it for the full year 2024 (January through December). You should get 12 rows back.
  • Highlight both EXEC statements (from the comment line through the final GO), then click Run.

You should see two result sets appear in the results panel. The first shows 3 rows (January, February, March 2025). The second shows 12 rows (every month of 2024).

  • Verify the output by comparing the TotalRevenue, BadDebtRate, and CollectionRate columns to the same months in vw_MonthlyRevenueSummary. The numbers should match because both use the same underlying aggregation logic.

Not seeing any results?

  • Make sure you highlighted only the EXEC statements before clicking Run. If you run the entire file, the CREATE OR ALTER PROCEDURE block executes again first, which is harmless, but can make it harder to spot the result sets.
  • If you see Could not find stored procedure 'usp_GetRevenueReport', go back and run the full file (including the CREATE OR ALTER block) first to create the procedure.
  • Check that the date format uses single quotes around YYYY-MM-DD values, like '2025-01-01'.
  • help me troubleshoot my stored procedure EXEC results

✔️ Awesome, I've got everything!

Great. Double check you have saved 06_create_stored_procedure.sql for reference.

ⓧ I'd like to double check the full code

Here is the complete 06_create_stored_procedure.sql file. Make sure your version matches this exactly.

USE RevenueAnalytics;
GO

CREATE OR ALTER PROCEDURE usp_GetRevenueReport
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SET NOCOUNT ON;

    WITH FilteredRevenue AS (
        SELECT
            d.Year,
            d.MonthNum,
            d.MonthName,
            d.FullDate,
            ps.StatusName,
            f.Amount,
            f.TransactionCount
        FROM FactRevenue f
        INNER JOIN DimDate d ON f.DateKey = d.DateKey
        INNER JOIN DimPaymentStatus ps ON f.StatusKey = ps.StatusKey
        WHERE d.FullDate >= @StartDate AND d.FullDate <= @EndDate
    )
    SELECT
        Year,
        MonthNum,
        MonthName,
        FullDate,
        SUM(Amount) AS TotalRevenue,
        SUM(CASE WHEN StatusName = 'Collected' THEN Amount ELSE 0 END) AS CollectedRevenue,
        SUM(CASE WHEN StatusName = 'BadDebt' THEN Amount ELSE 0 END) AS BadDebtAmount,
        SUM(CASE WHEN StatusName = 'Unbillable' THEN Amount ELSE 0 END) AS UnbillableAmount,
        SUM(TransactionCount) AS TotalTransactions,
        ROUND(
            SUM(CASE WHEN StatusName = 'BadDebt' THEN Amount ELSE 0 END)
            / NULLIF(SUM(Amount), 0) * 100,
        2) AS BadDebtRate,
        ROUND(
            SUM(CASE WHEN StatusName = 'Collected' THEN Amount ELSE 0 END)
            / NULLIF(SUM(Amount), 0) * 100,
        2) AS CollectionRate
    FROM FilteredRevenue
    GROUP BY Year, MonthNum, MonthName, FullDate
    ORDER BY Year, MonthNum;
END;
GO

-- Test with different date ranges
EXEC usp_GetRevenueReport @StartDate = '2025-01-01', @EndDate = '2025-03-31';
GO

EXEC usp_GetRevenueReport @StartDate = '2024-01-01', @EndDate = '2024-12-31';
GO

Your SQL analytical layer is complete. You have views for broad analysis and a stored procedure for targeted reporting. Next up, you will export this data and build an interactive Tableau dashboard to visualize everything.

Export Data and Build the Tableau Dashboard

Your SQL analytical layer is complete. You have views that calculate month-over-month changes, year-over-year comparisons, bad debt ratios, and collection rates.

Now it is time to bring that data to life. In this step, you will export the view results as CSV files, connect them to Tableau Desktop Public Edition, and build an interactive dashboard with KPI cards, trend lines, and filters.

In this step, get ready to:
  • Export three analytical views from Azure Data Studio as CSV files.
  • Build KPI cards, a revenue trend line, and category breakdown charts in Tableau.
  • Publish your finished dashboard to your Tableau Public profile.
Export the analytical views to CSV files

Tableau Desktop Public Edition does not include a SQL Server connector. It supports file-based data sources like CSV, Excel, and JSON.

To get your analytical data into Tableau, you will run each view as a query in Azure Data Studio and save the results as CSV files.

Why export to CSV instead of connecting directly?

Tableau Public Edition only supports file-based data sources like CSV, Excel, JSON, and Google Sheets. It does not support live database connections to SQL Server or other relational databases.

Exporting your view results to CSV is the standard workaround. Your views already do all the heavy analytical work, so the CSV files contain clean, pre-aggregated data ready for visualization.

  • Create a folder on your Desktop called revenue-exports. This is where you will save all three CSV files.
  • In Azure Data Studio, open a new query tab connected to RevenueAnalytics.
  • Run the first view query to load the monthly revenue summary by running:
SELECT * FROM vw_MonthlyRevenueSummary ORDER BY Year, MonthNum;
  • In the results grid, look for the action bar on the right side of the results panel.
  • Click the Save as CSV icon (the first icon in the action bar).
  • Navigate to your revenue-exports folder on the Desktop.
  • Name the file vw_MonthlyRevenueSummary.csv and click Save.

Alternative export method

You can also right-click anywhere in the results grid and select Save as CSV from the context menu. Both methods produce the same output with column headers included.

  • Run the second view query for revenue comparisons by running:
SELECT * FROM vw_RevenueComparisons ORDER BY Year, MonthNum;
  • Click the Save as CSV icon in the results action bar.
  • Save the file as vw_RevenueComparisons.csv in your revenue-exports folder.
  • Run the third view query for category breakdowns by running:
SELECT * FROM vw_RevenueByCategorySummary ORDER BY Year, MonthNum, CategoryName;
  • Click the Save as CSV icon in the results action bar.
  • Save the file as vw_RevenueByCategorySummary.csv in your revenue-exports folder.

Open the revenue-exports folder in Finder. You should see three CSV files: vw_MonthlyRevenueSummary.csv, vw_RevenueComparisons.csv, and vw_RevenueByCategorySummary.csv.

Don't see the Save as CSV icon?

Make sure you have run the query first. The action bar with export icons only appears after results load in the results grid.

If you are using VS Code with the MSSQL extension instead of Azure Data Studio, right-click the results grid and select Save as CSV.

Help me export query results to CSV from Azure Data Studio.

Connect Tableau to the CSV files and build KPI cards

With your CSV files ready, you can connect them to Tableau and start building visualizations. You will create three KPI card worksheets and a monthly revenue trend line chart.

  • Press Cmd+Space (macOS) to open Spotlight.
  • Type Tableau and press Enter to open Tableau Desktop Public Edition.
  • On the start screen, under the Connect pane on the left, click Text file under the To a File section.
  • Navigate to the revenue-exports folder on your Desktop.
  • Select vw_MonthlyRevenueSummary.csv and click Open.

Tableau takes you to the Data Source page. You should see a preview grid with columns like Year, TotalRevenue, BadDebtRate, and CollectionRate.

Why does Tableau call CSVs text files?

Tableau groups all flat file formats (CSV, TSV, TXT, TAB) under a single Text file connector. There is no separate "CSV" option. When you click Text file, Tableau auto-detects the delimiter and column types from your file.

  • Add the second data source by clicking Data in the top menu bar.
  • Click New Data Source.
  • Click Text file again.
  • Select vw_RevenueComparisons.csv and click Open.
  • Repeat the same steps to add vw_RevenueByCategorySummary.csv as a third data source.

You now have three data sources loaded in Tableau. You can switch between them using the dropdown at the top of the Data pane on any worksheet.

Build the Total Revenue KPI card

A KPI card is a single large number highlighting one key metric. Dashboards place KPI cards at the top so stakeholders see the most important figures at a glance.

  • Click the New Worksheet tab at the bottom of the screen (the icon with a plus sign).
  • In the Data pane on the left, make sure vw_MonthlyRevenueSummary is selected as the data source using the dropdown at the top.
  • Drag TotalRevenue from the Measures area to the Text mark on the Marks card.
  • Right-click the SUM(TotalRevenue) pill on the Marks card and select Format.
  • In the Format pane, under Numbers, select Currency (Custom) and set decimal places to 0.
  • Double-click the worksheet tab at the bottom and rename it to KPI - Total Revenue.

You should see a single large dollar amount displayed on the worksheet. This is the sum of all revenue across every month in the data.

Build the Bad Debt Rate and Collection Rate KPI cards
  • Click the New Worksheet tab.
  • Make sure vw_MonthlyRevenueSummary is still the selected data source.
  • Drag BadDebtRate to the Text mark on the Marks card.
  • Right-click the pill on the Marks card and change the aggregation to Average if it is not already set that way.
  • Right-click the pill again and select Format. Under Numbers, select Number (Custom) with 2 decimal places and add a % suffix.
  • Rename the worksheet tab to KPI - Bad Debt Rate.
  • Click the New Worksheet tab.
  • Drag CollectionRate to the Text mark.
  • Set the aggregation to Average.
  • Format the number with 2 decimal places and a % suffix.
  • Rename the worksheet tab to KPI - Collection Rate.

Why use Average instead of Sum for rates?

The BadDebtRate and CollectionRate columns are already calculated as percentages for each month by your SQL view. Summing them would add percentages together, which is meaningless. Averaging gives you the typical monthly rate across the entire dataset.

Build the monthly revenue trend line
  • Click the New Worksheet tab.
  • Make sure vw_MonthlyRevenueSummary is the selected data source.
  • Drag FullDate to the Columns shelf.
  • Tableau may default to grouping by YEAR. Right-click the FullDate pill on the Columns shelf.
  • Select the second Month option in the dropdown. This is the continuous version that shows actual dates, not just month names.
  • Drag TotalRevenue to the Rows shelf.
  • If the chart shows bars instead of a line, click the dropdown on the Marks card and select Line.
  • Rename the worksheet tab to Revenue Trend.

You should see a line chart showing monthly revenue from January 2023 through December 2025. Notice the seasonal dips in winter months and peaks in summer.

Don't see the right columns in the Data pane?

Make sure the correct data source is selected at the top of the Data pane. Click the data source name dropdown to switch between your three CSVs.

If columns appear as text instead of numbers, right-click the field in the Data pane, select Change Data Type, and choose Number (decimal).

Help me fix data type issues in Tableau.

You should now have four worksheet tabs at the bottom of Tableau: KPI - Total Revenue, KPI - Bad Debt Rate, KPI - Collection Rate, and Revenue Trend.

Build the complete dashboard and publish

Now you will create two more chart worksheets, assemble everything into a single dashboard, add interactive filters, and publish to your Tableau Public profile.

  • Click the New Worksheet tab.
  • Switch the data source to vw_RevenueByCategorySummary using the dropdown at the top of the Data pane.
  • Drag CategoryName to the Rows shelf.
  • Drag CategoryRevenue to the Columns shelf.
  • Rename the worksheet tab to Revenue by Category.

You should see a horizontal bar chart with four bars. LPT has the largest bar, followed by ExpressToll, InterestIncome, and LateFees.

  • Click the New Worksheet tab.
  • Switch the data source to vw_RevenueComparisons.
  • Drag FullDate to the Columns shelf. Right-click the pill and select the continuous Month option (the second Month in the list).
  • Drag MoM_PctChange to the Rows shelf.
  • On the Marks card, change the mark type to Bar if it is not already set.
  • Drag MoM_PctChange to the Color mark on the Marks card.
  • Click the color legend that appears and select Edit Colors.
  • Choose a diverging red-green palette. Set the center to 0 so that positive months appear green and negative months appear red.
  • Click OK to apply the colors.
  • Rename the worksheet tab to MoM Change.

You should see a bar chart where green bars represent months with revenue growth and red bars represent months with a decline. The first month (January 2023) will be empty because there is no prior month to compare against.

Assemble the dashboard
  • Click Dashboard in the top menu bar.
  • Click New Dashboard.
  • In the Size section on the left panel, click the dropdown and select Automatic so the dashboard adapts to different screen sizes.
  • Drag KPI - Total Revenue from the Sheets list on the left to the top of the dashboard canvas.
  • Drag KPI - Bad Debt Rate next to it to form a row.
  • Drag KPI - Collection Rate next to that, completing a row of three KPI cards across the top.
  • Drag Revenue Trend below the KPI row.
  • Drag Revenue by Category to the bottom-left area of the dashboard.
  • Drag MoM Change to the bottom-right area next to the category chart.

What is the dashboard layout?

The top row shows your three KPI cards so stakeholders see the big numbers first. The middle section shows the revenue trend over time. The bottom row breaks down the data by category on the left and month-over-month change on the right.

Add filters and cross-filtering
  • Click on the Revenue Trend chart on the dashboard to select it.
  • Click the small dropdown arrow in the top-right corner of the selected chart.
  • Select Filters, then select FullDate. A date range filter slider appears on the dashboard.
  • Click on the Revenue by Category chart to select it.
  • Click the small dropdown arrow and select Filters, then select CategoryName. A category filter dropdown appears on the dashboard.

Now enable cross-filtering so clicking a category bar filters all the other charts.

  • Click on the Revenue by Category chart on the dashboard.
  • In the top-right corner of the selected chart, click the funnel icon (the Use as Filter button).

Click on a category bar (for example, LPT). The Revenue Trend, MoM Change, and KPI cards should all update to show data for that category only. Click the bar again to deselect and show all categories.

What does Use as Filter do?

When you enable Use as Filter on a worksheet, clicking any mark in that chart sends a filter action to every other chart on the dashboard. This lets stakeholders explore the data interactively without needing to configure separate filter controls.

Publish to Tableau Public
  • Click Server in the top menu bar.
  • Click Tableau Public, then click Save to Tableau Public As.
  • Sign in with your Tableau Public account. If you do not have one, click the link to create a free account and complete the sign-up.
  • Enter Revenue Analytics Dashboard as the workbook name.
  • Click Save.

Tableau uploads your workbook and opens your browser to the published dashboard on your Tableau Public profile. The URL contains your profile name and the workbook name.

What happens when you publish?

Tableau Public creates an extract of your data and uploads the entire workbook to the cloud. Anyone with the link can view and interact with your dashboard. Workbooks published to Tableau Public are public and not private.

Dashboard not publishing?

Tableau Public requires an internet connection to upload. Check your network connection if the upload stalls.

If you see a "data source not supported" error, make sure all your data sources are CSV text files. Tableau Public does not support live database connections.

Help me publish my workbook to Tableau Public.

Your revenue analytics dashboard is live. You now have a complete BI pipeline from a star schema database all the way to an interactive published dashboard. Ready for more? The Secret Mission challenges you to add a cash flow waterfall chart and a bad debt aging analysis.

Secret mission

Add a Cash Flow Waterfall Chart and Bad Debt Aging Analysis

Your dashboard covers revenue trends and category breakdowns. Financial stakeholders also need to understand how gross revenue flows through deductions to net collected revenue, and where bad debt is accumulating over time. Extend your project with a cash flow waterfall chart and a bad debt aging analysis on a new dashboard tab.

Before you go

Clean Up Your Resources

This project runs entirely on your local machine using Docker, so there are no ongoing cloud costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • SQL Server 2022 Docker container.
  • Docker volume (sqlvolume) for database persistence.
  • RevenueAnalytics database with dimension and fact tables.
  • Azure Data Studio (installed application).
  • Exported CSV files in your local folder.
  • Tableau workbook published to Tableau Public.

Keep everything running

No action needed. Choose this if you are still actively building or plan to extend the dashboard soon.

  • Your Docker container continues running in the background and uses system memory.
  • Your Tableau Public dashboard stays live on your profile.
  • Your CSV exports and Azure Data Studio remain available on your machine.

Pause - I'll come back to this later

Stop the Docker container to free up memory while keeping your database and files intact.

  • Stop the SQL Server container by running this command in your terminal:
docker stop sql_server_2022

Your database is stored in the Docker volume, so all your data is preserved. When you are ready to resume, start the container again:

docker start sql_server_2022
  • Your Tableau Public dashboard stays published and accessible.
  • Your CSV files and Azure Data Studio stay on your machine.

Delete - I don't want to use this again

Remove all project resources from your machine and start fresh.

  • Stop and remove the SQL Server container by running these commands:
docker stop sql_server_2022
docker rm sql_server_2022
  • Remove the Docker volume that stores your database by running:
docker volume rm sqlvolume
  • Remove the SQL Server Docker image by running:
docker rmi mcr.microsoft.com/mssql/server:2022-latest
  • Delete the exported CSV files from your local folder.
  • Delete the SQL script files you created during the project.
  • To remove your Tableau Public dashboard, log in to your Tableau Public profile, open the workbook, and click the delete option.
  • Uninstall Azure Data Studio from your Applications folder if you no longer need it.

Mission accomplished

That's a Wrap!

Nice work! You just built a complete revenue analytics pipeline, from a star schema database in SQL Server all the way to a published interactive dashboard on Tableau Public.

You've learned how to:

  • Designed a star schema database with dimension and fact tables, then wrote SQL views using CTEs and window functions (LAG, SUM OVER) for month-over-month and year-over-year revenue analysis.
  • Built a parameterized stored procedure for flexible date-range reporting and tested it across multiple date ranges.
  • Designed and published an interactive Tableau dashboard with KPI cards, trend lines, category breakdowns, and filters to your Tableau Public profile.
  • Added a cash flow waterfall chart and bad debt aging analysis to take your dashboard to the next level.

Ready to quiz yourself?