Data continuity

[Petanek333]

Hi,

I would like to advise a best practice procedure for maintaining data continuity when working with databases.
I'm getting data from SQL server and the databases there are divided into years.
Now as the year 2022 has ended, it is about to go to a new database for 2023 accounting wise.
I would need the existing 2022 database to be loaded in the model, when refreshing it would no longer update and the 2023 database would be added to it and updated.
So I would like to ask if it is best practice to have the 2022 database without enable load option and append the 2023 database to it, or if this is handled some other way.
Thank you

Translated with www.DeepL.com/Translator (free version)

[BA_Pete · Accepted solution]

Hi @Petanek333 ,

Firstly, if you have any power over the database structure, you should be getting rid of the year partitions. It's generally poor database structure and causes exactly these types of problems.

Due to the fact that you have partitioned databases your query isn't going to fold however you cut it, so I think your 2022-Disable load/append to 2023 is as good a solution as any. However, I would probably suggest pushing this build process to a Dataflow so PQ isn't trying do it all each time your report refreshes. This also gives you a pre-made source for any future reports.

Pete

[Petanek333]

Thank you @BA_Pete , unfortunately I don't have any power over the database. I am not familiar with dataflow yet, so a great tip for what to learn.