Requirement Document for CNP Contractor Performance Power BI Report

1. Project Overview

The CNP Contractor Performance Power BI Report aims to analyze and visualize contractor efficiency, cable system performance, delay mitigation strategies, and crew foreman analytics. The report leverages data from SQL databases, Power Query transformations, and DAX calculations to provide meaningful insights.

2. Data Sources

Primary Data Sources:

• SQL Server Database: Stores raw contractor performance data, cable system analytics, and delay records.
• Excel/CSV Files: Supplemental data for crew assignments and mitigation strategies.
• APIs/Web Data: If external integrations are required for real-time updates.

3. Project Completion Method

The project will be completed in three stages: Data Extraction (SQL Queries), Data Transformation (Power Query), and Data Analysis (DAX Calculations).

Stage 1: Data Extraction (SQL Queries)

The SQL Server database contains performance metrics, delays, and mitigation strategies. The data will be extracted using optimized SQL queries.

Example SQL Queries:

-- Retrieve overall contractor performance summary
SELECT
    ContractorName,
    SUM(DelayHours) AS TotalDelayHours,
    COUNT(DISTINCT MDU_ID) AS TotalMDUs,
    AVG(DailyCablesInstalled) AS AvgDailyCables
FROM ContractorPerformance
GROUP BY ContractorName;

-- Fetch cable system performance by crew
SELECT
    CrewForeman,
    SUM(TestedCables) AS TotalCablesTested,
    AVG(PerformanceScore) AS AvgPerformance,
    COUNT(*) AS TotalAssessments
FROM CableSystemPerformance
GROUP BY CrewForeman;

-- Identify major delay factors
SELECT
    DelayCategory,
    SUM(DelayHours) AS TotalDelayHours,
    COUNT(*) AS Occurrences
FROM DelayRecords
GROUP BY DelayCategory
ORDER BY TotalDelayHours DESC;

Stage 2: Data Transformation (Power Query in Power BI)

Once data is extracted using SQL, Power Query will be used to clean, transform, and shape the data before loading it into Power BI.

Key Power Query Transformations:

• Data Cleaning: Removing duplicates, handling null values, and correcting data types.
• Column Splitting & Merging: Extracting necessary fields such as splitting full names, merging date fields, etc.
• Custom Calculated Columns: Adding new columns based on transformations like concatenating text values or deriving new data.
• Data Type Conversion: Ensuring consistency for numeric, text, and date fields.

Example Power Query Steps:

• Remove blank or duplicate rows.
• Apply filters to exclude inactive records.
• Merge multiple tables for enhanced data relations.

Stage 3: Data Analysis (DAX Calculations in Power BI)

DAX (Data Analysis Expressions) will be used to create calculated measures and columns to derive insights and KPI calculations.

Key DAX Measures:

-- Calculate Total Delay Hours Measure
Total Delay Hours = SUM(DelayRecords[DelayHours])

-- Calculate Performance Score Percentage
Performance Score % =
DIVIDE( SUM(CableSystemPerformance[PerformanceScore]), COUNT(CableSystemPerformance[AssessmentID]), 0 )

-- Calculate Average Daily Cables Installed
Avg Daily Cables = AVERAGEX( ContractorPerformance, ContractorPerformance[DailyCablesInstalled] )

-- Calculate Crew Efficiency Rating
Crew Efficiency Rating =
IF( [Performance Score %] > 80, "High", IF( [Performance Score %] > 60, "Medium", "Low" ) )

4. Report Structure

The Power BI report will have the following key pages:

• Dashboard Overview: Summary of key contractor metrics.
• Contractor Performance Analysis: Performance breakdown by contractor.
• Cable System Efficiency: Daily cable installation trends.
• Delay Mitigation: Analysis of major delay causes and solutions.
• Crew Performance: Foreman-wise efficiency tracking.

5. Conclusion

This project will follow a structured approach using SQL Queries for data extraction, Power Query for transformation, and DAX for insights and calculations. The Power BI report will offer interactive dashboards with actionable insights for performance evaluation and strategic improvements.

---