406 lines
13 KiB
SQL
406 lines
13 KiB
SQL
-- Case Analytics Database Schema
|
|
-- Optimized for performance og caching af beregnede nøgletal
|
|
|
|
-- ===================================
|
|
-- 1. PROJECT ANALYTICS TABLE
|
|
-- ===================================
|
|
CREATE TABLE IF NOT EXISTS project_analytics (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
project_id INT NOT NULL,
|
|
project_name VARCHAR(500),
|
|
customer_id INT,
|
|
customer_name VARCHAR(255),
|
|
employee_id INT,
|
|
employee_name VARCHAR(255),
|
|
|
|
-- Project Metrics
|
|
total_hours DECIMAL(10,2) DEFAULT 0,
|
|
total_material_cost DECIMAL(15,2) DEFAULT 0,
|
|
total_labor_cost DECIMAL(15,2) DEFAULT 0,
|
|
total_quote_value DECIMAL(15,2) DEFAULT 0,
|
|
actual_cost DECIMAL(15,2) DEFAULT 0,
|
|
profit_amount DECIMAL(15,2) DEFAULT 0,
|
|
profit_margin DECIMAL(5,2) DEFAULT 0,
|
|
|
|
-- Performance Metrics
|
|
quote_accuracy_percentage DECIMAL(5,2) DEFAULT 0,
|
|
time_estimation_accuracy DECIMAL(5,2) DEFAULT 0,
|
|
material_waste_percentage DECIMAL(5,2) DEFAULT 0,
|
|
|
|
-- Timeline Data
|
|
project_start_date DATE,
|
|
project_end_date DATE,
|
|
project_duration_days INT,
|
|
quote_created_date DATE,
|
|
|
|
-- Meta Data
|
|
last_calculated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
|
|
-- Indexes
|
|
INDEX idx_project_id (project_id),
|
|
INDEX idx_customer_id (customer_id),
|
|
INDEX idx_employee_id (employee_id),
|
|
INDEX idx_profit_margin (profit_margin),
|
|
INDEX idx_quote_accuracy (quote_accuracy_percentage),
|
|
INDEX idx_last_calculated (last_calculated),
|
|
|
|
UNIQUE KEY unique_project (project_id)
|
|
);
|
|
|
|
-- ===================================
|
|
-- 2. EMPLOYEE PERFORMANCE ANALYTICS
|
|
-- ===================================
|
|
CREATE TABLE IF NOT EXISTS employee_performance_analytics (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
employee_id INT NOT NULL,
|
|
employee_name VARCHAR(255),
|
|
period_month INT NOT NULL,
|
|
period_year INT NOT NULL,
|
|
|
|
-- Performance KPIs
|
|
total_projects INT DEFAULT 0,
|
|
total_revenue DECIMAL(15,2) DEFAULT 0,
|
|
total_profit DECIMAL(15,2) DEFAULT 0,
|
|
avg_profit_margin DECIMAL(5,2) DEFAULT 0,
|
|
|
|
-- Accuracy Metrics
|
|
avg_quote_accuracy DECIMAL(5,2) DEFAULT 0,
|
|
avg_time_accuracy DECIMAL(5,2) DEFAULT 0,
|
|
projects_over_budget INT DEFAULT 0,
|
|
projects_under_budget INT DEFAULT 0,
|
|
|
|
-- Productivity Metrics
|
|
avg_project_duration DECIMAL(8,2) DEFAULT 0,
|
|
total_hours_worked DECIMAL(10,2) DEFAULT 0,
|
|
revenue_per_hour DECIMAL(8,2) DEFAULT 0,
|
|
|
|
-- Rankings (updated monthly)
|
|
profit_rank INT DEFAULT 0,
|
|
accuracy_rank INT DEFAULT 0,
|
|
productivity_rank INT DEFAULT 0,
|
|
|
|
last_calculated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
-- Indexes
|
|
INDEX idx_employee_id (employee_id),
|
|
INDEX idx_period (period_year, period_month),
|
|
INDEX idx_profit_margin (avg_profit_margin),
|
|
INDEX idx_quote_accuracy (avg_quote_accuracy),
|
|
|
|
UNIQUE KEY unique_employee_period (employee_id, period_year, period_month)
|
|
);
|
|
|
|
-- ===================================
|
|
-- 3. CUSTOMER ANALYTICS
|
|
-- ===================================
|
|
CREATE TABLE IF NOT EXISTS customer_analytics (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
customer_id INT NOT NULL,
|
|
customer_name VARCHAR(255),
|
|
|
|
-- Business Metrics
|
|
total_projects INT DEFAULT 0,
|
|
total_revenue DECIMAL(15,2) DEFAULT 0,
|
|
avg_project_value DECIMAL(15,2) DEFAULT 0,
|
|
total_profit DECIMAL(15,2) DEFAULT 0,
|
|
avg_profit_margin DECIMAL(5,2) DEFAULT 0,
|
|
|
|
-- Relationship Metrics
|
|
first_project_date DATE,
|
|
last_project_date DATE,
|
|
customer_lifespan_days INT DEFAULT 0,
|
|
project_frequency_days DECIMAL(8,2) DEFAULT 0,
|
|
|
|
-- Risk Metrics
|
|
projects_over_budget INT DEFAULT 0,
|
|
avg_quote_accuracy DECIMAL(5,2) DEFAULT 0,
|
|
payment_history_score DECIMAL(3,2) DEFAULT 1.0,
|
|
|
|
-- Customer Segmentation
|
|
customer_tier ENUM('Bronze', 'Silver', 'Gold', 'Platinum') DEFAULT 'Bronze',
|
|
is_high_value BOOLEAN DEFAULT FALSE,
|
|
is_frequent BOOLEAN DEFAULT FALSE,
|
|
|
|
last_calculated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
-- Indexes
|
|
INDEX idx_customer_id (customer_id),
|
|
INDEX idx_total_revenue (total_revenue),
|
|
INDEX idx_customer_tier (customer_tier),
|
|
INDEX idx_profit_margin (avg_profit_margin),
|
|
|
|
UNIQUE KEY unique_customer (customer_id)
|
|
);
|
|
|
|
-- ===================================
|
|
-- 4. MATERIAL ANALYTICS
|
|
-- ===================================
|
|
CREATE TABLE IF NOT EXISTS material_analytics (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
material_name VARCHAR(255) NOT NULL,
|
|
material_category VARCHAR(100),
|
|
varenr VARCHAR(50),
|
|
|
|
-- Usage Metrics
|
|
total_usage_count INT DEFAULT 0,
|
|
total_quantity_used DECIMAL(15,3) DEFAULT 0,
|
|
total_cost DECIMAL(15,2) DEFAULT 0,
|
|
avg_unit_price DECIMAL(10,2) DEFAULT 0,
|
|
|
|
-- Price Trends
|
|
min_price DECIMAL(10,2) DEFAULT 0,
|
|
max_price DECIMAL(10,2) DEFAULT 0,
|
|
current_price DECIMAL(10,2) DEFAULT 0,
|
|
price_volatility DECIMAL(5,2) DEFAULT 0,
|
|
|
|
-- Performance Metrics
|
|
avg_waste_percentage DECIMAL(5,2) DEFAULT 0,
|
|
projects_used_in INT DEFAULT 0,
|
|
last_used_date DATE,
|
|
|
|
-- Profitability
|
|
markup_percentage DECIMAL(5,2) DEFAULT 0,
|
|
profit_contribution DECIMAL(15,2) DEFAULT 0,
|
|
|
|
-- Popularity Rankings
|
|
usage_rank INT DEFAULT 0,
|
|
profit_rank INT DEFAULT 0,
|
|
|
|
last_calculated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
-- Indexes
|
|
INDEX idx_material_name (material_name),
|
|
INDEX idx_category (material_category),
|
|
INDEX idx_varenr (varenr),
|
|
INDEX idx_usage_count (total_usage_count),
|
|
INDEX idx_profit_contribution (profit_contribution),
|
|
|
|
UNIQUE KEY unique_material (material_name, varenr)
|
|
);
|
|
|
|
-- ===================================
|
|
-- 5. DAILY BUSINESS METRICS
|
|
-- ===================================
|
|
CREATE TABLE IF NOT EXISTS daily_business_metrics (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
metric_date DATE NOT NULL,
|
|
|
|
-- Daily Summary
|
|
new_projects INT DEFAULT 0,
|
|
completed_projects INT DEFAULT 0,
|
|
total_daily_revenue DECIMAL(15,2) DEFAULT 0,
|
|
total_daily_profit DECIMAL(15,2) DEFAULT 0,
|
|
avg_daily_profit_margin DECIMAL(5,2) DEFAULT 0,
|
|
|
|
-- Active Metrics
|
|
active_projects INT DEFAULT 0,
|
|
active_employees INT DEFAULT 0,
|
|
active_customers INT DEFAULT 0,
|
|
|
|
-- Performance Indicators
|
|
avg_quote_accuracy DECIMAL(5,2) DEFAULT 0,
|
|
projects_over_budget INT DEFAULT 0,
|
|
projects_on_time INT DEFAULT 0,
|
|
|
|
-- Growth Metrics
|
|
revenue_growth_rate DECIMAL(5,2) DEFAULT 0,
|
|
profit_growth_rate DECIMAL(5,2) DEFAULT 0,
|
|
customer_growth_rate DECIMAL(5,2) DEFAULT 0,
|
|
|
|
last_calculated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
-- Indexes
|
|
INDEX idx_metric_date (metric_date),
|
|
INDEX idx_revenue (total_daily_revenue),
|
|
INDEX idx_profit_margin (avg_daily_profit_margin),
|
|
|
|
UNIQUE KEY unique_date (metric_date)
|
|
);
|
|
|
|
-- ===================================
|
|
-- 6. BENCHMARK & TARGETS
|
|
-- ===================================
|
|
CREATE TABLE IF NOT EXISTS business_benchmarks (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
metric_name VARCHAR(100) NOT NULL,
|
|
metric_category ENUM('Profitability', 'Accuracy', 'Performance', 'Growth') NOT NULL,
|
|
|
|
-- Benchmark Values
|
|
target_value DECIMAL(15,2) NOT NULL,
|
|
current_value DECIMAL(15,2) DEFAULT 0,
|
|
industry_average DECIMAL(15,2) DEFAULT 0,
|
|
best_performance DECIMAL(15,2) DEFAULT 0,
|
|
|
|
-- Performance Tracking
|
|
variance_from_target DECIMAL(15,2) DEFAULT 0,
|
|
performance_score DECIMAL(5,2) DEFAULT 0,
|
|
trend_direction ENUM('up', 'down', 'stable') DEFAULT 'stable',
|
|
|
|
-- Meta Data
|
|
unit_of_measure VARCHAR(50),
|
|
description TEXT,
|
|
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
-- Indexes
|
|
INDEX idx_metric_name (metric_name),
|
|
INDEX idx_category (metric_category),
|
|
INDEX idx_performance_score (performance_score),
|
|
|
|
UNIQUE KEY unique_metric (metric_name)
|
|
);
|
|
|
|
-- ===================================
|
|
-- 7. ANALYTICS CALCULATION LOG
|
|
-- ===================================
|
|
CREATE TABLE IF NOT EXISTS analytics_calculation_log (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
calculation_type ENUM('full_refresh', 'incremental', 'daily_summary', 'benchmarks') NOT NULL,
|
|
start_time TIMESTAMP NOT NULL,
|
|
end_time TIMESTAMP,
|
|
duration_seconds INT,
|
|
|
|
-- Processing Stats
|
|
records_processed INT DEFAULT 0,
|
|
records_updated INT DEFAULT 0,
|
|
records_inserted INT DEFAULT 0,
|
|
|
|
-- Status
|
|
status ENUM('running', 'completed', 'failed') DEFAULT 'running',
|
|
error_message TEXT,
|
|
|
|
-- Performance Metrics
|
|
cpu_usage_avg DECIMAL(5,2) DEFAULT 0,
|
|
memory_usage_mb INT DEFAULT 0,
|
|
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
-- Indexes
|
|
INDEX idx_calculation_type (calculation_type),
|
|
INDEX idx_status (status),
|
|
INDEX idx_start_time (start_time)
|
|
);
|
|
|
|
-- ===================================
|
|
-- 8. POPULATE INITIAL BENCHMARKS
|
|
-- ===================================
|
|
INSERT INTO business_benchmarks (metric_name, metric_category, target_value, unit_of_measure, description) VALUES
|
|
('avg_profit_margin', 'Profitability', 35.00, 'percentage', 'Target profit margin for projects'),
|
|
('quote_accuracy', 'Accuracy', 90.00, 'percentage', 'Target quote accuracy percentage'),
|
|
('project_on_time_rate', 'Performance', 85.00, 'percentage', 'Target on-time project completion'),
|
|
('customer_satisfaction', 'Performance', 4.50, 'rating', 'Target customer satisfaction (1-5 scale)'),
|
|
('revenue_growth_monthly', 'Growth', 5.00, 'percentage', 'Target monthly revenue growth'),
|
|
('employee_productivity', 'Performance', 75000.00, 'DKK', 'Target revenue per employee per month'),
|
|
('material_waste_rate', 'Performance', 5.00, 'percentage', 'Target maximum material waste'),
|
|
('project_overrun_rate', 'Performance', 15.00, 'percentage', 'Acceptable project cost overrun rate')
|
|
ON DUPLICATE KEY UPDATE
|
|
target_value = VALUES(target_value),
|
|
description = VALUES(description);
|
|
|
|
-- ===================================
|
|
-- 9. CREATE VIEWS FOR QUICK ACCESS
|
|
-- ===================================
|
|
|
|
-- Top Performing Employees View
|
|
CREATE OR REPLACE VIEW top_employees_view AS
|
|
SELECT
|
|
e.employee_name,
|
|
e.total_projects,
|
|
e.total_revenue,
|
|
e.avg_profit_margin,
|
|
e.avg_quote_accuracy,
|
|
e.revenue_per_hour,
|
|
e.profit_rank,
|
|
e.accuracy_rank
|
|
FROM employee_performance_analytics e
|
|
WHERE e.period_year = YEAR(CURRENT_DATE)
|
|
AND e.period_month = MONTH(CURRENT_DATE)
|
|
ORDER BY e.avg_profit_margin DESC, e.avg_quote_accuracy DESC
|
|
LIMIT 10;
|
|
|
|
-- High Value Customers View
|
|
CREATE OR REPLACE VIEW high_value_customers_view AS
|
|
SELECT
|
|
c.customer_name,
|
|
c.total_projects,
|
|
c.total_revenue,
|
|
c.avg_project_value,
|
|
c.avg_profit_margin,
|
|
c.customer_tier,
|
|
DATEDIFF(CURRENT_DATE, c.last_project_date) as days_since_last_project
|
|
FROM customer_analytics c
|
|
WHERE c.total_revenue > 100000 OR c.customer_tier IN ('Gold', 'Platinum')
|
|
ORDER BY c.total_revenue DESC;
|
|
|
|
-- Business Health Dashboard View
|
|
CREATE OR REPLACE VIEW business_health_dashboard AS
|
|
SELECT
|
|
d.metric_date,
|
|
d.total_daily_revenue,
|
|
d.total_daily_profit,
|
|
d.avg_daily_profit_margin,
|
|
d.avg_quote_accuracy,
|
|
d.active_projects,
|
|
d.revenue_growth_rate,
|
|
CASE
|
|
WHEN d.avg_daily_profit_margin >= 35 THEN 'Excellent'
|
|
WHEN d.avg_daily_profit_margin >= 25 THEN 'Good'
|
|
WHEN d.avg_daily_profit_margin >= 15 THEN 'Fair'
|
|
ELSE 'Poor'
|
|
END as profit_health,
|
|
CASE
|
|
WHEN d.avg_quote_accuracy >= 90 THEN 'Excellent'
|
|
WHEN d.avg_quote_accuracy >= 80 THEN 'Good'
|
|
WHEN d.avg_quote_accuracy >= 70 THEN 'Fair'
|
|
ELSE 'Poor'
|
|
END as accuracy_health
|
|
FROM daily_business_metrics d
|
|
ORDER BY d.metric_date DESC
|
|
LIMIT 30;
|
|
|
|
-- Material Performance View
|
|
CREATE OR REPLACE VIEW material_performance_view AS
|
|
SELECT
|
|
m.material_name,
|
|
m.material_category,
|
|
m.total_usage_count,
|
|
m.total_cost,
|
|
m.avg_unit_price,
|
|
m.profit_contribution,
|
|
m.price_volatility,
|
|
m.usage_rank,
|
|
CASE
|
|
WHEN m.usage_rank <= 10 THEN 'High Usage'
|
|
WHEN m.usage_rank <= 50 THEN 'Medium Usage'
|
|
ELSE 'Low Usage'
|
|
END as usage_category
|
|
FROM material_analytics m
|
|
WHERE m.total_usage_count > 0
|
|
ORDER BY m.profit_contribution DESC;
|
|
|
|
-- ===================================
|
|
-- 10. PERFORMANCE OPTIMIZATION
|
|
-- ===================================
|
|
|
|
-- Create additional indexes for common queries
|
|
CREATE INDEX idx_project_analytics_composite ON project_analytics (customer_id, profit_margin, quote_accuracy_percentage);
|
|
CREATE INDEX idx_employee_analytics_composite ON employee_performance_analytics (period_year, period_month, avg_profit_margin);
|
|
CREATE INDEX idx_daily_metrics_year_month ON daily_business_metrics (metric_date);
|
|
|
|
-- ===================================
|
|
-- COMPLETION MESSAGE
|
|
-- ===================================
|
|
SELECT
|
|
'Analytics Schema Setup Complete!' as status,
|
|
COUNT(TABLE_NAME) as tables_created
|
|
FROM information_schema.TABLES
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME IN (
|
|
'project_analytics',
|
|
'employee_performance_analytics',
|
|
'customer_analytics',
|
|
'material_analytics',
|
|
'daily_business_metrics',
|
|
'business_benchmarks',
|
|
'analytics_calculation_log'
|
|
); |