Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ellac
Helper III
Helper III

Hybrid tables for decreasing model size?

Hi community!

 

I have a 44GB data model in Azure Analysis Service that I would need to downsize (planning on migrating to PBI Premium). I have an idea that you can possibly reduce the size by converting some tables to hybrid tables. My thinking is that I don't want to import several years old history partitions (which are no longer updated but from which you still want to be able to access data), but use DirectQuery. But everywhere I read it says history partitions should be imported, and the current data should use DirectQuery, which I don't understand.

 

Is it not faster with Import and therefore I want such a connection for the current data? What takes up a lot of space is all "old" data, so how do I reduce the size of it?

 

Does anyone have experience with this and any best practice? Or other tips on getting the model size down?

 

Thanks in advance!

/Ella

1 ACCEPTED SOLUTION
v-huijiey-msft
Community Support
Community Support

Hi @ellac ,

 

Hope everything is going well.

 

I see your data source is Azure Analysis Service, you can try Live Connect mode.

 

If you want to use composite mode, here are my suggestions:

 

Configuring historical data as DirectQuery partitions in hybrid tables can help reduce model size.

 

Configuring the most frequently accessed data or current data as Import partitions in hybrid tables can help improve query performance.

 

For more information on configuring composite tables, see:

Semantic model modes in the Power BI service - Power BI | Microsoft Learn

 

Apply an incremental refresh strategy to the table. Not only does this help manage data volume by refreshing only changed data, but it also helps to utilize mixed tables more efficiently. This is especially useful for current data that is updated frequently.

 

For more details on incremental refresh, see:

Incremental refresh for semantic models and real-time data in Power BI - Power BI | Microsoft Learn

 

You can use tools to refresh a partition individually to prevent timeout. You can refer to the "Prevent timeouts on initial full refresh" section of the document:

Advanced incremental refresh and real-time data with the XMLA endpoint in Power BI - Power BI | Micr...

 

If you have other questions, please contact me at any time.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

View solution in original post

2 REPLIES 2
v-huijiey-msft
Community Support
Community Support

Hi @ellac ,

 

Hope everything is going well.

 

I see your data source is Azure Analysis Service, you can try Live Connect mode.

 

If you want to use composite mode, here are my suggestions:

 

Configuring historical data as DirectQuery partitions in hybrid tables can help reduce model size.

 

Configuring the most frequently accessed data or current data as Import partitions in hybrid tables can help improve query performance.

 

For more information on configuring composite tables, see:

Semantic model modes in the Power BI service - Power BI | Microsoft Learn

 

Apply an incremental refresh strategy to the table. Not only does this help manage data volume by refreshing only changed data, but it also helps to utilize mixed tables more efficiently. This is especially useful for current data that is updated frequently.

 

For more details on incremental refresh, see:

Incremental refresh for semantic models and real-time data in Power BI - Power BI | Microsoft Learn

 

You can use tools to refresh a partition individually to prevent timeout. You can refer to the "Prevent timeouts on initial full refresh" section of the document:

Advanced incremental refresh and real-time data with the XMLA endpoint in Power BI - Power BI | Micr...

 

If you have other questions, please contact me at any time.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Hi @v-huijiey-msft ,

Sorry for my late reply! Thanks for answering me 🙂

I understand now that I was a bit unclear about my problem, sorry for that! I'm talking about the connecting between our warehouse (Synapse dedicated SQL Pool in this case) and Azure Analysis Services - where all the tables are imported. When we connect to the semantic model via Power BI, we use live connection. 

Our semantic model has increased to be 44 GB, and I was wondering if this import mode between the DW and AAS can be changed and maybe reduce the model size? I can't find anything about this on the internet, that's why I'm asking here in the community.

Kind regards,

Ella

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.