If your data source rows contain "created date" and "modified date", you won’t need to match the fact rows with source rows for identifying changed fact records; conversely you have to match them using one of these two methods (you must match business key and time key in both of these methods):
- Database joins between the source data and the fact table: The source data and fact data must be at the same server in order to perform the join efficiently.
- Data correlation with the data flow (using the lookup to identify fact change record): you cannot identify deleted rows in this method.
Don’t forget about historical dimension changes and inferred members and late-arriving facts, you must handle those situations if your business supports them.
If you want to update or delete a row in a table, at the same time that you are inserting rows in it; you may cause locking issues. For preventing this situation turn off the table locking the data flow destination. If the problem is remained, turn off the fast load.