Forum Discussion
Reversed hybrid table with incremental refresh
Hi dmkblesser ,
Thanks for your response. You're looking for a Reverse Hybrid Table where recent data is in Import mode and historical data is in DirectQuery.
Have you tried this approach.
-
Set up Incremental Refresh in Power BI to create partitions.
-
Use Tabular Editor or TOM API to convert older partitions to DirectQuery.
-
Apply dataCoverageDefinition to specify which partitions should be Import vs. DirectQuery.
-
Validate Queries to ensure smooth performance.
Have you tried modifying partitions after setting up incremental refresh? Let us know where you're facing challenges.
Helpful References : Use hot and cold table partitions to optimize very large Power BI data models | Microsoft Learn.
Regards,
Yugandhar.-
Hi! we have tried to implement the hybrid model. The idea behind this was that one partition would include recent data no longer than 2 years, and the second partition would include data that is older than 2 years.
1 fact table multiptple partitions
1) Recent data less then 2 years - Import mode
2) Data older then two years - Direct mode
we have created partitions.tmdl file where we have set all partitions for the fact table (example view), like it was suggested in the article: https://learn.microsoft.com/en-us/analysis-services/tom/table-partitions?view=sql-analysis-services-2025
The update for partitions in the service cloud we have been running through using Datapiplines.
Result: It worked, BUT!
Once the report consumer was using the calendar slicer for year 2025 data, power Bi was still sending the queries to SQL database to the partition that kept the data < 2024, even though the SQL db was providing empty table as the result. All this operations were consuming time.
So as the result with this hybrid model (cold and hot partitioning) it was not possible to achieve that if the user do not quering cold data, power bi would NOT send queries to SQL DB.
It does make sense to still use this hybrid method in case if you have non-heavy reports that will contain just multiple measures, so there will still be latency however, you can survive. In case if you have more then 10 measures and a bunch of slicers and need to break down the data by dimensions, the speed of the report (even if you work with hot data) will not be sufficient.
Hopefully, PBI will have a nice way to combine direct and import in the future, but not for now.
Hi Kidata , I was able to make it work with only the fact table. That was purely for experiment and it won't apply to business use case. In my experiment, I only have one fact table which has the date column, then I create a year column based of that date column. In the DataCoverageDefinition, I defined it something like 'Fact'[year] in {2024,2023}. With all the other set up similar to your desciption, it worked. However, it just not realstic that you only have a fact table and I'm not able to make it work with combinations between fact and dim table.
