Online Data Provider: Microsoft Dynamics 365 Sales CRM

[On-prem release: 2022 Winter]
[On-prem build: 21.11.29002]

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:

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:

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

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

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

Go to API permissions and select Dynamics CRM...

...to add the user_impersonation permission:

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:

In Settings, go to Application users:

Search for the application you created in Azure:

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

Locate the System Administrator role and save the application user:

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:

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:

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:

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:

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