Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
04-18-2023 02:43 AM - last edited 04-27-2023 06:18 AM
The requirement was to visualise the Office of National Statistics Census 2021 data for our Local Authority (Birmingham) and present it on the Birmingham City Data Observatory.
The ONS produce the Census every 10 years and cover a range of subjects about the home, family, ethnicity, work, education, health and new for 2021 is gender identity. This data is usually available down to Output Area (OA). A small unit which can be built up into Wards, Constituencies or even entire citys.
Typically we have visualised the data at a Ward level but using the incredible flexbility in Power BI we wanted to push the boundaries of what is possible. Rather than just visualise 2021 census at Ward level. We wanted to include the entirety of the 2011 Census down to OA level as well. Going futher. The data model contains all OA level data for 2021 and our particular model contains OA level for the West Midlands from the 2011 Census. But, its easy to load any OA data.
We also had a big challenge. Make the entire thing available to everyone, the entire populous via our Birmingham City Observatory site https://www.cityobservatory.birmingham.gov.uk/. Not just internal Power BI users. For this we used the Power BI publish to web feature but had to overcome some user experience challenges.
When publishing for the web the only navigation is that of the next/back and page down the bottom. From observing users we found the majority of users were not aware of this and others didnt like how it worked. So we built in a familiar hamburger popup menu on all pages. This allowed us to avoid wasting valuable page real-estate to a page navigator and avoided sending people back to a dedicated navigation page. We could also relegate "read once" commentary to this menu and thus keeping the focus on the data.
This popup menu is now standard on all of our public reports.
To allow us to visualise and publish as report page within 2 hours of the data being released we needed an efficient datamodel with a simple set of common transformations.
Rather than download a specific set of data for our city we opted to download the bulk data and filter as necessary. As a result the entire data model contains all Output Areas for 2021 and the West Midlands for 2011. To change the model to another geography you only have to change 2 filters on the lookup tables and the Inner Join Merge used on all tables will take care of the rest.
We also had to convert the 2011 OA geography to the 2021 layout so we could perform comparisons. Luckily the ONS provide a number of 'best fit' lookups that will do this for you.
Each table then had a number of basic tansformations to make the 2011 and 2021 data match i.e changing "Ethnic group" to "Ethnic Group". We also filtered out the high level stats such as "Total number of usual residents" to only show the granular items which all made up the higher level. This would allow the data consumer to view the data in its most detailed, granular format.
All the data was then appended into 2 super tables containing all topics. These had a further set of transformations to break the attribute column into its parts. The ONS provided a series of colon and semicolon seperated items. This was broken into upto 3 levels of detail i.e "Ethnic group; White; White British". This could then be visualised in a drill down.
The attribute was also pre-sorted by their logical order rather than their numerical or alphabetical i.e for Health the logical order is "Very bad, Bad, Good, Very good". We took this natrual order and automatically applied a number to allow for custom sort.
Our data scientists from the University of Birmingham then provided a number of clever measures to compare the 2011 dats with the 2021 data to show how many % points the values have changed since the last Census. This was done as a % of the total population for their respective year. This allowed us to easily see, where possible the change.
We made use of tooltips to display rich information and often drill into each variable.
We used the preview feature for a shape map to visualise the data on a map and using bookmarks allowed the user to toggle between OA, Ward and Constituency geographies. At the time, Ward level data was not available. But by aggregating OA level data we achieved this with an accuracy ranging from 99.9998% to 99.99998% when compared to the later released official ward data.
The look and feel was inspired by our corporate "Be Bold" theme and made full use of the Power BI themes so we can easily change the look should we desire.
For a number of reasons the data is stored on a sharepoint rather than a SQL server. One being that we can package the files and PBIX in a single ZIP, share it with others who can then easily tweak for their Local Authority. This meant performing all transformations in the model (over 320 queries) with a mix of find/replace, merge, append and more. This made the model bloated and slow even on the most powerful of machines. We have recently moved the model to a dataflow so the only table presented to the report is the super tables. This has allowed us to now pick the data off of our virtual shelf and incorporate it into other reports with little to no performance issues.
Get in touch
Contact us via the Observatory or reach out to myself Danny O'Neill on LinkedIn
eyJrIjoiNDE3ZjYwMjgtOGE3Yi00NjEyLWEyZTktNmJjMTAyMTQ5OTdjIiwidCI6IjY5OWFjZTY3LWQyZTQtNGJjZC1iMzAzLWQyYmJlMmI5YmJmMSJ9