Sonoma Partners Microsoft CRM and Salesforce Blog

Power BI Accelerator for Dynamics 365 Update

Today's blog post was written by Keith Mescha, Principal Architect at Sonoma Partners.

In case you missed it, back in June we release a tool for helping you with using your Dynamics data in Power BI. Please read the original blog post for more details or watch our video overview on our YouTube Channel. I also covered this topic on the CRM Audio podcast.

The community response to this tool was great, and we received some feedback that we have incorporated into a fresh build. V1.1.7 was released and our download page has been updated with this version that addresses these key areas:

  • Datetimes will actually be of the datetime type in Power BI (you don’t need to do any conversion, we do that for you).
  • If the query you run has no data, an empty table will be generated with the correct column headings. Previously an error saying that “Column1 does not exist” was popping up.
  • There was an unreported error that sometimes the URL being generated was incorrect. For example, /actioncard instead of /actioncards.
  • The entity list was sometimes not in Alpha order. It now is sorted correctly
  • Removed non-user entities from the list that are not available through advanced find (e.g. actioncard). This should only remove some system entities that normal users don’t need access to anyhow.

We have made this Dynamics Managed Solution available on our website for you to try out.

We are happy to assist in your BI projects and have a team of data professionals ready to assist you as you extend your Dynamics system to other O365 offerings like Power BI.

Topics: Analytics Microsoft Dynamics 365

Accelerate Your Dynamics 365 to Power BI Analytics Path

Today's blog post was written by Keith Mescha, Principal Architect at Sonoma Partners.

If you have tried to connect to your Dynamics 365 org via the OData API feed through Power BI, you probably struggled to make sense of how to build something useful from that experience. I’m sure you have read all the blogs out there suggesting the solution of syncing the data down to a SQL database and building your reports from there. It is often a decent solution but requires additional setup, configuration, licensing, and gets beyond the abilities of many Dynamics Admins. We do support that approach and have delivered several solutions using this method including for our own reporting needs. However, we also felt there is a need for a better, quicker option for customers.

To that end, we sat down and designed an accelerator for Dynamics to make this a reality.

Why is this a better option?

  1. You can use existing Dynamics system or personal views to auto create Power BI Queries. We don’t limit you to this as you can also augment those with other fields, but they serve as good starting point. Not sure what you need in your report, use advanced find get close and save that as a view and then build that out more later as you go.

  2. Options sets and lookup values are automatically mapped for you in the query. When hitting the OData API by default, you get the integer values and GUIDs of options sets and related records. This requires a lot of additional work to relink related tables and values which is tedious work that is hard to maintain over time. With this tool, we do that mapping for you based on the Dynamics configuration.

  3. All fields are renamed to the labels as you have defined them in your Dynamics configuration. This cuts down on a bunch of work typically required when pulling in data other ways as schema names are what usually comes over. No more typos or copy paste errors, and the reports you build will have the same field names as users see in Dynamics on forms and views.

  4. Also is using the well-documented FetchXML queries options in the WebAPI you are limited to the 5,000 record limit to you queries. Well we have written some sweet M code to overcome that limit paging. Best of all you can deploy this to the PowerBI Service and schedule updates of your data so you are not limited to just PowerBI Desktop.

*Currently this solution will work in PowerBI Desktop for Online and on Prem IFD enabled Dynamics orgs. However, if you deploy your pbix file to the Online PowerBI Service you will only be able to schedule data refreshes if you have a Dynamics 365 Online Org.

Overall the main goal here is to let you work in Power BI building cool charts and analysis to solve your challenging business problems and less time plumbing and wrangling your data into Power BI from Dynamics.

Here is a quick walkthrough of what this solution provides.

Once you install our managed solution into your org you can access the tool via the solutions area of Dynamics.

Keith 1

