Sunday, October 14, 2007

Identifying changed fact records

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):

  1. 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.
  2. 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.

No comments: