W3SKILLSET

Requirement Document for Evergy Prioritized Action Power BI Report

1. Introduction

1.1 Purpose

The Evergy Prioritized Action Report provides insights into the performance, condition, and required actions related to electrical cables, terminations, circuits, and phases. This Power BI report aims to enhance decision-making by visualizing key performance indicators (KPIs), assessment results, and repair recommendations.

1.2 Scope

This Power BI dashboard will:

  • Present statistical insights on cables, terminations, and conductor miles.
  • Display various assessment and mitigation statuses.
  • Provide data-driven recommendations for cable repairs and replacements.
  • Enable tracking of cables tested over the years.
  • Visualize mitigation success rates and components requiring repair.

1.3 Target Users

  • Maintenance Teams: To determine which cables or components require immediate attention.
  • Decision Makers & Analysts: To assess the effectiveness of mitigation strategies.
  • Field Engineers: To view prioritized actions for system reliability.

2. Functional Requirements

2.1 Data Sources

The Power BI report will integrate data from:

  • Field assessments and cable inspections.
  • Electrical system monitoring logs.
  • Historical performance reports.

2.2 Report Components

The dashboard will consist of the following visualizations:

2.2.1 Key Performance Indicators (KPIs)

  • Days Worked: Total workdays recorded for assessment.
  • Total Accessories: Count of accessories assessed.
  • Circuit & Conductor Miles: Measurement of cables tested.
  • Total Assessed Cables: Breakdown of cable assessments.

2.2.2 Cable System Performance

  • Performance breakdown by percentage:
    • Meets Standards
    • Repair Required
    • Immediate Repair
    • Defer Action
    • Replace
    • Substandard
    • Partial Replacement
  • Cable assessments categorized by compliance with IEC 60502-2 standards.

2.2.3 Mitigation & Repair Analysis

  • Mitigation success rate (e.g., 75% mitigated vs. 25% not mitigated).
  • Mitigation steps performed (e.g., decontamination, correction, insulation fixes).
  • Repair type distribution (e.g., midspan repair, termination repair, insulation failure).

2.2.4 Historical Analysis

  • Cables tested per year (2006 – 2024).
  • Cables requiring mitigation over time.
  • Count of tests conducted by date.

2.2.5 Repair & Action Categorization

  • Cable repairs based on sensitivity and location.
  • Repair prioritization by substation and feeder.
  • Summarized recommendations for future maintenance actions.

3. Non-Functional Requirements

3.1 Performance

  • The dashboard should load within 5 seconds for typical data queries.
  • Aggregate summaries should be pre-processed for quick access.

3.2 Security

  • Access control will be enforced based on user roles:
    • Read-only access for analysts.
    • Edit access for maintenance teams.
  • Data encryption will be applied to protect sensitive information.

3.3 Scalability

  • The dashboard should support increasing data volumes as new assessments are recorded.
  • Future enhancements should allow integration with real-time monitoring systems.

4. Assumptions & Constraints

  • Assumptions:
    • The data is regularly updated and accurate.
    • Users have the necessary permissions to access Power BI.
    • Field engineers will provide timely input on repair actions.
  • Constraints:
    • Performance may degrade with very high data volume unless optimized.
    • Requires Power BI Pro or Premium for full functionality.

5. Future Enhancements

  • Real-time monitoring integration for predictive maintenance.
  • Automated alerts for cables requiring immediate action.
  • Geospatial mapping for visualization of substation and feeder repairs.
Evergy-Prioritized-Action-Report-LiveDownload

1. Data Collection & Storage (SQL Queries)

The raw data is sourced from a SQL database containing tables related to cables, terminations, circuits, and maintenance actions. The following SQL queries are used to extract, clean, and transform the data before loading it into Power BI.

1.1 Extracting Core Data

Cable Assessment Data

sqlCopyEditSELECT 
    CableID, 
    CircuitID, 
    Substation, 
    Feeder, 
    InstallDate, 
    InspectionDate, 
    ConditionStatus, 
    RepairRecommendation 
