Incremental refresh with Import Mode

[Anonymous]

Dear All,

We have couple of reports which are based on SQL queries using the 'Import' Data Connectivity Mode.

I have configured incremental refresh but I dont know if Incremental refreshes works with Import query or not.

I am also not able to see the Native Query in the data model. It is greyed out.

Need help to know if incremental refresh works with 'Import' Data Connectivity Mode or not..

Regards

[Tutu_in_YYC]

If you have premium capacity on your workspace, you can use SSMS and XMLA endpoint to connect to your semantic model, and check if the incremental partitions are created. Refer this blog.

https://medium.com/@yukithejapanese/introduction-to-the-xmla-endpoint-in-power-bi-76448a6c9216

But, if the native query is greyed out, there is a high chance that the query cannot be folded and incremental will not work. You can try forcing it. Refer to this video. https://www.youtube.com/watch?v=8hjdOCni_ZY

[Bipin-Lala]

Hi @Anonymous,

Yes, incremental refresh works with the Import data mode in Power BI. In fact, incremental refresh is designed specifically for scenarios where large datasets are imported, allowing only changes (new or updated data) to be loaded during refreshes, instead of reloading the entire dataset.

We are using Incremental Refresh in our organization for reports which have large volumes of data/ transactions flowing in everyday. The datasets are configured to use import mode and connect with our SQL Data Warehouse.

Use the following link to refer to configuring incremental refresh in Power BI - https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-configure 

As for the Native Query option being greyed out, it happens in cases where steps in Power Query are not folding back to the source (query folding). Query folding is necessary for incremental refresh to work efficiently, as it allows Power BI to push query steps directly to the source database.

Do let me know if you need further help in configuring incremental refresh for your tables. Happy to help!🙂

[Anonymous]

Thank you for the reply.

My query is simple and there are no steps implemented in Power Query. Still the Native Query option is disabled.

have you ever came accross such senario?

Regards

[Tutu_in_YYC]

yes, another reason why the native query is greyed out, is if you supplied a SQL query in your connection. Are you using a select statement for your query?

[Anonymous]

Yes it is a select statment with some SQL level filters.

So, which this setup is query folding possible? And if query folding is not possible(View Native Query grayed out) will incremental refresh work?

[Tutu_in_YYC]

Try hard coding the function to enable the query folding, as shown in this video:  https://www.youtube.com/watch?v=8hjdOCni_ZY

Even though it is greyed out, it should work, but please also validate it.