top of page

CASE STUDY

Employee Productivity Dashboard for a Construction Company

Industry: Construction & Real Estate
Solution Area: Workforce Productivity & Performance Management
Tools Used: Insightful API, Elmo Leave Tracker, Azure Data Factory, Microsoft SQL Server, Power BI, Power Automate

Screenshot 2025-08-26 122853.png

Business Impact

5–6 hours of reporting time saved weekly

by automating productivity and utilization tracking.

​

10–15% improvement in workforce efficiency

through accurate, leave-adjusted engagement and utilization insights.

Reduced overhead costs

​

by eliminating manual errors and enabling managers to rebalance workloads faster.

The Challenge

A construction company needed accurate, consolidated visibility into employee productivity across multiple teams. Their previous approach involved:

  • Productivity data tracked in Insightful and leave data captured separately in Elmo.

  • No mechanism to factor leave into utilization or productivity metrics.

  • Managers relied on fragmented reports, limiting their ability to identify dips, coach staff, and rebalance workloads.

This made it difficult to fairly measure performance, compare teams, and ensure accountability.

Our Solution

We developed a fully automated Employee Productivity Dashboard in Power BI. The solution combined:

  • Azure Data Factory for API ingestion and pipeline automation.

  • SQL Server for structured storage and business logic enforcement.

  • Power BI for visualization of utilization, engagement, and productivity KPIs.

  • Elmo Leave Tracker Integration with weekly server-based file refresh.

  • Power Automate for scheduled PDF email delivery of team dashboards.

Productivity Report (4).png

Key Capabilities

  • Company Overview Dashboard with KPIs: Work Time, Active Time, Productive Time, Utilization %, Engagement %.

  • Top Productive Apps & Teams to highlight efficiency drivers.

  • Team Drilldowns with weekly and daily views of employee productivity.

  • Leave-Aware Metrics – average productivity excludes planned leaves.

  • Engagement Trends – WTD, prior week comparison, and rolling 4-week averages.

Productivity Report (3).png

KPIs Delivered

Work Time Average

Average logged hours (target baseline)

Active Time Average

Time spent in active applications​

Productive Time Average

Active time in productive applications

Utilization %

Productive Time ÷ Work Time

​

Engagement %

Activity ratio over last week, prior week, and rolling 4 weeks

Weekly Trendlines

Organization, team, and employee-level comparison

​

Insights Unlocked

✅ Accurate, leave-adjusted productivity comparisons across teams.

✅ Identified employees and teams with declining engagement levels.

✅ Clear visibility into top-performing teams and applications.​

✅ Zero-touch distribution with weekly email delivery to team managers.

This Power Automate flow triggers on a new email, reads the subject to identify the team, and pulls the matching manager’s email ID. It tracks status and loops through subject names to send each team manager a tailored productivity performance email for their team.

image (81).png

Technical Highlights

Data Ingestion
YouTrac API → ADF Pipelines → SQL Server
Purpose: Extract employee productivity and engagement logs from Insightful and load structured data into SQL Server.
Elmo Leave Integration: Weekly file upload on central server → ADF → SQL → Power BI.
This Azure Data Factory pipeline automates the ingestion and transformation of employee and team data. After copying raw data, it performs a lookup on timestamp information to manage incremental loads. For Each activity loops through dates and executes downstream stored procedures (sp_create_employee, etc.) to update the target database. Parameters like api_token and schema are used for secure and flexible configuration.
Screenshot 2025-08-26 122853.png
Incremental Loading Logic
This SQL snippet demonstrates incremental loading logic, ensuring only new or updated records are processed. It compares the timestamp from the source table (insightful_logs) with the latest recorded load time in the etl_log_table. This approach optimizes performance by avoiding full reloads and reducing processing time.

-- Example: Load only new or updated recordsSELECT *FROM insightful_logs l
WHERE l.timestamp > (SELECT MAX(last_loaded_timestamp) FROM etl_log_table)

DAX: Engagement % Calculation
This DAX measure calculates the Average Engagement % for the last fully completed week in Sydney time. It adjusts UTC to Sydney’s timezone (handling daylight savings), identifies the most recent full week, and filters the ProductivityFlatTable data within that range. Finally, it computes the daily engagement percentages per employee and returns the weekly average, enabling consistent workforce productivity tracking.

Average Engagement % Flat Table =
VAR UtcNow = NOW()
VAR SydneyOffset = IF(MONTH(UtcNow) >= 4 && MONTH(UtcNow) < 10, 10, 11)
VAR SydneyTime = UtcNow + (SydneyOffset / 24)
VAR MaxDate = DATE(YEAR(SydneyTime), MONTH(SydneyTime), DAY(SydneyTime))
VAR LastFullWeekStart = MaxDate - WEEKDAY(MaxDate, 2) - 6
VAR LastFullWeekEnd   = LastFullWeekStart + 6

VAR WeeklyAvg =
    AVERAGEX(
        SUMMARIZE(
            FILTER(ProductivityFlatTable,
                ProductivityFlatTable[date] >= LastFullWeekStart &&
                ProductivityFlatTable[date] <= LastFullWeekEnd),
            ProductivityFlatTable[Employee name],
            ProductivityFlatTable[date],
            "DailyTotal", SUM(ProductivityFlatTable[engagement_pct])),
        [DailyTotal])

RETURN WeeklyAvg

DAX: Weekly Utilization %
This DAX measure computes the Weekly Average Utilization % for the most recent fully completed week. It identifies the date range for that week, then calculates the average utilization across days. For most teams, it returns the standard weekly average, while for the Sales team it applies a custom logic by dividing total utilization by 5 workdays because the sales team can work on any 5 days in a week and there are no fixed weekend leaves as per the leave policy for sales team. This ensures accurate and team-specific utilization reporting.

Weekly Average Utilization % =
VAR MaxDate = MAX(ProductivityFlatTable[date])
VAR LastFullWeekStart = MaxDate - WEEKDAY(MaxDate, 2) - 6
VAR LastFullWeekEnd   = LastFullWeekStart + 6

VAR WeeklyAvg =
    AVERAGEX(
        VALUES(DaySortTable[Day Name]),
        CALCULATE(
            AVERAGE(ProductivityFlatTable[utilization_pct]),
            ProductivityFlatTable[date] >= LastFullWeekStart &&
            ProductivityFlatTable[date] <= LastFullWeekEnd))

VAR weeklyavgsales =
    CALCULATE(
        SUM(ProductivityFlatTable[utilization_pct]),
        ProductivityFlatTable[date] >= LastFullWeekStart &&
        ProductivityFlatTable[date] <= LastFullWeekEnd) / 5

RETURN IF(SELECTEDVALUE(ProductivityFlatTable[team name]) <> "Sales", WeeklyAvg, weeklyavgsales)

Impact

Manual Hours Saved

~5–6 hours/week

Insights

Clear visibility on apps, teams, and dips

Report Accuracy

+100% (leave-aware, no manual errors)

Data Freshness

Daily refresh via API & automated load

Adoption

Managers across all departments use it

bottom of page