[bromcom] Uses for Microsoft PowerBI?

[themightymrp]

So... gaining access to live data from Bromcom is now a feature which I've been playing on and off with for a couple of months. Microsoft are now providing PowerBI for free through the Office 365 licenses too.

 

Does anybody have anything set up between Bromcom and PowerBI? If so, what are you using it for? Can you share a screenshot or anything useful which I can use to gain some idea of what could be possible? Our school Business Manager has been talking about his 'dream' for a while of having a live dashboard available with all the relevant data showing without having to manually run a ton of reports all the time. This seems like it could potentially head me in that direction.

 

Am I right?

 

Cheers

[TechMonkey]

I would find out what he would like and base it on that. PowerBI is one of those things that you can use a million and one different ways and it is only the one that is useful. I played around with it for accounts data, showing a live dashboard of debtors and top bills. I was in a conference session that talked about cross referencing data from accounts, timetable and MIS to analyse the 'cost' of teaching staff or subjects, per student costs and then modelling changes, for example if a subject was removed which students would the school lose (the example was a sixth form) and what other subjects would be affected.

[BrownShoes]

No experience of Bromcom - sorry themightymrp - but we've seen some stuff using PowerBI from SIMS and it looks quite interesting...with considerable potential as suggested in the previous post.

[matt40k]

Microsoft are now providing PowerBI for free through the Office 365 licenses too.

 

Are you sure? Power BI basic is free, but you can't share reports. You need pro - its included in A5 \ E5, but thats ££ \ ££££

 

Pretty sure Bromcom announced they have a number of pre-build reports\dashboards you can use out-of-the-box - can't imagine they are free.

 

Not a fan of "live" data, I mean what are you going to do with that data? So the debitors for example, you need a up-to-date list in terms of contacting them - but surely this should be from within your MIS - ideally this should be auto emailing them etc. In terms of BI - you don't need live data, you need historic to answer BI questions, how long does it take someone to pay vs the rest, do they always pay shortly after an email at 7pm more then say emailing them at 5pm.

[themightymrp]

I could very well be wrong about going down the Power BI route, I have zero clue on what it really can do. One of the things I'd like to be able to do, but can't within Bromcom, is to have attendance data analysis comparing the current year (month/week etc) with the same time last year.. on a graph. Something that should be so simple and yet you can't overlay it like that without first exporting it all to Excel and running manual manipulation. I've treble checked this with Bromcom support so it's not just me missing something.

 

If I can come up with anything of use I'll share it on here, just for the sake of curiosity

[matt40k]

Yer, that'll be dead easy. Also rolling it up from days to weeks\months\terms\years added prediction\trend lines etc

[themightymrp]

Yer, that'll be dead easy.

 

So says you I don't have a clue where to start LOL

[RootsSchImp]

Hi @themightymrp,

 

I'm working on BI for Bromcom at the moment and while I can't give you any marketing spiel, I hope the mods will be cool with me giving you some pointers here.

 

Firstly, rather use reports you might want to pull data in via an OData feed https://cloudmis.bromcom.com/nucleus/odata (username your school ID and password your API key from the user menu in the top right of the MIS). OData lets you bring through a set of tables with Assessment, headline figures, etc in one go and it's a lot easier than creating and maintaining ad hoc reports. You might need to get support desk to activate the feed for you.

 

Regarding costs/licensing you can configure the MIS such that everyone with permission to access the dashboards via Analysis| PowerBI Viewer does so via a shared account. You do this is in Analysis| Power BI Configuration. I believe you still need one power BI Pro licence but it can be a school account rather than an individual one. I *think* that upgrading an A3 Office licence to A5 will give you PBI and that it costs an extra £4 a month but best to check that with Bill.

 

As for what you can do...it's pretty cool. Way better than any native Analysis tools I've used (in Bromcom or elsewhere). We don't have any free BI resources to share yet I'm afraid, but if you have any specific queries tag me into a post here and I'll be glad to help if I can.

 

Have fun

 

Ed

[themightymrp]

Thanks @RootsSchImp - that sounds just like what I am aiming for!! I will look into that OData bit as you say. Anything useful would be great to post on here

[RootsSchImp]

OK great - and likewise, I'd love to hear what you do with it.

 

I double checked and you do need a pro licence to share content, but only one per school and for an educational/non profit org it's only $3 a month

https://www.microsoft.com/en-us/nonprofits/power-bi so even in these tight times, probably doable.

 

I also found the learning resources on Microsoft's site to be really good https://docs.microsoft.com/en-us/power-bi/guided-learning/

 

Have fun!

[Bromcom_John]

Thanks @RootsSchImp - that sounds just like what I am aiming for!! I will look into that OData bit as you say. Anything useful would be great to post on here

 

Hi @themightymrp

