[bromcom] Another PowerBI Sanity Check

[CyBeRkId2002]

I am currently creating some Attendance dashboards and am interested in doing some better analytics comparing students attendance history etc. I currently have columns comparing current attendance % against attendance % 7 Days ago and % 30 Days ago which works great most of the time, but as PowerBI has no knowledge of term dates/days it kind of falls apart following holidays etc. Also, I would love some weekly student charts to track individuals attendance histories (I am picturing an attendance graph not only for the current year, but possibly also comparing against the previous year).

 

I am thinking the best way to resolve this would be to create a date table in PowerBI. I would then add (and maintain) the following columns:

 

AcademicYear: 18-19

AcademicDay: 1, 2 etc.

AcademicWeek: 1, 2, 3 etc.

AcademicTerm: Spring, Autumn, Winter

 

Does this seem the most sensible way to go about it? Does anyone have any other suggestions that make more sense?

[bfelton]

That's a good way to go, you can then use the date table for behaviour as well.

[DODICT]

that'll work - but also worth mentioning to bromcom - there is a week structure put into Bromcom and if that was made available in BI it could avoid the need to duplicate and maintain this info. They recognise the value in power BI but I think its important they make it accessible as possible and maximise the value in having BI talking directly to the MIS. You're not getting that if your having to duplicate data structures in BI.

 

I note in vision they have more attendance summary datasets which do return aggregated data by week num but this falls down on a similar issue as its just an incremental so if you look at it by week you get gaps where looking over years. Its in Bromcom as terms and week number in that term - that would prob be the best way of naming it - you dont want to compare week 7 one year to week 1 of the next term one year due to differing lengths of terms. so comparing Aut 1 to Aut 1 would make sense. And that is there already in Bromcom just not available in the OData sources or the reporting engine to live link to that I can see.

[RootsSchImp]

Hi @CyBeRkId2002 we're working on all the info necessary to do curriculum analysis and that will include the academic year/term dates. Currently the spec is written up for the MAT Vision data feed but I'll ask the devs if we can add the same table to the MIS OData feed.

 

I had a look to see if a current MIS report could act as a stand in (you can feed Table Reports into BI using the Live Data Feed button). There isn't a perfect match, but if you use the domain student timetables you can pull out a list of dates when school is in session, which you'll still have to chop around a bit in BI, but it'd be easier than building up a calendar table from scratch.

 

Ed

[Redwinegirl]

Hi - noticing the reference to BI - I need to look at this for Bromcom analysis but my IT guy says it costs - about £120 a year? Is this the case as someone mentioned that schools should get this free? Keen to start playing with data more (sad I know :-) )

Thanks

[DODICT]

essentially no. bromcom can use a single free account to share any published reports to all users of your MIS. If you want to differentiate by user - i.e. different people see different reports then yes your need a pro to share reports to discrete audiences.

 

But certainly to get going you can use the free license in 365. We are looking at PRO accounts just to enable certain key staff access to the communication/chat features available in the PowerBI app to enable efficient flagging and discussion of any interesting points discovered in the analysis. We've produced a lot of valuable info so if anything our challenge now is enabling staff to recognise it and utilise it - this is a new problem !

 

Even then at edu pricing your looking at £3-4 per user per month but to get starting and playing there is no reason to be paying for a PRO account. We have all our attendance, exclusion, behaviour and contextual data shared to each school via a free BI account - so I'd suggest it will be a while before you getting to the point where your thinking I've got all these reports/dashboards and I really need to distribute them to different audiences.

Edited February 12, 2020 by DODICT

[Redwinegirl]

Thanks - my guy is still sayings it's not free - as we aren't on the A5 plan which is expensive.

