Live More new Guides on the way! Get notified by signing up to CAST's newsletter..

Home > Guides

Standardising data collection and automating reporting with Power BI

Contributed by

Catch22 designs and delivers services that build resilience and aspiration in people of all ages and within communities across the UK. This means services like alternative provision schools and colleges, employability programmes, services for young people and families and prison leavers.

Use this Guide when you want to create customised data dashboards that help your team understand organisation data without spreadsheets. It avoids manual repetitions of reporting tasks and makes it easier to work with dense or high volumes of data. 

Jump to steps
Key software:
Power BI
How they set it up:
Used skills they already had
Setup costs:
8-10 week setup time. It requires some technical learning. Microsoft has a co-pilot tool that can help you set it up. You could try it out straight away.
Ongoing costs:
Basic package included in Microsoft 365 subscription - check if you already have one. Free trial, non-profit discount and premium packages available.
Guide in use since:
February 2022

Steps to standardising your data and automating reporting

Identify what is not working with your existing data reporting processes and how this is impacting your organisation. It’s likely that your organisation has many different types of data and your people need a clear way to report it.

Start by understanding at a high level what KPIs and metrics your different staff or teams. Ask about their experience of creating reports each month and how long it takes them. Doing this is sometimes known as a ‘data landscape discovery’.

It’s very likely there will be someone in your organisation who has to report on something every month.

Catch22's version of the steps included in this Guide. They actually have 10 steps.

Catch22 identified two problems:

  • They were spending too much time creating reports from scratch every month - repeating the same processes.

  • A risk of not being contractually compliant in services they delivered if they weren’t reporting well enough

These problems were affecting most staff - from frontline practitioners to the Head of Finance. Reports were poor quality and included hasty Excel graphs. They knew they could be doing it better.

Try Power BI out first. Experiment by adding old or inconsequential data.

Power BI will create live, interactive dashboards for you. You can embed these into other places - for example presentation decks. Do this so you can see what's possible.

Share these with others who are responsible for reporting. Then they will see what is possible too.

You could use other data tools to Power BI. These include Databox and Google Data Studio.

 Power BI's display of ONS crime data that Catch22 used to see what was possible with Power BI.

Catch22 tried Power BI with some ONS data first. They downloaded crime data for an area as a CSV file from the ONS website and uploaded this to Power BI.

This gave Catch22 an idea of what was possible. They saw how they could drag and drop columns of data, add calculations and replicate formulas like those they used in Excel - but much faster.

They used Power BI tutorial videos from YouTube to help them learn how to do this. Most videos included onscreen text and example code as part of their tutorial. To help them copy what the video was showing they used a Google Chrome browser extension called ‘Select text’. This extension enabled them to copy on-screen text from the video and use it in their Power BI experiments.

If you want to copy a video's on-screen text you could use the select text Chrome browser extension.

List all your organisation’s data sources. Do this by identifying where data comes from or is collected. Include all the systems that provide information you need to report on. This includes:

  • Case management systems

  • CRMs

  • Spreadsheets (include Excel and Google Sheets)

  • Social media analytics

  • Any website analytics

  • Any other online data source (Power BI can connect to any URL)

Catch22’s data lead contacted all their teams for a list of their data sources. Where necessary they spoke to each team to get a sense of all the data they used and how it was structured.

You need to find out what data people in your organisation need to report on. This will form their requirements. Consider:

  • KPIs, comparison data, variations in data

  • Frequency of reporting - for example monthly, quarterly, annual.

  • The most important fields they need to report on

Most people know what KPIs are working to and what depth of data they need to see in their reports.

Catch22 built a questionnaire to gather people’s requirements. The questionnaire asked:

  • What is the end deliverable you want to see?

  • What are the different measures you want to see?

  • Who is the audience?

  • How often do you want your dashboard’s reports to automatically refresh?

  • Who needs access to the dashboard?

Want a hand with finding out people's reporting requirements? Use Catch 22's Automated Report Requirements template.

Look at the data sources and people’s requirements. Identify which dimensions or fields from the data sources match which requirements. This may take some thinking.

For example, your business development person or team may want to see all the grants, contracts and opportunities they could be bidding on. They might want to see the close date, delivery start date, value etc. In this case identify the individual pieces of data you need to make it happen.

Do this with only one team, person or project first. You might need to meet and show them what could be possible within their requirements. Use dummy data to do this. This will lead to a more creative and engaged conversation.

You should also talk about personal information and data security with each team. If your data dimensions include or create a need to include any personal identifiable data then even though this data will remain within your organisation you should consider password protecting any dashboards you create that will use that data.

Catch22’s data team meets with each project before creating a data dashboard for them. They prepare for each meeting so they have a good idea of what the requirements and dimensions are likely to be. They begin by showing what is possible using dummy data. They try to guide and suggest what will be useful. They try to balance what is possible with what the person wants and what will be most useful.

“Try to spend as much time talking to people about what they need and want as you spend building a data model. People can change their minds a lot and what they think they need at the start is not always what they need at the end.” - Magid El Amin, Director of Evidence & Insight, Catch 22

Most of their dashboards would not need to use any personal data because they aggregated data. But when a report did need to include personally identifiable information Catch22 password protected and tagged it as highly confidential within that Power BI dashboard.

Combine all the relevant data tables and sources, linking them by what they have in common. This is usually called a ‘star schema’. Spend time experimenting with this until you have got it right as it's a lot of work to go back and change it later. You’ll want to get feedback on your prototype data model (see Step 8).

