Forum Discussion

kkanda's avatar
kkanda
Resolver II
5 months ago
Solved

Refresh time too long

Hi All,

I believe that this subject is not new. I have seen the earlier posts on this topic. 

Let me state my problem. I have a data source in SAP HANA which is about 20 million records. I applied the SQL filters at the source and I am downloading about 20K records. Whenever I refresh the dashboard, the process runs through all 20 million records and it takes nearly 10 min on my computer. Is there any way that I can quicken the process? Can we create an intermediate data table that captures the filtered data from SAP HANA periodically and I can take the data from the intermediate data table? Basically we will create a connection from the data table to the AWS database with automatice refresh, say once in a day. 

This may not be feasible. Is there any other way this problem can be tackled?
I'll be grateful if someone can suggest a suitable process.

 

Need Help
  • Kedar_Pande's avatar
    Kedar_Pande
    5 months ago

      

    HANA ignores your PQ filters and scans full 20M rows because query folding breaks. Check View Native Query - if grayed out, that's your issue.

    Fixes:

    1. Push ALL filters to HANA SQL view/calc view at source (not PQ)

    2. Enable Incremental Refresh (Premium) on date partition - refreshes ~1/30th data

    3. Your AWS idea works: Export filtered 20K to AWS table daily via Dataflow/Logic App, connect PBI to AWS instead

      Incremental refresh is fastest if Premium available.

    kkanda

  • cengizhanarslan's avatar
    cengizhanarslan
    5 months ago

    In Power Query, right-click the step where you apply the filter and check whether View Native Query is available. If it is not, then the filtering is probably not being pushed to SAP HANA.

     

    1) Keep filtering at the source
    Apply the filter as early as possible and avoid steps that break folding, such as:

    • adding custom columns too early

    • complex merges

    • certain text transformations

    • row-by-row logic

    Try to keep the query simple until after the source filter is applied.

     

    2) Create a source-side view
    Idea of an intermediate table/view would be a good option.

    In practice, this is usually done as:

    • a database view in SAP HANA or AWS

    • or a staged table refreshed on a schedule

    Then Power BI connects to that smaller prepared object instead of the large transactional table.

    This is often the most reliable option for performance.

     

    3) Consider incremental refresh
    This only helps if you are loading historical data repeatedly and the model supports a date-based partition strategy. Also keep it mind that incremental refresh works in Power BI Service not in Desktop.

5 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    5 months ago

      

    HANA ignores your PQ filters and scans full 20M rows because query folding breaks. Check View Native Query - if grayed out, that's your issue.

    Fixes:

    1. Push ALL filters to HANA SQL view/calc view at source (not PQ)

    2. Enable Incremental Refresh (Premium) on date partition - refreshes ~1/30th data

    3. Your AWS idea works: Export filtered 20K to AWS table daily via Dataflow/Logic App, connect PBI to AWS instead

      Incremental refresh is fastest if Premium available.

    kkanda

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    5 months ago

    In Power Query, right-click the step where you apply the filter and check whether View Native Query is available. If it is not, then the filtering is probably not being pushed to SAP HANA.

     

    1) Keep filtering at the source
    Apply the filter as early as possible and avoid steps that break folding, such as:

    • adding custom columns too early

    • complex merges

    • certain text transformations

    • row-by-row logic

    Try to keep the query simple until after the source filter is applied.

     

    2) Create a source-side view
    Idea of an intermediate table/view would be a good option.

    In practice, this is usually done as:

    • a database view in SAP HANA or AWS

    • or a staged table refreshed on a schedule

    Then Power BI connects to that smaller prepared object instead of the large transactional table.

    This is often the most reliable option for performance.

     

    3) Consider incremental refresh
    This only helps if you are loading historical data repeatedly and the model supports a date-based partition strategy. Also keep it mind that incremental refresh works in Power BI Service not in Desktop.

  • Demert's avatar
    Demert
    Resolver III
    5 months ago

    Hi kkanda 

     

    Can you clearify where you apply the SQL Filters? Are you doing any filtering in PowerQuery or only filtering in the advanced options SQL statement of the connector. Normally PBI shouldn't load all 20 million records when you are already filtering in your SQL statement.

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Community Support
    5 months ago

    Hi kkanda ,
    Thanks for reaching out to Microsoft Fabric Community.

     

    Just wanted to check if the responses provided were helpful. If further assistance is needed, please reach out.
    Thank you.

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Community Support
    5 months ago

    Hi kkanda ,

    We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out.
    Thank you.