Sonoma Partners Microsoft CRM and Salesforce Blog

Power BI Accelerator for XRM Toolbox

Today's blog post and tool were developed and written by Keith Mescha, Principal Architect at Sonoma Partners.

The Dynamics CRM community asked, so we are delivering.

Power BI Accelerator for the XRM Toolbox is now available for download from our website.

If you are implementing or supporting a Dynamics CRM implementation and don’t know about XRM Toolbox then push that rock off you and go read this first.

Powerbi xrm1

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 You Tube Channel. Additionally, I was recently asked to speak about this on the CRM Audio podcast. The community response to this original tool was great and we received some great feedback which we have incorporated into the current build (v1.1.8).

Many folks, including a couple we ran into at the CRMUG Summit last month have asked us to build a version for XRM Toolbox. One huge advantage of this is that you don’t have to be a CRM admin in an org to generate Power BI queries. Seeing that our original implementation was solution driven you had to have access to the Setup portion of Dynamics.

Now with this new XRM Toolbox Plugin you can use that framework to connect to any org you have access to and generate the Power BI queries you need without having admin access to the org. You also don’t need to install anything into your org to utilize this version as you do with the managed solution version.

All the prior fixes in the managed solution version are included in this 1.0 release. As will all first releases there are some known issues but we wanted to get it out so you can start to benefit immediately. Happy to take any feedback via comments to this post and we will do what we can to continue to enhance the plugin over time and release new versions.

The plugin can be downloaded on our website for you to try out. We hope to get this into the Plugin Store soon but for now it’s a manual install with 2 easy steps.

  1. Download/Run XrmToolbox on your machine if you haven’t already to generate the plugins folder.
  2. Place the DLL in %appdata%\MscrmTools\XrmToolBox\Plugins.
    1. %appdata% is C:\Users\<username>\AppData\Roaming\ if it’s for some reason unbound on your machine.

Once installed you can connect to your org just like any other XRM Toolbox plugin. Upon connection, you can press the “Retrieve Entities” button. From there the interface is very similar to what is spelled out in the original blog post.

Generate a query in 3 easy steps

  • Pick an entity
  • Choose a view
  • Click Generate

Powerbi xrm2

After you click Generate you are presented with a screen that allows you to copy the queries into Power BI Desktop Directly. If you used the previous solution you will notice we cleaned up the query generated in this version so it’s bit easier to read and debug.

Powerbi xrm3

From here normal Power BI modeling best practices kick and you should have some reports and dashboards up and rolling in no time.

Please be aware of a few small known issues we have on our list to address in the near future.

  1. The user is required to hit Retrieve Entities once they load the tool and connect to an Org.
  2. There are some performance issues with drawing the attribute checkboxes where the page freezes for larger entities with many attributes.
  3. Personal/System Views are in the same drop down and not split out so it’s not apparent they are all there.

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