Case Study 1: Analytical Reporting Suite for Executive Dashboard
Background
Meridian National Bank's Chief Financial Officer, Diana Ramirez, has grown frustrated with the monthly reporting process. Today, the finance team spends the first five business days of every month manually assembling spreadsheets from multiple database exports. Branch managers email CSV files. Analysts copy-paste data between workbooks. By the time the executive dashboard reaches the C-suite, it is already stale, occasionally inconsistent, and impossible to drill down into.
Diana has tasked the data engineering team with building a live executive dashboard backed by a set of SQL views that the BI tool (IBM Cognos) can query directly. The requirements are specific:
- Branch Performance Summary: For each branch, show current-month deposits, prior-month deposits, month-over-month change (absolute and percentage), and rank among all branches.
- Customer Concentration Report: For each branch, show the top 5 customers by deposit volume and their share of the branch's total deposits. Flag any customer who represents more than 25% of a branch's deposits (concentration risk).
- Trend Analysis: For each branch, show a 6-month rolling average of monthly deposits overlaid with the actual monthly figures.
- YTD Scoreboard: Year-to-date cumulative deposits by branch with rank, updated daily.
- Multi-Level Summary: A single result set that shows detail (branch + month), subtotals (branch), and a grand total — suitable for a collapsible drill-down report.
All of this must be expressed in SQL that Cognos can execute without modification. No stored procedures. No application logic. Pure SQL.
The Schema
The team works with these tables (simplified for this case study):
BRANCHES(branch_id, branch_name, region, open_date)ACCOUNTS(account_id, branch_id, customer_id, account_type, current_balance, open_date)CUSTOMERS(customer_id, customer_name, signup_date)TELLER_TRANSACTIONS(txn_id, account_id, branch_id, teller_id, customer_id, txn_date, txn_amount, txn_type)
Solution: Requirement 1 — Branch Performance Summary
The team builds this as a view:
CREATE OR REPLACE VIEW V_BRANCH_PERFORMANCE AS
WITH monthly AS (
SELECT branch_id,
YEAR(txn_date) AS yr,
MONTH(txn_date) AS mo,
SUM(txn_amount) AS total_deposits,
COUNT(*) AS txn_count,
COUNT(DISTINCT customer_id) AS active_customers
FROM TELLER_TRANSACTIONS
WHERE txn_type = 'D'
GROUP BY branch_id, YEAR(txn_date), MONTH(txn_date)
)
SELECT b.branch_id,
b.branch_name,
b.region,
m.yr,
m.mo,
m.total_deposits,
m.txn_count,
m.active_customers,
LAG(m.total_deposits, 1) OVER (
PARTITION BY m.branch_id
ORDER BY m.yr, m.mo
) AS prev_month_deposits,
m.total_deposits - LAG(m.total_deposits, 1) OVER (
PARTITION BY m.branch_id
ORDER BY m.yr, m.mo
) AS mom_change,
CASE
WHEN LAG(m.total_deposits, 1) OVER (
PARTITION BY m.branch_id ORDER BY m.yr, m.mo
) > 0
THEN DECIMAL(
(m.total_deposits - LAG(m.total_deposits, 1) OVER (
PARTITION BY m.branch_id ORDER BY m.yr, m.mo
)) * 100.0 /
LAG(m.total_deposits, 1) OVER (
PARTITION BY m.branch_id ORDER BY m.yr, m.mo
), 7, 2)
ELSE NULL
END AS mom_pct_change,
RANK() OVER (
PARTITION BY m.yr, m.mo
ORDER BY m.total_deposits DESC
) AS monthly_rank
FROM monthly m
JOIN BRANCHES b ON m.branch_id = b.branch_id;
Cognos filters this view by year and month to show the current period. The month-over-month comparison is pre-computed — no application logic needed.
Solution: Requirement 2 — Customer Concentration Report
CREATE OR REPLACE VIEW V_CUSTOMER_CONCENTRATION AS
WITH branch_customer_deposits AS (
SELECT t.branch_id,
t.customer_id,
c.customer_name,
SUM(t.txn_amount) AS customer_deposits,
SUM(SUM(t.txn_amount)) OVER (
PARTITION BY t.branch_id
) AS branch_total_deposits,
RANK() OVER (
PARTITION BY t.branch_id
ORDER BY SUM(t.txn_amount) DESC
) AS customer_rank_in_branch
FROM TELLER_TRANSACTIONS t
JOIN CUSTOMERS c ON t.customer_id = c.customer_id
WHERE t.txn_type = 'D'
AND t.txn_date >= DATE(CURRENT TIMESTAMP - 1 MONTH)
GROUP BY t.branch_id, t.customer_id, c.customer_name
)
SELECT branch_id,
customer_id,
customer_name,
customer_deposits,
branch_total_deposits,
DECIMAL(customer_deposits * 100.0 / branch_total_deposits, 7, 2)
AS pct_of_branch,
customer_rank_in_branch,
CASE
WHEN customer_deposits * 100.0 / branch_total_deposits > 25.0
THEN 'CONCENTRATION RISK'
ELSE 'OK'
END AS risk_flag
FROM branch_customer_deposits
WHERE customer_rank_in_branch <= 5
ORDER BY branch_id, customer_rank_in_branch;
Key technique: SUM(SUM(t.txn_amount)) OVER (PARTITION BY t.branch_id) — the inner SUM is the GROUP BY aggregate; the outer SUM with OVER computes the branch total across all grouped rows. This nested pattern avoids a separate subquery for the branch total.
Solution: Requirement 3 — Trend Analysis (6-Month Rolling Average)
CREATE OR REPLACE VIEW V_BRANCH_TREND AS
WITH monthly AS (
SELECT branch_id,
YEAR(txn_date) AS yr,
MONTH(txn_date) AS mo,
SUM(txn_amount) AS monthly_deposits
FROM TELLER_TRANSACTIONS
WHERE txn_type = 'D'
GROUP BY branch_id, YEAR(txn_date), MONTH(txn_date)
)
SELECT branch_id,
yr,
mo,
monthly_deposits,
AVG(monthly_deposits) OVER (
PARTITION BY branch_id
ORDER BY yr, mo
ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
) AS rolling_avg_6mo,
MIN(monthly_deposits) OVER (
PARTITION BY branch_id
ORDER BY yr, mo
ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
) AS rolling_min_6mo,
MAX(monthly_deposits) OVER (
PARTITION BY branch_id
ORDER BY yr, mo
ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
) AS rolling_max_6mo,
COUNT(*) OVER (
PARTITION BY branch_id
ORDER BY yr, mo
ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
) AS months_in_window
FROM monthly;
The months_in_window column is a diagnostic — for the first 5 months of data, the window is smaller than 6 rows. Cognos can use this to annotate the chart: "rolling average based on N months."
Solution: Requirement 4 — YTD Scoreboard
CREATE OR REPLACE VIEW V_YTD_SCOREBOARD AS
WITH daily_deposits AS (
SELECT branch_id,
txn_date,
YEAR(txn_date) AS yr,
SUM(txn_amount) AS daily_deposits
FROM TELLER_TRANSACTIONS
WHERE txn_type = 'D'
GROUP BY branch_id, txn_date, YEAR(txn_date)
)
SELECT branch_id,
txn_date,
yr,
daily_deposits,
SUM(daily_deposits) OVER (
PARTITION BY branch_id, yr
ORDER BY txn_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS ytd_cumulative,
RANK() OVER (
PARTITION BY yr, txn_date
ORDER BY SUM(daily_deposits) OVER (
PARTITION BY branch_id, yr
ORDER BY txn_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) DESC
) AS ytd_rank
FROM daily_deposits;
The PARTITION BY includes yr so the cumulative total resets each January 1. The rank is recalculated daily — a branch that was ranked #3 on March 10 might move to #2 by March 15 as it accumulates more deposits.
Solution: Requirement 5 — Multi-Level Summary with ROLLUP
CREATE OR REPLACE VIEW V_MULTILEVEL_SUMMARY AS
SELECT COALESCE(b.branch_name, '** ALL BRANCHES **') AS branch_name,
COALESCE(CHAR(YEAR(t.txn_date)), '** ALL YEARS **') AS yr,
COALESCE(CHAR(MONTH(t.txn_date)), '** ALL MONTHS **') AS mo,
SUM(t.txn_amount) AS total_deposits,
COUNT(*) AS txn_count,
COUNT(DISTINCT t.customer_id) AS unique_customers,
GROUPING(b.branch_name) AS is_branch_rollup,
GROUPING(YEAR(t.txn_date)) AS is_year_rollup,
GROUPING(MONTH(t.txn_date)) AS is_month_rollup
FROM TELLER_TRANSACTIONS t
JOIN BRANCHES b ON t.branch_id = b.branch_id
WHERE t.txn_type = 'D'
GROUP BY ROLLUP(b.branch_name, YEAR(t.txn_date), MONTH(t.txn_date));
Cognos uses the is_*_rollup columns to control row styling — subtotal rows appear in bold, the grand total in a highlighted banner.
Outcome
The new reporting suite replaced the 5-day manual process with real-time views. Key outcomes:
- Time to dashboard: Reduced from 5 business days to real-time.
- Data consistency: One source of truth — no more conflicting spreadsheets.
- Drill-down capability: Cognos can filter any view by branch, date range, or customer, providing interactive exploration.
- Performance: The window function approach eliminated hundreds of correlated subqueries from the old report SQL. Average dashboard load time dropped from 45 seconds to under 3 seconds.
- Concentration risk: The customer concentration report immediately identified two branches where a single commercial customer represented over 40% of deposit volume — a regulatory finding that would have taken weeks to surface manually.
Lessons Learned
-
Window functions replace application logic: Every calculation the finance team previously did in Excel — running totals, moving averages, rank, percentage of total — can be expressed declaratively in SQL.
-
Frame specifications matter: The YTD scoreboard initially used the default frame (RANGE) and produced incorrect running totals on days with multiple transactions per branch. Switching to
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWfixed it immediately. -
ROLLUP vs. multiple queries: The multi-level summary replaced three separate queries (detail, branch subtotal, grand total) that were UNIONed in the old reports. ROLLUP produces all levels in a single pass.
-
Nested aggregates with OVER: The
SUM(SUM(...)) OVER(...)pattern in the concentration report was initially confusing to junior team members. The team adopted a standard of using CTEs to separate the GROUP BY aggregation from the window function, improving readability even when it was not strictly necessary. -
Naming views consistently: The
V_prefix convention and descriptive names made the Cognos configuration self-documenting. When auditors reviewed the dashboard, they could trace every number back to its SQL definition.
This case study demonstrates Sections 10.2-10.5 (ranking, aggregate windows, LAG) and Section 10.9 (ROLLUP) working together to solve a real-world reporting challenge.