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 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's avatar
Koritala
Post Patron
1 year ago

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

    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.