Create Reports by Connecting Microsoft Dynamics 365 to Tableau

Microsoft Dynamics 365 is a set of business applications for enterprise resource planning (ERP) and customer relationship management (CRM).  The Progress DataDirect Microsoft Dynamics 365 driver supports the following Microsoft Dynamics 365 applications:

• Microsoft Dynamics 365 for Customer Service
• Microsoft Dynamics 365 for Field Service
• Microsoft Dynamics 365 for Marketing
• Microsoft Dynamics 365 for Project Service Automation
• Microsoft Dynamics 365 for Sales
• Microsoft Dynamics 365 for Finance
• Microsoft Dynamics 365 for Retail
• Microsoft Dynamics 365 for Supply Chain Management
• Microsoft Dynamics 365 for Human Resources

1. Download the Progress DataDirect Microsoft Dynamics 365 connector 
2. Installing the Driver
   • If you are on Windows, extract the PROGRESS_DATADIRECT_JDBC_DYNAMICS365_6.0.0_WIN.zip and run the PROGRESS_DATADIRECT_JDBC_INSTALL.exe to install the JDBC driver.
   • If you are on Linux, extract the PROGRESS_DATADIRECT_JDBC_DYNAMICS365_6.0.0.zip and run the PROGRESS_DATADIRECT_JDBC_INSTALL.jar to start the installation.
3. After you have completed the installation, you can find the Progress DataDirect Microsoft Dynamics 365 connector in the following locations:
   • On Windows, you will find it at
     C:\Program Files\Progress\DataDirect\JDBC_60\lib\dynamics365.jar
   • On Linux, you will find it at /home/<username>/Progress/DataDirect/JDBC_60/lib/dynamics365.jar

1. Progress DataDirect Microsoft Dynamics 365 connector comes with a JDBC Configuration Manager that helps you configure the connection to your Microsoft Dynamics 365 instance easily.
2. To Launch the Microsoft Dynamics 365 Configuration Manager, double click the dynamics365.jar or run using java -jar dynamics365.jar.
3. You should now see the below configuration manager launched in your browser.

   

4. Populate the “Service URL” textbox with the URL to the root of your OData API.
5. Next, choose the Authentication Method you want to use from the drop down.The Microsoft Dynamics 365 driver currently only supports “OAuth2” authentication, so enter your username and password. The following fields are required in order to authenticate and retrieve an OAuth token.The image below depicts the values populated in the required fields.
   • Token URI, Client ID, Client Secret and OAuth Scope

   

6. Click on “Fetch OAuth Token”.Once logged in, values for “Access Token” and “OAuth Refresh Token” will be automatically populated.You will need to have a valid Microsoft Dynamics 365 account in order to authenticate.
7. Click on “Test Connect”. Now you should see the list of tables exposed by the Progress DataDirect Microsoft Dynamics 365 driver.
8. Here you will be able to write some ad hoc SQL queries to better understand the Microsoft Dynamics 365 data model.The configuration manager creates a JDBC connection string dynamically based upon the properties defined.You can copy the JDBC connection string so that you can start using the Progress DataDirect Microsoft Dynamics 365 driver with other applications or in your programs.

The Progress DataDirect Microsoft Dynamics 365 driver supports all the OData APIs that are available as services from within Microsoft Dynamics 365 along with custom OData APIs (i.e. custom objects).  The driver itself can extract, update, delete and create data using any of the existing APIs that support these functions.

Below you will find an example of using the Progress DataDirect Microsoft Dynamics 365 driver with Tableau.  Keep in mind that the Microsoft Dynamics 365 driver can be used with any JDBC (or ODBC)-compliant application. 

1. To get started, copy dynamics365.jar from the Progress install location to Tableau’s driver location
   1. For Windows: copy dynamics365.jar to C:\Program Files\Tableau\Drivers
   2. For Mac: copy dynamics365.jar to ~/Library/Tableau/Drivers
2. Open Tableau and create a new data source
   1. Select “Other Databases (JDBC)”, copy and paste the JDBC connection string create above using the Progress DataDirect Configuration Manager into the “URL” textbox
   2. Click “Sign In”, which will have the driver connect to Microsoft Dynamics 365, normalize the data and Tableau will then display the relevant metadata (below). 
      
   
   
3. Creating reports in Tableau is easy once you can access the data that is important to you.Below is a report that compares the total actual cost of campaigns versus the budgeted cost.This data is found by relating the “LEADS” table with the “CAMPAIGNS” table.
   
   

We hope this tutorial helps you to get started with the Progress DataDirect Microsoft Dynamics 365 JDBC connector and connect to your data in Microsoft Dynamics 365. Feel free to contact us if you have any questions about using the driver and we will be happy to help you.