ACDC 2021 – Final delivery

My last post described the ACDC hackathon and what it’s all about, but this post is what our company ended up delivering for the Arctic Cloud Developer Challenge after 2 1/2 days of pure geeking 💪

Meet the Team

Area: Microsoft 365, SharePoint, Developer, Power Apps

Areas: Dynamics 365, Power Apps, Developer,

Areas: Microsoft 365, Azure, Developer

Areas: Power BI, Data Platform, Power Apps

Areas: Dynamics 365, Power Apps, Power Platform

Welcome to LEGO CITY

Our idea was to create an interconnected city with bleeding edge technology for monitoring the status quo of the citizens safety. The City is built next to a mountain that recently has been showing signs of activity. Geologist say that it could blow at any time. Luckily the city has invested heavily in technology that could help them save lives ❤

The city has a train track surrounding a lake. The Train track is crucial for transporting the LEGO’s around, and providing transportation for factories producing parts. In the city you have a Mayor’s office, LEGO factory, houses, and THE MOUNTAIN OF DOOM!! 🔥 Based on this drawing, or mission was to create the technology needed to make the city a safe place to live.

On the day of the event we started building right away, and this is what we created within a few hours😅

But since this is a technical blog, I will get into the makings of the Connected Lego City details, that made this project on of the coolest city concepts I have seen in a while!

The technology

Mountain of DOOM!!🔥

Let’s start at the top near the mountain. We placed 2 IOT sensors for monitoring temperature, and movement. We used the Azure IOT dev kit sensors for this purpose. Both IOT sensors were pushing data to Azure IOT hub, and then over to Stream Analytics. The constant changes were reported to the Majors office. The impressive part here was

Train Track Switch

On the top left we connected an Arduino device with a servo Switch connected. This was used to change the train track from long track, to short track.

By sending a boolean to the device, it would mechanically change the trains direction. You can barely see the device hidden beneath the track with a wire for power under the blue “water”. To see it in action, just watch the video on top.

Majors Office – Command Central

In the bottom left we had the majors office (aka command central), where the major could minor all thigs happening in his city. This is where we were looking at the output from the city sensors, and reporting via Stream Analytics to Power BI.

We included Dynamcis 365 information on the left to give a status on all ongoing city work orders (fixing problems), and on the right side we had live data from the mountain. One of the charts were showing the constant temperature, and others were measuring movement. Below we connected to the Norwegian weather API, so that we could understand what potential conditions would effect our emergency response the next days.

Train conductor – remote worker

During these Corona times we wanted to give the Train Conductor the possibility to work from home. Therefore we mounted a camera on the train, and connected it to a Raspberry PI. The Raspberry PI was also running a piece of software that could control the USB part of the LEGO Boost. For this we combined projects from:
1: https://motion-project.github.io/motion_config.html
2: https://github.com/virantha/bricknil

We also created a webserver running the live feed of the camera with the controls. The conductor could then log in and power the train in any direction needed 🚆🚂

Citizen Self Service

When there was an issue in the City, the citizens could report this via porta to Dynamics Customer Service. We connected with The Portal Connector for this scenario.

Submitting the case would deliver the case to Dynamics 365 Customer Service, where we used a few PCF components from pcf.gallery for picture rendering in CRM.

LEGO Factory – Field Service

Once the case had been evaluated, we would create a work order for booking our trusted technician for the job. For this case we used Field Service OOTB, and the technician would used the new mobile app for service delivery. We also connected the warehouse for picking parts from the factory based on the initial pictures that the technician would have seen.

Payment – Vipps

The technician fixed the broken houses, he would then send an invoice via the Norwegian payment service Vipps. The awesome thing about this was that it was all done by using a Power Automate Flow for the job! Once the Work Order was complete, we simply created a payment for the Vipps API, and received our money.

Crisis team

If the IOT sensors detected crisis, they would start a flow that the mayor would have to approve.

From this flow we would also trigger sending of SMS notification to the citizens that were in dataverse.

CDS vs Dynamics Connector Power Platform

There are several discussions regarding the use of CDS connector VS the Dynamics connector in the Power Platform. Recently I have been updating my knowledge a lot on PowerApps, PowerBI and Flow. The odd thing is that these environments behave quite different even thought they are so closely connected in regards to connectors.

