Callum

Callum Green's Blog

Embed PowerApps into Power BI Dashboards – Part 1

Having read Matt How’s blog (found here) about PowerApps, not only did it get me interested in the technology, I also wondered how well (if at all possible) it could integrate with Power BI. Our friend Google soon told me that it was already possible to embed PowerApps into Power BI, released in the April update. However, apart from this blog by Ankit Saraf, there aren’t many professionals sharing their experiences.

In addition, leveraging Direct Query mode in Power BI means we can simulate real time user input and reporting. To replicate my solution below, you will need an understanding of PowerApps, Azure SQL Database, Flow and the Common Data Service (CDS). The Further Reading section provides some good links to get you up to speed.

I have broken the blog into 2 parts:

-          Part 1: How Power BI visuals and PowerApps can be used together.

-          Part 2: Benefits and Drawbacks of the tools/processes used.

Solution

I picked a typical use case that would link Power BI and PowerApps – Actual vs. Target. The Power App will be used for adjusting target values, whilst an Azure SQL Database will contain the original target and actual values. All data and Power App interaction will be embedded into a Power BI Dashboard.

Create Sample Tables and Data in Azure SQL Database

Create and populate two tables – dbo.SvT for static actual vs. target data and dbo.SvTAdjusted that will eventually contain the adjusted target data from the PowerApps form.

clip_image001[4]           

clip_image002[4]

Note:     Azure SQL tables require a Primary Key column to communicate with Flow and consume CDS data.

Create PowerApp

Create an Environment within the PowerApps service, adding two new Connections:

 

clip_image003[4]

1.       Connection to the CDS, using my company Microsoft account. This is where the adjusted budget values reside.

2.       Connection to the Azure SQL database, which will become the destination table to store the CDS Power App data.

 

The next step is to import the SQL Data from dbo.SvTAdjusted directly into a CDS PowerApp.

 

clip_image005[4]

 

This automatically creates a user form containing the data. Here is where you can customise the PowerApp, such as making fields read only and configuring look and feel.

 

clip_image006[4]

 

Publish the App and test and change the ‘Target’ values to test.

Create Flow trigger

Navigate to https://emea.flow.microsoft.com/en-us/ and login. Create a new flow, searching for ‘Common Data Service’ as the connector. Select the below and create the Flow.

 

clip_image008[4]

 

Select the PowerApp CDS Entity (Adjusted Target) as source.

 

clip_image009[4]

 

Add a new step (Add an Action) and search for ‘SQL Server’. Select SQL Server – Update Row as the destination and map to the dbo.SvTAdjusted table. The column data types between CDS and Azure SQL Database must match when being mapped. Save the Flow.

 

clip_image011[4] 

 

Create Power BI Report

Create a Power BI Desktop report and connect to the Azure SQL Database.

clip_image013[4]

Set up the one to one relationship on ‘PrincipalID’, between the tables.

clip_image014[4]

Create some KPI’s and a table to compare dbo.SvT and dbo.SvTAdjusted metrics. In the below example, the ‘Adjusted Budget’ metric will change when we make changes in the CDS Power App.

clip_image016[4]

Embed Power App into Dashboard

Publish Power BI Desktop report and pin as a live page. To embed the PowerApp into the Dashboard, add a Tile and select Web Content. The App ID can be found under Apps in the Power Apps web portal. Simply paste the App ID into [AppID].

 <iframe width="98%" height="98%" src="https://web.powerapps.com/webplayer/iframeapp?hideNavBar=true&source=powerbi&screenColor=rgba(165,34,55,1)&appId=
/providers/Microsoft.PowerApps/apps/
AppID
]

clip_image018[4]

 

The PowerApp is added as a Dashboard tile. It is now possible to change the ‘Budget’ values.

clip_image020[4]clip_image022[4] 

 

 

Time to test everything works. Change the values for all three records and refresh the Power BI Dashboard. The values have changed almost instantly!

 

clip_image024[4]clip_image026[4]

 

Further Reading

Check out Part 2 of the blog, where I will be discussing the benefits and drawbacks I have found with using Power BI and PowerApps together. Find other recommended resources below.