Upon launch, you can pick any entity and any view saved against that entity. In this example, we are using the out of the box Active Accounts view. After selecting that view I can chose additional attributes or even select the “All Attributes” option and pull all fields. I will caution that the more you pull, the slower and harder your data will be to work with in Power BI so the best practice is to be as selective as you can in building out a query.

Keith 2

One you have all the attributes you need you can click the Generate button.

Keith 3

At this point we present to you a screen where you can copy queries to the Power BI desktop application. The first item is the URL for the API of the org you are in. Be diligent with the version at the end of the query. Dynamics 365 is going to be V8.2. This serves as the anchor for other queries and should be created first in Power BI as other queries will rely on this.

Keith 4

Copy this URL into a Blank query in Power BI desktop and rename that query “CRMServiceUrl”. Casing is important here so be sure to make sure it’s put in exactly as seen here.

Keith 5

On the Accelerator page hit the Next button to move onto the next query which is the main data query for the Account view we selected. As you can see here there is quite a bit going on. What this step is doing is generating a FetchXML query using an OData Query against the WebAPI. This is returned to us as a JSON document which we use the M language of Power BI to parse into a table and then rename all the fields to the Labels as you have defined in your org configuration. Finally returning that into usable data set, essentially doing all the heavy lifting of pulling your data out of Dynamics for you so you can focus on building reports.

Keith 6

Paste that query into Power BI as another blank query and rename it “Accounts.”  Notice that this query is referencing the URL we created in our first data set.

Keith 7

This point you should have a valid data set with fields that match your labels on the entity as setup in Dynamics. You will need to follow the auth prompts and login to your org to refresh the data.

Keith 8

You can build whatever reports you need at this stage in Power BI. If you want to pull other related data from Dynamics, you can do that and model the relationships in Power BI as needed.

Keith 9

Here I have created a simple chart counting the number of accounts by City. I then deploy that solution to My Workspace on Powerbi.com. At this point I can go into my deployed data set and schedule a refresh for my data.

Keith 10

From here I can enter credentials for my org. Note that the data refresh will use that account when making the calls to the API so if be sure to use an account that has the rights to read all the data.

Keith 11

Choose Oauth2 as the Authentication method, which from we have found is only currently available with Online orgs. We have tried on IFD enabled orgs and do not get the Oauth2 prompt to allow us to configure the refresh. You will be prompted to enter your credentials on a few other pages not show here. After this step is complete your data will start refreshing on the schedule you set.

Keith 12

You can check the Refresh history from the dataset area in PowerBI.com.

Keith 13

From here you can keep on building out additional queries by using our solution then copying those queries into your Power BI desktop file. After you make changes deploy them to the Power BI Service for use in dashboards.

We have made this Dynamics Managed Solution available on our website for you to try out.

*Please make sure that you have downloaded and are always using the latest version of Power BI Desktop when using this solution.

If you'd like to view this in solution in action, you can watch our demo video here.

We are happy to assist in your BI projects and have a team of data professionals ready to assist you as you extend your Dynamics system to other O365 offerings like Power BI.

Topics: Analytics Microsoft Dynamics 365

Power BI Online Integration with Dynamics CRM On-Premise

Today's blog post was written by Hayden Thomas, Associate Developer at Sonoma Partners.

Integrating Power BI online with Dynamics CRM On-Premise is not currently supported natively. Recently we had a need to integrate a Power BI Report with a Dynamics CRM On-Premise environment, so we needed to create a custom solution to enable embedding reports and dashboards from Power BI into CRM.

Power BI natively allows reports to be ‘Published to Web.’  Doing this would allow us to simply IFrame the report on a Dynamics form or dashboard, but this makes it accessible to anyone who may have the link which is not very secure. This is unsuitable as the reports we’re looking to embed may have sensitive data which we want to make absolutely sure no one has outside access to.

