Single Dimension field relates to many fields in further Dimension table

[#1]

Hi,

I have an issue whereby I have a single master table containing all staff, each of whom have a Staff ID - nothing unusual there.  However, in my main customer dimension table, there are multiple fields which relate to staff ID's.  For instance, Staff Member A1 is the primary account owner, Staff Member A2 is the Specialist for Product Group A1 Sales into that customer, Staff Member A3 is the Field Sales representative for that customer.

My question is, how can I relate the single table of Staff to each of these fields in my customer dimension?

I currently load the Staff table 8 times to achieve this, with 8 Staff ID Key fields being used to create the associations in the Customer table.  I want to try and get this down to a single staff table in the application.

Any ideas greatly appreciated.

[MarcoWedel · Accepted solution]

Hi,

you could create a link table between your customer table and your master staff table using a crosstable resident load of your customer table.

please provide a sample of both your tables to demonstrate.

see also:

Canonical Date

Why You sometimes should Load a Master Table several times

hope this helps

regards

Marco