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 

Recommendations:

1. Optimal Design:

   • Combine Historical and Current data in the dataflow or source database before loading into Power BI.
   • Apply Incremental Refresh to the final Appended Table only.

2. Incremental Refresh for Appended Table:

   • Ensure a consistent date column for partitioning across Historical and Current data.

3. XMLA Endpoints:

   • Tables must be loaded to manage partitions with XMLA. Use pre-combined data to avoid redundant loads.

Answers:

1. Is Current Design Best Practice?

   • No, pre-append Historical and Current data outside Power BI and load only the final Appended Table.

2. Disable Table Load for XMLA?

   • Not possible. Manage partitions on the final Appended Table to reduce overhead.

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