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.

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

 

Digital Inking – Improves Teaching & Learning

Inking.PNG

I have visited a number of schools recently and demonstrated many of the natural hand gestures for editing and Ink Replay available in Office365 and the response is always very positive from teachers and students alike.

I have recently found the above infographic showing independent research from Sharon Oviatt, an expert in human centered and multi-modal interfaces and use of pen inputs on computers. I find that the numbers in the infographic resonate with teachers that I’ve been working with who are using Digital Inking to prepare student work, provide feedback and mark assessment.

When I was still at St Andrew’s College I recorded an interview with the Head of English and she explained how she marks English assignments using her Surface Pro 3 and digital ink:

DigitalPenUsageThere are many and varied compelling reasons to try Digital Inking and with a wider range of devices now supporting this, from entry level OEM offerings through to the newly announced Surface Laptop,  there is bound to be a device that meets your budget and requirements.

If you are interested in further research and information from Sharon Oviatt on the “power of the pen” then I encourage you to check out this blog from the Microsoft In Education team where it goes into more depth about the impact of computer interfaces on learning.

You can read the full blog post here.

Student Note Taking: Typing or Hand Writing?

I’ve written a few posts lately about the power of Digital Inking and ways to “Think in Ink”  and I am more convinced than ever that equipping students with devices that allow them to doodle, draw, annotate and just straight hand write notes and ideas is a necessity.

I know there is also the following quote from Microsoft promotional material:

Studies indicate that diagraming thinking before solving a science problem leads to a 25%-36% higher score

A quick search on the web reveals people doubt this but a link to the research findings can be found here. Tonight I read an article on LinkedIn titled:

What is a more effective way of taking notes – laptop or notepad?

As usual, I encourage you to read the entire thing yourself, it’s only a 3 minute read after all, and it talks about research completed in 2014 showing that students that hand wrote notes had far better conceptual comprehension of the content they had been taught compared to students who typed their notes up. Even when students who had typed their notes were given ten minutes to revise them before a test a week later, they still scored worse than those that had handwritten their notes in some way.

I’ve seen similar tests completed elsewhere so this finding is not new to me, however it certainly highlights the value of digital inking and usefulness of the basic “ruled lines” in OneNote:

OneNote.PNG

I find it interesting that when testing for factual recall, students scored the same whether they handwrote their notes or typed them, but only when testing for conceptual understanding did the differences emerge:

Typing-vs-paper.jpg

Again, do take time to read the original article and ponder how you record information in your daily life and if you’re a decision maker in a school, what devices are you recommending to your students to bring along.

Big Data & Social Profiling

Big-Data.pngI read an interesting article tonight called The Data That Turned the World Upside Down and it explores the idea that perhaps the recent 2016 US Election was influenced by political campaigns being able to profile undecided voters based on their online digital footprint, and then target them with highly personalized adverts.

It’s worth taking some time to read the entire article and learn how through a combination of Facebook users completing a free online personality quiz, combined with their Facebook activity (primarily what they “liked”), researchers were able to establish highly accurate models for predicting certain things about users.

The strength of their modeling was illustrated by how well it could predict a subject’s answers … before long, [researchers were] able to evaluate a person better than the average work colleague, merely on the basis of ten Facebook “likes.” Seventy “likes” were enough to outdo what a person’s friends knew, 150 what their parents knew, and 300 “likes” what their partner knew.

Another fascinating quote from the article showed that the researchers were able to:

prove that on the basis of an average of 68 Facebook “likes” by a user, it was possible to predict their skin color (with 95 percent accuracy), their sexual orientation (88 percent accuracy), and their affiliation to the Democratic or Republican party (85 percent). But it didn’t stop there. Intelligence, religious affiliation, as well as alcohol, cigarette and drug use, could all be determined. From the data it was even possible to deduce whether someone’s parents were divorced.

1984.jpgIt’s a timely conversation because last Friday I was chatting with colleagues from the former school I worked in and one of them mentioned that students these days have no sense of privacy or concern about the volume of data that is known about them by large companies and/or the government. One of the teachers I was speaking to compared it with George Orwell’s famous novel, Nineteen Eighty Four which if you’re unfamiliar with this the Wikipedia overview describes it as:

a dystopian novel published in 1949 by English author George Orwell. The novel is set in Airstrip One (formerly known as Great Britain), a province of the superstate Oceania in a world of perpetual war, omnipresent government surveillance, and public manipulation.… The superstate is under the control of the privileged elite of the Inner Party, a party and government that persecutes individualism and independent thinking as “thoughtcrime”, which is enforced by the “Thought Police”. (Underlines are my emphasis)

