(2020-Mar-30) Little did I know that I can use Azure Data Factory (ADF) to extract data from the Common Data Services (CDS). This data platform enables you to securely store and manage data that can be consumed by other business applications. You are totally free to rely on Standard CDS datasets (entities) or challenge yourself and create additional custom entities with cloud-based storage options for your data.
Microsoft also has a very good set of documentation to clarify some of your knowledge gaps (those gaps are mine too 🙂 in using Azure Data Factory with CDS. This blog post is more to save my notes that I can use later to help and remind myself about some other additional aspects in creating data integration projects with CDS.
I also would like to mention my coworker's name Dennian Clarke who helped me with finding my way around among myriads of standard and system CDS entities and showing me how to dynamically extract data from CDS using FetchXML queries.
Common Data Services (CDS) entity names:
If you have never worked with Microsoft Dynamics 365 CRM system, look at it as a scalable and secure relational database with tables (called entities). You have a UI interface to access and manage your data entities; in addition to that, data connectors are available if you want to use CRM data in your external applications.
Each CDS entity can be described by several attributes, some of those attributes are:
- SchemaName: Typically, a Pascal cased version of the logical name. i.e. Account
- CollectionSchemaName: A plural form of the Schema name. i.e. Accounts
- LogicalName: All lower-case version of the schema name. i.e. account
- LogicalCollectionName: All lower-case version of the collection schema name. i.e. accounts
- EntitySetName: Used to identify collections with the Web API. By default, it is the same as the logical collection name.
- DisplayName: Typically, the same as the schema name, but can include spaces. i.e. Account
- DisplayCollectionName: A plural form of the Display name. i.e. Accounts
- Description: A short sentence describing the entity
And that's where I was confused the most, let me give a few examples.
If I want to extract the CRM system [role] entity via ADF data connector then I will need to look for [Security Role]:
and if the point of my interest would be to check the internal system [audit] dataset, then this could only be possible if I search for the [Auditing] entity in ADF data connector:
Strange world, I know :-), and the XrmToolBox is a great tool to explain the reason seeing different names for entities. Go ahead install it, and don't forget to enable "Metadata Browser" plugin there.
It will show you that whoever worked on ADF data connector for CDS had used "DisplayName" attribute for referencing entities.
One more tip: if you try to search for particular CDS entities in Power BI, then you will need to use the "EntitySetName" attribute value to locate them, it took me some time to understand this as well 🙁
The rest is easy!
(A) Creating CDS Linked Service in ADF
To create a linked service to CDS Dynamics online using Office365 authentication you will need to set several properties: (1) Service Uri, (2) Authentication type, (3) User name, (4) Password.
(B) Creating CDS entity dataset in ADF
Then you have a choice either to create a direct reference to CDS entity (table) at the dataset level in Azure Data Factory by selecting its name in a dropdown menu; don't forget that "DisplayName" values will populate this list.
(C) Submitting dynamic XML fetch queries to CDS via Copy Data ADF activity
Or get more flexibility and submit a Fetch XML query to your CDS Dynamics 365 data repository. In this particular case, I'm attempting to extract all System [audit] entity records generated in March of 2020 and later.
Like I said at the beginning of my blog post that little did I know about Azure Data Factory support of Common Data Service (CDS) connectivity to Dynamics 365 data. It's flexible and it works!
My next request for such flexibility is to have support for Common Data Models (CDM) as a sink in Azure Data Factory. I hope it will become a (Private Preview > Public Preview > General Availability) reality one day! 🙂