Follow these step by step instructions to connect your Microsoft Dynamics CRM to Tableau via OData.
There are many great BI tools available today, each of which can connect to data in many ways. Having so many options really bolsters the ability create great reports and visualizations. One of those many connection options is called OData. This REST based connection was established by Microsoft and has a growing market of users. Hence the focus of this tutorial: connecting Dynamics CRM to Tableau via OData.
Required tools for this tutorial:
If you need any of the required tools, please follow the links below:
We first need a few pieces of information from your Dynamics CRM environment. Once you’re logged in and your dashboard has loaded, click the “Main – Show Work Area” pull down (the three horizontal lines between Microsoft Dynamics CRM and Sales) shown below.
From here we will want to select Settings, then Customizations.
We are interested in some of the content within Developer Resources. Select this.
The information we need is under Organizational Service. The Endpoint Address is what DataDirect Cloud will use to ping Dynamics. Keep this URL handy.
Login into DataDirect Cloud (D2C). Once the main page has loaded you’ll see some options on the left-side panel. Select Data Sources.
On the Data Sources page, you’ll see many options to connect to great sources. Select Microsoft Dynamics CRM.
Once the “Create Dynamics CRM Source” page is loaded, be sure to select the Cloud option. You should see a few changes to the form made between the On-Premise and Cloud options.
From here we will fill out the form:
From this point please test hit “Test Connection.” After a few seconds you should get a message seen below. If not, please confirm that all of the connection fields are set correctly.
From here we will move over to the OData tab. This tab will need to be populated with some info to properly configure an OData connect. First, let’s select “Configure Schema.”
On the Configure Schema page, select DYNAMICSCRM from the ‘Select Schema’ pull down.
From here you can decide to map which ever of the tables or objects available to you from Dynamics. As a note, this holds true for all of the DataDirect Cloud OData connections that establish.
For the purpose of this tutorial, we will just use ACCOUNT. Select the ACCOUNT table by clicking the circle to the right of the name. It should turn blue with a checkmark once selected. Once that is select, click Save & Close.
You will be returned back to the OData settings. Now you will see that the Schema Map field is populated.
At this point, D2C is configured to open an OData endpoint against your Dynamics data. To be sure that the OData calls are working, I always like a quick test. Copy the string under Access URI. This is the OData endpoint which tool will hit. Paste this into a new browser tab and hit enter. You will be prompted to Authenticate. Enter your DataDirect Cloud User Name and Password.
Once authenticated, the response you should see is XML with Service and Workspace info populated. You will notice the the collection ACCOUNTS appears. This is the same table that we selected while configuring the Schema. At this point we are ready to configure Tableau to consume the Dynamics data via OData.
Open Tableau. As always, at the launch screen, you’ll get several connection options. One of which is OData, which can be found under More Servers.
Once selected, the OData Connection prompt will appear. It will need a few bits of info:
After a few seconds Tableau will populate metadata about the table. More specifically it shows the available columns from that table. Select Sheet 1 towards the bottom and you are free to make great visualizations.
By following these steps you will be able to connect your Microsoft Dynamics CRM to Tableau via OData. Enjoy!
Julien Mansier is a Sales Engineer at Progress. Prior to his time as an SE, he was a developer focusing mostly on testing and fault-tolerance. Julien’s interests include OData, Mobile Development (iOS), and IoT; in fact, he can often be found tinkering with his Raspberry Pi. Julien strives to utilize his experience and technical prowess to develop high-performance applications.
Julien Tweets @JulienMansier.
Copyright © 2018 Progress Software Corporation and/or its subsidiaries or affiliates.
All Rights Reserved.
Progress, Telerik, and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks for appropriate markings.