Skip to content

INSTALLATION.md

Latest commit

 

History

History
262 lines (194 loc) · 6.72 KB

INSTALLATION.md

File metadata and controls

262 lines (194 loc) · 6.72 KB

Installation and Setup Guide

Prerequisites

  • SQL Server 2019 or later (Express, Standard, or Enterprise Edition)
  • SQL Server Management Studio (SSMS) 18.0 or later
  • OR Azure Data Studio 1.30 or later

Quick Start

Option 1: Using SQL Server Management Studio (SSMS)

  1. Connect to SQL Server

    • Open SQL Server Management Studio
    • Connect to your SQL Server instance

  2. Execute Scripts in Order

    -- Step 1: Create Database
-- Open: 01-Schema/01-CreateDatabase.sql
-- Note: Modify file paths in the script to match your SQL Server configuration
-- Execute the script

-- Step 2: Create Tables
-- Open: 01-Schema/02-CreateTables.sql
-- Execute the script

-- Step 3: Create Constraints
-- Open: 01-Schema/03-CreateConstraints.sql
-- Execute the script

-- Step 4: Create Functions
-- Execute each file in 02-Functions/ folder
-- Execute: 02-Functions/fn_CalculatePnL.sql
-- Execute: 02-Functions/fn_GetExposure.sql
-- Execute: 02-Functions/fn_CalculateVaR.sql
-- Execute: 02-Functions/fn_GetBusinessDays.sql

-- Step 5: Create Stored Procedures
-- Execute each file in 03-StoredProcedures/ folder
-- Execute: 03-StoredProcedures/sp_ProcessTrade.sql
-- Execute: 03-StoredProcedures/sp_CalculatePortfolioRisk.sql
-- Execute: 03-StoredProcedures/sp_CheckRiskLimits.sql

-- Step 6: Create Views
-- Execute each file in 04-Views/ folder
-- Execute: 04-Views/vw_ActivePositions.sql
-- Execute: 04-Views/vw_RiskMetrics.sql
-- Execute: 04-Views/vw_CounterpartyExposure.sql
-- Execute: 04-Views/vw_PortfolioSummary.sql

-- Step 7: Insert Sample Data
-- Open: 05-SampleData/InsertSampleData.sql
-- Execute the script (this will take a few moments)

-- Step 8: Create Indexes
-- Open: 07-Indexes/CreateIndexes.sql
-- Execute the script

Option 2: Using Azure Data Studio

  1. Connect to SQL Server

    • Open Azure Data Studio
    • Create a new connection to your SQL Server instance

  2. Execute Scripts

    • Follow the same script execution order as SSMS option above
    • You can use Ctrl+Shift+E (Windows/Linux) or Cmd+Shift+E (Mac) to execute selected text

Option 3: Command Line (sqlcmd)

# Navigate to the project directory
cd /path/to/SQL_SERVER

# Execute scripts in order
sqlcmd -S localhost -d master -i 01-Schema/01-CreateDatabase.sql
sqlcmd -S localhost -d MarketRisk -i 01-Schema/02-CreateTables.sql
sqlcmd -S localhost -d MarketRisk -i 01-Schema/03-CreateConstraints.sql

# Functions
sqlcmd -S localhost -d MarketRisk -i 02-Functions/fn_CalculatePnL.sql
sqlcmd -S localhost -d MarketRisk -i 02-Functions/fn_GetExposure.sql
sqlcmd -S localhost -d MarketRisk -i 02-Functions/fn_CalculateVaR.sql
sqlcmd -S localhost -d MarketRisk -i 02-Functions/fn_GetBusinessDays.sql

# Stored Procedures
sqlcmd -S localhost -d MarketRisk -i 03-StoredProcedures/sp_ProcessTrade.sql
sqlcmd -S localhost -d MarketRisk -i 03-StoredProcedures/sp_CalculatePortfolioRisk.sql
sqlcmd -S localhost -d MarketRisk -i 03-StoredProcedures/sp_CheckRiskLimits.sql

