my concern is about saving tabular cube data before processing, to have a possibility to go back to a version of data when needed, to be able then to compare between different versions in a power bi report.

what is the best way to do this ?

Thank you !

i thought about adding a column to the fact table where the version name is saved, but that would increase memory usage in the cube, not efficient as the number of versions grow.. expecting a more efficient way to do this.

CodePudding user response：

You can backup and restore data from SSMS. This is a good article about it: https://www.mssqltips.com/sqlservertutorial/3614/sql-server-analysis-services-backup-and-restore/

If you are just comparing a data refresh to the previous version, you might process the model in Visual Studio, and compare that version to the deployed database. You can get the server name for the VS development copy from Solution Explorer, clicking on Model.him, and scrolling to the bottom of the Properties pane to the development server name. Then connect to both databases for your compare. If you want to compare to an older version, I’d restore a backup to a dev server.

How to do the compare? It depends on the amount of data. For tables < 1 million rows, I’d probably dump them to Excel and compare them there. I’m not aware of any tools to help with this. For our models, we just use our normal validation reports and point one at the deployed copy and one at our development copy and eyeball the changes.

If you want to compare metadata, see http://alm-toolkit.com/. It does everything you could want.

CodePudding user response：

i thought about adding a column to the fact table where the version name is saved, but that would increase memory usage in the cube, not efficient as the number of versions grow.. expecting a more efficient way to do this.

Well that's how you do it. If you want the users to be able to write reports that compare two versions, then the versions have to be part of the same model.