Mixed refresh schedule to append current data with annual archive

[MarkPalmberg]

I have a report of financial transactions that's sourced from a SQL query that unions data from an archive table -- which is refreshed once a year on July 1 via a SQL stored proc -- with transactions for the current fiscal year.

I'd like to read from the archive table only once a year on July 1. The current data would still refresh daily. I'm looking for strategies/best practices for how to combine these mixed refresh schedules and lighten the daily load on the SQL server. A dataflow that runs on July 1 to refresh a table in the service which I then append the current data to in my pbix...?

[MarkPalmberg]

I guess I'm still going to need to employ Power Automate to do the annual refresh. 🤔

[danextian · Accepted solution]

hi @MarkPalmberg 

By default, all tables in a semantic model are evaluated during a refresh unless a query is explicitly excluded. Your options are:

• Exclude the query mentioned in your first paragraph from the refresh and manually trigger its refresh from the desktop when needed then publish the model.
• Store the query results in a dataflow and connect your semantic model to that dataflow instead. Refresh the dataflow only when necessary, ensuring that the semantic model refreshes the dataflow results rather than querying SQL directly.

Note: dataflows require at least a pro workspace.

More information on dataflows -https://learn.microsoft.com/en-us/power-bi/transform-model/dataflows/dataflows-introduction-self-ser... 

[MarkPalmberg]

Thanks, @danextian . I think this is probably my path forward:

---

@danextian wrote:

• Store the query results in a dataflow and connect your semantic model to that dataflow instead. Refresh the dataflow only when necessary, ensuring that the semantic model refreshes the dataflow results rather than querying SQL directly.

---

We do have a capacity that allows this process.

I appreciate your reply.