Microsoft Dynamics 365 | Integration, Dataverse...

Using Data factory to pull data from Dynamics

(0) Share

Report

Posted on by Data_Newbie0412

Data newbie here**

Currently, to run analytics report on data pulled from Dynamics 365, I use Power BI. Issue with this is, I've noticed Power BI is quite slow processing large data. I carry out a number of transform steps (e.g. Merge, Join, deleting or renaming columns, etc). So, when I try to run a query in Power BI with said steps, it takes a long time to complete.

So, as a solution, I decided to make use of Azure Data Factory(ADF). The plan is to use ADF to pull the data from CRM (i.e. Dynamics 365), perform transformations and publish the data. Then I'll use Power BI for visual analytics.

HI am trying to make use of the Dynamics 365 connector, but I am getting stuck on the authentication type, when I'm trying to set up a new linked service - as I don't know what to use. I'm following the 'Use Multi-Tenant Server-to-server authentication' guideline, but this process is looking so long. I suspect this is not something I should be worried about.

My question is:

What connector do I use? Basically, how do I connect to Dynamics CRM? I am trying to make use of this resource (https://docs.microsoft.com/en-us/azure/data-factory/connector-dynamics-crm-office-365). But it raises too many questions.

What azure service will I need in addition to Data Factory? Will I need to store the data I pulled from CRM somewhere - like Azure Data Lake or Blob storage? Or can I do the transformation on the fly, right after the data is ingested?

Initially, I thought I could use the 'copy' activity to ingest data from CRM and start playing with the data. But using the copy activity, I needed to provide a sink (destination for the data. Which has to be a storage of some sort). I also thought, I could make use of the 'lookup' activity. I tried to use it, but getting errors (no exception message is produced).

I have scoured the internet for a similar process (i.e. Dynamics 365 -> Data Factory -> Power BI), but I've not been able to find any.

Most of the processes I've seen however, utilises some sort of data storage right after data ingest.

All response welcome. Even if you believe I am going about this the wrong way. Thanks.

I have the same question (0)

All responses (4)

Answers (0)

Jason Jiang 20 on at

Like (0)

Report

Hello, I am trying to do a similar operation.

I am going to sync customers and items from and to Salesforce and PDM.

I think you should connect BC by Odata connector.

Was this reply helpful? Yes No
Linn Zaw Win 3,407 on at

Like (0)

Report

I have never done anything like that personally but I have seen some articles about it.

Did you happen to come across those links?

www.sqlservercentral.com/.../extracting-data-from-common-data-services-microsoft-dynamics-365-using-azure-data-factory

crmchap.co.uk/.../

Was this reply helpful? Yes No
Suggested answer

Zaidel Zertuche M 187 on at

Like (0)

Report

Hi Data_Newbie0412 first of all I'm not totally sure if your are in the best path to achieve your requirement, which I understand that is:

You want to analyze data on PowerBI but PowerBi take too much time to made changes and update the datasets to analyze the data if you made some updates on the datasets. Then you are trying to give the data already processed/transformed to PowerBI which will only show the data in a pretty way. right?

That last is where Azure Data Factory shows up, to obtain the data from Dynamics 365 processing it and then push it against PowerBI. right?

Well, ir order to do so, you were on the right path with this article: https://docs.microsoft.com/en-us/azure/data-factory/connector-dynamics-crm-office-365 then you can use the Copy Data tool to copy data

First of all you need to create your Azure Data Factory then you can Start the Copy Data tool the "connection" that you are asking for and the one that you need to select/use is available on the step 3 of this article.

3.-On the Source data store page, complete the following steps:

a. Click + Create new connection to add a connection.

b. Select the linked service type Common Data Services. Select it from the gallery, and then select Continue.

On the New Linked Service (Common Data Serivces for Apps) page sepcify:

Name for your linked service.

Connect Via integration runtime = AutoReslveIntegrationRuntime

Deployment Type: "Online"

Uri: your Dynamics 365 instances should be https://'myorg'.crm'X'.dynamics.com

Authentication Type "Office 365" add your name and your password

click on test connection, and then select Create.

Unfortunately you will need to store the copied data to a new storage the you can transform the data and finally get the final file with the data transformed.

hope this can help you to get through this connection to transform your data and finally inject into PowerBI to graph it

Was this reply helpful? Yes No
NoemieNoemie 5 on at

Like (0)

Report

Hi Zaidel Zertuche M,

I have followed the steps above to create the linked service to connect ADF to Dynamics365.

I used the Office365 Authentication and made sure my credentials are correct and I have admin system privileges on the D365 side.

However I am facing the follow error when testing the connection "Failed to connect to Dynamics: Unable to Login to Dynamics CRM: Invalid Login Information : An unsecured or incorrectly secured fault was received from the other party."

Have you ever come across this error ?

Thanks for your help,

Was this reply helpful? Yes No