I am struggling figuring out how to transform a one to many table structure from a transact database into a one to one dimension for a data warehouse.
In the transaction database the data structure is as:
Root Record (Parent_ID)
Lookup Table "Many Parent_IDs" (Patent_ID, Child_ID)
Reference Table (Child_ID, Name)
Joining to my reference table brings back a data set that looks like such:
╔═════════════╦═══════════╦══════════╦════════════╗
║ Root_ID ║ Root_Code ║ Child_ID ║ Child_Name ║
╠═════════════╬═══════════╬══════════╬════════════╣
║ 1 ║ 1000 ║ 22 ║ Name 1 ║
║ 1 ║ 1000 ║ 23 ║ Name 2 ║
║ 1 ║ 1000 ║ 24 ║ Name 3 ║
║ 1 ║ 1000 ║ 25 ║ Name 4 ║
║ 2 ║ 1150 ║ 67 ║ Name 5 ║
║ 2 ║ 1150 ║ 68 ║ Name 6 ║
║ 2 ║ 1150 ║ 69 ║ Name 7 ║
║ 3 ║ 2019 ║ 11 ║ Name 8 ║
║ 3 ║ 2019 ║ 12 ║ Name 9 ║
║ 5 ║ 2010 ║ 91 ║ Name 10 ║
║ 5 ║ 2010 ║ 92 ║ Name 11 ║
║ 5 ║ 2010 ║ 93 ║ Name 12 ║
║ 5 ║ 2010 ║ 94 ║ Name 13 ║
║ 5 ║ 2010 ║ 95 ║ Name 14 ║
║ 5 ║ 2010 ║ 96 ║ Name 15 ║
║ 6 ║ 5010 ║ 33 ║ Name 16 ║
╚═════════════╩═══════════╩══════════╩════════════╝
What I am after in my dimension is to know, Root_ID (1) has the values of Child_ID(22,23,24,25) and be able to reference that using the generated key on the dimension.
Keep in mind I can never predict how many child records there will be, so creating X about of columns in my dimension isn't an option.
I am sure a wise data warehouse mind than me knows how this is normally handled.
To illustrate the desired result:
Fact Record (Dimension_Root_Key = 1)
Dimension (Key 1, Name 1, Name 2, Name 3, Name 4)
Turns out I should have studied a little more. I have learnt the nomenclature of "Bridging" tables.
Knowing that the kimball group has some great demos on how to achieve what I wanted to. For further reading on Bridging tables