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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Kfausch
Helper II
Helper II

Appending To Large Static Data Set

Hello,

 

I am trying to reduce refresh times in my report by splitting historical data from current, only refreshing current data, and then appending both datasets. This has reduced the refresh time from 3.5 hours to 2 hours, but I think I am missing something. This is how I have it setup.

 

Table 1:

ILE (Current) – only data from 2023
• Enable Load – enabled
• Include in report refresh – enabled

• 300MB of data


Table 2:
ILE (History) – data from 2018-2022
• Enable Load – disabled
• Include in report refresh – disabled

• 8.7GB of data

Table 3:
Item Ledger Entry – Table 1 and Table 2 appended
Source = Table.Combine({#"ILE (History)", #"ILE (Current)"})

• Enable Load – enabled
• Include in report refresh – enabled
• 9GB of data

Table 2 is static historical data so I have disabled the refresh, but what I am seeing is that Table 3 still takes a long time to load. If I just want to append a small dataset that refreshes to a large static table, should I be doing something differently? It seems like Table 3 is still loading/parsing all of the data. I was hoping in doing this, I would only need to wait for table 1 to refresh which takes 20-30 minutes.

 

Thanks in advance for any suggestions.

Kevin

1 ACCEPTED SOLUTION
AnkitKukreja
Super User
Super User

 Hi @Kfausch 

 

I think what you have done till step 2 is correct. Then try using Append on table 2 where enable load should be enabled (this refers to your table that would be visible in the model or not). Just come to table 2 with Refresh disabled (static data) and then do append table 1 to this table. I believe it should work.

Also, you can avoid this by using incremental refresh as well, where probably you can store the whole data and then just refresh the latest data (1-10 days to months, quarters or years as per your requirement).

 

 

If my response has successfully addressed your question or concern, I kindly request that you mark this post as resolved. Additionally, if you found my assistance helpful, a thumbs-up would be greatly appreciated.
Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904

View solution in original post

3 REPLIES 3
AnkitKukreja
Super User
Super User

 Hi @Kfausch 

 

I think what you have done till step 2 is correct. Then try using Append on table 2 where enable load should be enabled (this refers to your table that would be visible in the model or not). Just come to table 2 with Refresh disabled (static data) and then do append table 1 to this table. I believe it should work.

Also, you can avoid this by using incremental refresh as well, where probably you can store the whole data and then just refresh the latest data (1-10 days to months, quarters or years as per your requirement).

 

 

If my response has successfully addressed your question or concern, I kindly request that you mark this post as resolved. Additionally, if you found my assistance helpful, a thumbs-up would be greatly appreciated.
Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904

Appending the current data to the history table did reduce the refresh time further. Thanks for your recommendation!

 

Kevin

@Kfausch  How did you load data to Table 2 and publish it if its size is 8.7 GB?

I have a similar issue, however my historical data is too big to load and then publish to Power BI Service. I need to use XMLA endpoints to load the data using SQL Serve Management Studios Analysis Services connection.... which requires the table to be loaded before I publish it in order for me to access it via XMLA endpoints ( am I mistaken?).

I do not know how to disable the load of a table using XMLA endpoints or other tools, or if its even possible, so I now have a historical table that's loaded ... but only used for appending with current data which is not ideal.

Please let me know if you have any advice @Kfausch , and any input you have @AnkitKukreja  would be welcome as well.

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.