Nexus Infrastructure

Financial Business Intelligence through SSIS Power BI

A leading provider of essential infrastructure services, Nexus Infrastructure offers these services to UK housebuilding and commercial sectors. It has three divisions under which it provides various speciality services. TriConnex provides multi-utility services; ESmart Networks provides energy transition services; and Tamdown provides civil engineering services. The services are provided with customer focus as priority during design, procurement, and delivery stages.

The Nexus Infrastructure reporting systems did not have any capability of providing simple and clear dashboards for reporting on sales, financial, and other performance metrics. They have three different internal databases (one for each division) which all had their own systems to generate reports on demand, but this was in Excel or PDF formats for back-office use only. The requirement was to build a unified data warehouse out of these three different databases and create bespoke dashboards which they could use monthly during board meetings. These reports had very detailed and precise specifications that needed to be met.

The Solution

The initial part of the solution involved setting up a unified data warehouse which brought together the data from all three divisions into a single place and in a single and consistent format across them, rather than having three completely distinct databases. First the data structure was established with a focus on making sure all necessary data for reporting would be captured. This was particularly challenging as the data sources later needed to capture data not only from the SQL databases, but also from Excel files across different file shares. For this, a combination of a .NET console application together with an SSIS package allowed monthly imports of complex Excel files into the data warehouse as well. These packages were also automated to run monthly, ensuring that the data was always up to date whenever the finance teams updated their workbooks.

Following that, each of the report requirements were created as views to simplify the data being returned, specific to each report item.

The bulk of the work was creating the required dashboards. Power BI was the chosen platform, and there were three separate dashboards for sales, cash flow/debtors, and financial performance. With each division requiring their own version of each of these dashboards, that meant nine dashboards in total that were delivered.

The bulk of the work was creating the required dashboards. Power BI was the chosen platform, and there were three separate dashboards for sales, cash flow/debtors, and financial performance. With each division requiring their own version of each of these dashboards, that meant nine dashboards in total that were delivered.