Forum Discussion
How to keep history data and never refresh
Hi,
I have a log dataset that history data never changed. It has 6+ million data. Incremental refresh is broken because of the amount of data. It was starting to give 'insufficient memory' error when schedule refresh run.
I wanted to keep historical data (SearchData-2022-1 : First 6 months of data)in a different way. I want to see all history data but I don't want to refresh them when I refresh the report. I am trying to do another history data source and append with the dataset with needed to refresh (SeachData-2022-2 : last 6 months of data). But I am not sure is this the best way to solve my problem.
(My dataset is on Azure sql server and I use power bi embedded)
Do you have any recommendation to keep unchanged big data in the report?
Thanks.
7 Replies
v-yadongf-msftCommunity Support
Hi burcubelen ,
You can choose import mode. As long as you don't run manual refresh or schedule refresh, refreshing the report won't trigger a refresh of the dataset.
Best regards,
Yadong Fang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Tutu_in_YYC
Super User
burcubelen And you can also disable the refresh of just one table if you need to.
Refer to this guide in the "Include in Report refresh" section Hi v-yadongf-msft,
Thank you for your respond.
SearchData-2022-1 and SeachData-2022-2 tables all in same columns and data types. The difference is only the time interval. I split data because of the amount of data. If I get them seperately like I said, eventually I need to append them to use in only one source. And if I select refresh only appended source, it is again refresh it from scratch. I think I can't merge all the same tables.If I use the Id column to merge, there is no common Id value in both. I hope I can explain the problem clearly.
If you have any other idea, I would happy to talk about it. I'm still searching.
Thanks
- Tutu_in_YYC
If the historical data is in SQL server, you can use hybrid mode: Direct Query the historical data and only import current data .
Thank you for quick response.
What is the advantages of using Direct Query in this situation? I only used import so I'm a little bit confused about your answer.- Tutu_in_YYC
Import will create a copy of the data and store them in the power bi dataset.
Whereas Direct Query will not keep the data in the dataset, instead it will query the data source directly when you want to view the data in Power BI report. This reduces the refresh time of the dataset.
Composite mode (using both modes) is a common approach when dealing with alot of data.
The different types of modes
https://docs.microsoft.com/en-us/power-bi/connect-data/service-dataset-modes-understand
Composite mode
https://docs.microsoft.com/en-us/power-bi/connect-data/service-dataset-modes-understand#composite-mode