If you need any additional tech assist, such as with data structure advice and certainly if there are data items that you need but cannot find then get in touch.

Usual routes, we'll be here over the entire summer break.

 

If you're feeling particularly ambitious it should be noted that the BI content you can create is NOT limited to data from the MIS.

So if you have access to multiple feeds from other sources (oData, SQL or even Google Sheets) you can embed that data as well and create some really impressive content.

My particular favourite visualisation is the map. Visually showing admissions data etc. bound to be a nice to have for someone.

 

One item from earlier in the thread that I noted. You were looking to look at attendance for this year and compare it to last year.

We recently added a selection of insight widgets to the user dashboard. The "Attendance by Year group" one has the year to date attendance for each year group and an indicator of last years attendance for the same cohort.

Edited August 2, 2018 by Bromcom_John

[Bromcom-PR]

So says you I don't have a clue where to start LOL

 

Power BI is all about Data Visualisation. It will rely on you to choose right data domain and comparisons to have meaningful and useful output.

 

Do you have a mock up of what you want to produce? Share here then we can comment if we already have the same or similar then you can take it and adapt it. Just to be clear, this assumes you have licensed Bromcom Analytics (ARB/BI) module.

Edited August 3, 2018 by vikpaw

[deshills]

So... gaining access to live data from Bromcom is now a feature which I've been playing on and off with for a couple of months. Microsoft are now providing PowerBI for free through the Office 365 licenses too.

 

Does anybody have anything set up between Bromcom and PowerBI? If so, what are you using it for? Can you share a screenshot or anything useful which I can use to gain some idea of what could be possible? Our school Business Manager has been talking about his 'dream' for a while of having a live dashboard available with all the relevant data showing without having to manually run a ton of reports all the time. This seems like it could potentially head me in that direction.

 

Am I right?

 

Cheers

Hi themightymrp

 

We've (Harris Federation) have just started getting into developing this now and happy to share with you some of the stuff we are doing, and are looking to do over the next few weeks and months. (Which will include integrations into Finance, HR, online National stats etc)

 

I think bromcom would agree that the demand for this, and much of the requirements (the oData feed e.g.) came from us, as we felt having a tool like Power BI (or it could have been Tableau i guess) integrated with our MIS was the next step for us in how we analyse data across the MAT. (and not linked to a limited reporting system as previously)

 

I see this tool being very useful for MATs as it gives quick access to multiple schools and the dashboards you can create in BI are very good.

 

At school level, i think it will have value, but we need to be careful to make sure that we are using data effectively, and not just for the sake of it.

 

I read somewhere on here about content packs, if that is something that people want to look at collectively, i am happy to help with that where possible.

Edited August 3, 2018 by deshills

[bringmoredata]

Hi,

 

Good to see this conversation... Just to throw in my two cents:

 

- Power BI is definitely gaining traction as the dataviz tool of choice in education. Kudos to Bromcom (with LOTS of input from deshills and the Harris team) for their pioneering work on this front. Oasis were also early to the Power BI party, so props to them too. And Matt40k has been (rightly) advocating for Power BI for years now...

- Groupcall are now offering a MAT analytics product powered by Power BI, which might be of interest to non-Bromcom MATs... and Assembly (my day job) are also experimenting with Power BI for MATs with more custom needs than we can serve with our core MAT analytics tool.

- My main anxiety with Power BI (or in fairness any BI tool) in MATs is that I've yet to see an implementation that requires zero customer maintenance - and maintaining dashboards can quickly snowball from a small task (fun project for a Friday night!) to a big task (employing one or more full-time Power BI people). E.G. Assembly Analytics (our main analytics product) contains lots of benchmarks built from public data, and we update them behind the scenes whenever required... but it's much harder to do something similar with Power BI. Not impossible... but harder.

- A big and important question is also whether you want to power dashboards from an API (like Bromcom do with OData) or a data warehouse (like Groupcall do). Both have strengths and weaknesses - and we're experimenting with Power BI attached to both at Assembly. Short answer: it's great to do as much as possible via an API, but at some point it may become necessary to have an underlying dataset that does pre-aggregations / historization etc.

- I also think it's worth avoiding the temptation to use every visualisation the tool offers - particularly on the same dashboard! You can do a lot with bar charts, which are easy to interpret.... But I'm veering in to subjective stuff so I'll shush now.

 

Hope that's helpful context :-)

[bigdannyb79]

I think the key point for me here is, no disrespect, but if you're asking why you should be using it you aint the right person to be looking at it ! I'd say most data leads in a school could see the value in real time info.

 

The battle with all data systems is ensuring you get value from the data you collect and that normally requires an analysis to be produced to then inform future actions. This takes time so where you can have dash boards showing for example attendance hotspots or behavior being able to view them in real time allows that intervention to be more immediate and potentially effective. And with power BI and similar you can present it in a pretty visual format that can be processed and understood by the masses and then also sliced and diced when needed. It allows the analysis piece to become a interactive beast that can support multiple audiences with very little maintenance to update the data.

 

