Importing to CDS with relationships

[ScottMcCarty]

Hello!

I've got a unique problem that I've been unable to locate a soution for.  I'm working to create a dashboard of data coming to CDS from the Microsoft Graph, specificly targeting data from our Azure Active Directory instance.  I'm using the data integration facilities to accomplish the lift of info from Graph to CDS.

One of the first things I wanted to be able to show was group membership.  So, I've created the entities in CDS that consist (at a high level) of People, Groups and GroupMembership.  I've also got the appropriate relationships setup between those entities.  Now I'm at the point of pulling in the data that shows "who's in what group?" and I'm having a hard time making the data fit such that it meets the relationship criteria that was setup in CDS.

Has anyone attempted to import data that they've been able to transform to meet the relationships between the new entities?  Any help would be appreciated!

Thanks in advance,

Scott McCarty

Executive Director, IT

OPTiMO 

[v-xida-msft]

Hi @ScottMcCarty,

Do you want to import data into CDS entity with relationships?

Please check and see if the following article would help in your scenario:

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/relationships-overview

More details about importing data into CDS Entity, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/data-platform-import-export

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/data-platform-cds-newentity-pq

Best regards,

Kris

[ScottMcCarty]

Hi!  Thank you for replying.  I have reviewed those items, but they are not meeting this particular scenario (or I am missing the important nuggets).  Let me boil this down to a simple use-case of what I'm looking for:

I am building a custom, model driven app built on CDS for our management team to be able to manage particular user data, but also to be able to go easily gather information.  One basic example of this is that I would like to show user and group membership.  Using an ODATA connector to the Microsoft graph, I am able to retrieve the users, and I am also able to retrieve groups and their member IDs.  I load each into their own custom entity -- this part works well.  The problem comes in when I want to create the table that contains the many-to-one relationship from an entity (call it GroupMembers for purposes of this dicsussion), but when I attempt to load that data, I can't force the lookups to occur, nor can I reference my own pre-loaded IDs from users and groups. 

If I'm missing somethign obvious, I'd love to get your feedback.  And, if it's just not possible on the current version, I'd love to know that as well.

Thank you for taking the time to read this post,

Scott

[Drrickryp]

Hi @ScottMcCarty and @v-xida-msft and @Anonymous  and @Shanescows ,

I have encountered the same problem as you have and the above references are unsatisfactory. I asked the question again here and did not get a good answer. Visualizing-data-in-CDS-Model-driven-app Is there anywhere that gives a step by step approach to importing data into CDS entities from related tabular data (Primary and Foreign keys)  that preserves the preexisting relationships? If there is, I haven't found it.  I am trying to create a model driven app for a client from this kind of data.  In my opinion,  the inability or difficulty required to maintain pre-existing relationships is hindering the wider acceptance of Model Driven Apps in PowerApps.  If there is a method for doing this, it would make a good video presentation. 

[bettc]

Im having the same issue at the moment

how are people getting exiting app data into CDS if they cant bring in relationships?

i have near millions of PK:FK relationships so i cant use Model Driven as there is no way to import them?

that just seems a bit weird

[barlowse]

I have been unsuccessful in getting data into the cds entities where relationships are defined. The only way I got my data over was to use flow, and create new records, letting the system assign the associated guid. Really not optimal, but I was copying from a cds production environment to a development environment. Every time I tried to use the import - I got data type mismatch -string <-> guid. I assumed that it was my related entity guid...

[Dayananthan]

Exactly i also have the same question @v-xida-msft but didn't able to find any examples?

[kman42]

How did you use flow to do this? I am trying to get data into CDS and maintain relationships, but can't figure out how to get the primary key/foreign keys to map into the GUID when using dataflow. I need to refresh the data daily so I set up a scheduled refresh in dataflow, which works, but I don't have any of the relationships.

Isn't CDS supposed to integrate data from multiple sources? And shouldn't it be expected that those disparate sources will continue to generate new data that has to be refreshed in CDS regularly? I don't understand why there is no way to import data on a schedule and maintain existing relationships. What am I missing?

[barlowse]

Mine is not an elegant solution.

I have 9 CDS entities that have relationships. And I want to take the data from one environment to another. I mapped out my relationships hierarchical (see below).  My process only works if you have a field in your entity that is unique (or a combination of fields that are unique) that you can use in an ODATA filter.

Entity 1

Entity 2 relates to Entity 1

Entity 3 relates to Entity 1 and Entity 2

Entity 4 relates to Entity 3

Entity 5 relates to Entity 4

Entity 6,7,8,9 all relate to Entity 4 and Entity 5

1. I read the data from my source environment for entity 1 using the List records, for each I create a record in environment 2. New GUID is assigned to each record on create.
2. I read the data from my source environment 1 for entity 2 using the List records.
   1. For each of the records
      1. I lookup the related record on entity 1 (using Get Record) in the source environment and save off my unique identifier in a variable. (You don’t have to save it in a variable, it just helps me keep track of things.)
      2. Then I lookup the “same” record in environment 2 using the List Records by doing an ODATA filter using my unique field. This gets me my newly created GUID on entity 2 in environment 2.
      3. Then Create Record for entity 2 on environment 2 – setting the related field for entity 1 to the GUID - I just retrieved in the previous step.

  (if I have more than one related entity – I will repeat the steps above for the other entities.)

My load to environment 2 must be done sequentially based on my relationships defined above.

I must load entity 1 before I can load entity 2, and I have to load entity 1 & 2 before I can load entity 3, etc.