Power Pivot in Excel and Power BI are amazing tools that can deliver substantial efficiency savings and provide an awesome level of analysis and insight; working with data has never been easier.
Our story
Five years ago we realised, like most housing organisations, that we had lots of data in a variety of different databases and spreadsheets. A huge amount of staff time was being wasted every day, manually extracting data, manipulating it and then putting the results into reports for circulation. The output usually consisted of PDFs circulated by email.
We started the search for a tool that could simultaneously extract data from a variety of databases and spreadsheets. A tool that could cope with data that was in different formats, cope with missing data and cope with errors in the data. A tool that could automatically clean, transform and then produce amazing insights from that data. After a lot of research, we realised we already owned one of the best tools for the job; it was called Microsoft Excel and we just needed to use Power Query and the Power Pivot add-in. We embraced the technology and started to produce a variety of automated reports.
Four years ago, Power BI was released and this took our journey of automation and reporting analysis to a much higher level. Interactive reports with hierarchies, drill-down, cross-filtering, custom tool-tips and drill-through have transformed the end-user experience.
We now have a bunch of fully automated reports churning out incredible analysis every hour and day of the year. A summary of our reports is as follows:
- Weekly arrears report (rent arrears analysis) – data source: SQL db: 104 days saved p.a.
- Weekly flexi-time report (staff flexi-time analysis) – data source: Cloud HR system: 52 days saved p.a.
- Monthly development analysis (detailed 5-year development programme analysis) – data source: SQL db: 12 days saved p.a.
- Quarterly development reconciliation (automatic reconciliation of Brixx forecast to development db) – data source: SQL db & Brixx spreadsheet report: 4 days saved p.a.
- Annual SDR (statistical data return for the regulator) – data source: SQL db and spreadsheets: 15 days saved p.a.
- KPI analysis (KPIs for board and leadership teams) – data source: Sharepoint list: 6 days saved p.a.
- Interest accrual (calculation of interest accrual) – spreadsheets: 2 days saved p.a.
- Management accounts (interactive management accounts) – data source: SQL db and spreadsheets: 4 days saved p.a.
- Annual repairs analysis (insights into repairs) – data source: spreadsheets: 1 day saved p.a.
- Miscellaneous (various other finance reports) – data source: spreadsheets: 12 days saved p.a.
How to get started
Here’s what you need to know to start your own Power Pivot and Power BI adventure.
In Office 365, the Power Pivot add-in is now built into Excel, you just need to activate it (to activate it, go to File, Options, Add-ins then click ‘Go’; next, go to Manage Excel Add-ins and then tick ‘PowerPivot utilities’). For Power BI, simply download the free desktop app.
The same powerful data modelling engine is built into Excel and Power BI, so the knowledge you gain working with data in Excel is directly transferrable to Power BI and vice versa.
To create a report, you need to connect to your data, create a data model and then create your pivot tables or visualisations.
You use the ‘Get data’ button to connect to your data. Once you’ve connected to your data, you can manipulate and transform it using Power Query. This automates the time-consuming process of manipulating the raw data your systems produce into a usable format. It’s also very user-friendly and simple to use.
Data connections can be made to multiple data sources in a single model so, for example, a model can get its data from both a single SQL database and multiple Excel spreadsheets.
You only need to hit the ‘refresh’ button for fresh data to be pulled from the sources, automatically cleaned/transformed by Power Query and then loaded into your model.
Once you’ve connected to your data, you can create a data model. Beginners often learn the basics by working on a single table of data. However, as you progress you will learn how to include multiple tables of data within a single data model. So, a table containing details of your properties can be connected to numerous other tables of data if all the tables contain a Unique Property Reference Number (UPRN) field.
This ability to effortlessly pull data together from multiple sources is of particular benefit for housing organisations, where data is often held in many separate systems, databases and spreadsheets. We are working with HouseMark on a pilot project to develop a mechanism to access benchmarking data directly from PowerBI which will allow us to integrate this gold mine of information into our own reports, thereby enriching the organisational data we already hold.
A formula language called Dax enables you to do calculations with your data. It’s very similar to Excel’s normal language for formulae; the main difference is that it works on tables and columns of data rather than individual cells.
Once the model has been built, you can build Power Pivot tables in Excel or visualisations in Power BI and create a report.
Sharing your work
You can publish Power BI reports and share them through the online Power BI service. Published datasets can use a scheduled refresh to automatically keep them up to date. Users throughout your organisation can then access these ‘golden data models’ to build their own reports in Power BI and Excel (through a Power BI add-in for Excel).
The gateway
You can install a gateway to link your online data models to on-premise data sources, such as your housing database.
The report server
You can publish to a report server on your internal network if you don’t want to use the online service.
Publishing reports for the general public
You can publish reports on the internet for the general public; for example here’s a link to our interactive housing stock analysis: bit.ly/37lC1lR.
Start your own journey now
Download and open the free Power BI desktop app. A welcome screen is displayed that has links to various training resources and videos. There is also a great free dataset you can connect to for learning purposes. It’s an oData connection and the link is: services.odata.org/northwind/northwind.svc.
Good luck, and please feel free to get in touch via news@housing-technology.com if you have any questions.
Gary Pliskin is the finance director at Islington & Shoreditch Housing.
