Friday, September 30, 2011

Late Arriving Dimension Rows

Many a times when loading fact data, you may find that for a fact record, the corresponding dimension record is not available. To get around this, a new row is inserted into the dimension table with the surrogate key and natural key as a placeholder record. These placeholder records are known as late-arriving dimension rows.
Example: At my current work place, we process our facts everyday and dimensions once a week . Now guess what will happen when a new employee joins and starts working from the next day? Since we do our time entry on a daily basis, this new employee’s hours will show up in the fact table(WIP table) but the corresponding dimension record(employee name etc) will be missing.
To summarize, late arriving dimension rows are:
  • added during fact processing when the business key from the fact source doesn’t have a match in the dimension table
  • updated during dimension processing when the dimension record comes in from the source
SQL Server's Slowly changing Dimension Wizard refers to late-arriving dimension rows as Inferred Members. For more details on how to handle late-arriving dimension rows using SQL Server Integration Services(SSIS), please refer Slowly Changing Dimensions in Data Warehouse.

No comments:

Post a Comment