So I’ve been a fan of the power platform for a long time now. I’ve done presentations about the use of power query in excel to replace the need of even more complicated excel macros to do data trnasformation for those regular tasks of recoding and transforming data in the sector. I started with Access Databases and I’m aware that there are a number of charities who still use Microsoft Access to transform their weekly data feeds for importing into their CRM system.
So ADRFM, our name for our reporting platform, where did it start? One of my first jobs in the sector as database manager was looking at monthly reporting and looking at volunteering numbers and identifying the number of people who dropped out of the recruitment process. I found it fascinating, now I look back and think that if only Power BI existed then (we’re talking about 2006) then fundraisers would have understood what I was trying to explain with a wall of data. Yes I fell into the trap of presenting tables of data and just expecting people to get it. I’ve found out that they don’t!
For years I’ve worked with clients who have needed insight into their fundraising activities. Some have just wanted high level numbers and then some people wanted to drill down into the data, so we’ve taken these lists of requirements that we’ve seen over the years and turned it into a vast array of visualisations that make sense to anyone working within the charity space. So how did we get here.
We looked at the various different areas of CRM systems that we’ve worked with and came up with a data model that provided us with what we needed for reporting.
Then I needed to review what others were putting out there in terms of visualisation styles. Doing an image search on google revealed lots of different styles of reporting and insight see here but what I wanted was something that meant that users didn’t need to ask for a “could I just” once I’d created it. These range from could I see this by a different field instead or now that we have this could I see this as well please. One of the things that frustrates database people is their lack of time for things that are outside of the standard day job, business as usual (BAU) stuff. Reporting and Insight falls into this category normally, but a larger number of job opportunities for reporting and insight opportunities seem to be offered by the larger organisations which is great.
So now I have a model and I have an idea of what’s possible with Power BI so it was now a question of learning Power BI. Enter a pandemic and most of the consulting work I was working on was put on hold whilst Charities tried to deal with BAU, so I decided to learn Power BI. I started with a quick youtube search, I wanted something I could just dip my toe into enter the series of videos by “Guy in a cube” without a shadow of a doubt these guys are probably my favourite bite sized learning duo friends. If you’ve not watched a couple of their videos do it, you won’t regret it.
Then I needed something to help me with the transfer of my skills. Writing DAX is like writing excel formula, most of us only scratch the surface of what’s possible there’s a couple of great resources if you’re starting on your DAX journey, the ones I’ve used the most are: Enterprise DNA and Home – SQLBI. The SQL BI one was really useful for me as it compared SQL code to how you’d tackle that in dax. With Enterprise DNA, the deep dives that Sam and his team have done (again which can be found on youtube) are really excellent. As always I’ve got a couple of reference books that I regularly have by my side but more and more I find using videos better as things change so quickly these days that books seem out of date by the time they are written (apologies to any authors out there reading this!) The one thing that I tried to stay away from was the microsoft documentation. I’ve never got on with Microsoft’s documentation, I’m just not wired that way. If you get Microsoft’s documentation great, crack on, there’s loads of stuff on Power BI it just doesn’t work for me.
So now I have a model, an idea of what page layouts I wanted and a map of how to create the “measures” of the things that I want to count, the next bit was understanding the different options for “cards” the actual thins that you can put on a page. On the SQL BI website there’s this really cool reference tool that helps you understand what visuals you should use and when, you can check it out here: Power BI Visuals Reference – SQLBI
Then it was a case of building it. Blood sweat and tears, lots of sleepless nights, lots of rabbit holes of “how cool is this” and “wouldn’t it be great if..” and then iterating with customers to see what works for the users.
I’m proud to say that during our first year we were shortlisted for a DMA award with the initial work we did with RNID, now into our 2nd year and we’ve split it out to a new company, let me introduce you to Actually Data Analytics, which has three main products a lite version for people just starting out on their reporting journey, a pro version which is everything you could ever want and more (I hope). I promise to talk about this more or check out the website and a medium version for those organisations where only specific areas are ready to tackle reporting.
So I hope that these resources are helpful, there are other tools that I’ve used as well including DAX studio, Power BI helper which have pointed me in the right directions. I talk about these in a different post where I talk about the pitfalls of Power BI for consultants.
If you’d like to find out more then please get in touch or if you’d like to connect about reporting and insight let me know.