Reversed hybrid table with incremental refresh

[dmkblesser]

Hi guys, 

Has anyone successfully constructed a reverse hybrid table with incremental refresh?  (having the cold data in DQ and hot data in import mode) 

I was trying to follow some tutorials but just couldn't ever get it to work. 

The dataCoverageDefinition  property is great, but where do I apply it? Do I just set up a standard incremental refresh and have it refreshed first, then go to edit the partitions? 

https://learn.microsoft.com/en-us/analysis-services/tom/table-partitions?view=asallproducts-allversi...

https://www.youtube.com/watch?v=Hpx52kOodPc 

https://www.youtube.com/watch?v=1GvVCHeqJz4

could someone have done similar practices to share some experience with me? Really appreciated!

[Kidata]

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-...

table Fact_table

    partition Fact_table_DIRECT = m

        mode: directQuery

        dataView: full

dataCoverageDefinition:

        expression: "RELATED('D_DATE_FC'[YEAR_INT]) < 2024"

        source =

            let

                Source = Snowflake.Databases("sql-db-adress", WAREHOUSE, [Role=ROLE]),

                PLAYDM_DEV_Database = Source{[Name=DATABASE, Kind="Database"]}[Data],

                DBO_Schema = PLAYDM_DEV_Database{[Name="REPORTING", Kind="Schema"]}[Data],

                D_INTERNAL_Table = DBO_Schema{[Name="Fact_table_DIRECT", Kind="Table"]}[Data],

Filtered = Table.SelectRows(D_INTERNAL_Table, each [D_DATE_ID] < 20240101)

            in

                Filtered

    partition Fact_table_LONG = m

        mode: import

        source =

            let

                Source = Snowflake.Databases("sql-db-adress", WAREHOUSE, [Role=ROLE]),

                PLAYDM_DEV_Database = Source{[Name=DATABASE, Kind="Database"]}[Data],

                DBO_Schema = PLAYDM_DEV_Database{[Name="REPORTING", Kind="Schema"]}[Data],

                D_INTERNAL_Table = DBO_Schema{[Name="Fact_table_LONG", Kind="Table"]}[Data],

                Filtered = Table.SelectRows(D_INTERNAL_Table, each [D_DATE_ID] >= 20240101)

            in

                Filtered


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.

[dmkblesser]

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. 

[V-yubandi-msft]

Hi @dmkblesser ,

we wanted to check in as we haven't heard back from you. Did our solution work for you? If you need any more help, please don't hesitate to ask. Your feedback is very important to us. We hope to hear from you soon.

Thank You.

[V-yubandi-msft]

Hi @dmkblesser ,

We noticed we haven't received a response from you yet, so we wanted to follow up and ensure the solution we provided addressed your issue. If you require any further assistance or have additional questions, please let us know.

Your feedback is valuable to us, and we look forward to hearing from you soon.

Thank You.

[V-yubandi-msft]

Hi @dmkblesser ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution we provided for your issue worked for you  or let us know if you need any further assistance?

Your feedback is important to us, Looking forward to your response. 

Thank You.

[V-yubandi-msft]

Hi @dmkblesser ,

Thanks for reaching out to the Microsoft Fabric community. On the basis of your question and the suggested solution by the @lbendlin , We reaching out to check whether you have managed to set up the desired configuration using TMSL/XMLA commands. Do you get a hybrid table successfully configured with incremental refresh or have any issues in setting up the partitioning?

Thank you, @lbendlin  for sharing your valuable insights.

If you need any additional details, feel free to ask we are here to assist you.

Best regards,

Yugandhar.

[dmkblesser]

Hi @V-yubandi-msft , unfortunately, it didn't work out for me. Could you please provide some examples or detailed steps to set up a sample table? Thank you!

[V-yubandi-msft]

Hi @dmkblesser ,

Thanks for reaching out. We found a related thread that might help you with your issue. Check out the link below.  it could have the answers you're looking for.

Link: Incremental Refresh and Hybrid Tables in Power BI - Microsoft Fabric Community

If you need any more help or additional info, don't hesitate to let us know  we're here and happy to assist.

 If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[dmkblesser]

Hi @V-yubandi-msft , the link you shared is a regular hybrid table. but I'd like to have it in a reversed order. What I mean is having import for the most recent data and DQ for the historical portion. 

[V-yubandi-msft · Accepted solution]

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.

1. Set up Incremental Refresh in Power BI to create partitions.

2. Use Tabular Editor or TOM API to convert older partitions to DirectQuery.

3. Apply dataCoverageDefinition to specify which partitions should be Import vs. DirectQuery.

4. 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.

[lbendlin]

It's also possible to convert an Import table to a hybrid table by adding a DirectQuery partition using Tabular Model Scripting Language (TMSL) or the Tabular Object Model (TOM) or by using a third-party tool. For example, you can partition a fact table such that the bulk of the data is left in the data warehouse while only a fraction of the most recent data is imported. This approach can help to optimize performance if the bulk of this data is historical data that is infrequently accessed. A hybrid table can have multiple Import partitions, but only one DirectQuery partition.

Is that what you are trying to do?

[dmkblesser]

Hi  @lbendlin, thanks for the reply. The part you refered to, it covers the hybrid table part but not the incremental refresh part. what I'm trying to achieve is say I have data from 2020 till now.

I want 2020-01-01 - 2023-12-31 in DQ and 2024-01-01 to 7 days before today in archive (Import) and incrementally refresh the past 7 days data (import). 

[lbendlin]

Use your favorite tool to set the partition refresh policy and the partition type

Create command (TMSL) | Microsoft Learn

Refresh command (TMSL) | Microsoft Learn