techcasestudies/_posts/2016-10-27-KPMG-PBIE.md

layout	title	author	author-link	date	categories	color	image	excerpt	language	verticals	geolocation	sticky
post	How KPMG is using Power BI Embedded to develop a secure, flexible analytics platform	Andrew Fryer and Amy Nicholson	http://andrewfryer.com	2017-03-02	Power BI Embedded	blue	images/2016-10-07-KPMG-PBIE/feat_reports.PNG	Microsoft partnered with KPMG Healthcare to help develop an analytics platform that provides a secure front-end reporting solution with Power BI Embedded. This platform makes actionable insights available to a wide range of care staff.	English	Health	Europe	false

Healthcare is under pressure in every country in the world. The challenge is to identify how to make spending more efficient without having a negative impact on patient outcomes. The data required to gain the necessary insights comes from many sources. In the United Kingdom (UK), these sources are located either in the National Health Service (NHS) or within local government bodies such as Social Services.

Key technologies used

• Azure SQL Data Warehouse
• Azure Data Factory
• Azure Machine Learning
• Power BI Embedded
• Power BI Desktop

Project team

Microsoft

• Andrew Fryer – Technical Evangelist leading the project
• John Donnelly – Technical Evangelist advising on architecture
• Amy Nicholson – Technical Evangelist (Power BI Embedded Technical Project Owner)
• Jonathan Collinge – Technical Evangelist on application development
• Gina Dragulin – Director of Audience Evangelism, leading Intelligent Cloud Projects
• Mark Westhenry – Advanced Analytics/IoT Partner Business Lead

KPMG Healthcare

• Paul Henderson – Director of Health Data and Analytics
• Yusuf Ermak – Associate Director at KPMG
• Khurram Asif – Manager Data Insight team
• Nazia Khan – Senior BI Developer

Customer profile

In the UK, the public sector and health practice of KPMG Healthcare brings together a range of disciplines—including management and risk consulting, CIO advisory services, technology solutions, mergers and acquisitions, restructuring, and tax and audit—to provide solutions and services to the UK and international public sector. In health and social care, it has dedicated teams providing support on care system redesign and improving operational excellence. In particular, KPMG's network member firms have a strong data and analytics practice that is supported through a global alliance with Microsoft.

KPMG member firms have offices across the globe. (List of worldwide offices.) During this project we worked with a team from KPMG in the UK’s London office in Canary Wharf.

Problem statement

Healthcare faces the challenges of increasing demand, budgets that are falling in real-terms, increasing service costs, and increasing levels of expectation from the public. If we are to meet these challenges, we must be able to:

• Predict demand.
• Profile our population.
• Segment and risk-stratify citizens and patients according to their demands.
• Redesign care systems and processes.
• Digitize services.
• Forecast and budget.
• Model the most effective service configuration.
• Monitor progress toward achieving change.

We have to demonstrate that the outcomes delivered by the NHS and its partners have addressed the triple aim of delivering health and well-being for the nation through providing safe, quality, and cost-effective care.

Solution, steps, and delivery

What we worked on and what problems this helped solve

Microsoft technical evangelists assisted KPMG in the UK in addressing the need for a technical platform that could collect data from multiple sources and in multiple formats, process that data into a common model, predict patient and citizen behavior using machine learning and data science, set outcome goals, and track progress.

The goal of the project was to establish a robust, secure but flexible analytics platform that solves the problem of making actionable insights available to a range of care staff—from the strategic to the operational in a complete, accurate, timely, relevant, and consumable format.

Privacy of the data has been a top priority for the team across the entire project.

We kicked off the project with a workshop to establish the architecture and define scope for the solution. From this, we planned out the critical mass for a proof of scope:

• Use Azure Data Factory to load data to Azure SQL Data Warehouse.
• Use traditional SQL stored procedures for data transformation as many of these were already created.
• Create sample Azure Machine Learning web services to add features to the data warehouse.
• Use Power BI Embedded in an application to access the data warehouse and use row-level security (RLS) to control user access to the correct data.

The data pipeline/overall architecture

Azure Data Factory: Loading data into Azure

To get the team moving quickly, we created Data Science virtual machines on Azure, so the team could quickly start using the Cortana Intelligence Suite from any machine KPMG would be working on.

