Setting up Online Data Provider for Microsoft Dynamics 365 Sales CRM

Requirements

  • Access to your organization's Microsoft Azure Portal
  • Access to your organization's Microsoft Power Platform
  • A running Microsoft Dynamics 365 Sales. If you don't have one, you can create a trial instance from here: https://dynamics.microsoft.com/en-us/dynamics-365-free-trial/
  • A TARGIT license for the 'Dynamics 365 CRM' Online Data Provider:

mceclip0.png

 

Register a Dynamics CRM app on Azure portal

Log in to you Azure portal at https://portal.azure.com/

Go to the Azure Directory to create a new App registration:

mceclip1.png

 

Choose a name for your app and register it as single tenant or multitenant:

mceclip4.png

 

Copy the Application ID and the Tenant ID to e.g. Notepad. Finalize this dialog by adding a secret:

mceclip6.png

 

Copy the secret value (not the ID) to Notepad:

mceclip7.png

 

Go to API permissions and select Dynamics CRM...

mceclip8.png

 

...to add the user_impersonation permission:

mceclip10.png

 

Configure the application user in Power Platform

Log in to Power Platform admin center: https://admin.powerplatform.microsoft.com/

Go to Environments and select your CRM environment. Once selected, copy the Environment URL to Notepad and then go to Settings:

mceclip6.png

 

In Settings, go to Application users:

mceclip1.png

 

Search for the application you created in Azure:

mceclip2.png

 

Select your (CRM) Business unit and click the pencil to set up Security roles:

mceclip4.png

 

Locate the System Administrator role and save the application user:

mceclip5.png

 

Load data with TARGIT InMemory ETL - Designer UI

Create or open a project in TARGIT InMemory ETL Studio and add a new data source of the type Microsoft Dynamics 365 Sales - Designer:

mceclip7.png

 

From your Notepad, fill in the corresponding fields:

  • Address. Your Environment URL (including https)
  • Tenant ID
  • Client Id is another word for Application ID
  • Client Secret

Once you connect, you will be able to select tables, columns and apply filters to this data source:

mceclip9.png

 

With this new data source in the ETL project, you can now create an Import task (and a Save task) to load data from your CRM:

mceclip11.png

Example SQL for ETL Studio:

SELECT * FROM [] (OPTIONS: depth=3)

 

In the TARGIT InMemory Query Tool, running the above project might result in a table like this:

mceclip12.png

 

Load data with OData query

This type of the data provider will allow you to extract data using the OData URI. SQL queries is not allowed here.

OData URI conventions: https://www.odata.org/documentation/odata-version-2-0/uri-conventions/

Examples:

https://[Business unit name].crm4.dynamics.com/api/data/v9.2/accounts?$select=numberofemployees,merged,opendeals&$filter=numberofemployees gt 50
https://[Business unit name].crm4.dynamics.com/api/data/v9.2/accounts?$select=numberofemployees,merged,opendeals
https://[Business unit name].crm4.dynamics.com/api/data/v9.2/accounts

 

Example SQL for ETL Studio:

SELECT * FROM [] (OPTIONS: depth=3)

 

Load data with SQL

Allows to extract data using SQL queries.

Examples:

SELECT * FROM sys.tables
SELECT * FROM sys.columns WHERE TableName = [accounts]
SELECT address1_composite, donotpostalmail, accountratingcode, numberofemployees, marketingonly, revenue_base FROM accounts
SELECT [numberofemployees], [merged], [opendeals] FROM [accounts] WHERE numberofemployees <> 50

 

Was this article helpful?
0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.