The contrast the teacher made, however, was rather than the government obtaining all of this information through huge surveillance and intrusion into the private lives of citizens, nowadays people are freely giving up this data to multi-nationals who are on-selling this to research firms who are then finding correlations. Again, from the original article linked above, the research firms explained how they obtained the data to support their programmes:

buys personal data from a range of different sources, like land registries, automotive data, shopping data, bonus cards, club memberships, what magazines you read, what churches you attend … in the US, almost all personal data is for sale. For example, if you want to know where Jewish women live, you can simply buy this information, phone numbers included [They then] aggregates this data with the electoral rolls of the Republican party and online data and calculates a Big Five personality profile. Digital footprints suddenly become real people with fears, needs, interests, and residential addresses.

(Emphasis applied is mine)

Now much of this is not new to me – I attended a great session at the AIS NSW ICT Managers Conference in Canberra last year where a Facebook advertising expert demonstrated in real time just how easy it is to create targeted Facebook adverts to virtually any demographic. However when one thinks about this level of data science being used to shape electioneering and potentially sway the final outcomes of elections it should lead to pause for thought.

Like the teacher I was talking to pointed out – we all give up data about ourselves to some extent, the question is really do we consider the true cost of this action?

Azure Media Analytics – Search EVERYTHING In Your Videos

Azure Media Analytics

Last week I was in Singapore at the Microsoft APAC Education Partner Summit where over 140 partners attended two days of sessions. One that really stood out for me was a session on Azure Media Analytics.

The idea behind this service is to deliver deeper insights into the media content of an organisation, far beyond the simple number of plays on a video. The best platform for viewing this concept is below:

https://www.videobreakdown.com/

The demo was impressive and highlighted a range of really interesting use case scenarios on how Azure Media Analytics might be leveraged by organisations. As per the graphic above there is a lot of individual features and some of the ones that stood out to me included:

  • Facial Identification/Recognition – tagging of names to faces
  • Transcription – automatic transcription of content either live or post-recording
  • Translation – automatic translation of the above transcripts
  • Visual Text Recognition – a really unique one that allows you to search for any text that appears on screen, whether a sub-title or PowerPoint deck being shown.
  • Indexing – all of the above is searchable from a global search box

From a real world usage perspective there is a range of very clever scenarios that could leverage this type of technology e.g.

  • An organisation tags important people e.g. their senior leadership / VIP guests. They could then search their video archives to find footage where two people are in the same video shot such as a Prime Minister coming to open a new building at the school and the Principal is in the video shot with the Prime Minister.
  • Recorded Lectures – students/lecturers could search recorded lecturers for anything e.g. key words either spoken or displayed on screen.
    • Because indexing is applied the above search results will play a few seconds before the keywords are spoken/displayed on screen.
  • Providing searchable transcripts for visually/aurally impaired users, increasing the accessibility of content to all users.
Azure Filters

The search capabilities available on the www.videobreakdown.com website

Another cool feature demonstration was Microsoft Hyperlapse a tool that was released last year that allows you to create smoothed accelerated time-lapse  videos. Designed to improve the viewing experience of “first person” videos filmed on devices like GoPro cameras that are often very shaky, the significant processing power of the cloud is used to improve these videos.

Video showing actual speed, 5x accelerated time-lapse and then 5x accelerated time-lapse with HyperLapse

There is a good summary from The Verge here, and the video below shows how Hyperlapse Pro can be used:

These all represent new ways to engage with rich visual media and maximise the value of it by making it searchable.

Consolidating Data Reporting With SQL2016 & PowerBI

UOT

This afternoon I read a really interesting case study from the University of Tennessee on how they transformed their business reporting and compliance through consolidating their data onto SQL2016 & PowerBI.

Read the full case study here.

It’s worth reading the entire article (about 5mins) as the University has five campuses and two institutes which previously had individual reporting and analytics tools. To resolve this, a three pointed focus was created around:

  1. Consolidated repository
  2. Report verification process
  3. Data access and stewardship process

As the University was already using Office365, the decision to use PowerBI and SQL2016 as the backbone of their new BI solution made a lot of sense. Some impressive gains were made from this digital transformation project:

  • Data verification time decreased from 45minutes to 10 seconds (a 99% reduction)
  • This allowed the organisation to do multiple validations a day, rather than waiting until the close of business to perform a single audit.
  • The University is a USD$1.2 billion dollar enterprise and yet despite their size they are able to support their BI with a team of just six staff.
UoT by Degree

Click the above to view some sample reports of real data. Note that you can scroll through five different reports using the arrows at the bottom of the report (visible once you’ve clicked above).

For larger HigherEd / Tertiary institutions the above is likely a compelling story in data transformation and reporting.