MadCowInternet recently undertook a project with a local authority to help them to provide an online rent account to customers in the Council’s social housing.
They were an established Firmstep customer using Firmstep’s Self product as the front-end to develop a MyRent solution. However they needed some help to export the data from their Capita OpenHousing back-office system.
We were lucky enough that the Council already had a nightly export of from OpenHousing that they used for reporting, so that gave us an ideal starting point. We then needed to write a number of SQL queries to select data from the reporting database into the MyRent database where it could be queried by the front-end. These queries were then set to run in the early hours each morning so a customer’s rent account view would be a snapshot of the previous day’s data.
To make the front-end queries as efficient as possible we needed to flatten data stored across seven tables into two MyRent tables. So we are performing queries with up to five table joins during the nightly batch update, making the customer-facing data queries much more efficient and providing a snappy end-user experience.