Using Power Query in Excel 2016 To Ready CSV Files for Student Data Sync (SDS)

Student Data Sync, or SDS, is a core tool from Microsoft that helps schools prepare their student, teacher and class data ready for use in great platforms such as Teams for Education (formerly Microsoft Classroom) and Intune for Education.

In countries outside of the USA (where API exist), schools need to prepare six CSV files containing the relevant information from their Student Management System (SMS). Fortunately, Microsoft has provided some sample scripts and files (along with a toolkit to verify your data integrity) to help.

SDS

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 powerquerystepped 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.

From The Garage: Presentation Translator

Presentation Translator

The annual Microsoft Build conference is running at the moment and amongst other announcements I saw this one from the Microsoft Garage called “Presentation Translator”

This looks to be a plugin for PowerPoint that will offer some pretty neat features and is described on the website as:

As you speak, the add-in allows you to display subtitles directly on your PowerPoint presentation in any one of more than 60 supported text languages.

Additionally, up to 100 audience members in the room can follow along the presentation in their own language, and on their own phone, tablet, or computer.

There is some obvious scenarios where this functionality will be awesome when you have a multi-lingual audience you’re presenting to, however I can see a lot of value for this within Education as well. One situation would be for students who prefer to be able to read content to deepen their understanding. Having real time transcription (and translation) will certainly be an amazing demonstration of technology that will add real value to students and teachers alike.

This is not an official release yet but you can sign up for the preview here.

 

PowerBI – Major Licensing Changes

powerbi-getting-startedPowerBI is a fantastic visualisation and reporting tool that I’ve written about extensively on this blog already, as well as having created numerous screencasts and blogs in an educational context from my time at St Andrew’s College.

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.
  • PowerBI Pro = Very similar to what was previously being delivered with this paid for license (academic pricing is generally available in most countries) but it appears this is increasingly becoming the “default” license if you’re wanting to share or collaborate on reports, or have your data automatically refreshing using the Personal Data Gateways from a wider range of data sources. The best link to understand what constitutes content requiring a PowerBI Pro license can be found here and I would encourage you to check this out. From my quick scan, the features which have moved out of the “free” and into the “Pro” license appear to be:
    • 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.
PowerBI Dashboard.png

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).

 

End Of Support for Dir Sync & Azure AD Sync Approaching

DirSync & Azure AD Sync will reach end of Support on April 13, 2017.

Azure AD will stop accepting connections from DirSync and Azure AD Sync after December 31, 2017. For more information about the DirSync and AAD Sync upgrade, please see the DirSync and Azure AD Sync deprecation documentation. If you have questions or feedback about this change, you can leave the team a comment on the blog linked below or reach on Twitter using the #AzureAD hashtag.

https://blogs.technet.microsoft.com/enterprisemobility/2017/04/10/end-of-support-for-dirsync-and-azure-ad-sync-is-rapidly-approaching-time-to-upgrade-to-aad-connect/

So now, it’s time to get cracking and move to Azure AD Connect if you’ve not done so already:

AD Connect.png

Azure AD Connect – keeping your identity synchronized in the cloud.

Free Azure Cloud Migration Assessment Tool

As more organisations, including schools and tertiary institutes, explore cloud migrations from on-premise servers, the number one question is always “how much will it cost me and how much can I save?”

Recently, Microsoft have released a free cloud migration assessment tool that you can see here:

Click here to start the free cloud migration assessment

There are three ways you can import data into this assessment tool:”

  1. Manual Import – essentially entering the RAM/CPU/OS specs of your physical/virtual servers one at a time into the online tool
  2. Bulk custom import – download an Excel template to manually update the specs and then import into the online tool
  3. Automated discovery and import – this utilises the free Microsoft Assessment and Planning (MAP) Toolkit. This can be configured to scan through all/part of your server network to automatically identify the specifications being used and then generate an import file for the online tool.

You can watch a detailed instruction on how to use these three methods in the YouTube clip below:

cloud migration toolkit

Click the above image to watch the YouTube clip

What I really like about this tool is that you get a report with calculated costs for Azure hosting compared to your on-premise infrastructure costs:

Azure Report.PNG

An example of the predicted cost savings using Azure compared to on-premise hosting.

This is, of course, based off assumptions in terms of costs to maintain on-premise services and this is where the tool is very flexible. If you have already done a TCO or ROI exercise and know the costs of on premise services you can manually update these costs in the assumptions calculator to truly match your known costs:

Azure Assumptions

This tool allows great flexibility in terms of entering your true/known costs into the assumptions tab to get accurate comparisons

Tools like this can give organisations certainty in terms of predicted costs for moving their infrastructure into the Azure cloud. I’d love to hear first hand from educational institutes out there that have used this tool – feel free to post a comment below if you have.