In our case, we are connecting to Power BI through Azure. Azure uses OAuth 2.0 and Active Directory services for authentication. We need to be able to store:

  1. A Client ID that represents a connection to Power BI through Azure.
  2. A Tenant ID for our Azure Active Directory.
  3. An access token that will allow us to request a report from Power BI.
  4. A refresh token that will allow us to programmatically keep our authentication alive, so that we don’t need to continuously keep putting in our username and password.
  5. The lifespan of the authentication and the date-time we obtained it, so we can check if our current authentication is still good.

The Client and Tenant ID are the same for all of the users in our org, so we simply created a configuration record to hold these values.

The other fields will either be created for every user and we need to ask the user to enter their authentication to populate these fields, or we need to create a service account that can be authenticated in the background without the user needing any information on how the reports are being displayed.

Our solution used the second option. Our reports are shared amongst a group in Power BI. In order to have everyone log in and have their own tokens, it would require them to all be added to that group (and in turn, require everyone to have a Power BI Pro license). This also allowed us to just add the authentication fields to the configuration record, along with the service account’s username and password.

Powerbi brendan 1

Our next step is to make sure we actually have an app registered to our Azure AD that we can authenticate this user against. If we log in to dev.powerbi.com/apps, we can register one directly to ensure that it’s set up correctly. In order to make sure we don’t need to handle anything with redirect URLs, since we expect to move this around to different orgs without much issue, we use Native app from the App Type drop down, and for our redirect URL we use this link. For our case, where we only want to be able to read dashboards/reports, we only give it the read all dashboards and read all reports access levels. Once done, we can click Register App to obtain the Client ID we will use for our configuration record.

We have our app created, but haven’t yet given permission from our service account to the app to be able to log the user in programmatically. In order to give permission, we wrote a LINQPad script that does nothing but connect to the Client ID of our app, and allow the user to log in to give access.

Powerbi brendan 2

Running the script will pop up a dialog to allow a user to log into the App created with the specified client ID.

Powerbi brendan 3

In order to connect and display the report, we look to the Power BI documentation on how to show a report in an IFrame. https://powerbi.microsoft.com/en-us/documentation/powerbi-developer-integrate-report-load-report-iframe/. We see that we need an embed URL and an access token. Since we need to send information to the IFrame after it’s already set, and because we want to be able to use different reports in different areas, we create an HTML web resource that’s got an Iframe in it, and set the frame contents accordingly using JavaScript.

Powerbi brendan 4

Excess code for styling and other libraries used in JavaScript removed for brevity.

The JavaScript in this page does a number of things. When the resource initially loads it parses the report ID and the group ID, in which the report is stored, from the query string. This lets us use the same web resource on the same page to load multiple reports. In the web resource properties on dynamics, we can set this report and group ID field accordingly in the custom parameters.

Powerbi brendan 5

It then triggers a custom action that takes in both of those parameters. The custom action triggers some plugin code that loads the configuration record, ensures the authentication is up to date, and then queries Power BI for the embed URL for that report.

