PowerBI and Moodle lessons learned

I’ve spent much of the last couple of years building reports and dashboards in PowerBI against the CABI Academy Moodle database. It’s a site with over 27,000 users and a plethora of courses of different types. So the data is complex to say the least…

Here are ten lessons I’ve learned in the process:

🔶 Be prepared to get down and very dirty with SQL (Structured Query Language) - W3Schools SQL tutorial

🔶 The ad hoc contributed reports in Moodle docs are a fantastic starting point to help get you acquainted with the tables and their relationships.

🔶 The Examulator by Marcus Green is your go to reference to explore those relationships. Be aware though, it won’t tell you anything about tables generated by third party plugins. You’ll need to explore these directly in the database.

🔶 Unless you know exactly what you’re doing from the outset, your data model in PowerBI will grow organically. Eventually you’ll get problems with ambiguous relationships and impossible cross-filters. Be prepared to rebuild the model significantly at some point. You’re aiming for a star schema, although with Moodle it’ll be way more complicated than any of the single star examples you’ll find online.

🔶 A solid data model in PowerBI will make your visualisation work so much easier (and more accurate)!

🔶 You’ll need to test your queries against a copy of the database. If you use the live system you will probably crash it temporarily at some point - especially when working with a large dataset. It also helps to have a tool to help you build and debug your SQL queries. I use MySQL Workbench.

🔶 If you need any visualisation that involve dates, you will need to create a Date table. Just do it.

🔶 PowerBI is not like Excel. In many ways, there are things that are easy to do in Excel that become far more complicated in PowerBI (like frequency charts and pivot tables for example). You will need to learn some elements of DAX (Data Analysis Expressions) and of Power Query. These are two similar but different “languages” used in data modelling and data transformation respectively.

🔶 Test, test and test again. Check what PowerBI is telling you against the data you can access directly inside Moodle. It just takes a small error in joining two data tables to throw things out completely.

🔶 Be really clear to yourself about why you’re presenting the information, who it’s for, and what they’ll do with it. One of the hazards of data analytics is that you can produce beautiful visualisations which are meaningless and serve no purpose. Watch out for rabbit holes!

And a couple of comments on PowerBI…

  1. It’s not my ideal modelling and visualisation tool. It’s powerful, but can be painful to use. The editor runs as an app on your Windows PC, rather than in the browser. The online version (like most Microsoft online tools) lacks the capabilities of the desktop app, but is necessary for publication. This makes the editing, reviewing, publishing and version control workflow pretty convoluted at times. I would prefer an entirely cloud-based application, accessible through the browser.

  2. You will need a decent comprehension of data modelling, relational databases and data queries before you can get much out of PowerBI - unless you’re just working with a very simple set of data sources. Although that’s true pretty much of any data analytics platform.

Posted: 02 May 2024

Tags: Solutions architecture Moodle Information architecture

Related articles

Learning Technology Connections newsletter

Sign up now for information, ideas, tools and techniques - sent monthly