In data modelling is it acceptable for a dimension to have a surrogate key to another dimension as an attribute or should this always be a business key?
I have an Item dimension, which has a Department Number as an attribute. I also have a Department dimension. Is it acceptable for the Item Dimension to hold the SK to the Department Dimension or just the business key?
Usually you would avoid to have both the natural and the surrogate key as foreign keys in the table, because that is redundant and can lead to data inconsistency. Example: Someone updates the natural keys and forgets to also update the surrogate key.
In a data warehouse, which you tagged your request with, however, redundancy is not so much considered a problem. There is usually an transaction processing system with loads of inserts, updates and deletes, and then there is the data warehouse. The data warehouse gets all its data beautifully arranged from the processing system and there simply are no updates as the one mentioned above. If data is redundant, who cares? It simplifies data access. You can even store the employee-department join as a table with all the department data redundant. A data warehouse is all about easy and quick access to data, so as to make reporting easier. Redundant foreign keys are no problem in a data warehouse.