Quick Measures and built-in calendars in Power BI

The April 2017 update to Power BI introduced a ground breaking feature: Quick Measures. This article discusses the Quick Measures that deal with time intelligence.

Currently there are five sections in Quick Measures Read more

Generating lists in Power BI from scratch

Learn how to generate lists in Power BI from scratch

Disconnected lists of items can be useful for various purposes. For example, one can be used as a parameter table.

In Power BI, you can generate lists from scratch with both M and DAX. We’ll start with generating a continuous list of integers.

Read more

Power BI Embedded Row Level Security available

An early Christmas present in my inbox was the announcement that Power BI Embedded Row Level Security was quietly completed in Dec. The documentation also popped up a few days ago. An early criticism of Power BI Embedded was that you couldn’t really use it in customer portals because it was not possible to filter the data shown to the customer. It was an all-or-nothing affair which rendered it unusable in any scenario where you needed to present different subsets of the data depending on who was viewing it.

How to enable Power BI Embedded Row Level Security

Part 1: Roles in the Power BI Model

This works in two parts – first in the Power BI model, the procedure for creating security is the same as for normal Power BI RLS implementation. Firstly create roles to define the groups of people you want to apply filters to. Then for each role create a filter on a target table using the USERNAME() function:

Power BI Embedded Row Level Security Roles

Power BI Embedded Row Level Security Roles

How you manage the application of the security is up to you – either having intermediary tables that indirectly filter data, or simple direct filters on data tables – depends on your actual requirements.

Part 2: Apptoken in the Web App

In an Enterprise scenario, the USERNAME() function equates to a Windows username. This obviously doesn’t apply in the Power BI Embedded world as your report consumer is outside your corporate network.

In this case the USERNAME() can be fed in via the apptoken that allows your web app to communicate with the Power BI service. An example (lifted from the Power BI blog) is below:

Power BI Embedded Row Level Security Apptoken

Power BI Embedded Row Level Security Apptoken

By modifying the code of your Web App you can dynamically set the username based on your Web App’s own login system. As long as that translates properly to the filters you want your role to apply in the model then you have now got Power BI Embedded Row Level Security!

For the full documentation see here.

 

 

 

SSRS Hosting of On-premise Power BI Reports – Update Dec 2016

What is the current state of on-premise hosting of Power BI reports?

During the PASS Summit in October Microsoft announced that they would be releasing a technical preview of on-premise hosting of Power BI reports through SSRS. The preview, which can be accessed through the Azure marketplace, allowed anyone to spin up a virtual machine that included an installed and configured version of SSRS with sample data/files so customers could start exploring the solution immediately. It is worth mentioning that the current preview is very raw and rough; for one it can only be accessed through the Azure Marketplace and hence cannot currently be installed on your own environment and second it only supports live connections to Analysis Services cubes.

PBIX-in-SSRS

What’s to come with the next release?

The next technical preview is planned for January 2017 and unlike the current preview, you will be able to download and install it on your own virtual machine or server. Within this release, they plan to add support for mobile consumption, custom visuals and other data sources.

When will there be a production release?

Microsoft announced that it is planning to release the production ready version of SSRS on-premise hosting of Power BI files with the next release of SQL Server, explicitly adding that it will not be added into the SQL Server 2016 product. Interestingly, this implies that we could expect the next version of SQL Server mid-2017.

Healthcare Analytics at the Spinal Cord Injury Network Thought Leadership Forum

The Spinal Cord Injury Network is advocating for a National Registry of persons with Spinal Cord Injuries. I had the privilege of attending and presenting at their Thought Leadership forum held on the 15th of November  2016. While most presenters were coming from a healthcare practitioner background, I brought my perspective on Healthcare Analytics, enabled by some great support from the team at Microsoft.

What is the Spinal Cord Injury Network National Registry?

A key aim of the Spinal Network is to Support the establishment of the SpinalCARE Registry which would track persons affected by Spinal Cord injuries at a national level. At the forum I was fortunate enough to hear from Dr Philip Clayton (Consultant Nephrologist at Royal Adelaide hospital) and Dr Ralph Stanford (Orthopaedic and Spinal Surgeon) on the value these kinds of registries bring. By enabling longitudinal studies of patient outcomes, often long after the patients have taken part in RCT‘s, they have been able to improve patient outcomes by the deeper insights available by having greater amounts of data that can be  connected through a common registry.

Dr Philip Clayton

Dr Philip Clayton

The Role of Healthcare Analytics

Fortunately – given the vastly more experienced subject matter experts in the room – the objective of my presentation was oriented towards showcasing what is becoming possible from a technological perspective as outcomes of Healthcare Analytics. This was a great opportunity to share what I think is one of the most amazing applications of the Cortana Suite I have seen this year, the “Seeing AI project”:

In addition to the amazing work of Saqib, I also got to talk about the proactive healthcare management solutions being offered by ImagineCare which are also very Azure / Cortana driven:

These two examples really demonstrate the art of the possible, and how Data and IT are offering amazing opportunities to improve lives.

I hope my contribution has managed to further the establishment of this worthwhile registry. It was great to see from some of the other presenters how similar registries have transformed patient outcomes through the healthcare analytics they have enabled.