o   Matt How’s Blog - http://bit.ly/2CpbTYI

o   Embed PowerApps into Power BI - http://bit.ly/2ywgsNX

o   PowerApps - http://bit.ly/2Brjys4

o   Flow - http://bit.ly/2CoL2vW

o   Common Data Service - http://bit.ly/2CnXXhv

Contact Me

If you have any questions or want to share your experiences with PowerApps and Power BI, feel free to leave a comment below. All scripts and workbooks are available upon request.

Twitter:                @DataVizWhizz

Embed PowerApps into Power BI Dashboards – Part 2

Part 2 of this blog focuses on my experiences with PowerApps, Flow and Power BI. Part 1 was more of a demo and ‘How to’ guide, but when I read an article online, I always find known limitations, challenges or workarounds as the most interesting takeaways. Without further ado, here are my findings.

 

A summary of both blogs below:

-          Part 1: How Power BI visuals and PowerApps can be used together.

-          Part 2: Benefits and Drawbacks of the tools/processes used.

Benefits

-          Easy to get started. Rolling out Power Apps, Flow and Azure databases into production of course needs careful thought, but for Proof of Concept’s, Flow (2,000 runs per month) and PowerApps (for Office 365 users) are free to use. Links to the price breakdowns are provided in the Further Reading section below.

-          There are a range of Wizards, Templates and GUI’s. All the tools used offer great templates for moving or inputting data and the fact barely any code is needed, makes it simple for business users. Following a couple of YouTube tutorials on each technology will get people up to speed very quickly.

-          Azure technologies provide seamless integration between Microsoft tools. Whilst there are some other well-known, reputable cloud service providers around, using one product is always going to produce a slicker solution. Having less configuration steps means less chance of human error.

-          Customisable features of PowerApps give the ability to mask, validate and format the PowerApp screens. It also makes the user entry a more pleasant experience, as the forms are more intuitive.

Limitations

-          You can only embed PowerApps into a Dashboard – as a Tile. I am not sure if moving PowerApps into a Power BI Report is on the roadmap, but I would be surprised if it was never supported.

-          Power BI Dashboards are cached and not entirely real time. You can change the cache settings to 15 minutes, but the best way to ensure your visuals contain the latest Power App data is to manually refresh your page in the browser. Reports do update automatically, which makes it even more frustrating.

-          Common Data Service (CDS) is a preview Data Connector in Power BI. As a result, you need to either have your environment set as ‘America’ and/or been given the beta by Microsoft. If I had access to this connector, there would have been no need to have the Azure SQL Database or Flow trigger. Milinda Vitharana’s blog shows how to enable CDS Power BI Integration.

-          If you wanted to use an on-premise database instead of an Azure database, an additional step is needed. A Data Gateway (link here) must be installed to move the Power App data back into the SQL database. Therefore, I would always recommend (where possible) using PaaS or other cloud services, as they talk to each other natively.

-          The error handling within the PowerApps is still quite limited. If Flow fails when updating data between PowerApps and Azure SQL Database, nothing is captured within the form itself. An Admin would need to check the Flow job or set up email alerts for user’s peace of mind.

 

clip_image002[4]

 

Conclusion

The initial signs look promising for Power BI and PowerApps integration. I managed to create an Actual vs Target Proof of Concept in just a matter of hours, without any real coding. There are still quite a few drawbacks and hoops to jump through to bring everything into a Power BI Dashboard, but I can only see things getting easier from this point.

There are other use cases for embedding a PowerApp into Power BI, such as monitoring live sales and re-ordering stock within a PowerApp or updating product descriptions that automatically updates the Dashboard attributes. Giving someone the ability to directly interact with a Dashboard and make instant business decisions is priceless in today’s fast paced world.

Further Reading

Find other recommended resources below.

o   PowerApps Pricing - http://bit.ly/2j5sN69

o   Flow Pricing - http://bit.ly/2kw0MFr

o   Milinda Vitharana’s blog - http://bit.ly/2BfkywQ

Contact Me

If you have any questions or want to share your experiences with PowerApps and Power BI, feel free to leave a comment below. All scripts and workbooks are available upon request.

Twitter:            @DataVizWhizz