Its by no means a quick pass to success but if you have the people, with the right skills, and the understanding to recognise where it can be applied to enhance your context then it is a big step forward to those of us working with data etc.

 

From struggling with Progresso in its infancy to now working with SIMS I'd take the former any day of the week as it gave you the power to use your data as you saw fit. So its great to see Bromcom and others recognising the power of your MIS is primarily what you can get out of the system not with how familiar your workforce is with putting information in . . .

 

Its definitively the way we need to go but in these relatively early stages in schools the issue is having the staff with the expertise to produce the outputs that don't necessarily already exist as you might require them. I dont like to see NM's dipping into writing reports as its 'complex' - you have an important job to do and I think leading the data systems and functions in a school is another one entirely.

 

But glad 'most' of the MIS market is recognising fully featured access to your data is fundamental to what an MIS should provide.

Edited August 9, 2018 by bigdannyb79

[matt40k]

Your right about asking what the system can do, isn't right, but there is a element of inspiration required - especially when you've been suck in a rut for so long not being able\allowed to do anything and suddenly, anything possible.

 

real time allows that intervention to be more immediate

 

Careful - BI is about looking at the cogs turns and how you can improve it. Rather then turning the cogs. Basically don't get fooled by this myth that PowerBI (or any BI tool) is a magical unicorn that will solve all you're problems. BI is slow.

 

Example, you create a pretty dashboard about missing students, you then do what? Export it then work through the list? So the data has to be pulled from your MIS, into PowerBI, then exported into Excel - best will in the world, you're looking at 5min of lag. A better solution would be accept this is a MI report and do it in the MIS system and ditch the wallpaper. Better yet, get the MIS system to trigger a phone call (again, this isn't BI)

 

Think we are all agree PowerBI is awesome and so its the fact the data is getting more and more open to customers!

[deshills]

Your right about asking what the system can do, isn't right, but there is a element of inspiration required - especially when you've been suck in a rut for so long not being able\allowed to do anything and suddenly, anything possible.

Careful - BI is about looking at the cogs turns and how you can improve it. Rather then turning the cogs. Basically don't get fooled by this myth that PowerBI (or any BI tool) is a magical unicorn that will solve all you're problems. BI is slow.

Example, you create a pretty dashboard about missing students, you then do what? Export it then work through the list? So the data has to be pulled from your MIS, into PowerBI, then exported into Excel - best will in the world, you're looking at 5min of lag. A better solution would be accept this is a MI report and do it in the MIS system and ditch the wallpaper. Better yet, get the MIS system to trigger a phone call (again, this isn't BI)

Think we are all agree PowerBI is awesome and so its the fact the data is getting more and more open to customers!

 

Really interesting post with some great points.

I personally see BI as being more of an overview tool for senior leaders, but would also potentially be used as a tool for class teachers to use as part of their assessment analysis. How this will work out, i guess only time will tell but we're working on it!

With GDPR, we've become a lot more conscious about having lots of sensitive data being moved around un-monitored in Excel. I think BI (be that Power BI/Tableau) could be a possible 'solution' to this. I also like the fact that it takes alot of the difficulty out of the calculations, so you can be a little bit more reassured that the data and the filters work (depending on your Power BI skills i guess!)

 

Your point on time lag is spot on, and this is something we have to be conscious, because BI data isn't typically live, ours is on a 60 minute refresh, so if you are using for assessments, i dont think that is an issue, if you are using for daily monitoring of attendance/behaviour, then there is potential for this to be an issue, and you're absolutely right, MIS providers need to make sure that access to this data (actionable data and not sheets of static numbers!) needs to contained within the MIS itself.

 

So in my opinion, for assessment analysis, KPI/contextual data for senior teams and looking at aggregated data for MATs its potentially a winner, for day to day monitoring of students, i agree with you this is for the MIS!

[RootsSchImp]

I think the key point for me here is, no disrespect, but if you're asking why you should be using it you aint the right person to be looking at it !

 

I read @mightymrp 's original post as asking 'is the output I can get out of BI worth the time investment required to learn it' and I think that's a really reasonable question.

 

I also think @JoshAssembly made an excellent point about the potential for the maintenance of BI to snowball until you're basically in charge of a parallel, homemade MIS. That's the aspect that worries me most about building useful resources for schools in BI, for dashboards to be viable for school users there has to be a minimum of backend faff. I hope we can mitigate against the downside risks of that by continuing to engage on Edugeek so that new adopters can get help and support and by collaborating elsewhere as well. If people adopt, as far as is possible, common approaches and nomenclature in BI then it will be easier to share work and ideas and to pick up and run BI resources made by someone else.

 