# Views
sqlcmd -S localhost -d MarketRisk -i 04-Views/vw_ActivePositions.sql
sqlcmd -S localhost -d MarketRisk -i 04-Views/vw_RiskMetrics.sql
sqlcmd -S localhost -d MarketRisk -i 04-Views/vw_CounterpartyExposure.sql
sqlcmd -S localhost -d MarketRisk -i 04-Views/vw_PortfolioSummary.sql

# Sample Data
sqlcmd -S localhost -d MarketRisk -i 05-SampleData/InsertSampleData.sql

# Indexes
sqlcmd -S localhost -d MarketRisk -i 07-Indexes/CreateIndexes.sql

Important Configuration Notes

Database File Paths

Before running 01-CreateDatabase.sql, update the file paths to match your SQL Server configuration:

-- Default paths in the script:
FILENAME = N'C:\SQLData\MarketRisk_Data.mdf'
FILENAME = N'C:\SQLData\MarketRisk_Log.ldf'

-- Common alternative paths:
-- Windows Default: C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\
-- Linux: /var/opt/mssql/data/
-- macOS (Docker): /var/opt/mssql/data/

To find your SQL Server data directory:

SELECT SERVERPROPERTY('InstanceDefaultDataPath') AS DefaultDataPath;

Verification

After installation, verify everything is set up correctly:

USE MarketRisk;
GO

-- Check all tables exist
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME;

-- Check all views exist
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
ORDER BY TABLE_NAME;

-- Check all functions exist
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION'
ORDER BY ROUTINE_NAME;

-- Check all stored procedures exist
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
ORDER BY ROUTINE_NAME;

-- Verify sample data
SELECT 'Trades' AS TableName, COUNT(*) AS RecordCount FROM dbo.Trades
UNION ALL
SELECT 'Positions', COUNT(*) FROM dbo.Positions
UNION ALL
SELECT 'Instruments', COUNT(*) FROM dbo.Instruments
UNION ALL
SELECT 'MarketData', COUNT(*) FROM dbo.MarketData;

Expected output:

  • 14+ tables
  • 4+ views
  • 4+ functions
  • 3+ stored procedures
  • Sample data in all tables

Testing the System

Run some analytical queries to test the system:

-- View active positions
SELECT * FROM dbo.vw_ActivePositions;

-- View risk metrics
SELECT * FROM dbo.vw_RiskMetrics;

-- View counterparty exposure
SELECT * FROM dbo.vw_CounterpartyExposure;

-- Test stored procedure
EXEC dbo.sp_CalculatePortfolioRisk @CalculationDate = '2026-01-10';

Running Analytical Queries

Explore the advanced queries in:

06-Queries/AnalyticalQueries.sql

This file contains 10 complex queries demonstrating various SQL techniques.

Troubleshooting

Issue: Permission Denied

Solution: Ensure you have appropriate permissions on the SQL Server instance:

-- Run as administrator/sysadmin
GRANT CREATE DATABASE TO [YourUser];

Issue: File Path Not Found

Solution: Update file paths in 01-CreateDatabase.sql or create directories:

# Windows
mkdir C:\SQLData

# Linux/macOS
sudo mkdir -p /var/opt/mssql/data
sudo chown mssql:mssql /var/opt/mssql/data

Issue: Syntax Errors

Solution: Ensure you're using SQL Server 2019 or later. Some features may not be available in older versions.

Cleanup (Optional)

To remove the database:

USE master;
GO

ALTER DATABASE MarketRisk SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

DROP DATABASE MarketRisk;
GO

Next Steps

  1. Customize the data for your specific use case
  2. Add more instruments and counterparties
  3. Extend the risk calculations
  4. Create additional reports
  5. Integrate with BI tools (Power BI, Tableau, etc.)

Support

For issues or questions:

  • Check the README.md for project overview
  • Review comments in SQL files
  • Open an issue on GitHub

Happy querying! 🚀