How can I keep historical data in Power BI?

[pthapa]

Hello Experts,

What is the best way to keep the historical data in PBI?
It seems like incremental refresh in PBI might do the job however, wanted to check with all experts in PBI.

Is there other better options available or incremental refresh is the one we should go with to hold the historical data in pbi? 

Limitations with incremental refresh is:

 1. The dataset can't be downloaded after it is published in the power bi service.
 2. Workspace should be 'pro', 'premium' or higher.

Can someone please provide some insight, that would help to all power bi community.

Appreciate your time.

Thanks

Pthapa

[Anonymous]

I have a similar problem. My data source is a published PowerBI dataset and is connected using a direct query. However, I want to maintain historical views to compare current data with historical statuses. Any idea how this can be achieved in a scenario like this ??

[AnalyticsWizard]

@pthapa 

Incremental refresh is indeed a powerful feature in Power BI designed to handle large datasets by only refreshing the most recent data and maintaining historical data without the need to reload the entire dataset. This can dramatically reduce the amount of time and resources required to refresh your reports.

However, as you've noted, there are limitations such as the requirement for a Power BI Pro license or higher, and the inability to download the dataset after it's published to the Power BI Service.

Here are a few alternative approaches to handle historical data in Power BI:

1. Snapshottin: You can create snapshots of your data at regular intervals (daily, weekly, monthly, etc.) and store them in a database or a folder as flat files. You can then load these snapshots into Power BI as separate tables and build a report that combines them.

2. Azure SQL Database: If you're using an Azure SQL Database, you can utilize features like Temporal Tables to keep historical data. Power BI can connect directly to Azure SQL Database, allowing you to leverage its historical data handling.

3. Data Warehousing: A traditional data warehousing approach involves ETL processes to transfer data into a data warehouse. The data warehouse is designed to store historical data efficiently, and Power BI can connect to it for reporting.

4. Parameterized Queries: If historical data is identified by date ranges or other parameters, you can use Power Query parameters to define the scope of the data you're loading into your model.

5. Hybrid Tables: With Power BI Premium, you can use the hybrid tables feature, which allows you to have a DirectQuery part for the most recent data and an Import part for the historical data.

6. Custom Storage Mode: Create aggregations in Power BI for historical data at a higher granularity and use DirectQuery for detailed, recent data. This is only available in Power BI Premium.

Each of these alternatives has its own set of requirements, benefits, and limitations. The best option for you will depend on factors such as the size of your datasets, the nature of your historical data, your organization’s infrastructure, your budget for Power BI licenses, and your technical capabilities for setup and maintenance.

Before deciding on a strategy, you should evaluate the importance of historical data in your reports, the frequency of access to historical data, and how up-to-date the data needs to be. This evaluation will help you determine the most cost-effective and efficient method for your situation.

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

[pallavi_r]

Hi @pthapa , 

What is the size of dataset? If the volume of dataset is high and then we need to go for incremental refresh.

There are some workaround too which can be done without incremental refresh enabled.

• We can keep 2 tables in power bi, one table we can disable refresh and other table refresh 2 weeks data. Combine these 2 table for reporting. can you try this and let me know if facing any issue. Please do share the sample pbix file so can provide details solution.

Thanks,

Pallavi

[pthapa]

Hello collinq,

Thanks for your concerns on my topic. Here are my comments.

What I mean by keeping historical data is that; yes, my data changes over time and I only want certain time lenght of data to change not the entire 2 years of data in power bi keeping hisotocial data.

No my older data will not be eliminated. 

Not sure if I understood your last question, here are my thoughts to that;

Yes, I want to keep 2years (or maybe more) data to store in pbi however not refreshing all 2 years of data when data refreshes. My older data on the source will not get removed. I want to keep getting 2 years worth of data and only allowing 2 weeks of data to refresh. This  is exactly performed in incremental refresh. 

I am wondering if there is other options available that might be better; that's all.

Hope I answered your concerns.

Thanks

pthapa

[pthapa]

Appreciate your input.

Thanks,

pthapa

[collinq · Accepted solution]

Hey @pthapa ,

Based on your response, incremental refresh will probably be the best option.  You can use incremental refresh on any date/time field.  Just be sure to understand the "policy" part of the incremental refresh to make sure it is getting the right time period.

[pthapa]

Any idea why I am getting 'A cyclic reference was encountered during evaluation' upon applying filter on datetime field with RangeStart and RangeEnd parameter in PBI?

Source of my data is sql server and using stored procedure.

thanks,

pthapa

[collinq]

Hi @pthapa ,

I am wondering what you mean by "keeping historical data"?  Does this mean that the older data gets modified but you do not want to change it from what it was?  Or, do you mean that the older data's data source is eliminated and if you have not saved that then it is gone forever?  

Since you mentioned incremental refresh - do you just mean that you want to keep adding the new data from the same data source and that data source does not every "remove" or "lose" older data?