Configure an Incremental Refresh for Power BI Reports

Objective 

To configure an incremental refresh for Procore Analytics 2.0 Power BI Reports. If enabled, this results in faster, more reliable data refreshes and improved performance with reduced resource consumption.

Background 

The configuration of incremental refresh for Out-of-the-Box (OOTB) Procore Analytics Power BI Reports is simple and offers great benefits. However, it’s essential to understand several key considerations to maintain the quality of the report while enhancing the performance of the model refresh.

Things to Consider 

• Record Deletion: Incremental refresh will not detect records deleted outside of the active refresh timeframe. 
• Record Duplication: Overlapping archived and active refresh timeframes can lead to duplicate records. This overlap could also cause report failures if a relationship has a cardinality of '1'.
• Column Changes Affect Reports:
  • New Columns: New columns added to a table in a release cycle will not appear in your report.
  • Deleted Columns: If columns are deleted from a table in a release cycle, your report will fail.
• Table-Specific Setup: Incremental refresh policies and filter parameters must be set up for each table. However, it's not necessary to enable incremental refresh for all tables.
• Recommended Table Structure: Procore recommends using incremental refresh with large and simple tables.
• Complex Queries: Incremental refresh may not function correctly or generate accurate results with queries that involve dependencies, references, or complex execution.
• Refreshed Timestamp: While you can use any date-time column, Procore recommends using the refreshed_timestamp column for incremental refresh.

Steps 

1. Open the desired OOTB report in Power BI Desktop. For example, Project Management.
2. Add RangeStart and RangeEnd parameters to set a brief, arbitrary timeframe for the policy.
   
3. Filter the desired table(s) on the refreshed_timestamp column using the parameters. For example, the Photo table.
   
4. Click Close & Apply.
5. Navigate to the Photo table, right-click on it, and select Incremental refresh.
6. Under Set import and refresh ranges, toggle the Incrementally refresh this table switch to on.
7. Set the archive data period. For example, 5 years before the refresh date.
8. Set the incremental refresh data period. For example, 1 month before the refresh date. 
   Note: This creates a rolling window. For example, the next month's refresh will automatically roll forward one month.
   
9. Click Apply.
10. Repeat steps 3-9 for any other tables you want to configure with incremental refresh.
11. Publish the report to a Fabric workspace.
12. Perform an initial refresh operation on the model.
    Note: After the initial refresh, all subsequent refreshes (individual or scheduled) will be much faster because only the new incremental data partitions will be refreshed.