Link table help

[blunckc1]

Hi team,

I need to create a link table and unsure how to approach this.  My model has 4 separate fact tables:

EmployeeData: //that contains identifiers for an individual
PID,
PAN

LeaveTaken:  //that contains the leave an employee has taken and importantly, the type of leave they have taken, this connects to the employee data table at the PAN level
LeaveTaken.WBPAN as PAN,//this is an employees identifier
LeaveTaken.LeaveType as TypeOfLeave

LeaveBalances: //that contains leave balance data and importantly, the type of leave balance it is, this connects to the the employee data table at the PID level
[LeaveBalances.Employee Code] as PID,
[LeaveBalances.Leave Group] as TypeOfLeave

FutureLeave: //because our systems suck, leave greater than 6 weeks into the future, comes as a separate file with a different file structure to the LeaveTaken file.  This also joins at the PAN level
EMP_ID as PAN,
LeaveTakenType as TypeOfLeave

The outcome I am wanting in a link table, that joins the 4 tables "together", so when filtering the data, what leave balance an individual has what leave they have taken all connects.  Like this:

LeaveLink:
PID,
PAN,
TypeOfLeave,
Plus additional keys to make the joins work properly.  Just stuck on getting all four table to combine together properly.

How would I go about creating this? 

Appreciate the guidance here!

Thanks

Carl

[JordyWegman]

Hi Carl,

Maybe don't use a link table and go for a concatenated fact table? Just concatenate them and then you can also filter on the fields you want. This is probably a bit easier and faster.

Jordy

Climber

[blunckc1 · Accepted solution]

Hey @JordyWegman 

Thanks for the response, that helped. Got something to work in the end through a combination of concatenation and left joins.  Script below in case anyone else needs to something similar.

Cheers

Carl

//Use the employee data as a base for the link table that will join the employee data, leave balance data and leave taken data together
LeaveLink:
Load PID as [LeaveLink.PID],
PAN as LeaveLink.PAN
Resident EmployeeData;

//Left join the leave balance types onto the leave link table at the PID level
Left Join (LeaveLink)
LeaveBalanceTypes:
Load distinct [LeaveBalances.Employee Code] as [LeaveLink.PID],
[LeaveBalances.Leave Group] as MasterLeaveType
Resident LeaveBalances;

//load the leave taken types to act as a temp table
LeaveTakenTypes:
Load distinct LeaveTaken.LeaveTakenPID,
LeaveTaken.WBPAN,
LeaveTaken.LeaveType as LeaveTakenType
Resident LeaveTaken;

//concatenate the future taken leave types onto the temp leave balance taken types table above
Concatenate (LeaveTakenTypes)
FutureLeaveTakenTypes:
Load distinct FutureLeaveTakenPID as LeaveTaken.LeaveTakenPID,
EMP_ID as LeaveTaken.WBPAN,
LeaveTakenType as LeaveTakenType
Resident FutureDatedLeave;

//reload and concatenate the combined leave taken types table onto the LeaveLink table
Concatenate (LeaveLink)
Load LeaveTaken.LeaveTakenPID as [LeaveLink.PID],
LeaveTaken.WBPAN as LeaveLink.PAN,
LeaveTakenType as MasterLeaveType
Resident LeaveTakenTypes;
Drop Table LeaveTakenTypes;

//reload the LeaveLink table and finalise the keys
NoConcatenate
LeaveLinkFinal:
Load *,
[LeaveLink.PID] & MasterLeaveType as LeaveBalances.PID_LeaveType_Key,
LeaveLink.PAN & MasterLeaveType as LeaveTaken.PAN_LeaveType_Key, //connects to the leave taken table
LeaveLink.PAN & MasterLeaveType as PAN_LeaveType_Key, //connects to the future dated leave taken taken table
[LeaveLink.PID] & LeaveLink.PAN as UniqueID //connects to the employee data table
Resident LeaveLink;
Drop Table LeaveLink;