Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Unable to refresh Azure SQL DB Composite model with incremental refresh enabled

When publishing a pbix file using composite models connected to Azure SQL DB + incremental refresh, the following error is encountered in the service when viewing the dataset settings: 

 

 You can't schedule refresh for this dataset because the following data sources currently don't support refresh:

Data source for '{"protocol":"ado-dot-net","address":{"options":{"data source":"myazuresqlserver.database.windows.net","initial catalog":"myazuresqldb","encrypt":"True","trustservercertificate":"False"},"provider":"System.Data.SqlClient"},"authentication":null,"query":null}'

 

Connectivity in the published PBIX was created using get data -> Azure SQL DB, depicted by the following M snippet below:

Source = Sql.Database("myazuresqlserver.database.windows.net", "myazuresqldb")

 

Steps to reproduce: 

  1. Create a PBIX file with two tables using Azure SQL DB as a source
  2. 1 table configured as import
  3. 1 table configured as direct query
  4. On the import table, add range parameters (RangeStart & RangeEnd) and Table.selectRows statement for Incremental refresh
  5. Enable incremental refresh on the import table
  6. Publish the report to the Power BI service
  7. Observe the error above when navigating to the workspace -> dataset -> settings

 

  1. When removing incremental refresh from the report and publishing to the service, no issues are observed. 
  2. When removing the direct query table from the report and publishing to the service, no issues are observed.

 

Only the combination of composite model + incremental refresh results in an error. As per documentation for incremental refresh, composite models should be supported: https://docs.microsoft.com/en-us/power-bi/service-premium-incremental-refresh#limitations

Status: New
Comments
v-yuta-msft
Community Support

@jpretorius ,

 

I would suggest you create a support ticket here.

Support Ticket.gif 

 

Regards,

Jimmy Tao