Request For Guidance On Automating Daily Data Snapshots In Power BI With AWS Redshift

[krichmond]

I am utilizing Power BI Desktop linked to an AWS Redshift database for analytics purposes. My objective is to perform day-to-day comparative analyses on a particular data dimension to monitor changes over time. The primary challenge I face is that this dimension only displays the most current data without historical tracking. I am keen to automate the process of historical data capture within Power BI, particularly using the "Transform Data" module.

Specifically, my requirement is to automatically create a snapshot of the "Live" data dimension each day and preserve this as the "Yesterday Version," which would continuously update to reflect the data from the day immediately preceding the current one. This approach would facilitate direct comparisons between the "Yesterday Version" and the "Live" version each day.

Can you advise if Power BI provides the capability to automate this snapshot process natively, or if it requires custom scripting within the "Transform Data" module? Furthermore, if this task necessitates a workaround involving both Power BI and AWS Redshift, could you provide a comprehensive guide on how to implement this solution effectively, including any necessary scripts and scheduling tactics?

[audreygerred · Accepted solution]

The only way I have seen people keep historical snapshots is to have that in their datawarehouse as a table.