Forum Discussion

Koritala's avatar
Koritala
Post Patron
1 year ago
Solved

Incremental 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 t...
Need Help
  • v-ssriganesh's avatar
    v-ssriganesh
    1 year ago

    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's avatar
v-ssriganesh
Community Support
1 year ago

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.