IMCORP Power BI Report

Requirement Document for IMCORP Power BI Report

1. Introduction

This document outlines the requirements for the Power BI report based on the IMCORP DB Stats dataset. The report provides insights into cable system performance, conductor statistics, and phase testing results, aiding in decision-making for cable system management and maintenance.

2. Purpose

The Power BI report is designed to:

Analyze overall cable system performance.
Evaluate conductor and circuit statistics.
Assess phase testing and performance.
Identify mitigation opportunities.
Track repair and maintenance trends.
Provide insights into electrical infrastructure quality and reliability.

3. Scope

The Power BI report covers:

Cable System Overview
- Total cables, conductor miles, and circuit miles.
- Unique phase IDs and average cable length.
Cable System Performance
- Overall performance classification (Pass, Repair, Inspect, Replace, etc.).
- Performance over different years.
- Comparison of guaranteed cables vs. deferred actions.
Phase Testing & Sensitivity Analysis
- Overall phase performance by sensitivity levels.
- Visibility by Partial Discharge (PD) levels.
- Phase testing trends across different voltage classes.
Splice and Termination Performance
- Percentage of cable systems meeting standards.
- Percentage of failures requiring repair.
Cables by Vintage
- Performance of cables by year of installation.
- Historical trends in cable failure and mitigation.
Mitigation and Repair Success Rates
- Success rates of cable mitigation over time.
- Worthwhile vs. non-worthwhile mitigations.
- Performance improvement trends after mitigation.

4. Data Sources

The report utilizes data from the IMCORP DB Stats dataset, containing:

Test results from various years.
Electrical infrastructure details.
Performance classifications and phase testing.
Repair and mitigation records.

5. Key Performance Indicators (KPIs)

Cable system pass/fail percentage.
Repair and maintenance statistics.
Phase performance by sensitivity level.
Cable failure rate by vintage.
Mitigation success rates over time.

6. Dashboard Features

Interactive Filters
- Filter by year, phase, sensitivity, voltage level, and company name.
Drill-Down Capabilities
- Clickable graphs to explore trends in depth.
Comparative Analysis
- Compare performance over different periods or categories.

7. User Roles & Access

Management: High-level performance overview.
Engineers: Detailed phase and sensitivity analysis.
Maintenance Teams: Repair and mitigation tracking.

8. Functional Requirements

Data Refresh: Automatic data refresh for up-to-date insights.
Export Capabilities: Ability to export data into Excel or PDF.
Visual Representation: Charts, graphs, and tables for easy interpretation.

9. Non-Functional Requirements

Performance: Dashboard should load within 5 seconds.
Scalability: Should accommodate future datasets.
Security: Role-based access to sensitive data.

IMCORP DB Stats Download

1. Data Extraction Using SQL Queries

The data required for this report is stored in a relational database. SQL queries are used to extract and preprocess the data before loading it into Power BI.

1.1 SQL Queries for Data Extraction

Extracting Cable System Data

sqlCopyEditSELECT 
    CableID, 
    ConductorMiles, 
    CircuitMiles, 
    ConductorsFt, 
    UniquePhaseIDs, 
    ConductorKm, 
    CircuitKm, 
    AvgLength, 
    TestDate, 
    Status 
FROM CableSystem;

Extracting Cable Performance Data

sqlCopyEditSELECT 
    CableID, 
    TestDate, 
    PerformanceCategory, 
    SensitivityLevel, 
    PartialDischargeLevel, 
    RepairStatus 
FROM CablePerformance;

Extracting Cable Mitigation Data

sqlCopyEditSELECT 
    CableID, 
    TestDate, 
    MitigationType, 
    MitigationSuccess, 
    RepairAction, 
    VoltageClass 
FROM CableMitigation;

Extracting Splice and Termination Performance

sqlCopyEditSELECT 
    SpliceID, 
    CableID, 
    TestDate, 
    SplicePerformance, 
    TerminationPerformance 
FROM SpliceTermination;

The extracted data is loaded into Power BI for further transformation.

