powerobjects: Data Export Service: How to Handle Lookup Columns

[Blog bot]

Источник: https://www.powerobjects.com/2018/01...ookup-columns/
==============


In this blog, we will uncover how primary fields and look up fields are created and synchronized in the Microsoft Azure SQL Database, in a customer-owned Microsoft Azure subscription using Data Export Service.

Unlike Dynamics 365 On-Premise installations where developers and administrators have some level of control on backend databases and can facilitate an organization’s reporting with ETL teams to query data directly from a SQL server for reporting, a Dynamics 365 Online implementation does not allow users to query a CRM database directly in online environments. To overcome this challenge, Microsoft has come up with a Data Export add-on service available on Microsoft AppSource that adds the ability replicate Dynamics 365 data to an Azure SQL Database. This helps facilitate reporting and allows ETL teams to consume data for reporting and analytic purposes.

The Data Export Service add-on transforms data columns while replicating data from Dynamics 365 to an Azure SQL database. Here are a few points that need to be considered:

Get Lookup Values from the Data Export Service Database

The Data Export Service synchronizes lookup fields slightly differently in the Azure SQL database tables than in the same columns created by the Dynamics 365 on-premises database.

Data Export Service creates two columns for each lookup in Dynamics 365 as opposed to three columns in the on-premises database.

1. ID – This column is similar to the id column created by Dynamics 365 in on-premises databases. It holds the GUID of the primary id of the referenced table
2. _entitytype – This column holds the entity schema name of the referenced entity. This is different from Dynamics 365 on-premises database, where the database is used to store the referenced entity’s ObjectTypecode in Type column.
3. name: Data Export Service never copies the ‘name’ column in the Azure SQL Database; so it is always empty for every lookup field.

Example: Refer to the screenshot below to compare the differences between the same CustomerId look up field created by Data Export Service in Azure SQL Database vs. the on-premises Database created by Dynamics 365.

Dynamics 365 On-Premises Database:




Azure SQL Database Look up field created by Data Export Service:




TargetMetadata Reference Tables for Lookup


The Data Export Service creates a TargetMetadata master table to hold all the referenced entities and attributes for lookup fields. Each row represents the lookup between the Primary (Referencing) entity and the referencing attribute to Referenced entity and attribute.

Example: The screenshot below shows all the referenced look ups for the contact entity. Here the CreatedBy on contact table is referencing the system user table’s systemuserid attribute.



The Data Export Service is only available for Microsoft Dynamics 365 Online, version 8.0 and onwards.

Learn more about Microsoft Dynamics 365 in the PowerObjects blog!

Happy Dynamics 365’ing!



Источник: https://www.powerobjects.com/2018/01...ookup-columns/