We created an Azure Data Warehouse with schema and test data from an earlier internal and on-premises project KPMG had started work on. To populate the data warehouse we used pipelines in Azure Data Factory and we created these by using the simple copy wizards in the Azure portal to lift and shift .csv files to Azure Data Warehouse.

We then recoded the pipelines using the Data Factory tooling in Visual Studio to call Azure Data Warehouse-stored procedures to do the transformations that had already been created. This enabled KPMG to quickly migrate its existing work from using SQL Server Integration Services (SSIS) on-premises to the cloud.

Azure Machine Learning: Building models

Having created an initial cut of the warehouse design, we then invited in the KPMG data science team to see how we could quickly develop and deploy machine learning models with Azure Machine Learning (AML). As an example, the NHS in the UK has a lookup for the expected time needed in hopsital for every kind of operation. However, in reality not every patient takes exactly this time to recover, so we built a regression model in Machine Learning using the built-in Poisson Regression algorithm, which they then tuned the parameters for. Once tuned, a Machine Learning web service was created. The data was then played back into Power BI for the demo where in production the resultant web service would be called as part of the transform process in Data Factory.

The importance of security

KPMG needs to have good security throughout this solution. The data is already pseudonymized, so we spent some time building a demonstrator of this that combined the Power BI samples.

Power BI Embedded: Visualizing this end-to-end solution in an application

The data pipeline had been created and data was being loaded into Azure via Data Factory, transformed and stored in a SQL Data Warehouse. Now KPMG needed to know it could put the data where its customers were, in the application the customers use every day to understand the operational efficiency of the health industry and their sector. KPMG had been using Power BI Desktop previously to create reports—for example, cost reduction, workforce performance, payroll—in hospitals being able to track efficiency of outpatients, inpatients, and delays. Now the key was to embed these reports into the application securely and know that each user would only see the data they have access to.

We designed a sample .NET web application with ASP.NET Identity framework to simulate the authentication process and allow sample users to log in. Once authenticated, a list of all the reports within the workspace collection is displayed and the user can then select a report to view. At this stage, row-level security is applied to filter and restrict the report based on the user's security roles.

The following image shows the application, logged on, using a sample dashboard where row-level security has been applied. The demo user manages one type of store, so you can see the pie chart is automatically filtered on login to reflect this.


Full architecture



Architecture in progress during the hack


Flipchart architecture in the image above converted into final architecture.


Power BI Embedded architecture

As well as implementing Power BI Embedded into a solution specifically for KPMG, the Microsoft team also created a sample application in C# for anyone who might want to do this for their own application. This is the bare bones of an application to really highlight the simplicity of adding Power BI Embedded into a solution—by removing any complexity from a nice-looking demo code sample, it allows you to see the crucial code needed.



Final outcome: the KPMG analytics platform

https://home.kpmg.com/uk/en/home/insights/2016/09/using-analytics-to-drive-system-reform.html


Technical details of implementation

Customer journey through sample architecture:

• Step 1. Customer logs on to the application with credentials (in this case we are using ASP.NET Identity and SQL Authentication to store our users credentials in a SQL database). Each user has a 'PowerBiRole' associated with them that they defined when they registered. In this instance, Andrew works for Fashions Direct. As we saw with the application screenshot above, this means row-level security can be applied to filter what Andrew can see and excludes and protects the rest of the multitenant data.

  

• Step 2. Customer clicks on a reports tab and a list of possible reports is populated.

public ActionResult Index() { // Display all PowerBi reports using (var client = this.CreatePowerBIClient()) { var reportsResponse = client.Reports.GetReports(this.workspaceCollection, this.workspaceId);

        var viewModel = new ReportsViewModel
        {
            Reports = reportsResponse.Value.ToList()
        };

        return View(viewModel);
 }

}

- Step 3. Customer chooses a report to view. At this point the application checks the authentication of the user and retrieves the user's role from a SQL database. Power BI uses this role as a predicate to filter the report for the given user if they have access.

  **Code snippet description: Is the user authenticated?**

@if (Request.IsAuthenticated) { @:

@Html.ActionLink("Reports", "Index", "Reports")

}

  **Code snippet description: Get the selected report, create the JWT with *Workspace Collection*, *Workspace ID*, *Report ID*, *username*, and *role*. Then sign this token with the Power BI Embedded *access key*.**

