Cable Log & JSA Power BI Report

1. Overview

The Cable Log & JSA Power BI Report is developed using a structured process involving SQL Queries, Power Query (M language), and DAX to extract, transform, and visualize compliance data effectively.

---

2. Project Execution Steps

Step 1: Data Extraction (SQL Queries)

Data is extracted from the database using SQL queries to retrieve:

• JSA Submission Data
• Cable Control Log Data
• Late & Missing Submission Records
• Compliance Scores by Employee, Date & Location

Example SQL Queries

1. Extracting JSA Compliance Data

sqlCopyEditSELECT 
    EntryDate, 
    SafetySheet, 
    Status, 
    FS_Name AS Employee, 
    WorkSummaryURL, 
    FieldTallyID
FROM JSA_Submissions
WHERE EntryDate BETWEEN '2023-01-01' AND '2024-12-31';

2. Extracting Cable Control Log Data

sqlCopyEditSELECT 
    EntryDate, 
    SafetySheet, 
    Status, 
    FS_Name AS Employee, 
    WorkSummaryURL, 
    FieldTallyID
FROM Cable_Control_Log
WHERE EntryDate >= DATEADD(MONTH, -6, GETDATE()); 

3. Extracting Late & Missing Entries

sqlCopyEditSELECT 
    EntryDate, 
    Status, 
    FS_Name AS Employee, 
    WorkSummaryURL, 
    FieldTallyID
FROM Compliance_Records
WHERE Status IN ('Late', 'Missing');

---

Step 2: Data Transformation (Power Query – M Language)

Once the raw data is extracted, Power Query is used for data cleaning, transformation, and structuring.

Power Query Transformations

• Remove DuplicatesmCopyEditTable.Distinct(Source)
• Replace Null ValuesmCopyEditTable.ReplaceValue(Source, null, "Not Submitted", Replacer.ReplaceValue, {"Status"})
• Add Compliance Score ColumnmCopyEditTable.AddColumn(Source, "Compliance Score", each if [Status] = "Ontime" then 1 else 0, type number)
• Convert EntryDate to Date FormatmCopyEditTable.TransformColumnTypes(Source, {{"EntryDate", type date}})
• Filter Data for Last 6 MonthsmCopyEditTable.SelectRows(Source, each [EntryDate] >= Date.AddMonths(DateTime.LocalNow(), -6))

---

Step 3: Data Modeling & Relationships

Data is structured into a Power BI Data Model with the following relationships:

Table 1 (JSA Data)	Table 2 (Cable Log Data)	Relationship
EntryDate	EntryDate	One-to-One
FS_Name	FS_Name	One-to-Many

• Compliance Scores are linked to JSA and Cable Control Log data for holistic analysis.
• A Date Table is used to enable time-based filtering.

---

Step 4: Data Visualization (DAX Measures & Calculations)

Power BI’s DAX (Data Analysis Expressions) is used to calculate compliance scores, trends, and KPIs.

Key DAX Measures

1. Total JSA Submissions

DAXCopyEditTotalJSASubmissions = COUNT('JSA Data'[EntryDate])

2. Total Cable Control Log Submissions

DAXCopyEditTotalCCLSubmissions = COUNT('Cable Log Data'[EntryDate])

3. Late & Missing Compliance Rate

DAXCopyEditLateMissingRate = 
DIVIDE(
    COUNTROWS(FILTER('Compliance Data', 'Compliance Data'[Status] IN {"Late", "Missing"})), 
    COUNTROWS('Compliance Data'), 
    0
) * 100

4. Compliance Score Calculation

DAXCopyEditComplianceScore = 
DIVIDE(
    SUM('Compliance Data'[Compliance Score]), 
    COUNTROWS('Compliance Data'), 
    0
) * 100

5. Monthly Submission Trends

DAXCopyEditMonthlySubmissions = 
CALCULATE(
    COUNT('JSA Data'[EntryDate]), 
    DATESMTD('Date'[Date])
)

6. Compliance Score Trend Over Time

DAXCopyEditComplianceTrend = 
CALCULATE(
    SUM('Compliance Data'[Compliance Score]), 
    DATESYTD('Date'[Date])
)

---

Step 5: Power BI Report Development

Power BI Visuals Used

• KPI Cards: Compliance Score, Late & Missing %.
• Bar Chart: Monthly Submissions Trends.
• Pie Chart: Ontime vs Late vs Missing.
• Table Visualization: Employee-wise Compliance Report.

---

6. Project Completion & Validation

Validation Process

1. SQL Query Verification – Ensure extracted data is complete and correct.
2. Power Query Testing – Validate transformations match reporting needs.
3. DAX Measure Testing – Cross-check calculations with business rules.
4. Stakeholder Review – Ensure compliance team approves the final report.
5. Deployment – Publish Power BI Report and schedule automated data refresh.

---

7. Deployment & Maintenance

• Automated Data Refresh – SQL queries will update data daily.
• Power BI Service Deployment – Publish reports for stakeholders.
• Continuous Monitoring – Regular validation of data accuracy.
• Enhancements – Future KPI additions based on evolving compliance requirements.

---

Conclusion

This structured Power BI Report enables real-time tracking of JSA and Cable Control Logs, ensuring compliance, accountability, and efficiency in safety documentation.

Cable Log & JSA Power BI Report format.