FROM CableAssessments 
WHERE InspectionDate >= '2010-01-01';

Mitigation Actions Data

sqlCopyEditSELECT 
    CableID, 
    RepairType, 
    MitigationStatus, 
    RepairDate, 
    InspectionYear 
FROM MitigationRecords
WHERE RepairDate IS NOT NULL;

Historical Testing Data

sqlCopyEditSELECT 
    YEAR(TestDate) AS TestYear, 
    COUNT(DISTINCT CableID) AS CablesTested 
FROM CableTests 
GROUP BY YEAR(TestDate) 
ORDER BY TestYear;

2. Data Transformation & Cleaning (Power Query)

Once the SQL queries load the required data into Power BI, Power Query is used to clean, shape, and optimize the data model.

2.1 Transforming Cable Assessment Data

  • Remove Duplicates: Ensures no redundant records affect performance.
  • Convert Date Formats: Standardizes InstallDate and InspectionDate formats.
  • Split Condition Categories: Creates separate columns for “Meets Standards,” “Repair Required,” and “Immediate Repair.”

2.2 Creating Relationships

  • Merge CableAssessments and MitigationRecords on CableID for a unified data table.
  • Join CableTests on CableID to connect test results with assessments.

2.3 Data Quality Enhancements

  • Replace NULL values with "Not Assessed" or "Pending Action".
  • Remove outliers (e.g., incorrect dates or negative repair costs).

3. Data Analysis & Measures (DAX)

Once the data is transformed, DAX (Data Analysis Expressions) is used to create calculated fields, KPIs, and measures.

3.1 KPI Measures

Total Cables Assessed

DAXCopyEditTotalCables = COUNT( CableAssessments[CableID] )

Total Repairs Completed

DAXCopyEditTotalRepairs = CALCULATE( COUNT( MitigationRecords[CableID] ), MitigationRecords[MitigationStatus] = "Completed" )

Mitigation Success Rate

DAXCopyEditMitigationSuccessRate = 
    DIVIDE( 
        CALCULATE( COUNT(MitigationRecords[CableID]), MitigationRecords[MitigationStatus] = "Mitigated" ), 
        COUNT( MitigationRecords[CableID] ), 
        0 
    )

3.2 Performance Breakdown by Repair Type

DAXCopyEditRepairsByType = 
SUMMARIZE(
    MitigationRecords, 
    MitigationRecords[RepairType], 
    "Total Repairs", COUNT(MitigationRecords[CableID])
)

3.3 Yearly Cables Tested Trend

DAXCopyEditCablesTestedYearly = 
CALCULATE( 
    COUNT( CableTests[CableID] ), 
    ALLEXCEPT( CableTests, CableTests[TestYear] ) 
)

4. Power BI Visualizations

Once the data is prepared and measures are calculated, the following Power BI visualizations are created:

  1. KPI Cards:
    • Total Cables Assessed
    • Total Repairs Completed
    • Mitigation Success Rate
  2. Bar Chart:
    • Cables Tested Over Time (CablesTestedYearly)
  3. Pie Chart:
    • Repairs by Type (RepairsByType)
  4. Table Visualization:
    • Repair Actions and Status
  5. Heatmap:
    • Substation-wise Repair Density
  6. Drill-through Report:
    • Detailed repair recommendations per substation.

5. Project Deployment & Automation

  • Data Refresh: Scheduled daily for real-time insights.
  • Role-Based Access Control (RBAC): Users can access only relevant data.
  • Export Options: Report can be exported as PDF, Excel, and CSV.

Conclusion

This project combines SQL, Power Query, and DAX to create an optimized, interactive Power BI dashboard for Evergy Prioritized Action Reporting, ensuring better decision-making and maintenance planning.

sample data file for Power BI report download it here:

Sample_Cable_DataDownload

This Excel file includes:

  • Cable Assessments (Cable conditions and recommendations)
  • Mitigation Actions (Repair types and statuses)
  • Cable Tests (Yearly testing data)

Leave a comment

Your email address will not be published. Required fields are marked *