Extracting Data from Dynamics 365/CRM
Published:
Browse All Articles > Extracting Data from Dynamics 365/CRM
Extracting Data from Dynamics 365/CRM
When Microsoft were expanding their Dynamics CRM systems (with their ERP offering) to become the Dynamics 365 platform, they introduced a package named Microsoft Dynamics 365 Customer Engagement (Dynamics 365 CE). While it was mainly the (original) CRM, it was an excellent addition offering a range of new capabilities.
More recently, it did appear that Dynamics 365 CE was being dropped (in terms of what we always knew it to be). But it survives and is now known as Microsoft Dynamics 365 Customer Engagement (on-premises). Microsoft in their wisdom have simply added "on-premises" in brackets. For online users, there are the equivalent functions, and can be found within the Power Apps/Dataverse (once known as Common Data Service or CDS) platform as positioned in Microsoft Customer Engagement .
For simplicity, this Article will use the generic name of Dynamics 365 CE. There are links at the bottom of this Article where you can find all relevant information. Now onto the main purpose of this Article - extracting data.
There are multiple ways to extract data from Dynamics 365/CRM using Dynamics 365 CE. We will discuss the more popular. Below is the list of different methods to extract data from Dynamics 365/CRM.
The basic and advanced features are already present in Dynamics 365 CE. The more powerful is Advanced Find. It gives you great UI so that you can easily build queries for your complex conditions. You can always create and save these queries for future references as Personal Views.
You can also use Advanced Find to prepare data for export to Office Excel. You can analyse, summarize, or aggregate data, or create PivotTables to view your data from different perspectives.
Export data to a standard Excel file that you can use on any device such as your phone, tablet, or desktop computer. The data is exported in the same format as you see in Customer Engagement.
You can go to any view in Dynamics 365 CE and export the data to Excel Sheet. There are constraints on how many rows (100,000) you can export, and so, best for snapshot analysis type requirements. These are the two popular options for Export to Excel.
XrmToolbox is a tool with different plugins developed by different contributors across the globe. These plugins are used to manipulate the data as well as metadata. One tool which is specifically designed to help export the data is FetchXml Builder.
The plugin is an advanced version of Advanced Find feature already available in Dynamics 365 CE. However, it gives you more features to query CRM (and other data) for information not (easily) found in the UI.
Applications in .net can be written to connect to Dynamics 365 CE programmatically and fetch the data. Microsoft has provided set of SDK assemblies and end point in form of Web Service (organization service which is deprecated and will be discontinued at some stage in the future - there will be alternatives) and WebAPI. RetrieveMultiple() method can be used to retrieved the data by passing either FetchExpression or QueryExpression object. FetchExpression accepts a query in form of Xml which can be retrieved via Advanced Find.
Similar to custom applications described in above point, one can build SSIS package to extract data from Dynamics 365 CE. There are multiple integration tools which provide different controls/data connectors to directly fetch data from Dynamics 365 CE. KingswaySoft SSIS Integration Toolkit, Cozyroc, Devart SSIS Data Flow Components are different providers developer can work with and extract the data.
You will find all information used in this Article - including links to the online versions via :
https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/overview
https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/basics/save-advanced-find-search
https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/basics/export-data-excel
I hope you found this article useful. You are encouraged to ask questions, report any problems, or make any other comments by posting your comment below.
Note: If you need further Support about this topic, please consider using the Ask A Question feature of Experts Exchange. I monitor questions asked and would be pleased to provide any additional support required in questions asked in this manner, along with other EE experts.
Please do not forget to press the "Thumbs Up" button if you think this article was helpful.
More recently, it did appear that Dynamics 365 CE was being dropped (in terms of what we always knew it to be). But it survives and is now known as Microsoft Dynamics 365 Customer Engagement (on-premises). Microsoft in their wisdom have simply added "on-premises" in brackets. For online users, there are the equivalent functions, and can be found within the Power Apps/Dataverse (once known as Common Data Service or CDS) platform as positioned in Microsoft Customer Engagement .
For simplicity, this Article will use the generic name of Dynamics 365 CE. There are links at the bottom of this Article where you can find all relevant information. Now onto the main purpose of this Article - extracting data.
There are multiple ways to extract data from Dynamics 365/CRM using Dynamics 365 CE. We will discuss the more popular. Below is the list of different methods to extract data from Dynamics 365/CRM.
1. Advanced Find
The basic and advanced features are already present in Dynamics 365 CE. The more powerful is Advanced Find. It gives you great UI so that you can easily build queries for your complex conditions. You can always create and save these queries for future references as Personal Views.
You can also use Advanced Find to prepare data for export to Office Excel. You can analyse, summarize, or aggregate data, or create PivotTables to view your data from different perspectives.
2. Excel Sheet
Export data to a standard Excel file that you can use on any device such as your phone, tablet, or desktop computer. The data is exported in the same format as you see in Customer Engagement.
You can go to any view in Dynamics 365 CE and export the data to Excel Sheet. There are constraints on how many rows (100,000) you can export, and so, best for snapshot analysis type requirements. These are the two popular options for Export to Excel.
a. Static Excel Sheet
From : Export data to an Excel static worksheet we read :
When you go to any view and export it to Excel, it will be a Static Excel. Basically what you see in the view (the data, columns, data sorting order) will be exported as it is in the excel sheet. You can go directly to Advanced Find, select the view and edit columns to modify the columns in your desired way.
b. Dynamic Excel Sheet
From : Export to an Excel dynamic worksheet we read :
When you export a dynamic worksheet, a link will be maintained to Dynamics 365 Customer Engagement organization. So when any data is updated at the CRM level when refreshed, will be updated to the Dynamic Excel Sheet and vice versa.
3. XrmToolbox Plugin — FetchXml Builder
XrmToolbox is a tool with different plugins developed by different contributors across the globe. These plugins are used to manipulate the data as well as metadata. One tool which is specifically designed to help export the data is FetchXml Builder.
The plugin is an advanced version of Advanced Find feature already available in Dynamics 365 CE. However, it gives you more features to query CRM (and other data) for information not (easily) found in the UI.
4. Custom Applications
Applications in .net can be written to connect to Dynamics 365 CE programmatically and fetch the data. Microsoft has provided set of SDK assemblies and end point in form of Web Service (organization service which is deprecated and will be discontinued at some stage in the future - there will be alternatives) and WebAPI. RetrieveMultiple() method can be used to retrieved the data by passing either FetchExpression or QueryExpression object. FetchExpression accepts a query in form of Xml which can be retrieved via Advanced Find.
5. SSIS Packages
Similar to custom applications described in above point, one can build SSIS package to extract data from Dynamics 365 CE. There are multiple integration tools which provide different controls/data connectors to directly fetch data from Dynamics 365 CE. KingswaySoft SSIS Integration Toolkit, Cozyroc, Devart SSIS Data Flow Components are different providers developer can work with and extract the data.
Links and References used in this Article
You will find all information used in this Article - including links to the online versions via :
https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/overview
https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/basics/save-advanced-find-search
https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/basics/export-data-excel
I hope you found this article useful. You are encouraged to ask questions, report any problems, or make any other comments by posting your comment below.
Note: If you need further Support about this topic, please consider using the Ask A Question feature of Experts Exchange. I monitor questions asked and would be pleased to provide any additional support required in questions asked in this manner, along with other EE experts.
Please do not forget to press the "Thumbs Up" button if you think this article was helpful.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)