Forum Discussion
Help with incrementally refreshing an entity computed from two separate dataflows
Hi everyone,
I am have a dataflow that I am trying to optimize in my workspace and was hoping to get some advice regarding the incremental refresh. Essentially the dataflow is generated from merging two other dataflows.
The first dataflow stages some data from sharepoint in an entity like so:
| date | id | used |
| 22/02/2024 | AEF | Y |
| 23/02/2024 | BCP | N |
And the second dataflow stages some data from the data warehouse into an entity like this:
| date | code | group | metric |
| 22/02/2024 | ACP | A | 1 |
| 22/02/2024 | AOP | A | 5 |
| 22/02/2024 | AEF | B | 3 |
| 22/02/2024 | AEF | C | 4 |
| 22/02/2024 | BCP | B | 2 |
Essentially I want to filter rows in the second dataflow to only contain codes that match to ids in the first entity.
The dates in the first entity are irrelevant.
At the moment, I have been grouping the first entity by "id" and "used", then merging it (left joining) with the second entity and filtering rows where code is null:
grouped entity:
| code | used |
| AEF | Y |
| BCP | N |
After merge and filtering:
| date | code | group | metric |
| 22/02/2024 | AEF | B | 3 |
| 22/02/2024 | AEF | C | 4 |
| 22/02/2024 | BCP | B | 2 |
This is working, but it is very slow. I suspect this is due to the size of the second entity.
The data should be refreshed daily. However, while the table coming from the datawarehouse is being incrementally refreshed, I suspect that the full table for the final entity is doing a full refresh everyday.
Is there a way to ensure that only the most recent data is being refreshed in the final entity?
Would it make more sense to left join the second entity to the first instead?
Hi purple_SP ,
Instead of merging the entire second entity with the grouped first entity, consider filter the second entity firs, you may apply a filter to the second entity to only include rows with codes that exist in the first entity. This can be done using a list of unique IDs from the first entity. If possible, use an inner join instead of a left join, as it can be more efficient when you only need matching rows.
You can use parameters to dynamically filter the data being loaded. Create a parameter for the date range you want to refresh. then apply this parameter to filter the second entity before merging.Finally you may consider using staging tables to store intermediate results. This can help break down the process into smaller.
You can stage the filtered second entity, create a staging table that only contains the filtered rows from the second entity and then merge with the first entity, which perform the merge operation on the staged data.
Best regards.
Community Support Team_Caitlyn
1 Reply
Hi purple_SP ,
Instead of merging the entire second entity with the grouped first entity, consider filter the second entity firs, you may apply a filter to the second entity to only include rows with codes that exist in the first entity. This can be done using a list of unique IDs from the first entity. If possible, use an inner join instead of a left join, as it can be more efficient when you only need matching rows.
You can use parameters to dynamically filter the data being loaded. Create a parameter for the date range you want to refresh. then apply this parameter to filter the second entity before merging.Finally you may consider using staging tables to store intermediate results. This can help break down the process into smaller.
You can stage the filtered second entity, create a staging table that only contains the filtered rows from the second entity and then merge with the first entity, which perform the merge operation on the staged data.
Best regards.
Community Support Team_Caitlyn