Learn more
 
 

by Jerod Johnson | November 18, 2020

Drivers in Focus: Microsoft Dynamics 365

Microsoft Dynamics 365 Logo

Microsoft Dynamics 365 is a set of intelligent business applications that helps you run your entire business and deliver greater results through predictive, AI-driven insights. With a suite of available services for finance, sales, CRM, ERP, and accounting, Dynamics 365 powers core business operations for thousands of organizations across the globe.

The CData Drivers and Connectors for Microsoft Dynamics 365 offer unmatched integration possibilities when working with Dynamics 365 data. Our standards-based approach to data connectivity ensures that your Dynamics 365 data is accessible from any application or tool, including BI, ETL, data management, data prep, data science, integration, AI/ML, and much more. No matter which Dynamics service you use or where you want to work with your data, CData has the connectivity solution for you.

In this post, we walk through popular use cases for our Dynamics 365 connectors, elaborate on the specific features we support for each Dynamics 365 service, and highlight the different connectivity solutions we offer.

Microsoft Ecosystem Integration

Our Dynamics 365 connectors are used by our customers to solve a wide variety of integration problems, from those who wish to build dynamic dashboards on top of Dynamics 365 data to those who want to consolidate data access through federation or replication. Two common use-cases are outlined below: building data flows with SSIS and consolidating data access through ODBC.

Building Live Dynamics 365 Data Flows in SSIS

One of the most frequent use-cases we see with our Dynamics 365 connectors is in SQL Server Integration Services (SSIS), a platform for building enterprise-level data integration and data transformations solutions. When paired with the CData SSIS Tasks & Components for Dynamics 365, users can create data flows with access to live Dynamics 365 data and easily migrate data from Dynamics 365 to SQL Server. Once in SQL Server, the Dynamics 365 data can be enriched and combined with data from across the enterprise for 360 degree analytics, reporting, and more.

Creating a data flow to work with Dynamics 365 data in SSIS consists of 3 basic steps:

1. Connect to Dynamics 365

CData connectors provide a live, secure connection to Dynamics 365 using native Dynamics authentication. For Dynamics 365 Sales, the connectors use OAuth, meaning you authenticate using the Dynamics 365 website to provide access to your data.

2. Connect to SQL Server

SSIS is built to work with SQL Server instances, on-premise or in the cloud. Simply provide the server address and authentication scheme to connect to your SQL Server instance. In this example, we use Windows authentication.

3. Build a Data Flow

After establishing connections to Dynamics 365 and SQL Server, you can build a data flow to move your Dynamics 365 data into SQL Server to conduct analysis and uncover insights from your data.

With these three basic steps, you have a data flow task for migrating your Dynamics 365 data into SQL Server. The article linked below discusses Dynamics 365 Sales, specifically, but the principals can be applied to any of our Dynamics 365 connectors.

Jump to the Knowledge Base Article »

Consolidated Data Access through SQL Linked Servers

Another popular connection we see is using the CData ODBC Driver and SQL Gateway to create a Linked Server inside of SQL Server to Dynamics 365 data. With a linked server, you get access to live (not replicated) Dynamics 365 data alongside your SQL Server data, enabling real-time reporting and analytics on top of Dynamics 365 data. Below, you can watch a video walkthrough of creating a linked server, again in three basic steps:

  1. Connect to Dynamics 365 using the CData ODBC Driver
  2. Connect to the CData ODBC Driver with the SQL Gateway
  3. Create a linked server in your SQL Server instance



You can also read through the full walkthrough in the Knowledge Base article linked below.

Jump to the Knowledge Base Article »

Service-Specific Features

CData provides connectivity for the major Dynamics 365 services: Sales, Business Central, and Finance & Operations. Each of the CData connectivity solutions is specifically engineered to work with that service, providing high-performance connectivity to the broadest spectrum and most specific drilldown of Dynamics data. In addition to the specific features listed below, all the CData solutions support Dynamics-specific annotations that provide information about the readability and precision / scale of fields within Dynamics 365 entities. This support provides a seamless experience when working with Dynamics 365 in data-centric tools, applications, and platforms.

Dynamics 365 Sales Features

The CData Dynamics 365 Sales solutions provide SQL connectivity to your sales data. The drivers and connectors include:

  • Support for standard entities, such as sales orders, accounts, products, etc.
  • Support for server-side paging, providing faster access to your data

Dynamics 365 Business Central Features

The CData Dynamics 365 Business Central solutions provide SQL connectivity to your business data. The driver and connectors include support for:

  • The Web Services endpoint, including custom endpoints for user-configured collections and queries
  • The Common Services endpoint, including endpoints for Common Data Model entities
  • Test and production service endpoints
  • Sandbox accounts
  • Server-side paging, providing faster access to your data

Dynamics 365 Finance & Operations Features

The CData Dynamics 365 Finance & Operations solutions provide SQL connectivity to your accounting data. The drivers and connectors include support for:

  • Standard collections including sales orders & sales order lines, bank accounts, user groups, and more
  • Custom collections, providing configurable views of your data
  • Client-side paging, allowing the CData connectors to optimize performance

Available Connectivity Solutions

CData offers connectors for Dynamics 365 data that allow you to connect to any tool, application or platform that supports standard, SQL-based connectivity. We also offer a wide range of standalone tools to make working with your Dynamics 365 data easier. Whether you want to work in BI, reporting, or ETL tools using JDBC Drivers or ODBC Drivers, work with live data in Microsoft Excel or Power BI, build data flow tasks in SSIS, or create custom applications and scripting using ADO.NET Providers or Python, CData has the connectivity solution for you.

Additionally, if you are looking to easily replicate your Dynamics 365 data into a data warehouse (on-premise or cloud-based), CData Sync is the tool for you. With customizable, scheduled replication, you can automatically pipe Dynamics 365 data into your database when and where you need it.

More Information & Free Trials

For more information and to download a free, 30-day trial of any of our solutions, check out the the pages for each Dynamics 365 service offering and see the CData difference for yourself: