Requirement Document for Power BI Report: _Drill Productivity

1. Overview

The _Drill Productivity Power BI report provides insights into productivity metrics related to drilling operations. The report includes key performance indicators (KPIs), historical trends, and delay analysis to assist in decision-making and performance improvements.

2. Objectives

  • Monitor drilling productivity by tracking the days of cables, phases, and MDU (Multi-Dwelling Unit).
  • Evaluate performance trends over different years.
  • Identify and categorize delays affecting productivity.
  • Provide a detailed breakdown of delays by type, area, and severity.
  • Offer interactive filtering options for better analysis.

3. Key Metrics & KPIs

The report includes the following key metrics:

  • Total Cables Installed: 148,212
  • Total Phases Completed: 217,491
  • Actual MDU Days: 38,631
  • Average Cables per MDU per Day: 3.84
  • Effectiveness Score: 0.88
  • Delay Impact Score: 0.41

4. Visualizations & Data Breakdown

4.1 Productivity Trends
  • Yearly analysis of productive and delay hours from 2013 to 2024.
  • Trendline showing average cables per MDU per day.
4.2 Delay Analysis
  • Delay Categories by Day: Breaks down major delay types.
  • Delays by Area: Geographic distribution of delays.
  • Detailed Delay Breakdown: Categorized into different factors such as weather, structure access, unavailable cables, etc.
4.3 Adjusted Productivity Summary
  • Summarizes adjusted MDU days and average cables per MDU per day.

5. Filters & Interactivity

Users can filter data by:

  • Company Name
  • Entry Date (From 11/15/2013 to 8/8/2024)
  • Major Delay Category
  • Specific Delay Name

6. Data Sources

  • Data is extracted from internal operational records related to drilling.
  • Includes structured datasets for productivity tracking and delay categorization.

7. Expected Users

  • Operations Team (for daily monitoring)
  • Project Managers (for decision-making)
  • Executives (for high-level insights and performance evaluation)

8. Business Impact

  • Identifies productivity bottlenecks and enables proactive measures.
  • Improves resource allocation by analyzing delay trends.
  • Enhances transparency through interactive dashboards.

9. Assumptions & Constraints

  • Data accuracy depends on timely and correct data entry.
  • The report assumes that all recorded delays are categorized correctly.

10. Future Enhancements

  • Integration with real-time data sources.
  • More granular drill-down features for deeper analysis.
  • Predictive analytics for future productivity estimation.
drill ProductivityDownload

Project Completion Method for Power BI Report: _Drill Productivity

This section outlines the steps taken to complete the _Drill Productivity Power BI report, including SQL queries for data extraction, Power Query for data transformation, and DAX for calculations and measures.

1. Data Extraction (SQL Queries)

The first step is to extract relevant drilling productivity data from the database using SQL queries. The queries retrieve cable installations, phases, MDU days, delays, and performance metrics.

1.1 Extracting Productivity Data

sqlCopyEditSELECT 
    EntryDate, 
    CompanyName, 
    Total_Cables, 
    Total_Phases, 
    Actual_MDU_Days, 
    (Total_Cables / Actual_MDU_Days) AS Avg_Cables_Per_MDU_Day
FROM DrillProductivityData
WHERE EntryDate BETWEEN '2013-01-01' AND '2024-12-31'
ORDER BY EntryDate;

This query fetches essential productivity metrics, including total cables, phases, and MDU days, and calculates the average cables per MDU per day.

1.2 Extracting Delay Data

sqlCopyEditSELECT 
    DelayDate, 
    DelayCategory, 
    DelayName, 
    AreaName, 
    DelayHours
FROM DrillDelays
WHERE DelayDate BETWEEN '2013-01-01' AND '2024-12-31'
ORDER BY DelayDate;

This query pulls delays categorized by date, type, and location, which helps analyze the impact of delays on productivity.

2. Data Transformation (Power Query in Power BI)

After extracting data, it is cleaned, transformed, and structured in Power Query (M Language) before loading it into Power BI.

2.1 Handling Missing & Duplicate Data

mCopyEditlet
    Source = Sql.Database("ServerName", "DatabaseName"),
    DrillData = Source{[Schema="dbo", Item="DrillProductivityData"]}[Data],
    RemoveDuplicates = Table.Distinct(DrillData),
    RemoveNulls = Table.SelectRows(RemoveDuplicates, each [EntryDate] <> null and [Total_Cables] <> null)
in
    RemoveNulls
  • Removes duplicates
  • Filters out null values for key fields

2.2 Merging Productivity & Delay Data

mCopyEditlet
    Productivity = PowerBI.Data("DrillProductivityData"),
    Delays = PowerBI.Data("DrillDelays"),
    MergedTable = Table.NestedJoin(Productivity, "EntryDate", Delays, "DelayDate", "DelayData", JoinKind.LeftOuter),
    ExpandedDelays = Table.ExpandTableColumn(MergedTable, "DelayData", {"DelayCategory", "DelayHours"})
in
    ExpandedDelays

This joins the productivity and delay tables to provide a comprehensive view of drilling performance.

3. Data Modeling & Measures (DAX in Power BI)

Once the data is cleaned and loaded, we create DAX measures to calculate KPIs.

3.1 Calculating Average Cables per MDU per Day

DAXCopyEditAvg_Cables_MDU_Day = 
DIVIDE(
    SUM(DrillProductivityData[Total_Cables]), 
    SUM(DrillProductivityData[Actual_MDU_Days]), 
    0
)

This calculates the average number of cables installed per MDU per day.

3.2 Total Delay Hours

DAXCopyEditTotal_Delay_Hours = SUM(DrillDelays[DelayHours])

This measure calculates the total delay hours across all categories.

3.3 Productivity Effectiveness Score

DAXCopyEditEffectiveness_Score = 
1 - (DIVIDE(
    [Total_Delay_Hours], 
    SUM(DrillProductivityData[Productive_Hours]), 
    0
))

This measures productivity effectiveness, considering delay hours and productive hours.

3.4 Delay Impact by Category

DAXCopyEditDelay_Impact = 
CALCULATE(
    SUM(DrillDelays[DelayHours]), 
    ALLEXCEPT(DrillDelays, DrillDelays[DelayCategory])
)

This calculates the total delay impact per category, helping identify major causes of downtime.

4. Visualization & Final Report in Power BI

After calculations, we use Power BI visualizations to represent data:

  • Line Chart: Yearly productivity trends (Avg Cables/MDU/Day)
  • Stacked Bar Chart: Delays by category and area
  • Table & Matrix Visuals: Detailed delay breakdown
  • KPI Cards: Effectiveness Score, Total Cables, and Delay Hours

5. Project Completion & Deployment

5.1 Report Testing

  • Verified data accuracy by cross-checking SQL results with Power BI output.
  • Ensured filters and drill-through functionalities worked as expected.

5.2 Publishing & Sharing

  • Published the report on Power BI Service.
  • Configured access roles for stakeholders.
  • Enabled automatic refresh for real-time updates.

5.3 Future Enhancements

Develop AI-based delay prediction models in Power BI.

Implement real-time data streaming for up-to-date insights.

Here are two sample data files based on the _Drill Productivity Power BI report. The files contain:

  1. DrillProductivityData.csv → Includes productivity metrics (Cables, Phases, MDU Days, etc.).
  2. DrillDelays.csv → Contains delay details (Date, Category, Delay Hours, Area, etc.).
DrillProductivityDataDownload
DrillDelaysDownload