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.
- <Lookupfield>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
- <Lookupfield>_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 <Lookupfield>Type column.
- <Lookupfield>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’s New Power Apps Makes Building Business Apps Easier
Power Apps is a new service for building and using custom business apps that connect to your data. It also works across the web and mobile - without the time and expense of custom software development. Today's blog will walk you through the process of generating an app using a trial version of Dynamics 365 online.
Let's start off with a little background. Power Apps connects to existing data sources and services including Dynamics 365, OneDrive, Microsoft Azure, SharePoint, Salesforce, Excel, Google Drive, and Dropbox, to automatically generate a meaningful business app for your business. You can instantly publish apps via web and mobile including laptops, tablets, and phones (Android, iOS, and Windows). It also allows you to work in a visual designer to customize a fully-functional app and build apps that use device capabilities including cameras, GPS, and pen control. And the best part is that you get all of this without having to code!
Power Apps Works for Employees and Developers
Perks for employees:
- Quickly create apps that work on any device using templates to get started quickly and a visual designer to automate workflows
- Use built-in connections or ones customized by your company to connect Power Apps to cloud services such as Office 365, Dynamics CRM, Salesforce, Dropbox, OneDrive, and on-premises systems including SharePoint, SQL Server, Oracle databases, SAP and more
- Share Power Apps like documents. It's as simple as typing an email address and your coworkers can take advantage of an app you created
Perks for developers and IT professionals:
- Power Apps includes Azure App Service for employee-facing apps so native web and mobile apps get into employee hands faster than ever
- Build additional data connections and APIs to any existing business systems, thus empowering any users in your organization to create the apps they need
- Data security and privacy controls are respected by PowerApps, so you can manage data access and maintain corporate policies.
Generating an App Automatically
You can quickly generate apps using data sources such as:
- Common Data Model
- Dynamics CRM Online
- OneDrive for Business
Generating an App Using a Trial Version of Dynamics 365 Online:
Open Microsoft PowerApps and Select New
|Select Dynamics 365|
Select New Connection
Select Dynamics CRM Online & Select Connect
|Select Power Objects (Note: this will connect to the Dynamics CRM Instance associated with you Power Apps login)|
Displays all entities for the CRM instance
|Select an Entity & select Connect (Note: for this blog, I have chosen the contacts entity)|
|Building the Phone App Templates|
Power Apps OOB display 3 Screen templates
- Browse Screen
- Detail Screen
- Edit Screen
Each Template will have multiple corresponding Screen Layouts.
- Browse Items, One line Description
- Browse Items, One line Description, Rating
- Browse Items, Thumbnail image, Header and Description
- Browse Items, Header and Description
- Browse Items, Header, Sub Header and Description
- Browse Items, Header, Sub Header, Description and Footer
- Browse Items, Header, Description and Link
- Browse Items, Grid with Pictures
- Browse Items, Header, Pictures, Description and Footer