Bromcom’s MAT Vision product is a very easy way to integrate Power BI and your Bromcom data – simply point your Power BI at the Bromcom OData feed, enter your ID and password and you have deep access to the underlying data in the Bromcom MIS database. Most of the data you have to work with is straightforward and easy to understand, however reporting on class data is not intuitive and needs some explanation.

I’ve been talking to Sinan Ozcetin from Bromcom to understand how best to extract class data in ways that we can analyse with Power BI.

One of the most important data tables that Bromcom MAT Vision provides is the Collections table. In Bromcom, a ‘collection’ is a generic term for all the groupings that we organise pupils and staff by: tutor groups, classes, departments, year groups, houses and faculties. The Collections tables is effectively a lookup table, listing all the collections that each school has set up. Imagine that we filter the Collections table to just show collections of type CLASS. We would see one row for each class, defining the start and end date for the class and giving each class a unique collection ID.

Linking pupils to the collections we have a table called ‘CollectionAssociates’ – literally all the pupils associated with each collection (it can include employees as well, but to keep things simple I’ll just discuss pupils). This table lists a unique pupil identifier and the corresponding collection ID, plus a start and finish date. Imagine this table listing a each pupil across several rows, each row representing a class taken by the pupil, the class represented by the collection ID rather than the actual name of the class.

There is also a CollectionHierarchies table which identifies a parent collection for each entry in the main collections table. For a given class, this might reference a subject department. The CollectionLocations table identifies rooms in school and links collections to them, and finally the CollectionExecutives table identifies members of staff (for example, class teachers). Here’s my schematic:

You can see from the schematic that choosing a particular class from the Collections table will filter all the tables below it and hence filter the CollectionAssociates table, allowing us to select the details of a particular pupil, and hence their assessment results.

You can use this structure to analyse your assessment results by pupil, subject and class. But don’t take my word for it, Sinan and myself (mainly Sinan – I take credit only for any errors) have put together an example PBIX file which you are welcome to download and connect to your own MAT Vision OData feed. You can download the file here. The file includes everything you need to start creating assessment analyses by class, subject and pupils – it even includes some basic started visualizations to help you understand the principles. Let me know how you get on!