how much longer will they co exist, and why are they even different?

So here are my thoughts about the current state April 2019 (this might be outdated in one month 😂)

Flow CDS vs Dynamics 1 – 0

https://crmtipoftheday.com/1177/use-the-cds-connector-when-you-go-with-the-flow/
When using Flow, the CDS connector seems to be the correct choice. I personally feel that the action advanced settings is the most important reason why the CDS would be the correct choice.

CDS connector will only run the flow if country is changed saving you the flow, and the Dynamics connector will run no mater what costing you 1 flow.

PowerBI CDS vs Dynamics 2 – 0

https://www.inogic.com/blog/2018/10/connect-to-the-power-bi-using-common-data-service-cds/
There are maybe many reasons why you would choose the CDS connector over the Dynamics connector for API reasons, but the most important one I like to focus on is the Option Set (Pick List) from Dynamics.

Look at the Yellow columns. They show the display name of the Options Set, so no more need for hacks to convert the fetch from numbers to names. Obviously a huge benefit that this solution will update when the Option Set values update also!

PowerApps CDS vs Dynamics 2 – 1

But wait… It might not be over yet. While tampering with lookup controls for PowerApps I found out that CDS simply doesn’t have the lookup fields in the data!

CDS connector (Simply doesn’t have any lookups):

Dynamics Connector (With lookups):

Data presented is still in a GUID state, but there is a workaround for that:)

https://www.inogic.com/blog/2018/07/working-on-lookup-field-of-dynamics-365-in-powerapps/ <– How to fix GUID in PowerApps

Power BI RLS report for Dynamics 365

RLS stands for Row Level Security and is a function within Power BI used to securely manage what the users see. I am reusing the steps from the Power BI embedding blog, and continuing with the same dataset to define RLS. Another big thanks to Jeroen Vos for teaching me PowerBI.

We start the process in Power BI for Desktop. Open the same report, and then navigate to Modeling and Manage Roles.

This brings up a DAX console, and I will chose the “Address 1: Country/Region”

Now we can test the DAX by simulating how it is with another user login. This function is something I wish we had in Dynamics!!!

Notice that the data has changed!:)
NB! Remember to publish changes to PowerBI.com

Log into https://www.powerbi.com to complete the rest of the process

Next step is to add a user to the newly created security role. Navigate to datasets and open the security.

Last step is to Share the Dashboard with the user

Open Dynamics 365 and add a new Power BI Dashboard.

The Administrator (All data)

The Sales user (Only USA)

RLS reporting in Dynamics 365, secure and supported 🙂

Power BI embed to Dynamics 365 form with filtering

There have been several post about this topic before, but this one is the only way I got it to work. Rasmus wrote about this a few weeks ago, and I am using most of his code. Ida was also early out mentioning this possibility, but I just couldn’t get it all to work before now. Big thank you to Jeroen Vos for BI assistance:)

Bear in mind that I had to start from scratch here to make sense of it all. You can fast forward quite a bit if you already have a data model. I believe Microsoft will release a OOTB function sometime this year, but until then we can demo embedded BI in CRM forms like this.

Create a report

Start by opening Power BI dashboard, and creating a new project with “Get Data”

If you don’t know where to find the developer settings, you can copy the url from above, and just replace it with your org and crm* that you have in your country.

For this demo I am only choosing Account and Opportunity.

After loading, add the funnel graph.

Add the field salesstage and estimtedvalue. Both are from Oppty table.

Your report should resemble something like this. Now we have to format quite a bit for this to actually look presentable in Dynamics. Because this solution is highly custom at the moment, we need to make it fit the Dynamics IFrame for it to look decent.

The most important format of them all is the page format to the right. You need to make this one small for the Dynamics Tab to be able to open it in full screen. I made it half of the standard. Eventually you should end up like something below:

Numbers on the right are a picklist, but for the demo I didn’t care to rename them.

Publish to powerbi.com

Remember to copy the URL here

Dynamics 365 form configuration

This process requires a webresource with javascript and a tab with IFrame in Dynamics. Because of the new UI this layout is actually quite nice!

