Student Data Sync is the starting point to creating a correlation or framework that connects your students, teachers and classes together in a meaningful way, allowing you to leverage cloud based tools more efficiently.
However, often the challenge lies in the format of the exported data from the school’s SMS. This is where Grant Saul, the Director of ICT from Westlake Boys High School has stepped in and provided a fantastic tutorial on how to use Power Query, a tool that comes in Excel 2016, to tidy up the format of your source data and prepare it for import with Student Data Sync.
In Grant’s example, he takes a standard export from Kamar (a very popular New Zealand SMS) and shows how it can be transformed using Power Query into the correct format for importing into SDS. You can read his original post here (and I encourage you to do so) whilst watching his screencast below:
The great feature of Power Query is it records each step in the data transformation, allowing you to easily replicate / replay the changes when the source data is refreshed, creating a super efficient method of managing your data.
For schools that want to use Microsoft SDS this is a very helpful guide.
Last week, Microsoft announced some major changes to the licensing of PowerBI which will come into effect on June 1st 2017 with some potential ramifications for schools that are currently exploring the functionality in the free version. If you’re unfamiliar with the product in general, then PowerBI.com is the best place to start for an overview, and the key changes from the announcement on 3rd of May can be found on this blog post from PowerBI.com.
My take on these changes is as follows:
PowerBI Free = still available, you can download the free PowerBI Desktop app here, and you can still publish reports/dashboards to PowerBI.com in the cloud but you can no longer share these with other users in the free version (this will require a Pro license – see below). Perhaps a better way of looking at this would be “PowerBI Personal” – i.e. for your own data explorations and visualizations in situations where you have no intention to share or collaborate with others. The update is there is some increased functionality around frequency of data refresh rates using the Data Gateway, along with increased volumes of data.
Data from a dataset that connects to on-premises data using the Power BI Gateway – Personal or the On-premises Data Gateway, and for which a scheduled refresh is set.
A dashboard or report that’s installed from an app or an organizational content pack.
Export to CSV/Excel
Peer to Peer dashboard sharing
PowerBI Premium = The newest feature, all details can be seen here, this is intended for large scale deployment of dashboards and reports across your organisation, including the ability to share content with users who are not necessarily licensed in the traditional way as an individual user. It has a higher financial entry point, with a fixed monthly cost and I don’t see much application at a K-12 level (outside of a school investing in sharing PowerBI reporting with parents too). However this could be a game changer at HighEd / Tertiary institutions that are wanting to enable their entire staff and students to make better data-driven decision making.
Importantly, it appears that the embedding of dashboards into web apps and web pages is now a feature reserved for Premium usage, so this will have consequences for third party developers / ISVs wanting to use PowerBI as the reporting engine in their software.
Example K-12 Education dashboard made in PowerBI
It is going to take a bit for for me to fully understand how these changes will ultimately affect schools. For those that have invested in PowerBI Pro licenses for staff/students, not much will change I suspect and it will be business as usual. For those schools that have been experimenting with PowerBI and the free licenses I think the major implications are likely to be:
No ability to share content that has been refreshed automatically using the Personal Data Gateway, or has come from any sort of database / web source.
Restricted to sharing content that has been manually populated into PowerBI.com from limited data sources such as either PowerBI Desktop, Excel or CSV.
Given most schools want to move towards a “set and forget” approach when it comes to data configuration, it would appear that using PowerBI Pro licenses is the way forward for most schools.
For an external view of these changes have a read of this interesting summary from Matt Allington in Australia where he highlights five different user scenarios and identifies how the changes benefit them. He picks up on the value add for large organisations and the ability to deploy easily across users, splitting users between content creators (who will still need a PowerBI Pro license) and consumers (who will be covered by organisational PowerBI Premium consumption licenses).
One of the biggest challenges to using any data analytics tool is keeping the data fresh and up to date with the minimum of effort. When you’re getting serious, you usually opt for the ETL process and data warehousing of some sort, but for the smaller users who do not have access to high level technical skills, what are the best options to use?
This product has undergone a few name changes over the last year or so (Personal Gateway, Enterprise Gateway etc) but now it is nice and simple and consolidated into one tool which you can use on your own laptop/desktop or install on your server to keep your data synchronised with PowerBi:
With the on-premises gateways, you can keep your data fresh by connecting to your on-premises data sources without the need to move the data. Query large datasets and benefit from your existing investments. The gateways provide the flexibility you need to meet individual needs, and the needs of your organization.
Why is this a game changer for schools? Well many schools will be storing their data in simple Excel or CSV documents, perhaps even an Access database and will be making regular changes and updates to these files. In other products, users would need to re-import the modified file back into the data tool analysis tool to refresh the data set and visualise the changes.
Here is a tutorial showing you how you can set this up in 5 minutes:
A couple of things to note:
You need to have installed the PowerBI Data Gateway first and that it has permissions to go through any firewall restrictions you may have in place.
Take care where you store your original data source file (the Excel spreadsheet in this case). Once you have published the report to PowerBI.com from PowerBI Desktop, I’ve not found a way of changing the path to the source file for the scheduled refresh through the Data Gateway.
The default time for the data refresh is midnight so you may want to add another time/times for this refresh to take place (PowerBI free can only be refreshed daily, PowerBI Pro is hourly – see here for more info). This can be easily done as below for 9am:
Scheduling a different time for a refresh
Email saying a data refresh failed
This is an awesome way to keep things simple for schools and assists them in avoiding the dreaded manual reloading of data.