Like many businesses, they faced the challenges of spending too much time making reports and not enough time doing the analysis. The switch to Open Tolling added to their difficulties as this was a step into the unknown, with new processes and procedures required overnight. The large amount of data recorded in different systems made it hard to track and report on things with significant manual effort.

They were already using Power BI and asked Geordie Consulting to help them improve their methods of handling, modelling and analysing data, from the first data collection to paying for the journey (or the fine).

Many organisations start their journey with Geordie Consulting by using Power BI, which is a low-cost or even “free” tool to create and test a proof of concept. Often, the initial Power BI solution has to rely on existing data feeds and the native features of Power BI. The success of that first solution then helps an organisation to decide how much they want to invest in Power BI and the broader Azure Analysis/Data suite (now Fabric).

As an organisation dependent on service providers, the main data used is accessed in terms agreed by contract. One primary provider shares files through a gateway. A Power Automate Flow automatically transfers files from the gateway to a triage site in SharePoint. Another flow processes the files in triage, using a Microsoft List to control locations. This two-flow approach allows for consistent processing of different files, with only one line in the list needing to be updated for any changes or additions to the files or locations. A final flow deletes old files. Each file location refreshes its dataflow. This can be done by schedule or “on update”.

Successes/failures can also be monitored using another list, where the status of every flow can be seen centrally. This reduces the support effort while also minimising the need for extra subscriptions/licenses and the impact of an IT-led change to Azure Data Factory (if the organisation prefers).