URL can be any website since we are overwriting it anyway. The name of the iframe is used in the javascript. **Don’t** restrict any cross-frame scripting.

function SetBIFrame(executionContext){
    var formContext = executionContext.getFormContext();
    var pbiFrame = formContext.getControl("IFRAME_BI");
    var GUID = formContext.data.entity.getId().replace(/[{}]/g, "");
    //replace the Power BI url with the url from the Embed step
    var PBIurl = "https://app.powerbi.com/reportEmbed?reportId=d86fdc2b-316a-420e-b177-9e477c879031&autoAuth=true&ctid=83121429-cfda-4a1b-89c9-969eb42dc605";
    //accounts = account table in BI, and accountid=guid for account
    pbiFrame.setSrc(PBIurl + "&filter=accounts/accountid eq '" +GUID+ "'");
}

Only thing you need to change is the URL from the powerbi.com secure embed. Since I am using account and opportunity out of the box, the table names for both of them are “accounts” and “opportunities”.

In the filter above I am filtering the results in the Account table with the account ID that i will find within CRM. The result is to show a graph for only that single account.

Remember to Pass Execution Context here. I don’t really now why, but it wont work without. Normally when I script I don’t have to use this, but if someone would tell me what it does I am happy to learn:)

Publish all settings and open Dyanmics 365 to the account (that you know has some opportunities).

As you can see Alpine Ski House is showing a sales funnel of the opportunities related and nothing more. This is a filtered report in Dynamics based on the account in a BI tab on top. Navigation here in dynamics is pretty smooth.

Power BI embedded dashboard Dynamics 365

Download Power BI Desktop https://powerbi.microsoft.com/en-us/desktop/
and login to the Power BI with your credentials.

Then download the content pack file for Power BI
https://docs.microsoft.com/en-us/dynamics365/customer-engagement/analytics/customize-power-bi-content-packs

Open the .pbix file in Power BI desktop, and it should look something like this

If for some reason the dashboard isn’t showing any data, you might need to run through the Power BI Sales Analytics fix for Dynamics 365

Next step is to publish this to Power BI

Open https://www.powerbi.com and locate the report you uploaded. A report itself can’t integrate with Dynamics, so we have to create a Dashboard for CRM to be able to pick this up. Open up the report

Find a chart you like and pin it

Continue the process for the charts you like, but add them to the existing dashboard “CRM Sales”. Eventually you might have a dashboard looking something like this:

NB!!! I had to make the size of these charts fairly large. Otherwise Dynamics 365 was doing strange things with scaling. Just try different approaches.

Now we have to configure Dynamics 365 to allow Power BI embedded reports. Open https://www.admin.powerplatform.microsoft.com for Environment settings.

Open CRM in your app and you can now add a BI Dashboard.

The result will be nice if you remembered to scale the Dashboard in PowerBI.com large enough.

One little side note. At the moment I can only see the option to add Personal Dashboard with Power BI embedded.

Fix – Power BI Sales Analytics data for Dynamics 365

Problem

There is something wrong with the BI content pack for Dynamics 365. It might be my setup, but this is what I have to do before demos to fix the connection problem. The issue is related to the content pack filters locked. So after completing the setup you will se an almost blank BI dataset. I haven’t figured out why, but figured out a way around.

Solution

I will go through the steps to fix the report and make it ready for demo.

Download Power BI Desktop https://powerbi.microsoft.com/en-us/desktop/
and login to the Power BI with your credentials. Then download the content pack file for Power BI
https://docs.microsoft.com/en-us/dynamics365/customer-engagement/analytics/customize-power-bi-content-packs

In the filter view for every page you will notice that there is an active filter. This is the problem when looking into the data!”

I personally would remove all the filters pr page for the purpose of the demo. More data looks better:)

Next step is to fix the Dynamics source, so the system is getting data from the correct CRM system.

Find the query, advanced editor and change to the CRM org. Based on MS doc when writing this article, only the 8.2 was supported. When you are done, “close & apply”.

That’s it for the fix. This should now be ready to publish to Power BI online. Next step is embed Power BI report to Dynamics 365.