Data Modelling Issue

[#1]

Hello everyone,

I've been struggling with a data modelling issue and would like to get your advice on it. The issue primarily concerns linking a 'Monthly Target' file with Transaction data. and also linking all dates in both tables to the calendar.

Please find attached a PDF that shows the tables in my data model.

I'm also describing the business problem briefly. For every month there is a target to enroll students for a particular combination of (Centre/Circle/LOB/Process) which is specified in the Target table. Now, each student that is interviewed/enrolled is recorded in the Sourcing file along with his unique student ID, enrollment date, and the Centre/Circle/LOB/Process that he/she is enrolled for. So every student fulfills a target for a particular month based on his enrolled date.

Some students are rejected while some are enrolled. These enrolled students move to Training table and are assigned an Emp ID and go through various steps in training on different dates. These students (also now called agents with an Emp ID), have their performance recorded in Agent Perf. table. Agent Perf. table has many records per agent.

My challenge is to be able to link the Target table with sourcing table, and also link other tables, and at the same time get all dates from all tables linked to the Calendar.

I'll be glad to receive any help!

[Anonymous]

Manish,

To link Target to Sourcing, you need a combined key, for example:

Target:
autonumberhush128(month([FCM Date]),Centre,Circle,LOB,Process) as Key,
[FCM Date],
Target1,
Target2
FROM ...

Sourcing:
autonumberhush128(month([Enrollment Date]),Centre,Circle,LOB,Process) as Key,
Centre,
Circle,
...

Notice that I removed most of the fields from Target to avoid synthetic key.

As for the DocDate - I doubt that you really want to link it to all other dates.  Just ask the question - what do you want to see by selecting DocDate December 23, 2013?  Do you want, for example, to see Student IDs where Interview Date is Dec 23, or where Enrollment Date is Dec 23, or maybe where EmpIDs associated with this Student ID (in Training table) has Rec Date (Agent Perf table) on Dec 23?  Be careful what you wish for.  Maybe you better keep the Calendar as a data island.  Or connect it to only one date which is the most important in you situation.

Regards,
Michael

[#3]

Thanks Michael, this solution helps.