Joining Tables While Avoiding Synthetic Keys

[villegasi03]

I have quite a few QVDs that contain employee productivity statistics. My dashboard's audience are managers and everything revolves around the date field and the employee id field. In order to avoid synthetic keys I have been loading each QVD containing some type of employee stats and then concatenating them at the end. As you might know this is creating a ton of null values so I am looking to find more efficient ways of relating the information.

I have experimented with creating a table by performing an outer join on a my employee table and an auto generated calendar but quite frankly I don't know what I am doing. I also did a little poking around and say something that mentioned creating a key by combining the date and employee id field but again I am not sure if that's the best way to go.

Any assistance with this would be very much appreciated.

Thank you

[#2]

Could you send a model of how the relationship is getting your tables?

[villegasi03]

Here is an example of what I have. What I am currently doing is concatenating tables one thru four and then left joing to my calendar table. This would leave me with one big table. So because the data is a mixture of summary (aggregated daily for example, one record for each day for each employee) and some data is details (many records for one employee for each days) I would not be able to left join all the tables. Currently this is causing a great deal of obsolete null values for fields that were not native to the source table.

TABLEONE		TABLETWO		TABLETHREE		TABLEFOUR		CALENDARTABLE
DATE		DATE		DATE		DATE		DATE
EMPLOYEEID		EMPLOYEEID		EMPLOYEEID		EMPLOYEEID		MONTH
CALLSTAKEN		QUALITYAUDITDATE		ACTIVITYTYPE		ADHERENCEPCT		MONTHNAME
TIMEWORKED		QUALITYAUDITTYPE		ACTIVITYDURATION		INADHERENCEPCT		YEAR
		ACTIVITYNAME		OUTADHERENCEPCT		WEEKDAY
				QUARTER

[amars · Accepted solution]

Hi Ismael,

you can load all the tables in this sequence

TABLEONE(DATE1, EMP1, DATE_EMP_KEY, CALLSTAKEN, TIMEWORKED)

TABLETWO(DATE2, EMP2, DATE_EMP_KEY, QUALITYAUDITDATE, QUALITYAUDITTYPE, ACTIVITYNAME)

TABHETHREE(DATE3, EMP3, DATE_EMP_KEY,ACTIVITYTYPE, ACTIVITYDURATION, OUTADHERENCEPCT, QUARTER)

TABLEFOUR(DATE4, EMP4, DATE_EMP_KEY,ADHERENCEPT,......)

Now form a Master table

MASTER_EMP_DATE_TEMP:

Load distinct

      DATE1 AS DATE,

      EMP1 AS EMP

      (DATE1 & EMP1) AS DATE_EMP_KEY

Resident TABLEONE;

CONCATENATE(MASTER_EMP_DATE_TEMP)

TABLETWO...

TABLETHREE...

TABLEFOUR...

MasterEmpDate:

LOAD DISTINCT

DATE,

EMP,

DATE_EMP_KEY

Resident MASTER_EMP_DATE_TEMP;

drop table MASTER_EMP_DATE_TEMP;

Drop FIELD DATE1, EMP1 from TABLEONE;

....TABLETWO;

... TABLETHREE;

...TABLEFOUR;

CALENDARTABLE(DATE, MONTH,.....)

Now all thefour tables should be linked with the DATE_EMP_KEY and the CALENDAR should be linked with DATE.

Thanks...

[villegasi03]

It looks like this is going to do the trick. Its going to take me just a bit to set up my script so I should be able to let you know how it turns out in the morning. Thank you very much for your assistance and I will get back as soon as I get set up.

[villegasi03]

This worked like a charm. I really appreciate all your assistance.