Do this by building a star schema in Power BI for your first project. It pulls all data sources together and connects them based on similar fields. Learn how to do this using Power BI’s free training suite or YouTube videos. 

As you do this make sure you split tables into facts and dimensions.

A fact table is a table of all the information that is unchanging and that you’ll always want on a particular dashboard - for example clients, supporters, revenue, locations. These are your constants. You’ll always want to see these on a dashboard.

Your dimension tables contain all your variables. They are the ways you want to see that information or ‘slice’ the data - for example demographics, time of event.

You should have few fact tables and many dimension tables.

Then use Power BI to help you identify fields that appear in different tables. For example if you’re building a fundraising dashboard the supporter id would link to your demographics, donations and comms preferences tables. Power BI calls this type of data a ‘primary key’.

Take your time doing this. It is valuable to explore and experiment as it will help you understand the data set more intimately. This will lead to a better model.

Along the way you will uncover errors and inconsistencies in the original data that’s been entered. Report this to the team or project so they can fix it and improve the data quality.

Be aware that not all data sources can be automated into Power BI. Talk to your technical person or team if you find one that can’t be.

Learn how to use Power BI’s through its free training suite or YouTube videos. Catch22 recommend the following Youtube channels: 

Catch22’s data team experimented with different ways of connecting data sources. They would link them based on different data fields to find out if it was helpful. Some were helpful and some weren’t. They used trial and error, YouTube and the Power BI forum to learn.

Some datasets were easy to work with and took a couple of days to set up a model. Some were more confusing and needed more time to explore and understand what connections were useful.

They did all of this within a ‘sandbox’ - a Power BI workspace just for testing and prototyping. Final versions were published to their own workspace for each team.

“Spend more time building the data model than you think you should. It will feel like you haven’t produced anything but it's a critical exercise to get it right.” - Magid El Amin, Director of Evidence & Insight, Catch 22

Power BI offers an intuitive way to create data visuals.

  1. Spend more time on the data model than you do on visuals

  2. Create a prototype. Don’t aim for perfection.

  3. Explore the Power BI marketplace for templates that others have created

  4. Follow good design principles. Use lots of white space. Avoid clutter.

  5. Don’t use pie charts as they are confusing and make it difficult to see what is relevant

Catch22 used FT interactive’s visual vocabulary for ideas of visuals for different types of data. They create a lot of different visuals for each prototype so people can see what is possible and identify what will be useful.

To get ideas of how data can be visualised use FT interactive's visual vocabulary.

Give the first iteration of your dashboard (data model and visuals) to stakeholders and ask them for feedback. You could do this on a video call using a screenshare of the dashboard itself.

Talk through what you’ve done. Explain that it's a prototype. Point them to interesting things you’ve seen. Include errors too. Show them how it meets the end deliverable they said they wanted to see.

Ask them if they want to adjust any requirements. So they want to see things differently? For example, do they want to see client referral rates for each month as a percentage or a whole figure or both?

Catch22’s data team sends the dashboard prototype to the project with a feedback form. Once they've received the feedback they have a 30 minute call where they run through the feedback. Some stakeholders are really engaged.

After receiving feedback they offer 2 more iterations of the dashboard. To keep this manageable and within scope they:

  • Set deadlines for feedback and change requests

  • Limit stakeholders to 5 cosmetic (minor) changes and 2 data model (major) changes

  • Limit the time they spend implementing feedback

  • Try not to make changes that would go beyond the original scope.

  • Avoid aiming for perfection.

Need to make a feedback form for your dashboard? You can copy Catch22's form.

Iterate the data model based on feedback. Try and do it promptly while you and the stakeholder retain a memory of the feedback, even if its well documented. This will maintain stakeholder engagement and save time.

Improve or reduce the number of visuals based on feedback. Sometimes a stakeholder might ask for a new view - for example 3 years ago vs this year. This is easy to do in Power BI.

Catch22 try to deliver a second version within a working week. They find it helps to build on the momentum and understanding generated by the feedback meeting. Doing it promptly also helps the stakeholder remember what their feedback requirements were when they see the new version.

Schedule a periodic refresh to the dashboard so it refreshes every week, month or quarter etc.

Then you can publish the dashboard to a workspace and set permissions to make it available for others to view. They can interrogate the data but they can’t change it. Only the data team can do that.

Explain to stakeholders:

  • Not to change the name of data sources or their columns or fields.

  • Not to change where data sources are kept.

Doing either of these things will stop Power BI from using that data.

Catch22’s finance team’s dashboard is set to refresh weekly at 4am on a Monday at 4am, That way on Monday morning the dashboard is up-to-date.

The Justice team’s dashboard is partly populated by data the team downloads from a Ministry of Justice portal every month. The team used to add this data to an Excel document but now they just share it in Sharepoint and Power BI automatically picks it up and uses it to populate their dashboard.

You should periodically revisit each dashboard and check it’s doing what it needs to do.

Integrate forecasting by integrating historic trends.

Catch22 check in with each team 1, 3 and 6 months after they receive their dashboard.

They also run a Power BI usage metrics dashboard that shows how much each dashboard is being used by its team. When a team doesn’t use their dashboard much they can ask them why and offer support to make better use of it. This is particularly useful for staff who are learning how to use data.

They also periodically share some of the highest standard reports and dashboards with every team. This helps increase expectations and ambition about what is possible.

Further information