Create partitions along with the incremental partitions

[dmkblesser]

Is it possible to create a data partition along with the paritions created by the incremental refresh?

Here is what I've tried: I set up a model with incremental refresh with policies set to be archive 5 year data from now and refresh the most 30-day data. Publish the model to the service and let it refresh to create all the partitions. 

Then I connect to the model through XMLA, on top of all the partitions, I tried to create another partition with DirectQuery connection and target for all the data before 5 years from now and hope this partition can cover the cold data. However, when I tried to refresh the model from the Power BI service, the manually created partition got removed. 

Has anyone implemented anything similar and be able to retain the cold partition and let the incremental refresh take care of the future partition creation? Could you share what you have done to make it work? Appreciate the help!

[Deku]

Please see this video on how to setup reverse hybrid tables

https://youtu.be/Hpx52kOodPc?si=1Bg6aLlAv5gKXcjW

[dmkblesser]

Hi Deku, thanks for your response. This is the concepet I tried to implement and I actually started with that video. Just curious to know if you personally made it work by following the video instructed, cuz it didn't work out for me. If it does work for you, any modification you made from the instruction? Also, since in the video, Pat didn't use the DataCoverageDefinition to tell the engine which is truly the DQ parititon, I'm wondering if you have experienced any slow performance for the Import partitions when you implemented it. 

[Deku]

I've not really used them. In cases where I have wanted to use hybrid tables, I've wanted to use user defined aggregation more, and they are incompatible 

[Akash_Varuna]

Hi @dmkblesser For this set up incremental refresh for recent and archive data. After publishing, use the XMLA endpoint to manually create a cold data partition targeting older data. Ensure the incremental refresh policy excludes periods covered by the cold partition. Alternatively, pre-aggregate cold data in the source and include it as a separate table.

[dmkblesser]

Hi @Akash_Varuna, can you show me a sample table scrip or a screenshot of the setup? 
I tried with Tabular Editor and this was the error message I received: 

the error message sounds like mix custom partition and incremental partitions is not allowed.