I've just set a blog that I'm going to use to publish advice on BI in education, if anyone else wants to guest post about what they're doing (or link to their own blog) please get in touch. I'm doing this in a personal capacity and all advice/resources will be free so I hope the mods won't mind me publishing the address: Welcome to PowerBIforSchools

 

Cheers

 

Ed

[themightymrp]

@RootSchImp has hit the nail on the head for me. At this stage I don't know my way around BI and I don't really know what I can do with it. But I'm willing to dive in and have a go. Ultimately this won't fall under my remit, it will go to the data team to handle. But if I can show some proof of concepts on what is possible then I'm sure that the right people will start asking the right questions. Months ago I was asked for what I thought would be a pipe dream - a large screen dashboard showing the key data for the school, live (or as near as damn it). Power BI with Bromcom seems to make this a possibility and I'll invest the time if it is as good as people on here seem to be saying.

 

Cheers

[AsifMangera]

We work with Bromcom but our vision for BI is not technology led but outcome led. BI is a powerful tool but in our opinion, we consider the reporting framework the most important. Power BI just happens to complement the framework very nicely.

With anything to do with Data - we are using a framework, it's the ITIL Knowledge Management approach - data to information to knowledge and application of wisdom to make decisions.

So the taxonomy for reporting we are considering (just an example) -

Validation - Validation against a particular strategy (e.g strategy for all schools in MAT to obtain 90% plus attendance). Has this been met after the initiative was introduced?

Direction - Using baseline data to inform decision making and potentially influence a particular course of action (currently 30% of schools within MAT have less than 90% attendance - we need to do something)

Intervention - Corrective and preventative action (Weekly reports on attendance and comparing against actions/initiatives and then allowing further corrective action)

Applying a nested model (Meta - Aggregated MAT, Macro - Aggregated Cluster and Micro- Individual School)

In terms of cost, I would debate that if you get your reporting suite right, it should make life a lot easier and potentially save money due to past inefficient processes/lack of timely information.

The beauty of having a single data warehouse and reporting suite is the accuracy and consistency of data which almost certainly helps with good decision making.

Edited August 13, 2018 by AsifMangera

[themightymrp]

OK, I'm actually making some headway on getting useful data into this. I'm building a dashboard based on attendance percentages for various categories. A smoother and quicker method of some of the reports I've built in Bromcom. Does anybody know how I could show the percentage of students in a year group that are persistent absentees (<90% attendance)? Preferably that will work with the time slicer I've got set up.

 

So far I have one chart showing a simple attendance to date based on Years 7 -11 and a second chart showing the same data but only for pupils who are on pupil premium

[deshills]

OK, I'm actually making some headway on getting useful data into this. I'm building a dashboard based on attendance percentages for various categories. A smoother and quicker method of some of the reports I've built in Bromcom. Does anybody know how I could show the percentage of students in a year group that are persistent absentees (<90% attendance)? Preferably that will work with the time slicer I've got set up.

 

So far I have one chart showing a simple attendance to date based on Years 7 -11 and a second chart showing the same data but only for pupils who are on pupil premium

Me and @RootsSchImp working on this at the minute, once we have perfected, happy to share with you?

[themightymrp]

Much appreciated! I tried using the formulas from @RootsSchImp 's blog posted above but the data it spewed out was weird. The attendance % parts seem to look right though, or are pretty close to the mark

[RootsSchImp]

Hi @themightymrp,

 

The formulas in the post are for working out your school's persistent absence figures - so if you have the measure in a table of your students it will return either 100% or nothing (as then it works out the percentage of that student which is a persistent absentee).

 

There's a couple of things to check if you're getting unexpected results in another way:

 

1, Manage Relationships - you need a relationship between the Students and StudentAttendances tables

2, Filters and slicers - can affect your results, even if you put them there for another purpose.

 

If you post a bit more detail about what's happening, I should be able to tell you what's going on.

Edited August 14, 2018 by RootsSchImp

[themightymrp]

It's OK, I realised I'd done something wrong, the numbers are now pulling through. I need to double check the values it is showing against what Bromcom pulls out using the reports I made previously to make sure they tally up.

 

I'm currently trying to make a graph/chart which shows the percentage of persistently absent students who are also on Pupil Premium, and then split this by year group (only for years 7 - 11). I'm mostly there, apart from I have student numbers rather than percentages showing. When I change the data value to calculate as a percentage of the total, it treats each percentage as of the whole amount of PA students, whereas I'd like to show it as a percentage per year group. Current graph attached. Please note, some adjustments have been made to not show our true figures)

 

 

Currently you can see it shows a total number of pupils in each section. For year 7 I would prefer if the figures were percentage just for year 7, so 56% and 44% respectively. Any ideas how I can manipulate it that way?