# Lab: Upload Contoso Bank Data to Azure SQL Database
## Objective
Upload a production dataset (10 sheets, 4,500+ rows) from Excel to Azure SQL Database, then verify it works with the Azure SQL MCP Server in Copilot Studio.
**Duration:** 30–45 minutes
---
## Lab Files
| File | Description |
|------|-------------|
| `Contoso_Bank_Database.xlsx` | Production dataset — 10 sheets, 4,500+ rows |
| `upload_to_azure_sql.py` | Python upload script with schema, indexes, and verification |
---
## Dataset Overview
| Sheet | Rows | Description |
|-------|------|-------------|
| Branches | 20 | Bank branches across 20 Indian cities with region mapping |
| Employees | 200 | Staff with designation, department, salary, branch assignment |
| Customers | 500 | Customer profiles with PAN, income, KYC status, risk category |
| Accounts | 750 | Savings, Current, FD, RD, NRI accounts with balances |
| Transactions | 2,000 | Credits/debits across UPI, NEFT, ATM, POS, Net Banking |
| Loans | 300 | Home, Personal, Vehicle, Education, Business, Gold loans with EMI |
| Products | 10 | Bank product catalog (loans, cards, deposits, insurance) |
| Customer_Products | 600 | Product enrollments per customer |
| Complaints | 150 | Service complaints with priority, resolution tracking |
| Dashboard | — | Summary formulas (auto-calculated) |
**Total: 4,530 rows across 9 data tables + 1 dashboard**
### Relationships (Foreign Keys)
```
Branches ──┬── Employees (branch_id)
├── Customers (branch_id)
├── Accounts (branch_id)
├── Loans (branch_id)
└── Complaints (branch_id)
Customers ─┬── Accounts (customer_id)
├── Loans (customer_id)
├── Customer_Products (customer_id)
└── Complaints (customer_id)
Accounts ──── Transactions (account_id)
Products ──── Customer_Products (product_id)
```
---
## Prerequisites
Before starting, ensure you have:
- [ ] Python 3.8+ installed
- [ ] An Azure SQL Database (any tier — Basic is fine for this lab)
- [ ] Azure SQL Server firewall configured to allow your IP
- [ ] ODBC Driver 18 for SQL Server installed
### Check ODBC Driver
```powershell
# Windows PowerShell
Get-OdbcDriver | Where-Object {$_.Name -like "*SQL Server*"}
```
If not installed, download from: https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server
---
## Task 1: Set Up the Environment
### Step 1.1 — Create project folder
```powershell
mkdir contoso-bank-lab
cd contoso-bank-lab
```
### Step 1.2 — Create and activate virtual environment
```powershell
python -m venv venv
.\venv\Scripts\Activate.ps1
```
### Step 1.3 — Install dependencies
```powershell
pip install pandas pyodbc python-dotenv openpyxl
```
### Step 1.4 — Place files
Copy these files into the `contoso-bank-lab` folder:
- `Contoso_Bank_Database.xlsx`
- `upload_to_azure_sql.py`
### Step 1.5 — Create .env file
Create a file named `.env` (no filename, just extension) with your Azure SQL credentials:
```env
AZURE_SQL_SERVER=your-server.database.windows.net
AZURE_SQL_DATABASE=your-database-name
AZURE_SQL_USERNAME=your-username
AZURE_SQL_PASSWORD=your-password
AZURE_SQL_DRIVER=ODBC Driver 18 for SQL Server
```
> **Replace** the placeholder values with your actual Azure SQL credentials.
---
## Task 2: Explore the Excel File
Before uploading, open `Contoso_Bank_Database.xlsx` in Excel and explore each sheet.
### Step 2.1 — Review the Branches sheet
Note the columns: branch_id, branch_name, branch_code, city, state, region, address, pincode, phone, manager_name, opened_date, branch_type, total_staff, is_active.
**Question:** How many branches are in Maharashtra?
### Step 2.2 — Review the Customers sheet
Note the KYC and risk columns. The data includes PAN numbers, income brackets, and risk categorization.
**Question:** What occupation types are represented?
### Step 2.3 — Review the Transactions sheet
2,000 transactions across multiple channels (UPI, NEFT, ATM, etc.) and categories (Salary, EMI, Shopping, etc.).
**Question:** What transaction channels are available?
### Step 2.4 — Check the Dashboard sheet
The Dashboard sheet contains Excel formulas that summarize data from all other sheets. These formulas auto-calculate totals, averages, and counts.
---
## Task 3: Run the Upload Script
### Step 3.1 — Execute the upload
```powershell
python upload_to_azure_sql.py
```
### Step 3.2 — Expected output
```
============================================================
CONTOSO BANK — Excel to Azure SQL Upload
============================================================
Server: your-server.database.windows.net
Database: your-database-name
File: Contoso_Bank_Database.xlsx
── Connecting to Azure SQL ──
✓ Connected successfully
── Dropping existing tables ──
✓ Dropped Complaints
✓ Dropped Customer_Products
✓ Dropped Loans
✓ Dropped Transactions
✓ Dropped Accounts
✓ Dropped Customers
✓ Dropped Employees
✓ Dropped Products
✓ Dropped Branches
── Creating tables ──
✓ Created Branches
✓ Created Products
✓ Created Employees
✓ Created Customers
✓ Created Accounts
✓ Created Transactions
✓ Created Loans
✓ Created Customer_Products
✓ Created Complaints
── Reading Contoso_Bank_Database.xlsx ──
Found 10 sheets: ['Branches', 'Employees', 'Customers', ...]
── Uploading data ──
✓ Branches: 20 rows, 14 cols (0.3s)
✓ Products: 10 rows, 10 cols (0.1s)
✓ Employees: 200 rows, 14 cols (1.2s)
✓ Customers: 500 rows, 18 cols (2.8s)
✓ Accounts: 750 rows, 13 cols (3.5s)
✓ Transactions: 2000 rows, 11 cols (8.1s)
✓ Loans: 300 rows, 17 cols (1.8s)
✓ Customer_Products: 600 rows, 7 cols (2.4s)
✓ Complaints: 150 rows, 11 cols (0.9s)
── Creating indexes ──
✓ IX_Employees_BranchID
✓ IX_Customers_City
... (15 indexes total)
── Verification ──
Table Rows
───────────────────────── ────────
Branches 20
Products 10
Employees 200
Customers 500
Accounts 750
Transactions 2,000
Loans 300
Customer_Products 600
Complaints 150
───────────────────────── ────────
TOTAL 4,530
============================================================
UPLOAD COMPLETE
Total rows: 4,530
Tables: 9
Time: 21.3 seconds
============================================================
```
### Step 3.3 — Troubleshooting
| Error | Fix |
|-------|-----|
| `Login failed for user` | Check username/password in `.env` |
| `Cannot open server` | Check server name in `.env`, verify firewall allows your IP |
| `ODBC Driver 18 not found` | Install the ODBC driver (see Prerequisites) |
| `Contoso_Bank_Database.xlsx not found` | Ensure the Excel file is in the same folder |
| `Login timeout expired` | Azure SQL firewall may be blocking your IP — add it in Azure Portal |
### How to add your IP to Azure SQL Firewall
1. Go to Azure Portal → your SQL Server resource
2. Click **Networking** (under Security)
3. Click **Add your client IPv4 address**
4. Click **Save**
---
## Task 4: Verify in Azure Portal
### Step 4.1 — Open Query Editor
1. Go to Azure Portal → your SQL Database
2. Click **Query editor (preview)** in the left menu
3. Log in with your SQL credentials
### Step 4.2 — Run verification queries
**Count all tables:**
```sql
SELECT t.TABLE_NAME, p.rows as ROW_COUNT
FROM INFORMATION_SCHEMA.TABLES t
JOIN sys.partitions p ON OBJECT_ID(t.TABLE_NAME) = p.object_id
WHERE t.TABLE_TYPE = 'BASE TABLE' AND p.index_id IN (0,1)
ORDER BY p.rows DESC
```
**Check a sample customer:**
```sql
SELECT TOP 5 customer_id, first_name, last_name, city, annual_income, kyc_status, risk_category
FROM Customers
ORDER BY annual_income DESC
```
**Test a join — top customers by transaction volume:**
```sql
SELECT TOP 10
c.first_name + ' ' + c.last_name AS customer_name,
c.city,
COUNT(t.transaction_id) AS total_transactions,
SUM(t.amount) AS total_amount
FROM Customers c
JOIN Accounts a ON c.customer_id = a.customer_id
JOIN Transactions t ON a.account_id = t.account_id
GROUP BY c.first_name, c.last_name, c.city
ORDER BY total_amount DESC
```
---
## Task 5: Test with Copilot Studio MCP Agent
If you have the Azure SQL MCP Server running and connected to Copilot Studio, test these prompts:
### Basic Queries
```
"Show me all tables in the database"
"How many customers do we have?"
"List all branches in Maharashtra"
```
### Data Analysis
```
"What is the average salary by department?"
"Show me top 10 customers by annual income"
"How many loans are in default status?"
"What is the total outstanding loan balance?"
```
### Charts
```
"Show me a bar chart of customers by city"
"Create a pie chart of loan types"
"Visualize monthly transaction volume as a line chart"
"Show me account types distribution as a doughnut chart"
```
### CRUD Operations
```
"Add a new customer named Test User with email test@contoso.com in Mumbai"
"Update customer 1's KYC status to Verified"
"Search for customers named Sharma"
"What complaints are still open?"
```
---
## What the Script Does (Technical Details)
The `upload_to_azure_sql.py` script performs these steps in order:
1. **Connects** to Azure SQL using credentials from `.env`
2. **Drops** existing tables in reverse dependency order (child tables first)
3. **Creates** 9 tables with proper data types, primary keys, and foreign keys
4. **Reads** all sheets from the Excel file using pandas
5. **Uploads** data in batches of 100 rows per commit
6. **Creates** 15 non-clustered indexes for query performance
7. **Verifies** row counts match the Excel data
### Table Creation Order (Foreign Key Dependencies)
The script creates tables in this specific order to satisfy foreign key constraints:
1. `Branches` (no dependencies)
2. `Products` (no dependencies)
3. `Employees` (references Branches)
4. `Customers` (references Branches)
5. `Accounts` (references Customers, Branches)
6. `Transactions` (references Accounts)
7. `Loans` (references Customers, Branches)
8. `Customer_Products` (references Customers, Products)
9. `Complaints` (references Customers, Branches)
### Indexes Created
15 indexes are created for common query patterns:
- Branch lookups on Employees, Customers, Accounts, Loans
- City-based customer searches
- Account status filtering
- Transaction date and category filtering
- Loan status and type filtering
- Complaint status tracking
---
## Summary
In this lab you:
- [x] Explored a production-level banking dataset (10 sheets, 4,530 rows)
- [x] Configured Azure SQL Database credentials
- [x] Ran an automated upload script with schema creation, data loading, and indexing
- [x] Verified the data in Azure Portal Query Editor
- [x] Tested the data with Copilot Studio MCP Agent queries and charts
The database is now ready for use with the Azure SQL MCP Server.
