Power BI Licensing and Permissions

Building visuals and dashboards is only part of what you need to do before BI can drive any improvement of your school. Once it’s done you need to get those dashboards in front of the people who will turn your data into action. This post explores the various options for publishing and sharing, working from the lowest cost/most restrictive option to more feature rich but expensive paths.

Power BI for Free

Power BI dashboards are designed using a windows program (Power BI Desktop) then published to a web server which is where you would expect users to interact with them. PBI Desktop is free to download and creates files with the extension pbix. It’s also free to create an individual Power BI account so any user can publish to the secure BI site app.powerbi.com – what they can’t do for free is share dashboards securely (more on that below).

If you want a totally secure BI ecosystem without paying anything to Microsoft then you can do that by sharing PBI files but bear in mind this solution is in no way free – the cost is the time and hassle it will cause your users.

There are two file types in Power BI Desktop. The default is a pbix which saves the design of the report and the model AND all of the data. That means that having pbix files lying around on laptops or sharepoint is as bad from a GDPR point of view as exporting a bunch of CSVs from the MIS and leaving them somewhere. Typically they’ll be too big to email but that would also, obviously, be a big no-no.

Luckily the second file type avoids any GDPR issues and is much more portable – pbit or Power BI Template. A pbit file has the design of the report and model but not the data; when opened they automatically request the data from its sources but the user must provide credentials to access it. Pbits are relatively small too – usually under 1MB so they’re fine to email.

To get a pbit file go to File, Export and choose Power BI Template, then choose a file location.

OK, so lets say you have one PBI designer making reports for a five member SLT and ten Heads of Department. The designer makes the report and exports it as a pbit. The pbit contains no personal data so it can be bulk emailed to all 15 members of the team. Here’s where the fun bit starts. Microsoft have been releasing new versions of PBID every month and it doesn’t update itself automatically, so if the users have outdated versions the file may not work as intended/at all. Assuming everyone has up to date software, when they open the file they will need to sign in to get access to the data (hence know not just their usernames and passwords but also in some cases authentication types).

PBID remembers credentials, so the second time (assuming nothing changes) they won’t have to reenter credentials but will still need to download all the data.

Then the file has to download all the data. Depending on the size of the model/quality of connection this could take as long as 20 minutes. Once the data has been downloaded they need to save the file as a pbix, which can then be published to their BI account. Once this is done they need to go to app.powerbi.com to actually look at the dashboard. Crucially they need to then remember to delete the pbix which is a GDPR violation on their laptop/PC/wherever.

You could skip the publishing step and carry out investigations using PBID but the user experience is not great as elements can be accidentally moved, buttons have to be ctrl + clicked to work and, the biggest drawback, inexperienced users can change/break things like they can on an unprotected spreadsheet.

I’ve been a school data manager and I…don’t think this approach could ever work. If your team are very data positive and tech savvy then maybe, but I think it’s likely to be a false economy when you factor in the cost of all that waiting time plus support for when people have problems.

Managed Free Accounts

An alternative approach that also doesn’t require any payment to Microsoft is to still have your team of 15 using individual free accounts but to manage them centrally. In this model the same pbix file gets uploaded to each account separately by a Data Team. Bear in mind though, that there’s no way as far as I’m aware to automate this – they would need to log into each account separately and publish from desktop any time any changes were made. 

They would also need to keep track of these uploads as if they were interrupted halfway through you could end up with users looking at different versions of the same report. Bear in mind also that passwords for free BI accounts expire so both Data Team/user will need to notify the other when that happens and a new password is chosen.

Here the time cost is shifted to other people but there clearly is still a cost in terms of hassle and potential for errors/loss of access to the data.

Publish to Web

From a free BI account there is an option to publish a dashboard to the web. This creates an HTML embed code that lets you place the dashboard in a website, like this:

To do this you view the report in app.powerbi.com, click File then publish to web. That brings up the HTML code which you then have to paste into a website/blog that accepts HTML. (In Bromcom you can create ‘Custom Content’ via the Setup Module that put these into the MIS).

