I have 2 tables which I load, which are joined on a synthetic key (CENTER_ID and DATE). One contains the visits of members, and the other contains the visit targets for each center. I use them to show the performance to target.
I noticed that centers which had no visits on a certain day, did not show a target for that day as well. It seems like the synthetic key created by the platform, requires the combination of CENTER_ID and DATE to exist on both tables, otherwise it won't load the data.
I don't think it's how its supposed to behave (Correct me if I'm wrong), and it seems inconsistent as well. Certain loads the data would load as I expect, but the next one it would not.
I was able to work around this by creating a table that connects the other 2 using a concatenated key, instead of letting the system create a synthetic key automatically.
My question is this:
Is having every combination of CENTER_ID and DATE required on both tables, in order to used a system created synthetic key?
The synthetic key isn't your problem - whereby you should always avoid them even if they work identically to an own combined key. Your problem are missing key-data and you couldn't solve it with a table-association. One solution would be to concatenate both tables which is quite easy and performant and the other would be to create a link-table between both tables which contained all key-values. See for this: Concatenate vs Link Table
It seems that the calculation of vPriorYearEnd isn't working properly sometimes. If I only load my TARGETS, the data is always fine, but it seems that sometimes the system checks the max(CHECK_IN_DATE), based on the CHECKINS table, and not from the whole data set.
My biggest issue seems to be that it's inconsistent. I attached 2 screenshots of the same visualization. Each time was after I did a load (The load script hasn't changed, and the data files haven't changed either).
Removing the vPriorYearEnd from the set analysis doesn't help either. I tries with just Sum(Daily_Target), and it still cuts off at august, even though the data in the QVD exists up until the end of December.