Power BI and Microsoft Dynamics 365

Listing down the posts on Power BI and Microsoft Dynamics 365 for quick reference


Advertisements

Creating Power BI Report using Power Query (M) Builder plugin of XrmToolBox

Power Query(M) Builder XrmToolBox Plugin developed by Ulrik Carlsson and Mohamed Rasheed makes it very easy to develop Power BI Reports targeting Dynamics 365 CE.

Just listing down the basic steps below for quick reference à

Install the Power Query (M) Builder plugin from XrmToolBox

Click on Load Entities and select the Entity against which we want to write the report. Here we will be writing a report against Case entity (for e.g. Cases by Origin), so let us select the Case entity. For selecting the fields either we can make use of view or search and add the corresponding fields in Selected Fields Section.

Here we have selected the following 3 fields

Click on Update FetchXML button.

This opens the FetchXML tab, where we can select the fields (note we have formatted value field added for Origin OptionSet field) and also select to add record URL or to use all the attributes of the entity.

Here we have selected all the fields and also checked the option “Add Record URL”.

Click on Generate FetchXML, to generate the query.

The generated main query

Now back in Power BI Desktop, open the query editor and create a new blank query with following name and value

Dyn365CEBaseURL https://[org].crm.dynamics.com i.e. URL of the CRM Organization.

We can use the Generate Service URLs button to get this URL from within the plugin.

Similarly create a new blank query as

Get the value from the ServiceRoolURL tab from within the plugin.

Create one more Blank Query and click on Advanced Editor and copy the main query generated earlier and paste it.

Click on Done.

We can see the result generated for us in the Query Editor window

Click on Close and Apply

Back in report editor select the fields or any other visualizations to create the report

To format the URL, select the Link field and go to Modelling tab and in Data Category select Web URL to enable the links.

Thus, we are done with our report. Next steps would be to publish them, add them in dashboard, Embed them within Dynamics CE, Refresh them etc..

Hope it helps..

Dealing with OptionSet inside PowerBI in Dynamics 365 CE

Updated 8-Sep-2018 –> Please check the Power Query Builder tool of XrmToolBox. Thanks Scott Sewell for informing about this wonderful tool.

Suppose, we have created a Power BI Report which makes use of OptionSet field.

It is on incident entity and we have selected priority code and state code option set fields. The problem is that we only get the value for them, so to get the label either we can manually specify it or use the plugin Power BI Option Set Assistant. We’d see both the methods.

Let us take state code field first and specify label for them manually.

In the Query Editor, select New Query and specify following value and save it.

= #table({“value”,”label”},{{0,”In Progress”},{1,”Resolved”}})

Select the main query and click on Merge Queries

Select statecode and value to map them, specify left outer join for Join Kind as shown below

Select the new column added to the query and check the label.

We now have the label specified added as a new column to our query.

Here, for small set of values we can specify the label manually, however if there are too many values this might not be feasible and also if there are changes in OptionSet inside Dynamics 365 CE, we’d have to do it manually here, which makes it difficult to maintain.

So, let us use the wonderful PowerBI OptionSet Assistant plugin from our favorite XRMToolBox

Install the plugin

Click on Load Entities and select Case Entity.

Select Priority field and click on “Create records for selected option sets

Basically, it will create a new entity named gap_powerbioptionsetrefs entity, which will hold the records corresponding to each of the values of the optionset field selected.

Back in Power BI create a new query and select the gap_powerbioptionsetfrefs entity and save the query.

Now follow the same steps, select the main incidents query and perform merge queries operation as shown below

Expand the column to select the label field

Our final query à

Hope it helps..

Power BI with Dynamics 365 CE – Dynamics 365 Content Pack

In previous posts we covered

Creating a Power BI Report using Dynamics 365 Online Service.

Publishing it and Showing it inside Dynamics 365 and scheduling its Refresh.

Apart from creating our own Power BI Report another way of quickly be up and running is to use the Content Packs provided by Third Party Service, for e.g. here we will be using Sales Analytics for Dynamics 365 content pack provided by Microsoft.

Login to the Power BI service, click on Get Data, select Services.

Select Apps and choose the Sales Analytics for Dynamics 365 from the AppSource.

If we search for Dynamics 365, we can see around 15 Apps provided by Microsoft.

Back into our Sales Analytics for Dynamics 365 App, when we click on Get in now we are presented with the option where we need to Specify the URL of our Dynamics CRM Online Service and Fiscal Year End Month Number for that organization.

In the next screen, we need to select OAuth2 as the authentication method and followed by signing in.

This installs the app which would be available in the Apps section.

Clicking on the installed Sales Analytics App opens the Dashboard

Clicking on Ask a question about your data inside Dashboard allows us to analyze the data by allowing us to choose the criteria

Here we have filtered the accounts by created on by month along with the owner

