Microsoft Dynamics CRM to Tableau via OData

Microsoft Dynamics CRM to Tableau via OData

Posted on April 06, 2016 0 Comments
Microsoft Dynamics CRM to Tableau via OData 870x220

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.

Microsoft-Dynamics-CRM

From here we will want to select Settings, then Customizations.

Customizations

We are interested in some of the content within Developer Resources. Select this.

Developer Resources

The information we need is under Organizational Service. The Endpoint Address is what DataDirect Cloud will use to ping Dynamics. Keep this URL handy.

Organizational Service

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.

DataDirect Cloud

On the Data Sources page, you’ll see many options to connect to great sources. Select Microsoft Dynamics CRM.

Data Stores

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.

CRM Data Source

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

Edit CRM Data Source

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.

Cloud Dynamics Data Test

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.”

OData Configure Schema

On the Configure Schema page, select DYNAMICSCRM from the ‘Select Schema’ pull down.

DynamicsCRM Schema

 

 

Select Schema Close

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.

Configure Schema

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.

14 - Account

You will be returned back to the OData settings. Now you will see that the Schema Map field is populated.

OData Settings

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.

DataDirect Authenticate

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.

Code

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.

OData Connection

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.

DataDirect OData Connect

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.

Visualizations

Enjoy!

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

 

jmansier

Julien Mansier

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.

Comments

Comments are disabled in preview mode.
Topics

Sitefinity Training and Certification Now Available.

Let our experts teach you how to use Sitefinity's best-in-class features to deliver compelling digital experiences.

Learn More
Latest Stories
in Your Inbox

Subscribe to get all the news, info and tutorials you need to build better business apps and sites

Loading animation