Incremental Refresh With Redshift Views In Power BI

[Koritala]

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.

[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 · Accepted solution]

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 Source

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

[v-ssriganesh]

Hi @Koritala,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.
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, 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.

[Koritala]

Hi Sriganesh,

Thanks for your reply.

Can you plz share M-code format to pass the dynamic parameters to filter the data before loading into power bi with custom sql for redshift table?

Thanks,

Srinivas.

[v-ssriganesh]

Hi @Koritala,

Below is an example of M-code to pass dynamic RangeStart and RangeEnd parameters in a custom SQL query for a Redshift table in Power BI. This filters the data based on a date/time column (e.g: LastUpdatedDate) before loading, which can help with incremental refresh and query folding.

let Source = Sql.Database("redshift-server", "database", [Query="SELECT * FROM FactSales WHERE LastUpdatedDate >= '" & DateTime.ToText(RangeStart, "yyyy-mm-dd hh:nn:ss") & "' AND LastUpdatedDate < '" & DateTime.ToText(RangeEnd, "yyyy-mm-dd hh:nn:ss") & "'"]) in Source

Ensure RangeStart and RangeEnd parameters are defined in Power Query with the Date/Time type. Replace "redshift-server", "database", and FactSales with your actual Redshift server, database, and table names, and adjust LastUpdatedDate to match your date/time column. This query should support query folding, but you can verify by checking if “View Native Query” is enabled in Power Query Editor.

I trust this addresses your needs. If it does, please “Accept as solution” and give it a "kudos"  to help others find it easily.
Thank you.

[v-ssriganesh]

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.

[andrewsommer]

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.

[Koritala]

Hi Andrewsommer, 

Thanks for your response and is there any alternate to achive the incremental refresh in my case.

Thanks,

Koritala