For ensuring our authentication is up to date, we see if we have an access token or if our token has expired (based on the authentication lifespan and authentication obtained date time fields we have on our configuration). If we don’t have a refresh token, we need to use the password grant_type along with the service account username and password. (If we can refresh, we do something similar using grant_type refresh and the refresh token that we have stored in our configuration record. More details on Azure OAuth operations can be found here: https://docs.microsoft.com/en-us/azure/active-directory/develop/active-directory-v2-protocols-oauth-code). In this example, we deserialize into an AccessToken model class that’s described by the documentation above.

Powerbi brendan 6

With our access token, we can query Power BI for the reports which are shared with the Group ID we sent added as a parameter by doing an HTTP GET request against https://api.powerbi.com/v1.0/myorg/groups/GROUP_ID/reports with an Authorization: Bearer ACCESS_TOKEN header.

Powerbi brendan 7

This will give us a response that’s a JSON string which will be an array of all of the reports for the group. Each entry in the array will contain the report ID and the embed URL. There are additional fields, such as the display name for the report, but they’re unimportant for what we’re doing. We simply need to find the entry that has the report ID that we passed in, and return the embed URL and access token back to the web resource. Powerbi brendan 8

Once we have those fields in the client side, we can simply set the source of our Iframe to the embed URL we received, and post the access token to it.

Now we can see our Power BI report as an iFrame.  In this case we embedded as a Dashboard in Dynamics CRM.

Image5

Topics: Analytics Microsoft Dynamics 365 Microsoft Dynamics CRM

Import and Export Better than Art Vandelay

Today's blog post was written by Nick Costanzo (Principal Consultant) and Nathan Williams (Consultant) at Sonoma Partners.

If you've ever had to use the native import tool for Dynamics 365, you've more than likely had the experience of running into import errors of some sort. These errors are not always easy to resolve, and if you're importing large volumes of data, sorting through the errors can be very time consuming. Here at Sonoma Partners, we've had situations where client import files have 50k+ records and have resulted in thousands of errors on the initial import into a test environment. Dynamics 365 offers the ability to export the errored rows, but it doesn’t include the error codes. The export only includes the rows with the data you had already included in your import, which is not very helpful. Furthermore, you cannot access the error logs through Advanced Find.

Our team set out to find a better way to tackle this situation using Power BI.

Through our efforts, we came up with the following approach to better analyze these errors and resolve them more quickly. After all, we don’t want you to start yelling, “George is getting angry!” while dealing with import errors.

Here’s the approach we took:

1. First connect to CRM by choosing Get Data > OData Feed:

Nick c 1

2. Then choose the Import Logs and Import Files entities.

3. Next pull in the Web Service Error Codes published on MSDN, by choosing Get Data > Web:

Nick c 2

a. Note: Power BI will recognize the table of error codes on this page, but you will need to massage the data to get the Error IDs and text into their own columns:

Nick c 3

4. Now you can create your data model with relationships between these 3 tables:

Nick c 4

5. With your relationships in place, you can now create a report with visualizations to categorize your errors:

Nick c 5

  1. Create a slicer for the Import Date.
  2. Create a slicer for the File Name, in the event you have multiple files to import.
  3. Create a slicer for the Target Entity.
  4. Create a bar chart to count the errors per Field and Error Name.
  5. Create a bar chart to group the error by the field Value (i.e.  GUID from the source system).
  6. Create a table to display the record(s) based on which slicers have been selected.

6. The report now allows you to easily focus on which errors need to be fixed. In this case, we can see that 2 records were responsible for 1468 and 305 errors where the lookup could not be found. By fixing these 2 values, we’re much closer to a clean data set and can move on to the next ones.

7. Once you have resolved all errors in your source files, you can now reimport with a much higher level of confidence that the job will be successful.

If you wanted to take this a step further, you could set this up to analyze your data before importing to make sure it's clean. You would need to setup your lookup tables as data sources, and update the data model with those as well.  If you’d like help with these feel free to contact us, and our Power BI team would be glad to help!  Either way, you can certainly do more importing and exporting than Art Vandelay ever did!

Download our infographic on D365 for manufacturing

Topics: Analytics Microsoft Dynamics 365

Shifting to a PowerBI World

Today's blog post was written by Keith Mescha, a Principal Architect at Sonoma Partners.

Power B-What?

There's been a bit of buzz recently in the CRM circles around Power BI and how it fits into the overall CRM landscape. There is certainly no shortage of blogs, podcasts and event demos showcasing the power of this toolset. On a personal note, I'm am seeing the same level of interest based on the meetings on my calendar over the past few weeks.

What we're finding at Sonoma is that the topic of Power BI is still very confusing for most customers. In this multi-part series we hope to help demystify some of the confusion through real-life examples and suggest some best practices based on learnings from our internal use as well as implementations with our clients.

The Basics

Power BI is Microsoft's analytics and dashboard Business Intelligence (BI) suite of tools and solutions. It's made up of a few main offerings:

  • Power BI Desktop – Free development tool for building data models and dashboards
  • Power BI Service – Online PaaS offering to host and share Power BI solutions with a monthly licensing per user subscription based model
  • Power BI Embedded – Online PaaS offering for embedding Power BI solutions into applications for external consumption with a session based subscription model
  • Power BI Mobile – Free mobile app for consumption of deployed Power BI solutions on a mobile device
  • Power BI Enterprise Gateway – On Premise software used to extend on premise data to the Power BI Service

For latest and greatest features and release notes, refer to the Power BI site.

Real-Life Example

Every company I know has data quality issues or challenges to overcome. Unfortunately, we here at Sonoma are not exempt. One of the things we constantly struggle with is incomplete data and stale data. Often times, we will create a record without knowing all of the various data points we typically want to capture. If we do not go back and fill that information in once it becomes known, we are left with an incomplete and potentially inaccurate record. The genesis of the original report request was to provide a means for the data steward team to monitor newly created records.

The original ask was for a custom SSRS report that listed out all Accounts that were missing specific data across a pre-determined list of fields. This report would then be provided to our data steward team on a regular basis to ensure the data is augmented appropriately. When one of my colleagues stopped by to chat about the report, I happened to be in the middle of building a dashboard in Power BI for a customer. He was curious what I was working on, and as we talked and he explained his need, I fired up a new instance of Power BI desktop in an attempt to solve the problem.

image

Get Your Data

I pulled up the 'Filtered Account' view from the 'Get Data' option in Power BI. My User Account is a System Admin and the Filtered Account view resolves all the option set and lookup fields for us, so this was a quick and easy solution. When getting data for many of the source types, you have an option to Import or Direct connect. I’ll go deeper on that topic in my next post but for this solution I chose to import.

image


Shape Your Data

Initially Power BI pulls in all the attributes in the table, this can be a bit overwhelming so filtering down the attributes was our next step. My colleague had a list off the fields he wanted to audit and report, so I filtered down the data set to only include those attributes.

image

Within 15 minutes my colleague and I had a prototype dashboard built with the fields he needed, including a couple charts to use as filters. From there we iterated on a few designs, and ultimately landed on the below look and feel. End to end this entire process took a couple hours to build, including some custom columns to allow linking to our Account Forms on Dynamics and Salesforce.

image


Deploy Your Solution

Once we had a solid solution built in Power BI Desktop, I created a new O365 group for internal reporting, added a few users to the group and then deployed the Power BI solution to the Power BI service.

One key thing to note here is that we had to assign Power BI licenses to these users before they could login and see our dashboard. The fact that we wanted to schedule this a couple times per day and that we were managing this through our 0365 AD bumped us from the free to the paid license. We had a handful of users requiring access and plenty of licenses available so this was a decent option for us. If you have a different need there are other options, we can consider for staying on the free option.

image

Manage Your Solution

From there my colleague could interact with the report by logging into PowerBI.com.

image

After my colleague was happy with the presentation of the data and a few more cleanup steps, I was able to setup the data refresh of the data set in Power BI. We already had a Power BI Enterprise Gateway installed on our SQL Server for another solution, so including my data set in the refresh was very simple with just a few clicks.

Now that our solution was deployed, our data stewards have logged in and subscribed to the dashboards. Every morning they get an email with a snapshot of the dashboard and a link to access the dashboard where they get direct access to the records in Dynamics or Salesforce so they can easily update those with missing data.

In summary, the Power BI platform is a great set of tools that are easy to learn. There is quite a bit of information available through various internet communities, so getting started is easy. As shown here, from idea to solution was less than one day for a very simple deliverable. The toolset can extend into much larger solutions as needed, so please give us a call and let us help you take the next step in your analytics journey within your CRM applications.

In our next Power BI post, we will discuss in more detail the step of getting your data and how to decide on Data Import Vs Direct Query.

Topics: Analytics CRM Best Practices CRM for Professional Services Microsoft Dynamics 365 Salesforce