Requirement Document for Cable Performance Power BI Report

1. Overview

The Cable Performance Power BI Report is built using SQL Queries, Power Query, and DAX to extract, transform, and visualize cable system data. The completion process involves data extraction, transformation, modeling, and report visualization using Power BI.

---

2. Project Execution Steps

Step 1: Data Extraction (SQL Queries)

Data is extracted from the database using SQL queries. The key datasets include:

• Cable Performance Data
• Component Analysis Data
• Site Assessment Data

Example SQL Queries

1. Extracting Cable Performance Data

sqlCopyEditSELECT 
    Cable_ID, 
    Conductor_Miles, 
    Circuit_Miles, 
    CSQR AS Cable_Segment_Quality_Rating, 
    SQR AS Site_Quality_Rating, 
    Mitigation_Status
FROM Cable_Performance
WHERE Test_Date BETWEEN '2000-01-01' AND '2024-12-31';

2. Extracting Component Analysis Data

sqlCopyEditSELECT 
    Component_ID, 
    Component_Type, 
    Meets_Standards, 
    Does_Not_Meet_Standards, 
    Substandard_Percentage
FROM Component_Performance
WHERE Component_Type IN ('Termination', 'Splice', 'Phase');

3. Extracting Site Assessment Data

sqlCopyEditSELECT 
    Site_ID, 
    Site_Name, 
    Owner, 
    Cables_Assessed, 
    Passing_SQR_Percentage, 
    Cables_Mitigated, 
    Successfully_Mitigated_Percentage
FROM Site_Assessment
WHERE Year >= 2000;

---

Step 2: Data Transformation (Power Query)

Once the raw data is extracted using SQL queries, it is cleaned and transformed using Power Query (M language).

Power Query Transformation Steps

• Removing Duplicates:mCopyEditTable.Distinct(Source)
• Replacing Null Values:mCopyEditTable.ReplaceValue(Source, null, "Unknown", Replacer.ReplaceValue, {"ColumnName"})
• Adding Custom Columns for Mitigation Success:mCopyEditTable.AddColumn(Source, "Mitigation Success Rate", each [Successfully_Mitigated] / [Cables_Mitigated], type number)
• Data Type Transformation:mCopyEditTable.TransformColumnTypes(Source, {{"Test_Date", type date}, {"Cables_Assessed", Int64.Type}})
• Filtering Data for Recent Years:mCopyEditTable.SelectRows(Source, each [Year] >= 2015)

---

Step 3: Data Modeling & Relationships

• The extracted data is structured into a Power BI data model.
• Relationships between tables are created:
  • Cable Performance Data → Linked to Component Analysis (by Cable_ID).
  • Site Assessment Data → Linked to Cable Performance (by Site_ID).
  • Date Table → Used for time-based analysis.

Example Relationships:

Table 1 (Cable Performance)	Table 2 (Component Analysis)	Relationship
Cable_ID	Component_ID	One-to-Many
Site_ID	Owner_ID	One-to-Many

---

Step 4: Data Visualization (DAX for Measures & Calculations)

DAX (Data Analysis Expressions) is used to create measures for the Power BI visuals.

Key DAX Measures

1. Total Cables Assessed

DAXCopyEditTotalCablesAssessed = SUM('Cable Performance'[Cables_Assessed])

2. Mitigation Success Rate

DAXCopyEditMitigationSuccessRate = 
DIVIDE(
    SUM('Site Assessment'[Successfully_Mitigated]), 
    SUM('Site Assessment'[Cables_Mitigated]), 
    0
)

3. Percentage of Cables Passing Inspection

DAXCopyEditPassingPercentage = 
DIVIDE(
    SUM('Cable Performance'[Passing_SQR_Percentage]), 
    COUNT('Cable Performance'[Cable_ID]), 
    0
) * 100

4. Substandard Component Rate

DAXCopyEditSubstandardRate = 
DIVIDE(
    SUM('Component Analysis'[Does_Not_Meet_Standards]), 
    SUM('Component Analysis'[Meets_Standards] + 'Component Analysis'[Does_Not_Meet_Standards]), 
    0
) * 100

5. Trend Analysis for Cable Performance Over Time

DAXCopyEditCablePerformanceTrend = 
CALCULATE(
    SUM('Cable Performance'[Cables_Assessed]), 
    DATESYTD('Date'[Date])
)

---

Step 5: Report Development (Power BI)

• Interactive Dashboards are created using Power BI visualizations.
• KPIs & Metrics are displayed in tables, charts, and gauges.
• Time-based Trends are visualized using line and bar charts.

Power BI Visuals

• KPI Cards: Total Cables Assessed, Mitigation Success Rate.
• Bar Chart: Cable Performance Trends by Year.
• Pie Chart: Mitigated vs Non-Mitigated Cables.
• Table Visualization: Site-Wise Performance Data.

---

6. Project Completion & Validation

Once the Power BI report is complete, the following validation checks are performed:

1. Data Integrity Check: Ensuring SQL queries return correct and complete data.
2. Transformation Accuracy: Validating Power Query transformations match business rules.
3. Measure Validation: Testing DAX measures to confirm calculations are correct.
4. User Testing: Stakeholders review report usability and accuracy.
5. Final Deployment: Publishing the Power BI Report to the Power BI Service.

---

7. Deployment & Maintenance

• Scheduled Data Refresh: SQL Queries update data automatically.
• Power BI Service Hosting: Report is published for business users.
• Continuous Monitoring: Regular checks for data inconsistencies and performance issues.
• Enhancements: Future updates for additional KPIs and insights.

---

Conclusion

The Cable Performance Power BI Report is developed using SQL Queries for data extraction, Power Query for transformation, and DAX for calculations. The final report provides real-time insights into cable health, mitigation efforts, and substation performance, ensuring efficient decision-making.

Sample data files based on the Cable Performance Power BI Report format.