An obvious starting point
What happens when you have 30-something reports across multiple departments, all predicated on differing interpretations of the same performance measure? Aside from the obvious problems, such as misrepresentations, lack of clarity, a reduced impact on decision making and a whole mess-of-work for a BI team, you get an obvious starting point for a Power BI project.
Here at South Liverpool Homes, that project focused on arrears management and a journey into how Microsoft’s Power Platform could relieve the burden of arduous tasks in the income team. Ultimately, the primary goal was to create a homogenous reporting solution for our arrears’ performance. Simple? Apparently not. Even at a managerial level, what constituted ‘arrears’ wasn’t necessarily what an income officer gleaned from the policy. Or, adding to the mix, a member of finance. Worryingly, each one of these entities reported back to our executive management team with different numbers as a result.
Why Power BI?
Our key objective was to provide a single consistent view of our arrears’ performance. In order to achieve this, it was essential that we combined the key elements from the multiple reports that staff had become so reliant on over the years and condensed them into one multi-functional, visual dashboard.
Consistency and visibility were two of the key drivers behind creating a single view of arrears. If we were ever going to make sure a single dashboard was going to be wholeheartedly adopted and used to its full potential, we needed to ensure that it met the needs of the teams and catered for everyone.
Our ideas and scope for this implementation were far more than just a percentage and cash value of arrears. With the array of graphical tools, the ability to view data on so many levels and being able to connect to a multitude of data sources within Power BI, it was the perfect fit to give us the platform to put these ideas into practice.
Challenges
First of all, we needed a firm answer on what constituted ‘arrears’, and how the performance indicators should be measured. We engaged the relevant members of staff across the organisation and thrashed this out during a number of workshops. One of the bigger challenges was how we used completely different models to report on current tenant arrears (CTA), former tenant arrears (FTA), total arrears and other arrears. The idea of amalgamating these types of arrears into a single dashboard became the foundation of the build.
The policy was rubberstamped and coding could begin. Depending on the department, this was a process met with some rebuke. Inevitably, performance in some areas was about to suffer due to a change in policy. More accurately though, the performance was now clearer. It was properly defined regardless of the output. It was analytical and predictive, and apportioning resource was immediately more possible.
Power BI had been mobilised at SLH, but with slow buy-in from departments across the organisation, or rather, without a true purpose for managers to fully engage, it was never fully used. This was an opportunity to demonstrate what the tool could offer, how it could increase collaboration, clarity and the speed with which strategic decisions could be made. And because of Power BI’s position within Microsoft’s Power Platform, we could seamlessly integrate with SharePoint, Power Automate and Power App capabilities and integrate with our housing management system (Orchard) where needed. This was all easy to sell.
Highlights
The main overview screen provides everything you would expect from an arrears report, arrears total by patch, by income officer and how this was fluctuating. However, with the introduction of and changes to universal credit, it was fundamental to the design that we not only provided a picture of our arrears’ performance but also encompassed some cause and effect into how the changes in our tenants’ individual circumstances were affecting our performance as a whole. By being able to see at a glance the number of housing benefit claimants and how many UC, DHP and APA cases the team are managing provides some rationale to how these external factors are affecting our performance over time.
One of the demands on us while scoping the dashboard was that our staff craved a 360-degree view of an account and all of the related interactions with customers. A common frustration for teams was notes which were held in isolation in different areas of the housing management system. With this in mind, we set about creating an individual tenancy view which pulled together the tenant’s unique profile with a unified chronology of notes.
Staff can now view key data about each customer regarding their arrears history and the work that has gone into managing their account since their tenancy started. It highlights if they’re currently involved in any ASB or complaints cases, if they are currently being offered any support through SLH services and a repairs history for the previous 12 months.
This information has always been available but was disjointed and required running multiple SSRS reports and endless VLOOKUPs. However, by piecing it together and aligning it with how the account is performing, it provides insights on any potential triggers that could have caused an increase in arrears and allows early interventions. Any notes needing to be added into the customer’s account in Orchard or the sending of a follow-up SMS message can be done through the dashboard if required.
An additional analytical approach naturally grew when we started to work with the data – this is where our tenancy rating system originated from. Another overall indicator develops over time depending on the activity on an individual’s rent account – taking into consideration account balances, missed payments and cancelled direct debits among others, they all contribute to potential risks or behaviours that can be used to manage accounts more proactively.
This golden thread and the ability to go from the top-level service area performance through to an individual account is easily adaptable into all future dashboards, providing insights that go into more than just numbers.
Technologies at the core of the solution
The data for the solution was built in SQL. To speed up processing on the frontline, rather than processing years of arrears data with every dashboard interaction, we created stored procedures that constructed live tables that could be queried more efficiently. This concept was used across the board, with specialist elements, such as with TRS calculations, executing at specific relevant dates and times, for example, a tenant’s balance in line with their payment frequency or when a payment switched from direct debit to credit card.
Then, there was Power BI itself. Once you’ve connected to the tables we built, it was a simple case of placing the required visuals into the dashboard, building the drill-throughs and applying the relevant data aggregations and filters to said visuals. The interface is simple to use, so responding to ideas, making changes and building on our progress was simple to do following the many user acceptance sessions.
For writing notes back into our Orchard housing management system, we used SharePoint and Power Automate to push the data into SQL via SSIS. From there, we output data to the Orchard API. The process was similar for processing text messages, where we pointed the output to a messaging solution.
Data cross-pollination
Even working within the confines of arrears, we knew we could widen the analytical scope. Could we forecast arrears, how does the level of arrears vary relative to the payment history of a tenant over previous years, has the tenant missed a payment, and so on.
However, it quickly became apparent that with the massive array of data at our fingertips, we needn’t limit our reasoning to rent-related data. For example, we started to look at how repairs (i.e. a property in a bad state of repair) affected a tenant’s propensity to pay their rent, or whether the addition of new household members resulted in a sharp downwards turn in account credit, plus what about universal credit?
Because Power BI allows you to cross-pollinate data from what would appear to be disparate sources in the simple to use ‘relationship view’, you can create dynamic visuals that allow greater understanding of how external factors affect other areas of the business.
More Than just numbers
Power BI is inherently visual, which is perfect for high-level analytics and for detailed explorations of the underlying data. However, through usage, the scope of the product’s potential becomes more visible. A forecasting model has been implemented, our ability to respond more effectively to ‘outliers’ has been streamlined, and our overarching performance is realised clearer than ever before. Arrears are falling and tenancies being managed more proactively.
For us as a BI team, the possibilities are endless and the investment in development worthwhile. We’ve already begun to explore consolidated compliance solutions, asset profitability models, ASB case analysis, voids and lettings management. All of the dashboards use various integrations throughout Power Platform, cutting down multi-system usage and additional administration. The dashboards are also reducing the number of SSRS reports requiring ad-hoc maintenance and specific understanding that differs from strategic direction. Most importantly, there’s a resounding reception from users, from our executive management team through to our ‘feet on the ground’ staff.
If you would like to see more of what we have touched on above, feel free to contact us at michael.parsons@southliverpoolhomes.co.uk and philip.thompson@southliverpoolhomes.co.uk.
Michael Parsons & Philip Thompson are senior business intelligence analysts at South Liverpool Homes.