Looking at the websites it looks like we can get it free so confused! He wants to wait until May when we renew our licence, but I could do with it now :-(

[bfelton]

You can download and use the offline version for free for now to get used to the system.

[RootsSchImp]

The way BI works is you make dashboards with a windows program called Power BI Desktop - free to download from here https://powerbi.microsoft.com/en-us/downloads/

 

Once you've built your dashboard you can publish it to a Power BI Free account - no payment details needed to register - sign up for free here https://powerbi.microsoft.com/en-us/landing/signin/

@DODICT is right that it's best to start free and you can even share dashboards published to free accounts in Bromcom by putting the microsoft account login details as 'shared account' in Analysis| Power BI Configuration in Bromcom.

 

If you start using BI with lots of different users, or you want to use permissions in BI so different people have access to different data then you need Pro accounts which cost money - but to get started there's no need to spend anything.

 

Check out my blog powerbiforschools.blogspot.com for tips on how to do ed related things in BI

 

Good luck

 

Ed

[Redwinegirl]

Thanks - I thought we could - we aren't able to download anything with IT approving and installing, so will keep pushing!

[RootsSchImp]

Power BI is worth it - it's really cool. If you need to call in the SLT cavalry and want to explain what BI is this post has a sample dashboard which gives a flavour of what it can do - barely scratches the surface tho, the great thing is you can customise it to show you what you want.

 

Subject - Class - Pupil Table

[Redwinegirl]

Thank you - have raised with SLT to see if they put some weight behind my request!

 

in fact email just come in and I can have access ! Yay!!!!

 

Thanks all

[loftush]

although we haven't gone down this route yet I believe you can use the license page to purchase licenses for products rather then buying the entire A5 package. just depends which works out cheaper. https://docs.microsoft.com/en-us/microsoft-365/admin/manage/assign-licenses-to-users?view=o365-worldwide

[samread]

Hoping somebody out there can help. Started using PowerBi with Bromcom last year and was then assuming that this year would be able to make comparisons to last year but when I refreshed the data last week I just got this year’s data and last year’s isn’t pulling through. Any help appreciated, thank you.

[Redwinegirl]

https://docs.bromcom.com/knowledge-base/how-to-create-a-dynamic-filter-in-power-bi/ is this any use?

(I'm stuck on incremental refresh at the minute...:-( )

[RootsSchImp]

Started using PowerBi with Bromcom last year and was then assuming that this year would be able to make comparisons to last year but when I refreshed the data last week I just got this year’s data and last year’s isn’t pulling through.

 

The big drawback of custom BI datafeeds like Bromcom's OData is that you get what you're given.

 

As a workaround you can:

- create an ad hoc report in Bromcom with the data you want from last year

- Use the live data feed option to get a weblink to bring the data into BI (in power query choose Web as the data source, paste in the link and authenticate type basic wiht your school's 5 digit id and your API key)

- Still in Power Query make sure this last year data query has the same column names as the OData one (case sensitive). Then select the orginal query and use 'Append' to add the new one to it.

 

That will mean your table in BI combines this years data (from OData) and last years (from the report).

 

It means wrestling with Bromcom's garbage reporting module but you should be able to do that once then forget about it.

[Redwinegirl]

Quick reports are proving more useful as you can show different time periods in the same report. Dont think I use the OData feed, just live feed from reports.

[park_bench]

Quick reports are proving more useful as you can show different time periods in the same report. Dont think I use the OData feed, just live feed from reports.

 

I'm really pleased to hear you are finding Quick Reports useful - we've worked hard on updating the Adhoc reporting engine in Bromcom and Quick Report is becoming the default report type, making the others redundant. Quick Report allows really easy cross-domain reporting and, as you have highlighted, you can have as many time intervals in the same report as you need. It has inbuilt calculations and Excel-like functions so you need to do less post-processing of your data, and it's sooo much quicker than traditional report types in Bromcom!

 

Hoping somebody out there can help. Started using PowerBi with Bromcom last year and was then assuming that this year would be able to make comparisons to last year but when I refreshed the data last week I just got this year’s data and last year’s isn’t pulling through. Any help appreciated, thank you.

 

If you are using OData from MAT Vision, you should have access to the last three academic years of data. What is it you would like to report on? Feel free to drop me a message if I can help.