Incremantal refresh without a closedate

Hi tobny76 - To implement an efficient incremental refresh process for your semantic model, given your scenario where you lack a "close date" and rely on the status column to determine whether an order is "closed,"

Steps to Implement
1. Pre-process Data in Source (Optional but Ideal)
If you can preprocess data in your database or ETL pipeline:

Use a query or stored procedure to filter:
Active Orders: Orders where status != 'closed'.
Archived Orders: Orders where status = 'closed'.
Store them in separate staging tables for efficient incremental loads.

2. Implement Logic in Power BI
Use Power Query for incremental refresh setup:

a. Define Archived and Active Orders Queries
Archived Orders Query:

Use a filter to retrieve only orders where status = 'closed'.
Implement incremental refresh for this query:
Define a date field to partition data (e.g., Create_Date or Last_Modified_Date).
Configure the RangeStart and RangeEnd parameters for incremental refresh in Power BI.
Archive orders as they transition to the closed status.
Active Orders Query:

Filter for status != 'closed'.
Perform a full refresh daily, as these rows may change.
b. Merge the Queries
Once both queries are set up, combine the active and archived partitions into a single dataset:

Use UNION or append in Power BI or SQL.
3. Automate Daily Refresh
Set up a scheduled refresh in the Power BI service:
Active Orders: Full refresh daily.
Archived Orders: Incremental refresh daily.
4. Address the Lack of a Close Date
To avoid reprocessing all orders unnecessarily:

Use a Last Modified Timestamp: If available, this can help identify when an order's status last changed.
Derived Close Date: If feasible, generate a Close_Date in your source system or ETL by recording the date when status changes to closed. This can make incremental archiving more efficient.

 

Hope this approach helps.