• Home
  • Portfolio
  • Drainage services analytics transformation with Microsoft Fabric & Power BI

Drainage services analytics transformation with Microsoft Fabric & Power BI

To support business growth and enhance visibility and control over all its aspects, our customer aimed to improve their reporting and analytics capabilities. Itransition delivered the centralized data warehouse using Microsoft Fabric and a suite of Power BI dashboards for real-time insights and business intelligence.

Increased

business visibility and predictability

Improved

employees' performance visibility

95%

less time spent on financial reports

30%

more efficient planning

Drainage services analytics transformation with Microsoft Fabric & Power BI

All technologies used

Power BI

Microsoft Fabric

Power Platform

Engagement model

Full project outsourcing

About the customer

Our customer is a mid-sized European drainage and plumbing services provider that specializes in emergency response, routine maintenance, and advanced diagnostics of drainage systems. Its service spans from unblocking and repairs to planned preventative maintenance programs for businesses and public-sector organizations, helping their clients minimize emergency incidents and service interruptions and reduce long-term repair and asset replacement expenses.

Domain

Drainage and waste management

Geography

Europe

Size

Medium-sized enterprise

Client base

~ 3,000 customers per year

The challenge

Our customer relied on multiple systems to support their business operations - Sage 50 for accounting and BigChange for job management. Reporting and analytics were done manually and within individual departments such as sales, HR, finance etc. To produce reports, employees had to collect data not only from different systems, but also from Excel spreadsheets and e-mail communications. This fragmented approach made reporting processes very time-consuming, inefficient, and not always accurate.

The lack of standardized data and fragmentation across systems complicated the consolidation of information into a single, reliable source of truth. As a result, the company struggled to generate consistent, organization-wide insights.

Without unified and accurate data, company’s management had limited visibility into overall business performance, including team productivity and revenue forecasting. This influenced company’s ability to make timely and well-informed strategic decisions.

To address these challenges, the customer needed a reliable and experienced partner to review their data landscape, advise on the best course of action for BI adoption and assist with the implementation of a reporting solution.

The solution

At a glance

Itransition built a data warehouse using Microsoft Fabrics and implemented Power BI dashboards for real-time insights. Business data were ingested from the BigChange order management system and the Sage 50 accounting system into a centralized data lake for BI purposes.

By unifying the customer’s data and creating insightful dashboards, we provided the customer with clear, real-time visibility into their operations.

Analysis

Since our customer didn’t have any reporting system and had fragmented data across multiple platforms, our team began by conducting a comprehensive review of the customer’s data landscape. It included an analysis of existing business processes and alignment with management expectations for future reporting and dashboards. Itransition identified several areas that required business processes restructuring to enable efficient reporting. In collaboration with the customer, the team defined and agreed on various improvements, for example:

  • department-level changes, such as introducing responsible team members for the quotes analysis process, segmenting clients based on defined criteria and assigning dedicated account managers, linking financial data to maps and postal codes, highlighting employee skills in reports, etc
  • changes within the BigChange system, particularly in customer’s approach to engineers’ scheduling and workload planning, including changes in the works codes, in designating activities, materials, etc
  • adjustments to accounting processes, especially around invoice generation

Implementation

To address customer’s challenges, Itransition’s team built a data management system using the Microsoft Fabric - an analytics platform that supports end-to-end data workflows, including data ingestion, transformation, real-time stream processing, analytics, and reporting. To ensure a scalable and structured approach to data transformation, we adopted the medallion architecture, where data progresses through successive layers of architecture - Bronze to Silver to Gold - gradually improving in quality and usability.

The source data was coming from the BigChange and the Sage systems as well as external CSV files, and to ensure consistency, our team worked on reconciling and balancing all the data. Itransition’s experts suggested the optimal formulas for calculation of profit and revenue distribution.

To meet the requirements in scalability and flexibility, we implemented a multi-tenancy architecture in Microsoft Fabric. This way our customer could seamlessly add company’s subsidiaries through configuration rather than redevelopment.

Power BI dashboards

To help the customer get a comprehensive view of company’s operational and financial performance, and to support forecasting and strategic planning, Itransition implement several Power BI dashboards:

  • Revenue distribution dashboard with a high-level overview of financial performance. It allowed the management to track total revenue, profit, material costs, and profitability across clients, client types, and account managers
  • Engineer performance dashboard helped to monitor and assess the performance of engineers and teams based on key metrics such as revenue, job count, and profit. It also highlighted skill distribution, availability, and trends over time
  • Client retention & repeat business dashboard tracked client loyalty, repeat job activity, and geographical job distribution. It gave a great understanding on how often clients return, what client types drive volume, and where jobs were concentrated
  • Client segmentation analysis dashboard allowed the customer to compare their clients by revenue, job volume, average job value, and the variety of services they use, helping to identify the most valuable clients, high-volume accounts, and key service engagement trends
  • Quotes raised & accepted insights dashboard provided visibility into the quote management process, helping to evaluate the effectiveness of sales efforts, quote conversion trends, and client engagement through quoting activity
  • Seasonal demand analysis dashboard with a view of monthly and seasonal trends in job volume, revenue, profit, and service demand helped to identify high- and low-demand periods, supporting planning for resource allocation, staffing, and service delivery
  • Revenue comparison dashboard enabled the company’s management to compare monthly revenue performance across multiple financial years. It helped identify revenue growth and seasonal patterns, and changes in operational output over time
  • Engineer availability dashboard offered a clear and detailed view of engineer availability, both historically and in upcoming periods. It was used to support scheduling, coverage planning, and tracking of staff absences and working hours

To ensure secure access to sensitive data, our team went beyond implementing various custom data filters. We designed a user-specific experience where an employee automatically accesses tailored dashboard page based on their position in the company. The page may display different data sets, varying level of detail, or entirely distinct views.

For profit and revenue reporting, we implemented custom logic within CI/CD release pipelines to control the visibility of visual elements. This ensured that sensitive financial information is appropriately displayed or restricted according to each employee’s role.

Turn to an experienced data analytics partner
Book a call

The outcome

Itransition successfully built a centralized data warehouse, unified and standardized all the customer’s data, and delivered Power BI dashboards with real-time insights into revenue distribution and comparison, engineers’ performance and availability, client retention, etc, enabling more informed strategic decision-making. As a result, our customer has achieved:

01

Increased business predictability due to established seasonal demand and fluctuations analysis capability

02

Improved visibility into employees’ performance due to centralized reporting

03

Improved risk assessment and finance management due to high visibility of financial flows

04

95% less time spent on financial reports due to full reporting process automation

05

30% more efficient and simpler planning due to automated workload analysis