Help with Synthetic Key

[Ciara]

Hi

This is going to be pretty long winded and hard to explain but please bear with me.

I have 2 different datasets in my data model and have a synthetic key issue.

Firstly, should you always resolve synthetic keys or are there any instances where you can leave them in place?

My 2 datasets are explained below.

This dataset gives all operating room slots that are assigned to surgeons

EndoSchedule:

Load
ResourceID&'-'&ScheduledEndoscopist&'-'&EndoRoomScheduleDate as %Key,
ResourceID,
EndoRoomScheduleDate,
ScheduledEndoscopist,
EndoRoomDuration .....etc

And this dataset gives all the details around the operations that were performed in the operating rooms

Endo_Stats:

LOAD
OperationRoomID&'-'&SurgeonName&'-'&ScheduleOperationDate as %Key,
VisitID,
Num(PatientCaseID) as PatientCaseID,
ProcedureDuration,
StartDateTime, ....etc

As you can see, I've created a %Key to link these 2 tables.  However, there are times where the %Key in the Endo_Stats table doesn't have a corresponding match in the EndoSchedule table (if a surgeon who wasn't assigned a slot in the operating room performs a procedure on a specific date.)

I also have a Master Calendar that isn't using any specific date from my datasets.

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
min(ScheduleOperationDate) as minDate,
max(ScheduleOperationDate) as maxDate
Resident Endoscopy_Stats;

Let vMinDate = Num(Peek('minDate', 0, 'Temp'));
Let vMaxDate = Num(Peek('maxDate', 0, 'Temp'));
Let vMaxYear = Year($(vMaxDate));
Let vCurrentMonth = Month(Today());
Let vCurrentYear = Year(Today());
DROP Table Temp;

TempCalendar:
LOAD
$(vMinDate) + Iterno()-1 As Num,
Date($(vMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

MasterCalendar:
Load
TempDate AS Date,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
AutoNumber(MonthStart(TempDate)) as _MonthSerial,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay,
Year(TempDate) & '-' & Month(TempDate) as YearMonth,
If(Year(TempDate)>=$(vMaxYear)-2, 1,0) as 'Prev3Years'


Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

In my app I am trying to calculate utilisation %.

'Month' is my dimension - I take the sum of all ProcedureDurations and divide by the sum of EndoRoomDuration.

(Sum({<ScheduleOperationDate={">=$(=$(varMinDate)) <=$(=$(varMaxDate))"}, DurationOfSurgerywithCleaning2={'=DurationOfSurgerywithCleaning2>0'}>}DurationOfSurgerywithCleaning2))
/
Sum({<EndoRoomScheduleDate={">=$(=$(varMinDate)) <=$(=$(varMaxDate))"},Capacity={'1'}>}EndoRoomDuration)

So in order to capture all durations of procedures in a month AND all durations of the endoscopy room schedule I've had to call the EndoRoomScheduleDate and ScheduleOperationDate as Date (so they both link to the Master Calendar).

I don't know how to 'fix' the synthetic key issue.  Any advice is appreciated.

Thanks

Ciara

[rwunderlich · Accepted solution]

You do not need to resolve a synthetic key unless the key represents a data modeling problem/error.  That said, most people consider synkeys to be a "bad practice" because it forces other developers to spend time evaluating if a problem may exist. Synkeys also make the data model viewer harder to use. 

HIC's opinion on leaving synkeys: https://community.qlik.com/t5/Qlik-Design-Blog/Synthetic-Keys/ba-p/1472634

My tutorial on resolving synkeys: https://qlikviewcookbook.com/resolving-synthetic-keys/

It seems to me that if you load both Dates as Date you will get a circular reference, not a synkey, but hard to say without a picture. Generally when using multiple fact dates like this you will want to use a date link table. Here's a tutorial on using common dates from multiple tables.  https://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/ 

Another alternative is to concatenate these two tables into a single table and name the date field as Date to link to the master calendar. 

-Rob

[Ciara]

Thanks so much for your response Rob.  I'll take some time and read the links you sent.  I think I need to rethink my data model and come up with a better one 😄 It's not my strongest area of expertise haha but I'll give it a go.

Thanks again

Ciara

[Ciara]

Hi Rob

After reviewing your reply (and saving your tutorial!!!) I've decided that my data model is actually fine with the synthetic key.

There's no corruption of data and more importantly I'm getting both sets of data where needed when having both dates as 'Date.

Thanks for the assistance.

Ciara