Appending Current Data to Large Static Data Set & Incremental Refresh

[oneraj4u]

Unsure if this is the right forum, or PBI Service ( my apologies for duplication)

I'm designing a report that has two tables (Historical & Current) loaded from a Power BI Dataflow into a single Power BI dataset, which are too large to publish if I load the data before hand and will time out if I try to refresh it online. My current solution is to create incremental refresh partitions on a dev dataset, which keeps the report size small enough to publish, and then I utilize XMLA endpoint access via SQL Server Management Studio to tediously load data into the incremental partitions I created manually ( a few months at a time).

Design Goal:
I believe the two source tables should have their load disabled with only the Current table incrementally refreshed. Please note, I do not mean make the tables "invisible", I mean disable their load in Power Query. Then the table that results from the appending of the previous two is loaded and incrementally refreshed. (I'm assuming loading the tables takes up storage / processing power & isn't a best practice)

Design Issue:
However, because I am using XMLA endpoints in SQL Server Management Studio to load the data, I need the Historic & Current table to be loaded otherwise I cannot see them to the best of my knowledge.

QUESTIONS:
1. Is my "Current Design" approach the best practice, or is there a better approach? 

2. Per my "Current Design Issue" is there a way to disable a table from loading using XMLA endpoints ( either in SQL Server Management Studio Analysis Services or Tabular Editor 2.0) ?  Or is the loading of the tables a non concern?


Overview of my tables, in their current state. This is not the ideal state in my mind, I'd like to avoid loading the Historical and Current tables, just the appended one as I believe this takes up storage space & maybe processing power.

•  Historical Table:
  • Static data over 30 GB 
  • Refresh disabled
  • Load enabled (because I don't know how to load data via XMLA endpoints without the load enabled)
• Current Table:   
  • Updated daily, data over 2 GB
  • Incremental Refresh enabled
  • Load enabled (because I don't know how to load data via XMLA endpoints without the load enabled)
    
    
• Appended Table:
  • Result from previous two tables
  • Incremental Refresh enabled (not sure if this is possible?)
  • Load enabled ( I need this table loaded for use in the report)

[rohit1991]

Hi, @oneraj4u 

Great question! I’ve set this up a few times and here’s what works best (and keeps things efficient):

1. Do the append outside Power BI if you can (SQL, Dataflow, etc.), so you only load a single, combined table into Power BI. It keeps the model lean and refresh fast.
   
   

2. Apply incremental refresh to just the appended table, set up your date column for partitioning. Only new/current data gets refreshed; historic stays static.
   
   

3. Disable Load for the Historic/Current tables in Power BI. Just right-click and “Disable Load” so only your final table is loaded and visible. This way, extra tables aren’t taking up memory or storage.
   
   

4. For XMLA endpoints/partitioning: Only tables that are loaded can be managed with XMLA, so again focus on the appended table and keep everything else disabled.
   
   

So in short: prep and combine your data upstream, bring only the finished table into Power BI, and set incremental refresh on that. This will give you optimal refresh times and keep the workspace tidy.

[Akash_Varuna]

I think using the following approach will work:

1. Load only the appended table, disable the load for Historical and Current tables while keeping them for transformation (appending).
2. Move the logic for appending the tables to Power BI Dataflows to reduce dataset size in Power BI.
3. Set up incremental refresh on the appended table, so only new data gets refreshed, optimizing the process.