NOTE: this option publishes the dashboard to the web with no security. Even if you embed the code in a part of your website users must log in to see, it’s still being retrieved from the open internet, therefore no dashboard with personal student or staff data can be published in this way.

However, just because you can’t have personal data doesn’t mean this option is useless. There are many uses you can put BI to that don’t necessitate drilldown to the individual level. An audit of the curriculum, or even an analysis of behaviour incidents, as long as it didn’t identify which students were involved, would be fine to publish in this way. Although technically it’s available on the open internet, it’s not going to come up if someone googles ‘Academy Name Behaviour Dashboard’, so while you obviously can’t risk it with personal data, it’s fine for school level data you don’t necessarily want to share beyond certain staff members. Be careful though, of groups which because of their size can end up identifying individuals, if there is one Chinese student in a year, say.

Power BI Pro

The best way to manage multiple users is for those users to have Power BI Pro accounts. That allows you to share a single, centrally maintained, dashboard between them and to control what data they have access to within dashboards as well.

As with most best ways though, it’s also an expensive way. You get a Power BI Pro licence with the A5 Office 365 plan, which costs £7.05 per user, per month. Most schools have the A3 plan for staff which gives the desktop versions of the normal Office programs, that costs £2.85 a month, so the additional cost of a PBI Pro account is £4.20 a month or £50.40 per year (as of August 2019).

So for our team of 15 SLT + HODs + BI developer the total cost is 16 x £50.40 per year or £806.40. I’m going to write a separate post on weighing up the costs/benefits of BI so I won’t go into it now other than to say that for a secondary school I’d say that’s a cost big enough to warrant careful consideration but not so big to be out of the question for what BI can do.

Sharing Dashboards between Pro Accounts
Simpler administration is the big plus of sharing between pro users. After the BI designer publishes a report to app.powerbi.com they can click ‘Share’ in the top right of the screen. They then just enter the email addresses of everyone it’s to be shared with – these users can then access the report via their login to app.powerbi.com

Note that sharing is done via the app.powerbi.com website, not PBI Desktop. When sharing you have the option to control whether users you share with can share with others and whether they can build their own visuals using the underlying data.

Roles and Permissions

Another advantage of sharing between pro users is the ability to create roles and permissions to restrict what people can see in a shared report. You do this by creating roles, that have certain filters in PBI Desktop then assigning users to those roles in app.powerbi.com.

In PBI Desktop click ‘Manage Roles’ on the modelling tab. Create a role, choose a table then write a DAX expression that resolves to true/false to filter the table. In my example I’ve filtered Students[House] to “Jupiter” which means not only that the Students table will be filtered to members of that house, but also that only Attendance Records and Assessments of those students will be available because the tables containing those records are cross filtered by those tables.

If you are in a MAT and you have a table with your schools, if you apply a filter here to a particular school or schools, it will flow down to the students of that/those schools and the attendance/assessment of those students. Click ‘View as Role’ on the modeling tab to check your dashboard behaves as expected for these users.

Assigning Roles

You assign users to the roles you create in app.powerbi.com. The thing you are controlling access to is the Dataset, not the report, so from ‘My Workspace’ click on Datasets.

Click on the ellipsis to bring up the menu and select ‘Security’ (‘Manage Permissions’ is for changing the ability to share the report and build new ones off the dataset).

In the Security screen you can see the roles you created in PBID and assign users to them.

Pitfalls of Row Level Security

The big pitfall of RLS is it works too well. Let’s say on the front page of your dashboard you have a measure that shows the attendance of the whole MAT by looking directly at the AttendanceRecords table. If you apply a filter to a particular School within a MAT then AttendanceRecords is filtered to just records from that School and your user can’t see their results in the context of the whole MAT.

You can get round this by creating a ‘MATStats’ table using the ROW function. ROW creates a one row table with the arguments <name1>,<expression1>…. Your expression will be calculated without any filter context, so you’ll need to tell it exactly what to calculate, but it means you can have an overall attendance figure for the MAT, say, as a benchmark, which because MATStats isn’t related to any other table, won’t be filtered by the RLS.