Also, we can see around 10 different reports created inside that app.

  • Sales Performance
  • Sales Leaderboard
  • Win/Loss Analysis
  • Top Won/Lost Details
  • Sales Pipeline
  • Sales Pipeline Dashboard
  • Sales Activity
  • Open Activities
  • Lead Analysis
  • Account Analysis

Similarly, we can create our own Content Packs to be shared with either specific group or with the entire organization.

Click on settings gear inside Power BI Service and select Create Content Pack

Here we are creating a content pack choosing the Dashboard, Reports and the Dataset that we created earlier in the previous posts.

Once published, the user 2 can click on Get Data and search for and can see the content pack.

Clicking on Connect adds the Dashboard, Report, and the corresponding Dataset to the My Workspace of the user. Only the user who had created the content pack can edit the artifacts that are part of Content Pack, however, the other user of the organization can save a copy of it and can work on it.

More on it

https://docs.microsoft.com/en-us/power-bi/service-organizational-content-pack-manage-update-delete

Hope it helps..

Power BI with Dynamics 365 CE – Refreshing the Dataset

In the previous post, we used Dynamics 365 Dashboard and Power BI Tile to show Power BI Dashboard and report inside Dynamics 365.

https://nishantrana.me/2018/08/20/power-bi-with-dynamics-365-ce-showing-power-bi-dashboard-tile-inside-dynamics-365-ce/

Now as a next step, we want to make sure that the users are looking at the latest data possible inside the Dashboard\Reports.

In case of Online Services like Dynamics 365 (Online), we have the option to manually Refresh the data.

To see it in action, let us create a lead record in Dynamics 365.

This is how our Dashboard looks like for now à

Now back in Power BI Service right, click on ellipsis for the Dataset and select Refresh Now.

We can see the newly created lead in our Power BI Report inside Power BI Service and Dynamics 365 CE.

The other option we have is to Schedule the refresh.

Navigate to the Dataset and select Schedule Refresh

We can select the Refresh Frequency as Daily or Weekly.

And we can set it to refresh up to 8 times per day as shown below.

With Power BI Pro License, we can complete the scheduled refresh 8 times a day whereas the Power BI Premium allows the refresh up to 48 times per day.

https://powerbi.microsoft.com/en-us/power-bi-premium/

Some helpful links

https://docs.microsoft.com/en-us/power-bi/refresh-scheduled-refresh

https://www.powerobjects.com/2018/01/11/power-bi-data-refresh-performance/

https://crmtipoftheday.com/category/power-bi/

Hope it helps..

Power BI with Dynamics 365 CE – Showing Power BI Dashboard / Tile inside Dynamics 365 CE

In the previous post, we created a simple report and published it to the web and used IFrame to display them in Dynamics 365 CE.

https://nishantrana.me/2018/08/20/power-bi-with-dynamics-365-ce-publishing-power-bi-report-to-the-web/

Now let us create a Power BI
Dashboard using that report, which we will then display inside Dynamics 365 CE as a proper native Dynamics 365 CE’s Power BI Dashboard.

Let us take the fastest route to create them, back in Power BI Service, hover over the chart in the report and select the option “Pin Visual

We will be presented with the option of creating a new dashboard or add to an existing one.

Here we have created a dashboard named “My Dynamics 365 Dashboard

Similarly, we can pin or add the other chart in our Report to this Dashboard.

Apart from pinning the chart from the report into the Dashboard we also have the option to configure the Phone View for the Dashboard by clicking on Create phone view.

Phone view lets us specify how the report should look within the mobile device by dragging the reports in the designer surface.

This is how our Dashboard looks now inside Power BI service.

Now suppose we want to share this Dashboard with other users and also want to display this Dashboard inside Dynamics 365 CE, for this, we need to Share them.

However, as soon as we click on Share button we would get the below message.

To share and collaborate we need to have Power BI Pro license.

With Power BI Pro license, we can share it the with other users in the organization having the Power BI Pro License and also use the Reports \ Dashboard within Dynamics 365 CE.

Click on Try Pro for free and Start Trial which assigns the user 60-day free trial

After successfully assigning the license click on Share and select the user to grant access to.

Here we have selected user2 here.

If you remember from the previous posts, the Dashboards and Reports reside inside My Workspace.

Alternatively, we create a workspace and add the members (user 2 in our case) to it with edit access.

Note: We can publish the report that we created earlier from Power BI Desktop to this new app workspace.

While creating the new workspace we can also define the Access to it for the other users.

Here we have already assigned Dynamics 365 CE Enterprise Edition Plan and Power BI free license to User 2.

User 2 can see the report in Shared with me section inside Power BI service.

As we had mentioned earlier, when the user2 tries to open the report he is prompted with the message that Power BI Pro license is required to see the reports shared,

Click on Try Pro for free to start the trial for the User 2.

After successful activation user will be able to see the report inside Power BI Service. Here we are done with Sharing and assigning license part, now let us display it within our Dynamics 365.

Before we can do so we need to enable Power BI Visualization Embedding inside Dynamics 365 CE.

Navigate to Settings à Administration à System Settings and enable Power BI integration.

