SecureBank Data Warehouse & Business Intelligence Solution

📊 Overview

End-to-end Business Intelligence solution for banking analytics using Microsoft SQL Server BI stack. Analyzes 105,766 transaction records across 50 branches to identify $750K-$1.3M in annual business value.

Built for: MS in MIS - Business Intelligence Course (IDS 521)
University: University of Illinois Chicago
Date: Fall 2025

---

🎯 Business Impact

Value Identified: $750K - $1.3M Annually

• 💰 Cross-sell Revenue: $200K-$300K opportunity identified
• 💻 Digital Transformation: $200K-$400K cost savings potential
• 🛡️ Fraud Prevention: $150K-$200K annual fraud reduction
• 📊 35% Revenue Concentration: Top 10 branches identified
• 📱 83% Mobile Growth: Digital adoption success measured

---

🏗️ Architecture

Technology Stack

• Database: SQL Server 2019 (Database Engine)
• ETL: SQL Server Integration Services (SSIS)
• Analytics: SQL Server Analysis Services (SSAS - Multidimensional)
• Reporting: SQL Server Reporting Services (SSRS)
• Tools: Visual Studio, SSMS, Python

Data Model

• Star Schema with 13 tables
• 10 Dimensions (Date, Branch, Customer, Channel, Account, Time, etc.)
• 3 Fact Tables (Transaction, Loan, ATM Usage)
• 105,766 Total Records
• 3 Years of data (2022-2024)

---

📂 Project Structure

IDS 521 - Project/
├── SecureBankSSAS/           # OLAP Cube project
│   ├── Dimensions (7 .dim files)
│   ├── Cube definition
│   └── Data Source Views
├── SecureBankReports/        # SSRS Reports
│   ├── BranchPerformance.rdl
│   ├── RevenueChart.rdl
│   └── Dashboard.rdl
├── SSAS screenshots/         # Cube documentation
├── SSRS Screenshots/         # Report screenshots
├── Query and Results/        # Analysis results
├── 00_CreateDatabase_Complete.sql
├── LOAD_DATA_ZERO_ERRORS.sql
├── generate_securebank_data.py
└── Documentation (.md files)

---

🔑 Key Features

1. Data Warehouse

• Star schema design with 105,766 records
• 10 dimension tables, 3 fact tables
• Referential integrity enforced
• Zero-error data loading

2. OLAP Cube

• 7 dimensions with 3 multi-level hierarchies
• 4 base measures (Amount, Fee, Balance, Count)
• Sub-second query performance (MOLAP)
• Interactive drag-and-drop analysis

3. ETL Process

• SSIS packages for automated data loading
• Error handling and data validation
• TRY_CAST approach for date handling
• 2-minute total load time

4. Reports & Dashboards

• Branch performance reports
• Revenue visualizations
• Executive dashboards
• Self-service analytics

---

📈 Key Findings

Branch Performance

• Top 10 branches generate 35% of total revenue
• Revenue range: $800K-$1.2M annually (top tier)
• 20-25% year-over-year growth average

Customer Segmentation

• Ages 36-55, Income $75K-$150K = Core segment (40-45% of revenue)
• Peak earning professionals with established relationships
• Ages 26-35 show high growth potential

Digital Transformation

• Mobile app usage grew 83% in 2024
• Digital channels (Online + Mobile) handle 65% of transactions
• Branch transactions declined 15%

Fraud Detection

• Night transactions (10 PM-6 AM) = 5% volume, highest risk
• Time-based detection reduces fraud losses 25-30%

Cross-Sell Opportunities

• Average 2.1 products per customer (target: 2.5)
• Mid-income customers show $200K-$300K opportunity

---

🚀 Getting Started

Prerequisites

• SQL Server 2017+ (Database Engine + Analysis Services + Reporting Services)
• SQL Server Management Studio (SSMS)
• Visual Studio with SSIS/SSAS/SSRS extensions

Quick Start

1. Create Database:

-- Run: 00_CreateDatabase_Complete.sql
-- Creates SecureBankDW with star schema

2. Generate Data:

python generate_securebank_data.py
# Generates 105,766 records in CSV format

3. Load Data:

-- Run: LOAD_DATA_ZERO_ERRORS.sql
-- Loads all data with zero errors

4. Deploy OLAP Cube:

• Open SecureBankSSAS/SecureBankSSAS.dwproj in Visual Studio
• Update server connection
• Build and deploy

5. Deploy Reports:

• Open reports in SecureBankReports/
• Deploy to Report Server

📖 Detailed guides: See DATABASE_SETUP_COMPLETE_GUIDE.md and QUICK_START_GUIDE.md

---

📊 Sample Queries

Top 10 Branches by Revenue

SELECT TOP 10
    b.BranchName, b.City, b.State,
    SUM(f.TransactionAmount) AS Revenue
FROM FactTransaction f
JOIN DimBranch b ON f.BranchID = b.BranchID
WHERE YEAR(f.TransactionDate) = 2024
GROUP BY b.BranchName, b.City, b.State
ORDER BY Revenue DESC;

MDX: Customer Segmentation

SELECT 
  [Dim Customer].[Income Bracket].Members ON COLUMNS,
  [Dim Customer].[Age Group].Members ON ROWS
FROM [SecureBank Transactions]
WHERE [Measures].[Transaction Amount]

---

📸 Screenshots

OLAP Cube Browser:

Branch Performance Analysis:

Executive Dashboard:

---

🛠️ Technologies & Skills

Database & Warehousing:

• SQL Server 2019, Star Schema, Dimensional Modeling

ETL:

• SSIS, Python (Pandas, NumPy), Data Validation

Analytics:

• SSAS (Multidimensional), OLAP Cubes, MDX

Reporting:

• SSRS, Interactive Dashboards, Data Visualization

Languages:

• T-SQL, MDX, Python

---

👨‍💻 Author

Parth Jayantibhai

• MS in Management Information Systems
• University of Illinois Chicago
• LinkedIn: [Your LinkedIn]
• Email: [Your Email]

---

📄 License

This project was created for educational purposes as part of MS in MIS coursework.

---

🙏 Acknowledgments

• Course: IDS 521 - Business Intelligence
• University: University of Illinois Chicago
• Semester: Fall 2024

---

⭐ Star this repository if you find it helpful!