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:

1. Microsoft Dynamics CRM Account
2. Progress DataDirect Cloud Account
3. Tableau Desktop

If you need any of the required tools, please follow the links below:

1. Dynamics CRM: https://www.microsoft.com/en-us/dynamics/crm-concierge
2. DataDirect Cloud: https://pacific.progress.com/console/register?productName=d2c&ignoreCookie=true
3. Tableau Desktop: http://www.tableau.com/products/trial

Let’s Get Things Set Up

Gather What’s Needed from Microsoft Dynamics CRM

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.

Now Let’s Set Up DataDirect Cloud

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:

1. Data Source Name—You may call it whatever you like (I used MSDynamics)
2. Description—I have left it blank but you may add whatever you like
3. User ID—This is your Dynamics CRM login, not your regular Microsoft Account username
4. Password—This is your Dynamics CRM password, not your Microsoft Account password
5. Organizational Service URL—This is the URL we navigated to earlier within CRM and kept handy

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.

Configure Tableau to Consume Dynamics Data via D2C 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:

1. Server—This is the URL from D2C, just tested in browser. Note: Tableau requires the URL to point directly to the mapped tables. In our cause we will need to add /ACCOUNT to the end of the URL: https://service.datadirectcloud.com/api/odata/MSDynamics/ACCOUNTS
2. Select ‘Use a specific username and password. This will be your D2C username and password.

Hit OK.

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.

Enjoy!

By following these steps you will be able to connect your Microsoft Dynamics CRM to Tableau via OData. Enjoy!