Does Connect Cloud support incremental refreshes in PowerBI?

  • 23 February 2024
  • 2 replies

Userlevel 2

I am importing some very large tables into PowerBI. Each time the data refreshes, the process gets slowed down since all of the data is getting requested/refreshed.

Does Connect Cloud support updating/refreshing the data incrementally?


Best answer by James T 23 February 2024, 15:54

View original

2 replies

Userlevel 2

Incremental Refreshes are definitely supported by Connect Cloud for any source tables with a "datemodified" type of column. Please note that Microsoft only supports incremental refresh in Power BI Premium, Premium per user, Power BI Pro, and Power BI Embedded

Since Connect Cloud is facilitating the data connectivity, implementing any incremental logic in a data tool is up to the user. (If more straightforward data replication is desired to a destination like SQL Server, please consider CData Sync, our dedicated ETL/ELT tool.)

The following steps are an overview for setting up incremental refreshes in PowerBI:

Transform Data & Define Parameters

When importing your Connect Cloud data into PowerBI with Cloud's PowerBI Connector, select Transform Data (instead of Load):

PowerBI - Transform Data

In the Power Query Editor, select Manage Parameters and then New Parameter.

PowerQuery Editor

In the Name field, enter RangeStart. In Type field choose Date/Time. Enter a start date/time in the Current Value field. 

Manage Parameters

Then click New to create a parameter named RangeEnd and click OK when finished.

In the PowerQuery Editor, select the "DateLastModified" column and then choose the dropdown > Date/Time Filters > Custom Filter. (Change type to Date/Time if it is another date type)

PowerQuery - Custom Filter

Specify the first and second conditions using the parameters you created. (Make sure that "equal to" is on either RangeStart or RangeEnd, but not both). Then click OK.

Power Query - Filter Rows

On Power Query Editor's Home ribbon, select Close & Apply

Close Power Query Editor

Define Incremental Refresh Policy

After you have defined RangeStart & RangeEnd parameters and filtered the data, you can define an incremental refresh policy. This policy is applied only after the model is published to and a refresh is performed (manual or scheduled). Detailed instructions can be found in Microsoft's PowerBI documentation here.

Incremental Refresh Policy

Configure a Scheduled Refresh

Once your report is published, you must authenticate with Connect Cloud to schedule a data refresh. More details can be found in this CData Community article

Userlevel 5
Badge +1

This is amazing! Thanks for sharing @James T.