Incremental Refresh & Appending Current Data to Large Static Data Set

[oneraj4u]

Hello 😊
I am 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 would rathernot load 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)

[lbendlin]

Since you are already doing incremental refresh there is no real need for a Historical and Current table - this can be a single table with appropriately sized partitions. Manage your partitions as you see fit, either via the Power BI Service or via XMLA.

[oneraj4u]

I'm not sure you understood my post.

The whole point of my post is creating the single table from two pre-existing tables and avoiding a full refresh on all 3 tables. There's no reason to reload all the data each time.

[lbendlin]

That's the idea of incremental refresh.  Your single table has multiple partitions, but only very few of them (ideally just the newest) will be reloaded when you issue a refresh request.  The other partitions of that table are left alone (except for the consolidations at the end of periods, if you choose to do so).

[oneraj4u]

Yes I know about inrcemental refresh and am using it per my original post