Forum Discussion
Post PatronIncremental Refresh With Redshift Views In Power BI
Hi All,
Is this possible to set up Incremental refrsh on the Redshift Views in Power BI?
Could you please share the approach and sample code if possible. We have 13 million records in few fact tables and Instead of full refresh of the semantic model on daily basis, we want to load the data into semantice model in incermental appraoch.
Thanks,
Srinivas.
Hello Koritala,
Thank you for your feedback. Below is a revised M-code for your Redshift table, using standard SQL with CAST for timestamp compatibility to support incremental refresh and query folding.let Source = Sql.Database("redshift-server", "database", [Query="SELECT * FROM FactSales WHERE LastUpdatedDate >= CAST('" & DateTime.ToText(RangeStart, "yyyy-mm-dd hh:nn:ss") & "' AS TIMESTAMP) AND LastUpdatedDate < CAST('" & DateTime.ToText(RangeEnd, "yyyy-mm-dd hh:nn:ss") & "' AS TIMESTAMP)"]) in SourceCheck RangeStart and RangeEnd are Date/Time parameters, and update "redshift-server", "database", FactSales, and LastUpdatedDate to your setup. Please share the error details, table structure, or specific Redshift functions you’d like to use, so I can refine the solution.
If the response answered your query, kindly “Accept as Solution” and Give “Kudos” to help others in the community benefit from it as well.
Thank you.
13 Replies
- andrewsommer
Super User
Incremental Refresh on Amazon Redshift Views in Power BI is not natively supported
Please mark this post as a solution if it helps you. Appreciate Kudos.
KoritalaHi Andrewsommer,
Thanks for your response and is there any alternate to achive the incremental refresh in my case.
Thanks,
Koritala
- v-ssriganesh
Community Support
Hello Koritala,
Thank you for reaching out to the Microsoft Fabric Forum Community.
Yes, you can set up incremental refresh in Power BI on Amazon Redshift views, provided the view supports query folding (so that Power BI can push date-based filters to Redshift).- In Power BI Desktop, create RangeStart and RangeEnd parameters.
- In Power Query, apply a filter using these parameters to your view (ensuring the date column in your view is used for incremental filtering) and validate query folding by checking if Power Query can push the date filter down to Redshift (View Native Query option).
- In the Data view, enable Incremental Refresh on the table and set up your refresh and storage periods and publish the dataset to Power BI Service and set up scheduled refresh.
- If your Redshift view is complex (joins, window functions, etc.), it might not support query folding, and incremental refresh won’t work in that case.
If needed, consider using a table or materialized view in Redshift for better support of incremental refresh.
Thank you, andrewsommer for sharing valuable insights.
If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you. - v-ssriganesh
Hi Koritala,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.- Koritala
Hi, query folding is not enabling. May I know that is mandatory for any databasse?
- v-ssriganesh
Hello Koritala,
Thank you for followup query.Query folding is not strictly mandatory for incremental refresh in Power BI with any database, including Amazon Redshift, but it’s highly recommended for performance. Query folding allows Power BI to push filtering logic (e.g: based on the date/time column in your Redshift view) to the database, reducing the amount of data transferred and speeding up refreshes, especially for large datasets like your 13 million record fact tables. If query folding isn’t enabled, Power BI processes the filters locally, which can slow down the refresh process.
Since you mentioned query folding isn’t enabling, it’s likely due to a complex view definition (e.g: joins, window functions, or unsupported SQL elements). You can still use incremental refresh without query folding, but performance may be suboptimal.
If this helps, please “Accept as solution” and give a “kudos” to assist other community members.
Thank you.
- v-ssriganesh
Hi Koritala,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
- v-ssriganesh
Hi Koritala,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.- Koritala
Hi, your M code is not working. I think we should use redshift specific functions to work.
- v-ssriganesh
Hello Koritala,
Thank you for your feedback. Below is a revised M-code for your Redshift table, using standard SQL with CAST for timestamp compatibility to support incremental refresh and query folding.let Source = Sql.Database("redshift-server", "database", [Query="SELECT * FROM FactSales WHERE LastUpdatedDate >= CAST('" & DateTime.ToText(RangeStart, "yyyy-mm-dd hh:nn:ss") & "' AS TIMESTAMP) AND LastUpdatedDate < CAST('" & DateTime.ToText(RangeEnd, "yyyy-mm-dd hh:nn:ss") & "' AS TIMESTAMP)"]) in SourceCheck RangeStart and RangeEnd are Date/Time parameters, and update "redshift-server", "database", FactSales, and LastUpdatedDate to your setup. Please share the error details, table structure, or specific Redshift functions you’d like to use, so I can refine the solution.
If the response answered your query, kindly “Accept as Solution” and Give “Kudos” to help others in the community benefit from it as well.
Thank you.
