[bromcom] Uses for Microsoft PowerBI?

[themightymrp]

Another quick question, what is the benefit of using the odata connection to Bromcom instead of using the "Live Data" link available from when you go to run a report in Bromcom? I'm thinking I have some reports already set up which might make life easier to use as an additional data source?

[RootsSchImp]

Hi @mightymrp,

 

I think I know what you're after - I'm not sure my solution is the most elegant, so I encourage others to weigh in with improvements, but there's some good teaching elements to my approach.

 

First up, we're going to create a new table, using DAX, via 'New Table' on the modelling tab. I called mine PersiAbs and it lists just the persistentabsent students.

 

The formula is similar to that of the measure: PersiAbs = filter(students,and(calculate(StudentAttendances[Attendance%])<.9,isnumber(CALCULATE(StudentAttendances[Attendance%]))))

 

Next you need to relate it to the Students table by dragging studentid onto studentid on the manage relationships page.

 

Finally you create a new measure to use for the value of the clustered bar chart:

 

PersistentAbs2 = countrows(PersiAbs)/CALCULATE(countrows(PersiAbs),ALLEXCEPT(students,Students[YearGroup]))

 

This works because the numerator - countrows(persiabs) - respects the filtering of the chart, ie in each section of the chart it counts persistent absentees in the appropriate year who are/aren't eligible for PP

 

Adding calculate to the denominator stops it respecting those sections. So if you had Calculate(countrows(persiabs)) it would divide by all persistent abs students in all years. We want it to respect the year filter, but not the PP filter - that's what ALLEXCEPT is doing.

 

 

I prefer OData as it's a one step process - MIS to BI, without having to also set up and maintain ad hoc reports. If your reports are already built - maybe less of an advantage but it's still easier to maintain a BI file with one source rather than two.

Edited August 14, 2018 by RootsSchImp

[themightymrp]

Looking good sir! It doesn't seem to respond to the time slicer though? Is that to be expected? I have tried putting the PersistentAbs2 measure under both the Students table and the PersiAbs table - both make the graph work but the values don't adjust with the slicer.

[RootsSchImp]

Simple remedy. ALLEXCEPT in the formula can be understood as 'all filters/row contexts will be ignored except...' so if you adjust the formula to:

 

PersistentAbs2 = countrows(PersiAbs)/CALCULATE(countrows(PersiAbs),ALLEXCEPT(students,S tudents[YearGroup]),ALLEXCEPT(studentattendances,studentattendances[startDate]))

 

Then it will respect the date slicer (assuming the slicer is on Start Date in the Student Attendances table.

[themightymrp]

Hmm that seems to have thrown it out and looks more like the data I was getting before? Under which table am I supposed to be putting this PersistentAbs2 formula? I'm putting it in Students..

[RootsSchImp]

Hi @mightymrp

 

Yes, sorry I did that quickly and didn't notice.

 

However, my original measure:

 

PersistentAbs2 = countrows(PersiAbs)/CALCULATE(countrows(PersiAbs),ALLEXCEPT(students,Students[YearGroup]))

 

is responding to my date slicer in the test file I've got. Are you sure that's not the case with you?

 

Are there any other filters/slicers that might be interfering?

[themightymrp]

Definitely not responding to the date slicer. Am I correct in putting that measure under the 'Students' table? Screenshot attached - I have the value set to calculate as a percentage of the grand total but that's the only change I've made

 

[RootsSchImp]

It shouldn't matter where the measures are.

 

Is the relationship between the attendances table and student bidirectional? I think for this to work we need attendance to filter students, so if the arrows on your relationship only point one way that could be your problem. Double click the relationship and set the direction to 'both'. (see mine below)

 

[themightymrp]

That was it Cheers!

[themightymrp]

So... Power BI so far... It isn't usually within my remit to produce this kind of data. Normally our admin/data team and the data manager would create stuff like this. But it has always relied on a lot of data exporting to Excel, manipulation and editing to be able to produce things graphically. We use Sisra for a lot of analytics as well.

 

As the network manager, it was of more interest to me that we were being provided access to Power BI through our Office 365 subscription. Yes we currently only have the free version, but once I can nail down what kind of data we can show with this, I will purchase the Pro license. I'm not exactly clear on what needs to be done to get data back into Bromcom though? That side is very vague to me but I gather is possible?

 

Months ago our head came to me with a request. Basically, with 7 or 8 key pieces of data to hand, you can run an effective school (he is also an Ofsted inspector). He wanted me to try and create reports or Analysis settings in Bromcom to pull this data out. I mostly got there, but the results weren't pretty (Excel table heavy) and was by no means quick to run. It was spread across multiple reports and getting the data correct was a nightmare. I can foresee that I will be able to produce his data much quicker and easier using the Power BI interface which should please him no end!

 

Our schools Business Manager was also asking me about the feasibility of a live data dashboard only a couple of months ago. I told him that it was a pie in the sky dream to be able to tap into so much data and present it in one large screen. Power BI now makes it a distinct possibility, especially with the advantage of linking to multiple data sources (I'm thinking possible links to Sage accounting or accounts spreadsheets) so that in one dashboard he can see all of his useful data.

 

I've started to provision certain key users with Power BI licenses and will be installing the software for them over the coming weeks. I will run some training with them and then they can see what they can produce to enhance how the school is run. I just want to razzle and dazzle people at the minute so am cracking away at teaching myself the product (the Microsoft guided learning site is actually pretty good!)

 

I am happy to share any methods I come up with to show data in certain ways. I am currently trying to create a method of showing what percentage of our persistently absent students have SEND codes of K or E. I think I'm on the right lines by adding a new column in BI which uses an IF statement on the Provision column to pick out the two codes (well, descriptions) and combine it into a true/false (1/0) argument. I should then be able to use this as a filter on my charts, but I don't seem to have it quite right yet!!

 

I shall persevere... but if I somehow create a useful chart I'd be happy to share the methods. Props go to @RootsSchImp for the help so far

[matt40k]

Just a bit of info on Power BI - EDX (awesome free training), has a free Power BI training course -> https://www.edx.org/course/analyzing-visualizing-data-power-bi

 

It's very generic as it's for PowerBI, which I appreciate some folks will want a school focused course, but it's worth a look, as its free. It's especially good as it gives folk industry standard training that can be transferable outside of working in a school. It's a pretty amazing skill to have on your CV at the moment.

[RootsSchImp]

Hi Everyone,

 

A colleague asked me if I could produce this absence report for groups in BI

 

If anyone's interested my post on it is here: Absence Reporting

 

Give me a shout if you have any queries/suggestions for improvement

 

Ed

[themightymrp]

I like this, I may follow some of your methodology on the blog to generate some separate visuals as they roughly tie in with some of the requirements I've been asked for.