Retail Schema Extensibility
Let’s turn our attention to extending the initial dimensional design. Several years after the rollout of the retail sales schema, the retailer implements a frequent shopper program. Rather than knowing an unidentified shopper purchased 26 items on a cash register receipt, you can now identify the specific shopper. Just imagine the business users’ interest in analyzing shopping patterns by a multitude of geographic, demographic, behavioral, and other differentiating shopper characteristics.
The handling of this new frequent shopper information is relatively straightforward. You’d create a frequent shopper dimension table and add another foreign key in the fact table. Because you can’t ask shoppers to bring in all their old cash register receipts to tag their historical sales transactions with their new frequent shopper number, you’d substitute a default shopper dimension surrogate key, corresponding to a Prior to Frequent Shopper...