just wanted to see if any others have dealt with this issue and how.
I am building a star schema DW and currently in the process of structuring my dimension tables. Most of them are fine as the descriptions have IDs, for example I have an “action type description” and an “action type ID” which I can use to build my dimActionType dimension.
My issue is some of the description fields don’t have a corresponding business key - so for example I have a “group role” description but no business ID. What would be the best practice in putting this into a DimGroup table? I can select distinct group role into a table with auto increment but how would I then join my fact table to this?
Appreciate any thoughts/ideas
If the "group role description" uniquely identifies a group role then it is the business key - so just treat it as you would any other business key.