public async Task Report(string reportId) { // Display specific PowerBi report using (var client = this.CreatePowerBIClient()) { var reportsResponse = await client.Reports.GetReportsAsync(this.workspaceCollection, this.workspaceId); var report = reportsResponse.Value.FirstOrDefault(r => r.Id == reportId);

        // Grab username from ASP.NET identity
        var username = User.Identity.GetUserName();
        var userId = User.Identity.GetUserId();

        var manager = new UserManager<ApplicationUser>(new UserStore<ApplicationUser>(new ApplicationDbContext()));
        var user = manager.FindById(userId);

        // Populate the PowerBiRole
        var powerBiRole = user.PowerBiRole;

        PowerBIToken embedToken;
        var rowLevelSecurityEnabled = !string.IsNullOrEmpty(powerBiRole);

         if (rowLevelSecurityEnabled)
         {
             // Convert single PowerBi role to collection as required by SDK
             List<string> powerBiRoles = new List<string>();
             powerBiRoles.Add(powerBiRole);

              // By passing in Username and Roles we are allowing Row Level Security
              embedToken = PowerBIToken.CreateReportEmbedToken(this.workspaceCollection, this.workspaceId, report.Id, username, powerBiRoles);
         }
         else
         {
             embedToken = PowerBIToken.CreateReportEmbedToken(this.workspaceCollection, this.workspaceId, report.Id, username);
         }

         var viewModel = new ReportViewModel
         {
             Report = report,
             AccessToken = embedToken.Generate(this.accessKey)
         };

        return View(viewModel);
    }

}

- Step 4. JWT is passed to a Power BI Embedded service in Azure where the token is accepted/declined.
- Step 5. If accepted, the PBIX file uploaded to the Power BI Embedded Workspace is displayed in the application.
- Step 6. The data contained in the report displayed is always up to date, as Direct Query functionality to an Azure SQL Data Warehouse is set up.

**Other notable comments:**

* PBIX files created to show the capabilities of cross filtering and charting visuals over tables.
* Completed a manual run through of the creation of the Power BI Embedded workspace collection/workspaces/PBIX Upload via the C# SDK Provisioning Sample.
* Created a sample application and embedded a report in an IFRAME.
 
## Learnings from the Microsoft and KPMG team ##

**Learnings:**

* How to scale row-level security (RLS) by using the USERNAME() function as an input parameter and not always usernames and roles but send the role "department x" in as a parameter USERNAME to be evaluated in DAX expression [department] = USERNAME().  
* Using the sample provisioning application and REST APIs meant it was easier to understand the flow of setting up Power BI Embedded in Azure. This is compared to, say, the SDK, which does a lot of functionality for you.
* Understanding the solution lifecycle by looking into how to update a PBIX file (REST) in the Power BI Embedded workspace to reflect new updates.

This was a truly global effort with great support and responses from the Power BI Embedded team on the row-level security aspects of the project and making sure the UK KPMG team could take these insights and learnings further than just this project.

## Conclusion 

In conclusion, the three-day hackfest was a success. We achieved our goal of developing a healthcare platform using relevant parts of the Cortana Intelligence Suite and we were able to provide a secure front-end reporting solution with Power BI Embedded. The whole team gained valuable knowledge about how to use the Cortana Intelligence Suite to deliver analytics as a service. This learning will be dispersed around other teams in KPMG looking to apply these practices in other sectors.

**Paul Henderson from KPMG, in the UK, concluded that this technical engagement:**

*"... has enabled KPMG to develop a solution for data processing, storage and analytics that is delivering results for the business internationally. We have established a SaaS offer that enables the Canadian firm to use our platform, we have enabled other member firms to see the value such that they have built us into RFP [Request for Proposal] responses, and the innovation has been recognised by Microsoft such that we are part of key programmes like the Power BI partner programme. In terms of UK business, we are now ahead of target for market traction/sales."*

**A critical element of success has been the close relationship with the KPMG account team. Mark Westhenry, Advanced Analytics/IoT Partner Business Lead at Microsoft directly involved in this project, commented:**

*"... The relationship with the Evangelism team working on KPMG's health app development has been a great demonstration of One Microsoft. It has driven a broader UK pipeline as well as opportunity in other geographies. It’s also brought the partner quickly up to speed on the latest Microsoft capabilities across multiple service lines, establishing new opportunities in different verticals."*