I have been able to upload the dimensions that I will be using (most of them covers the suggestions given).

My process. I have several source data tables.

I create a table called Delegate which has as the source key of employee number.

I then create a surrogate key called DelegateID to use in my Delegate dimension

In all my other tables I create I am injecting the surrogate key DelegateID.

e.g.

SELECT

[Staff Group]

,DelegateID

,GroupTypeID

FROM

(

SELECT DISTINCT

[Staff Group]

,del.DelegateID

,ROW_NUMBER() OVER(PARTITION BY esr.Employee ORDER BY DelegateID) AS RowNum

,CASE WHEN [Staff Group] IN ('Administrative and Clerical', 'Estates and Ancillary') THEN 2

WHEN [Staff Group] IN ('Allied Health Professionals'

,'Add Prof Scientific and Technic'

,'Nursing and Midwifery Registered'

,'Additional Clinical Services'

,'Healthcare Scientists') THEN 1

WHEN [Staff Group] = 'Medical and Dental' THEN 3

ELSE 0

END AS GroupTypeID

FROM ESR esr JOIN Delegate del

ON esr.Employee = del.Employee) x

WHERE RowNum = 1

The surrogate key for Delegate dimension is in the StaffGroup query. When I create the StaffGroup dimension, the StaffGroupID surrogate key will be created by IDENTITY.

My dimension will look like:

StaffGroupID

DelegateID

GroupTypeID

StaffGroup

Hope that is clear.

Now, in creating the Fact Table, I am using the DelegateID as the anchor for the composite set of surrogate keys for for my fact

e.g. of what should happen when the fact table is loaded is (made up data) :

DelegateID, StaffGroupID, ConsentID, SafeguardingChildrenID, DateID, Status (fact)

1 , 1, 4, 0, 1200, 1

1, 1, 0, 5, 14, 1

0 meaning NA.

Hope this too is understandable.

The problem I am facing is that when I try to load the fact table by using a SQL join I am getting data that doesis not correct. An example would be:

Delegate 1 has done Consent and SafeguardingChildren and so on the JOIN, I will get four records

Consent and the ConsentDate

Consent and the SafeguardingDate

Safeguarding and the SafeguardingDate

Safeguarding and the ConsentDate

Two of these records are incorrect. Perhaps my JOINS are not correct

I tried using the LookUp function in SSIS but am running in to problems with the LookUps as it only loads some of the data. I am also not sure what to look up against. I am assuming it is the Delegate Table.

This is where I come to a standstill.

I hope this is clear and makes sense to others apart from myself.

I would like to thank you all again for taking time to help me. I really do appreaciate and I am reading a lot and learning a lot as well.

Regards,

Marvin.