Refresh Large Dataset (not sure if IR can help)

[Al79]

Hello, and hope you are all well.

I have built a PowerBI report, and it includes 10 tables which are linked to an external source. One of the tables (table a) contains close to 1,000,000 records which change quite often. It is not a table which only grows, data can aslo be updated. As you can guess, it takes ages to refresh and Id like to refresh the other 9 tables every 3 hours. Table a can be less frequent.

Is there a way to, for example, have the 9 tables follow one refresh schedule and the table a follow a different one? I cannot see how.

Or, does incremental refresh work for this? There are date colums which say the last time and date a record was modified.. however I dont see how the dataset can be compared with the source to update the row with the latest data.

So grateful for help or guidence.

Thank you

[JoeBarry]

Hi @Al79 

Do you need to refresh the whloe 1mil rows? Is there a cut off date where they data isn't refreshed? If there is a cut off, incremental refresh could help you here. 

If your company has Power BI Premium, then you can take the other 9 tables out of the Report and build a dataflow with all the tables. You actually just need to copy and paste the queries from desktop and paste them in the Dataflow. This will give you a seperate option to refresh. You can then connect to the Dataflow with Direct Query https://learn.microsoft.com/en-us/power-bi/transform-model/dataflows/dataflows-directquery 

Thanks

Joe

If this post helps, then please Accept it as the solution

[Al79]

Hi, 

Thank you for the information & suggestion.

Sadly, at least we have to at least consider that all data can be changed. So no cut off. I wish it would be true.

And, to sweeten the issue : I dont have premium ...

Any other ideas :)? Thank you so much

[JoeBarry]

Do you need all the columns in the 1 mil+ table? Remove any columns that are not needed. The less text in the column values the better. Remove any unnecessary date columns too.

Lets say for example you have an Employee Name and a Employee ID, remove the Employee name and use a Employee Dimension table to get the names. Similar can be said for Department Names and IDs. 

More information on the Star schema is here https://learn.microsoft.com/en-us/power-bi/guidance/star-schema setting up your data like this will improve performance and it will improve performance 

Thanks

Joe