Вернуться   AXForum > Microsoft Dynamics CRM > Dynamics CRM: Blogs
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

Опции темы Поиск в этой теме Опции просмотра
Старый 23.01.2018, 01:17   #1  
Blog bot is offline
Blog bot
25,475 / 846 (79) +++++++
Регистрация: 28.10.2006
powerobjects: Data Export Service: How to Handle Lookup 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!

Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
emeadaxsupport: SEPA affected objects Blog bot DAX Blogs 0 29.11.2013 13:11
NAV Team: How to: Enable Import/Export of Bank or Payroll Files Using the Data Exchange Framework Blog bot Dynamics CRM: Blogs 0 18.10.2013 02:33
atinkerersnotebook: Walkthrough & Tutorial Summary Blog bot DAX Blogs 1 09.09.2013 09:11
atinkerersnotebook: Using Service Management to Track Service Orders Blog bot DAX Blogs 1 25.08.2013 19:16
wiki.dynamicsbook: Changes Made in Navision Attain 3.60 Blog bot Dynamics CRM: Blogs 0 02.09.2008 13:23
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Часовой пояс GMT +3, время: 01:25.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.