Forum Discussion
Suggestions on Configuring Incremental Refresh
I have a shift table where shift details are stored. It contains shift details where shift date is from 2019 to 2 years in future with respect to todays date, It contains more than 5 million records. I have a few other tables with similar amount of data ranging from 4 million -15 million records. We need an hourly refresh of the dataset and it's consuming lot of resources(CU's) when loading all the records each refresh. I want to understand the best possible way to reduce the load. I've tried incremental refresh, but my table is not immutable so it's creating duplicate records.
Here is a sample of the table:
| ShiftID | ShiftName | Shift Date | Inserted Date | Updated Date |
| a1 | ACT-JAN-PPa1 | 1/01/2023 | 1/01/2020 | 1/02/2024 |
| a2 | CCP-MAY-UKr4 | 1/05/2023 | 15/05/2022 | 8/03/2023 |
| a3 | TDP-JUN-YCp3 | 2/06/2023 | 1/12/2022 | 18/03/2024 |
| a4 | PSK-DEC-JSp1 | 31/12/2023 | 3/01/2023 | 16/01/2024 |
| b1 | CBN-JAN-BRs1 | 1/01/2024 | 1/01/2022 | 17/02/2023 |
| b2 | POK-MAY-INa1 | 21/05/2024 | 15/10/2023 | 12/08/2024 |
| b3 | MOD-JUN-SHa3 | 23/06/2024 | 17/06/2024 | 15/01/2024 |
| b4 | CBN-AUG-BRs2 | 19/08/2024 | 1/01/2021 | 1/07/2024 |
| c1 | POK-SEP-INa2 | 15/09/2024 | 15/01/2023 | 1/01/2024 |
| c2 | MOD-OCT-SHa4 | 17/10/2024 | 14/01/2024 | 14/01/2024 |
| c3 | CBN-DEC-TDp4 | 31/12/2024 | 25/08/2024 | 2/03/2024 |
| c4 | ISR-JAN-PSn13 | 12/01/2025 | 27/07/2023 | 27/07/2023 |
| c5 | MOD-FEB-SHa5 | 13/02/2025 | 1/12/2023 | 1/12/2023 |
Can you please help me with the best way to reduce the load on the server and load only the rows that have been updated ?
The source data is coming from SQL database and I have written native queries to do all the transformations in the query itself. Is there a better way to solve this issue ?
fanatic ,
check the concept of direct query.
https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery
3 Replies
Hello fanatic ,
does the requirments require detailed data from 2019 till now ?
you can reduce the data with filtering the date for the needed data only.
you can also group by data if you don't need it detailed.
I don't think you would need these detailed data from 2019 till now, no one would check a specific person in feb 4 2019 what was his shift, they may need total only so you can group by.
Hi Idrissshatila ,
Thank you for the reply.
The business needs 18 months historical data and 12 months future data. We need every record during this time as business uses it for Audit purpose as well. After applying the above month filters we have around 6 million records (20,000 employees, 3-6 shifts a day including deleted records - needed for audit). They need hourly refresh of this data, the refresh takes around 10 minutes but uses a lot of CUs since we are in autoscaling mode and it's costing business extra than the pro/premium capacity we currently have. So wanted to understand is there a way to reduce the CUs consumption
fanatic ,
check the concept of direct query.
https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery
