Dimension Surrogate Key Question

[bioran]

Hi,

I have a store dimension. I also have a product dimension for products sold in each store. Products are identified uniquely within a store, but across stores product codes are not unique. In staging I uniquely identify the product by the product code + store code in order to create the surrogate key. When I populate the dimension in the datamart do I only put the surrogate key out there? I have some coworkers argueing to put the store ID in the dimension but I don't believe that is correct. The store can be identified in the store dimension through the fact table. We have the same issue with the customer dimension.

Thanks

 

[kishleo]

why dont you create a bridge dimension between store and product

and you can store the id from bridge dimension in the fact table so that you can identify the store and the product with the help of bridge dimension

bridge dimension structure(id,storeid,productid)

Note:
its not advised to have different product codes for same product accross the stores, i think you can solve this while loading the data using some kind of transformations at while ETL,
in product dimension store the unique products instead of looking for product code + srote code from the source, load your data based on product description or any other column which is same for the same product across the stores. ô¿ô Kishore

 

[Subhashab]

I agree to you view that there is no need to add store column to the product dimension. If that was the case then there was no need for the unique surrogate key which you have created. And the fact table is linked with ur product dimension uing this surrogate key and to the store through the store id. Thus the fat table keeps the link between the product and its store so the need for the bridge is also redundant