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

Create history (weekly snapshots) of table

Dear community,

 

we have a custom table with business critical data. In order to create better reports I want to have historical data for this table. Thus, I need to create regular snapshots. Is there a go to solution for this kind of problem, maybe Azure where I can just click together a workflow to create weekly snapshots?

 

I am currently thinking about just creating the same table in dataverse with an additional date time field containing the export date and regularly copying it over with power automate.

 

Another approach might be storing the data in Excel files.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Fubar
Solution Sage
Solution Sage

Look into Export to Data Lake (under Data in the make.powerapps.com), it uses the Entity change tracking to trickle timestamped updated data to a Data Lake and then you can report on the Data Lake data (as everything is timestamped you can do daily, weekly etc)

https://docs.microsoft.com/en-us/powerapps/maker/data-platform/export-to-data-lake

View solution in original post

5 REPLIES 5
ChrisPiasecki
Super User
Super User

Hi @kv,

 

You can create a hierarchical (self-reference) relationship and add your extra snapshot datetime field instead of creating a duplicate table. You can copy all the fields using Power Automate.

 

Your other option is creating the duplicate table and use Dataflows to copy it over.

 

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

Fubar
Solution Sage
Solution Sage

Look into Export to Data Lake (under Data in the make.powerapps.com), it uses the Entity change tracking to trickle timestamped updated data to a Data Lake and then you can report on the Data Lake data (as everything is timestamped you can do daily, weekly etc)

https://docs.microsoft.com/en-us/powerapps/maker/data-platform/export-to-data-lake

View solution in original post

dpoggemann
Resident Rockstar
Resident Rockstar

Hi @kv,

 

Just a quick note, you probably know this, but auditing is configurable for every table / field in the table that will track changes over time.  It will identify if the record was created, updated or deleted.  The challenge I have seen with this is reporting so it would take a bit to look into but with the Dataverse this is a great feature that is configurable and built out of the box for all tables.

 

https://docs.microsoft.com/en-us/powerapps/developer/data-platform/auditing-overview 

 

Hope this helps.   Please accept if answers your question or Like if helped in any way.

 

Thanks,


Drew 

Fubar
Solution Sage
Solution Sage

@dpoggemann  with audit history also have to be mindful that someone may decide to delete an old audit history partition (and then you can't get that older data)

kv
Helper I
Helper I

Data lake export seems like the way to go - will have a look into it. Thank you @Fubar!

 

@dpoggemann, using the AuditBase you can build a report around the audit logs, but as @Fubar said the audit logs might get deleted.

 

Using the hierarchical self-reference (@ChrisPiasecki) is also a nice approach, but I think you will have to be very careful not to mess up your production data.

 

Thank you for your thoughts!

 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (2,469)