Forum Discussion
Helper IHow to automatically transfer data from a semantic model to a new table
Hi,
A PowerBI report uses an imported semantic model.
The data source of the model is being refreshed on a daily basis.
The PowerBI report needs to create visuals with trends over time.
This requires a monthly snapshot of one of the tables (e.g. current table) in the imported semantic model.
The main objective is to create a new table (complete table) in the PowerBI with the same schema as the current table plus one attribute that will hold the YYYY-MM value. In the beginning of every month, the current table should be automatically appended at the complete table. By this way, the complete table will hold all current and historic data.
I would like to ask if this is possible to perform using the PowerBI desktop or any other tools in the same family suite.
Thanks in advance for your help.
Regards,
Akis
Hi lafakios,
Power BI does not have any native option for taking snapshots of data at a given point of time. So you'll have to make use of some staging area or database where you can hold such data.
Generally, this is the capability of a data warehouse. For example, in our organization, we have a sophisticated data warehouse implementation which is being used for this purpose and a lot more.
Below are some of the solutions you can start thinking about for achieving your desired results -
- BEST: Implement an External Storage (like Azure Data Warehouse, or Azure Data Lake or Databricks) along with an ETL process (like using Azure Data Factory or python scripts) to snapshot and load data in the external storage
- Good: Power Automate - You can use Power Automate to query your data source or even your Power BI Dataset itself, take monthly snapshots, and store them in CSV or text files. You can then use this snapshot data in your report as required. The following video can be referred to for implementing this solution - https://www.youtube.com/watch?v=SOQeNnSwp60&ab_channel=NextGenBIGuru
- Not Recommended: Use Power Query and Incremental Refresh - Though the purpose of incremental refresh is to improve model refresh performance by archiving older data and refreshing a smaller range of data, it can be intelligently used as a workaround for achieving your requirements. I can explain it here, but it would be better if you could simply refer to Chris Webb's blog for this - https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-data-to-it-using-incremental-refresh/
- Can refer to this as well - https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/
So you now have a couple of ways to implement your desired solution and achieve your requirements. Implementing a Data Warehouse or proper external storage can have barriers, so I would recommend looking at the Power Automate solution more seriously.
Do let me know if you have any questions or need any help!
2 Replies
- Bipin-Lala
Solution Sage
Hi lafakios,
Power BI does not have any native option for taking snapshots of data at a given point of time. So you'll have to make use of some staging area or database where you can hold such data.
Generally, this is the capability of a data warehouse. For example, in our organization, we have a sophisticated data warehouse implementation which is being used for this purpose and a lot more.
Below are some of the solutions you can start thinking about for achieving your desired results -
- BEST: Implement an External Storage (like Azure Data Warehouse, or Azure Data Lake or Databricks) along with an ETL process (like using Azure Data Factory or python scripts) to snapshot and load data in the external storage
- Good: Power Automate - You can use Power Automate to query your data source or even your Power BI Dataset itself, take monthly snapshots, and store them in CSV or text files. You can then use this snapshot data in your report as required. The following video can be referred to for implementing this solution - https://www.youtube.com/watch?v=SOQeNnSwp60&ab_channel=NextGenBIGuru
- Not Recommended: Use Power Query and Incremental Refresh - Though the purpose of incremental refresh is to improve model refresh performance by archiving older data and refreshing a smaller range of data, it can be intelligently used as a workaround for achieving your requirements. I can explain it here, but it would be better if you could simply refer to Chris Webb's blog for this - https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-data-to-it-using-incremental-refresh/
- Can refer to this as well - https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/
So you now have a couple of ways to implement your desired solution and achieve your requirements. Implementing a Data Warehouse or proper external storage can have barriers, so I would recommend looking at the Power Automate solution more seriously.
Do let me know if you have any questions or need any help!
- lbendlin
Super User
Power BI has no memory* . You need to manage your snapshots in the upstream system. * Yes, there is the self referencing option but that has no safety net.