2. Data Transformation Using Power Query

Once the data is imported into Power BI, Power Query is used to clean, transform, and shape the data for analysis.

2.1 Data Cleaning Steps

Handling Missing Values: Replace null values with appropriate defaults (e.g., “Not Assessed” for missing statuses).
Data Type Conversion: Ensure numeric fields (e.g., conductor miles, circuit km) are stored as decimals.
Merging Tables:
- Merge CableSystem with CablePerformance using CableID.
- Merge CableMitigation with CablePerformance using CableID.

powerqueryCopyEdit// Merging Cable System with Performance Data
let
    CableData = Table.NestedJoin(CableSystem, "CableID", CablePerformance, "CableID", "Performance", JoinKind.LeftOuter),
    ExpandedTable = Table.ExpandTableColumn(CableData, "Performance", {"PerformanceCategory", "SensitivityLevel"})
in
    ExpandedTable

Creating Custom Columns:
- Adding a Mitigation Success Rate column.
- Categorizing cables based on performance levels.

powerqueryCopyEdit= Table.AddColumn(CablePerformance, "PerformanceCategory", each if [SensitivityLevel] < 10 then "High" else "Low", type text)

3. Data Modeling and Calculations Using DAX

DAX (Data Analysis Expressions) is used to create calculated columns, measures, and key performance indicators (KPIs).

3.1 Calculated Columns

Cable Age Calculation

DAXCopyEditCableAge = YEAR(TODAY()) - YEAR(CableSystem[TestDate])

Mitigation Status Classification

DAXCopyEditMitigationCategory = 
SWITCH(TRUE(), 
    CableMitigation[MitigationSuccess] > 80, "Highly Effective",
    CableMitigation[MitigationSuccess] > 50, "Moderate Effectiveness",
    "Low Effectiveness"
)

3.2 Measures for KPIs

Total Cables Tested

DAXCopyEditTotalCables = COUNT(CableSystem[CableID])

Pass Rate Calculation

DAXCopyEditPassRate = 
DIVIDE(
    COUNTROWS(FILTER(CablePerformance, CablePerformance[PerformanceCategory] = "Pass")),
    COUNTROWS(CablePerformance),
    0
)

Repair Rate Calculation

DAXCopyEditRepairRate = 
DIVIDE(
    COUNTROWS(FILTER(CablePerformance, CablePerformance[PerformanceCategory] = "Repair")),
    COUNTROWS(CablePerformance),
    0
)

Average Sensitivity by Cable Type

DAXCopyEditAvgSensitivity = AVERAGE(CablePerformance[SensitivityLevel])

4. Report Development in Power BI

Once the data is transformed and calculated, Power BI is used to build interactive visualizations.

4.1 Visualizations

Cable System Overview Dashboard: Displays total cables, conductor miles, and average cable length.
Performance Dashboard: Shows pass/fail rates, repair trends, and mitigation effectiveness.
Phase Testing Analysis: Heatmaps and bar charts for sensitivity levels and PD detection.
Splice & Termination Performance Report: Insights into splice success rates and termination failures.
Cable Mitigation Trends: A timeline of mitigation success and repair actions.

4.2 Filters & Interactions

Year and test date filters
Performance category slicers
Voltage class selection
Interactive drill-down for vintage-based performance analysis

5. Automation & Performance Optimization

5.1 Data Refresh

The report is connected to a SQL database with scheduled refreshes via Power BI Service.
Refresh frequency: Daily or Weekly (depending on data update needs).

5.2 Performance Optimization

Use of Aggregations: Pre-aggregating data in SQL to improve load times.
Indexing SQL Tables: Adding indexes on CableID and TestDate for faster queries.
Reducing Data Load: Removing unnecessary columns in Power Query before loading to Power BI.

6. Conclusion

By integrating SQL, Power Query, and DAX, the Power BI report successfully provides insights into cable system performance, phase sensitivity, and mitigation success. The structured data pipeline ensures real-time decision-making and efficient electrical asset management.

sample data files in CSV format for different tables used in the Power BI report: