Ingesting INSIGHTLY CRM Data onto Azure Data Lake

Sup Folks!

 On my last blog I spoke about  Integrating data onto Power BI from INSIGHTLY CRM 
(link below)

 https://nilhanperera.blogspot.com/2019/10/integrating-data-onto-power-bi-from.html

 On today's blog, lets have a look at Using Azure Data Factory (ADF) to Copy Data from INSIGHTLY CRM onto Azure Data Lake (ADL).

 Much like the previous post we need to use the API provided by Insightly to get access to data.

 Let's have a look at the steps needed to do this
Pre-requisites

1. Access to INSIGHTLY. You can sign up for a Free 14 Day Trial.
2. Subscription to Azure Data Factory 
3. Azure Data Lake Storage
4. Azure Storage Explorer

Please have a look at my previous post to get an idea on how to obtain the API key and Request URL.

Steps

1. Test the Request URL using Postman (Chrome Extension Given Here)

  • Select the GET Method and paste the request URL 

  • Select Basic Authentication from the drop down list

  • Insert the API Key as the Username and then click on Send to test

2. Go on to the header tab and copy the value of the authorization Key onto a Notepad


3. Sign into your Azure Data Factory 

4. Click on the "Connections" Tab and Create New Linked Service


5. Here you will need to make 2 Linked Services. One for REST API and the other for Data Lake
  •      1.  REST API
    • Select REST from the Data Store Tab
    • Give a suitable name > Select an Integration runtime (The default Integration runtime is used on this demo) > Insert the Request URL as the Base URL and Select "Anonymous" for Authentication type > Test Connection and Create

  • Data Lake
    • Insert the relevant information 
      • URL can be found under Properties > Data Lake Storage > Primary ADLS file system endpoint
      • Storage account key can be found under Access Keys
    • Test Connection and create (edited version shown below)


6. Click on the + sign next to the "filter resources by name" text box and select copy data



7. On Copy Data Properties Window, give a proper name and select next


8. Select the Linked Service created for the source (in this case the REST linked Service)




9. Leave Relative URL blank > Select  "Get" as Request Method>Expand additional Headers and Add "Authorization" as Name and the value you copied from the Authorization header from POSTMAN > Click on Preview and you will get a preview of data in JSON format


10. Select the linked service made for destination (Azure Data Lake in this case) > Click Next


11. Browse and select folder path > Give the file a proper name > Give a Max number of Concurrent connections if required and select next > Click Next


12. Select the preferred file format ( In this case JSON) > Click Next

13. Do the relevant schema mapping

14. Keep default settings

15. Check the summary to see if everything is in order

16. Deploy (Click Finish) 

17. On the main ADF Page expand filters, Select the Copy Data Pipeline that has been  created > Click on Debug to Run



18. The Pipeline will run successfully if no errors are found


19. You can now view the data on Azure Storage Explorer



And that's about it.

Hope this comes in handy! 

Comments

  1. Jeewaka Micheal FernandoNovember 3, 2019 at 2:43 AM

    Great Blog.. Keep up the good work 👍👍👍

    ReplyDelete
  2. Lafay Tech PlazaApril 13, 2021 at 1:42 AM

    Most of the key features of Microsoft Dynamics CRM 2016, such as Service Cloud, Marketing, and Sales, are built on Microsoft Azure. This is why it is crucial to understand the benefits of bringing CRM data to anAzure Data Lake Store. Doing so enables you to get the most out of your CRM data. When you store your CRM data in a Data Lake Store, you can query and analyze it by using SQL query language. This gives you access to the CRM data no matter where it is. You can also combine it with other data in the Data Lake Store, including data from other business applications.

    ReplyDelete

Post a Comment

Popular posts from this blog

Integrating data onto Power BI from INSIGHTLY CRM

Image
Hi Guys, The other day I was looking at possibilities to get data from INSIGHTLY and visualize it through Power BI. I was given the following link that lead me to the INSIGHTLY Developer portal, https://api.insightly.com/v3.1/Help#!/Overview/Technical_Details and I came across the following article that showed how to directly connect the REST API to Power BI, https://dev.applicationinsights.io/documentation/Using-the-API/Power-BI I followed the steps mentioned on the page associated with the link above and voila! it worked! However, the above article talks about "Displaying a time series using the Metrics API". Let ma explain how this can be applied to get data from INSIGHTLY. For this example, lets look at the steps required to get Project related data onto Power BI. Pre-requisites 1. Access to INSIGHTLY. You can sign up for a Free 14 Day Trial. 2. Power BI Desktop. Steps 1. Login to INSIGHTLY CRM > Navigate to the user menu and se
Read more

Power BI - Importing fbprophet and using Anaconda as Default Python

Image
Hi Guys, The other day, I was trying to use the fbprophet package to do a Time Series Analysis using Python  through Power BI and "Smack!", I hit a roadblock. I tried installing the fbprophet packeage using pip install fbprophet but it threw a ton of errors. So naturally I looked around on the internet and came across quite a number of forums that confirmed there was a problem when installing fbprophet through pip on Windows 10. One such link is https://github.com/facebook/prophet/issues/2 While I was reading through a few more articles I found out that by using the Anaconda platform it was possible to install and implement the fbprophet package and so I tried it out and after a few struggles I managed to get it to work! These are the steps I took, 1. Uninstalled the existing python instance/s on my machine 2. Deleted the existing application folders 3. Downloaded the graphical installer(Python version 3.7) for Anaconda from the website below https://www
Read more

Power BI- Route Map

Image
Hey Guys, It’s been awhile :p So, I was working on a little project where I wanted to show a proper route that was mapped correctly (much similar to google maps) on Power BI, however there was no visual supporting it at the time. As always, I looked into alternatives for this and I found a visual called route maps on the market place by Weiwei Cui. Figure 1 : Route Map - PBI Marketplace I was also able to find the guide for this visual (link below) https://weiweicui.github.io/PowerBI-Routemap/ This did not quite do the trick as it showed the displacement of the two inserted locations and not the route to it. Figure 2 : Incorrect Route (Displacement) I was getting a little annoyed this point, so I googled alternate options and I found out these things. The ability to create customs points on google maps The ability to export it in KML/KMZ Format The ability to convert it to an Excel file
Read more