Data Integration. How to load into existing entity

[AndrewButenko]

We use SQL server data source through data gateway.

I can query my on-prem dataset and would like to load my list if people/employees/users into standard/system entity person/user/systemuser.

When I load that query data into custom entity I can choose key fields and populate data using option "Only load new or modified rows".

But I can't load my query into standard entity. First of all, list of standard entities which I can see in PowerApps Entites list does not correlate with scema https://github.com/Microsoft/CDM/tree/master/schemaDocuments/core

I am administrator of my environment and have enough priviladges to configure the solutions in Dynamics Admin section.

So, first questuion why I can't see half of standard entites in Power App?

Back to schemaDocuments. There are 2 entites for User:

User	core/applicationCommon/	Person with access to the Microsoft CRM system and who owns objects in the Microsoft CRM database.	Docs
User	crmCommon/service/	Person with access to the Microsoft CRM system and who owns objects in the Microsoft CRM database.	Docs

Meanwhile, I can see only User with Type=Standard, and Tag=System. Ok, may be I can use it as my people collection.

But when I try to load data into existing entities, I can't see User entity, but SystemUser one.

Second question - where is User entity?

Ok. May be I can use SystemUser, but when i try to map fields for the entity I can not choose key fields. All of them are grayed out as shown in attachement.

Third question - why I can't choose key fields?

Ok, for now I can delete existing data and load without primary key, but that is not right approach in the future.

Anyway when I click Done and tried to load one table into that entity I have got error:

Something went wrong. (Job ID: 4c32ae06-56a6-408b-b33b-a880851f480f)

Well, it was not descriptive and did not help.

BTW, using that Map entities dialog is not convenient when you have many entities. Need to scroll down, up.

Please add vertical scroll for Queries column.

Also, is there API to load/copy entities? I do not want to map all entities manually accross dev/test/prod environments or projects.

[v-micsh-msft]

If the standard entity is used here, then the SQL Table should have the same schema defined within it.

The schema could be referenced through the Common Data Excel connector:

Import or export data from Common Data Service for Apps

Working with data in the Common Data Service for Apps using the Excel Add-in

To answer your questions, 

1. first questuion why I can't see half of standard entites in Power App?

I think the issue here should be the default solution will not include all the entities listed, 

2. where is User entity?

If you would like to explore other available entities,  You can install a model-driven app called the Metadata Browser which will allow you to view all the hidden entities and metadata properties that are found in the system. More information: Dynamics 365 Customer Engagement Developer Guide: Browse the metadata for your organization

3. why I can't choose key fields?

If here you means the Primiary key, I think that is pre-defined for the standard entity,

 Is there API to load/copy entities? 

I don't think there is an API that will map the data automatically, at the current time.

Regards,

Michael

[AndrewButenko]

Hi Michael,

Let me rephrase my questions.

My environment Data\Entities menu item reflects 2 types: Stadard and Custom ones.

My environment Data\Option sets reflects 3 types: System, Standard snd Custom ones.

I can't see how these entites and option sets correlate with predefined schemas avaialble in Entity Navigator (aka Metadata Browser).

I want to start with very first collection of users (company employees, persons) which can be maped by one of the predefined entities. Not sure which entity perfectly fits with my collection, but it must be one of these:

• ActivityParty (Person or group associated with an activity. An activity can have multiple activity parties.)
• Contact (Person with whom a business unit has a relationship, such as customer, supplier, and colleague)
• User (Person with access to the Microsoft CRM system and who owns objects in the Microsoft CRM database.)

I am confused, expecting more generic entity like Person. But anyway, let's say User from core/applicationCommon/ is the best candidate for my company employees' collection. So, I want to reuse core User schema (not custom one!).

You mentioned that not all standard schemas are exposed by default and I have to use this Excel Add-in to be able mapping my entity. But that tool works only for visible entities where I can see only Contact and User standard ones.

BTW, that Excel Add-in is not allowed for Option sets, which means I can't reuse existing ones and have to create custom ones with manual entries in browser.

As for key field, yes I mentioned Primiary key, but Data integration project has the only definition "Key fields" which can be used in Loads Mode = "Only load new or modified rows". So, all key fields are disabled in that Field mapping section and can't be chousen manually in Power query wozard. Excel Add-in for User entity does not allow defining the key fields as well. So, the only load option I have at the moment is "Delete existing data on load", which is not acceptable.

There are many other unresolved issues (no API, not migration between environments, etc.), but I want to get the clear picture before moving on.

I think Power Apps entity mapping is not ready yet (as mentioned here) and there is no progress in that direction in last 12 months. As a result, we are not confident to migrate from PBI Desktop to Power Apps yet