Open CRM, and select New to create a new Personal Dashboard and select Power BI Dashboard

It will load the all the Workspaces and the corresponding Dashboard inside it to which the user has access to.

Enable for mobile will make the dashboard available on Dynamics 365 for Tablets and Phones.

Select the Dashboard and click Save.

The Dashboard inside Dynamics 365 à

Similarly, User 2 can create a personal Power BI Dashboard inside Dynamics 365 CE and select the dashboard that is shared.

Apart from adding the Dashboard, we can also add the Power BI Tile to the Dashboard.

Create a new Dynamics 365 Dashboard, click on add a Power BI tile option to add the tile as shown below.

The CRM Dashboard showing Power BI tile à

This completes the sharing and displaying the power bi dashboard and reports part inside Dynamics 365 CE, next we will cover how the data can be refreshed so that users are looking at the most up to date data possible.

Hope it helps..

Power BI with Dynamics 365 CE – Publishing Power BI Report to the web

In the previous post, we learned how to create a simple Power BI Report using Power BI Desktop

https://nishantrana.me/2018/08/20/power-bi-with-dynamics-365-ce-creating-report/

Now let us publish the report to Power BI Service from within the Power BI Desktop.

To do so, select the Publish button

Publish window will ask us to save the report file (if not saved) before we could publish it.

Here we are publishing it to the default “My Workspace” app workspace within Power BI Service.

Publishing the report from Power BI Desktop publishes both the report and dataset associated to it in the Power BI Service. We can think of DataSet as the Database.

After successful publish we can see the Report and Dataset being added inside the Power BI Service (https://app.powerbi.com)

From with Power BI Service, we have the option of Publishing the report on Web so that it could be accessed by anyone having that URL.

To do so,

Select File
à
Publish to Web to publish it.

The Embed dialog box pops up as shown below with all the information.

Clicking on Create Embed Code will open the confirmation dialog box, that shows the potential risk associated with sharing the information over the internet.

Here, clicking on Publish generates the link for the report and also the HTML code which can be used for embedding it within an IFrame.

Within Dynamics 365 CE, we can use this public URL to show it inside the IFrame component of the Dashboard.

The report within Dynamics 365 CE’s Dashboard.

Next, we will see how to create Dashboard from the reports created and how to share them with other users and how to use the Power BI Dashboard and Power BI Tiles feature of Dynamics 365.

Hope it helps.

Power BI with Dynamics 365 CE – Creating Power BI Report

To begin with, let us first create a Dynamics 365 Trial.

https://trials.dynamics.com/

After our CRM trial is setup up successfully, we will install the OOB Sample Data which we would be using for creating the Power BI report.

Next, we need to create a free trial for the above user to use Power BI Service.

Go to Power BI site, and click on Start Free to get started.

Use the same user account (trial) that was used to set up the Dynamics 365 trial.

https://powerbi.microsoft.com/en-us

Note: If we already have an organization account with Power BI License we can use it to log in to Power BI else can associate a free trial to the same organization account instead of creating a new one. The gmail, hotmail, yahoo etc personal account are not allowed. https://app.powerbi.com/.

Download the Power BI Desktop as well as click on Try Free.

Power BI Desktop is basically a feature rich windows application that allows us to get data from various services online or on-premise and develop Dashboard and Reports efficiently.

The alternate way to download is through Microsoft Downloads siteà

https://www.microsoft.com/en-us/download/details.aspx?id=45331

After installing it and signing in and click on Get Data ribbon button to connect to the Dynamics CRM Online.

Select Dynamics 365 Online as the Online Services to connect to.

Specify the Web API URL of the instance.

Copy it from Customization à Developer Resources

Select the Organization Account and Sign in to the CRM Instance.

In the Navigator window, we have searched Lead entity and clicked on Edit to select the columns that we need for our sample report.

In the Query Editor window, click on “Choose Columns“, we have selected the Revenue, Created On, Subject and Owning
User field to be used for the report.

Now to get the Full Name of the system user for Owning User, expand that column, and select Full Name as the value to be displayed as shown below.

Click on Close and Apply to apply the changes to the query and to go back to the main editor.

Back in the main editor, we can see the fields added.

Now from the Visualizations section pick any of chart type.

Here we have picked a Stacked Bar Chart to start with. We can drag the fields and specify Area, Legend, and Value and also use the Filters section to generate the report as shown below.

The report after adding the fields à

Similarly, we can add our filter in the FILTER section to filter the records further as shown below

We can also add multiple charts in the report by selecting it from the Visualizations section.

For e.g. here we have used a Clustered Column chart to see all the leads based on Owner and Created On Date.

This completes the creation of simple reports which uses Dynamics 365 Online as Data Source.

In the next post, we’d publish this report to Power BI Service and display it within Dynamics 365 CE.

https://nishantrana.me/2018/08/20/power-bi-with-dynamics-365-ce-publishing-power-bi-report-to-the-web/

Hope it helps..