cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Thoman
Helper I
Helper I

Entity unavailable while refreshing with Dataflow

I noticed something strange with my PowerApp that searches through some data that is stored in a CDS Entity. The search will return no results if a Refresh is in progress. I have a Dataflow that is pulling data from an OnPrem SQL server and it runs every 30 minutes pulling in just under 1000 records. If you happen to do a search at that time the Entity is empty. I have the setting checked to "Delete rows that are no longer returned in the query". Could this be why? If I don't have this checked, will my entity just keep adding records and have stale data after awhile? 

1 ACCEPTED SOLUTION

Accepted Solutions
EricRegnier
Super User II
Super User II

Hi @Thoman,

That is exactly why, delete rows that no longer exists option actually deletes all the records in the destination entity and then does a clean reimport. This is as design (for now) and hope Microsoft will implement a smarter mechanism to keep existing records intact.

As a workaround, any chance you can change the refresh setting to after business hours?

Hope this helps

View solution in original post

7 REPLIES 7
Rivius
Resolver III
Resolver III

I've had issues with dataflows and duplicate records myself (only with CDS to CDS across environments, I have not messed with SQL+CDS).  MS has told me it was a bug and will be fixed (sorry I don't have any links to provide backup to that).  If you're unable to make an environment to test your theory I would submit a ticket to get clarification for your specific situation.

I will open up a ticket and see what is going on. Defiantly getting multiple records when I look at the dataset through Excel.

I have noticed when you select the option to delete records that are no longer in the query, it fixes the duplicate record issue but then causes an issue what the CDS Entity is "Empty\Unavailable" while a refresh is in progress. This causes apps using the CDS to not return any records until the refresh is complete. Not ideal but a way to clean things up.

EricRegnier
Super User II
Super User II

Hi @Thoman,

That is exactly why, delete rows that no longer exists option actually deletes all the records in the destination entity and then does a clean reimport. This is as design (for now) and hope Microsoft will implement a smarter mechanism to keep existing records intact.

As a workaround, any chance you can change the refresh setting to after business hours?

Hope this helps

View solution in original post

Well that's dumb. There is a reason we set Key Fields. They should be matching against that. Unfortunately I need it to refresh every 30 minutes or so throughout the business day. So an evening refresh wont work. I wonder if I can take advantage of a Duplicate Detection Job to clean things up for me under the Environments Data Management section? If I can make that work I will post my steps here.

While it's not the greatest approach, it's not dumb as the solutions resolves edge cases where indexes may not exist and as it's run within a single transaction (note the "Entity unavailable" message when it runs) it also minimises the resources required. 

---
If this post has answered your question please consider it for "Accept as Solution" or if it has been helpful give it a "Thumbs Up".

Tomato Tamato. 😉

 

I was playing around with Duplicate Detection and Bulk Delete rules. Duplicate Detection doesn't really help since you have to manually interact with the detected duplicates. Bulk Delete rule could work but you can only schedule these to run at a minimum once a week. So then I looked into using Flow\Power Automate.

 

This might be my best option but I would like the trigger to be when a Dataflow finishes a refresh instead of when a record is created in Common Data Entity. Unfortunately it doesn't look like that option exists. So my next line of attack would be to schedule a flow to run every 30 minutes or so and then delete duplicate and\or records older than X minutes. Should be doable but still has the possibility of a random duplicate record showing up that I would have to account for in my Power App. I have a meeting today with MS Support to review still. Perhaps I just have something misconfigured someplace in my Entity or Dataflow. 

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Users online (73,299)