So, there’s a bit of a debate around whether interactions originating from various CRMs should have a hub or not.
I stand corrected if I’m wrong, but to me the interaction is not a participant.
For example, we have a source table called ‘SchedulledCall’, which is a collection of scheduled/booked interactions that are completed over time - telephone calls, e-mails, virtual, face to face, etc.
The participants involved are a project/campaign, sales representative, healthcare professional, healthcare organisation.
These are the same participants involved in configuring the audience target list (“a-la” campaign member in Salesforce)
I’m not convinced that a ‘CallId’ should be used to create a hub, whether or not it has satellites around it or not.
Would anyone create an interaction hub with the taskid from Salesforce as the BK??
Hi Mark,
I think you might better start defining the business concepts the business is all about. If the interaction is a valuable business concept, you most certainly have to model that in your datavault.
The next question would become: what identifies an interaction. So, what does the business key of the concept consist of. I think it will be something like a subject/title, date-time and maybe something else, just like in your agenda: what distinguishes the one agenda-item from the other?
The callid is a technical key, which is definitely not a businesskey. Therefore forbidden or at least strongly discouraged to use. At best it will be an attribute in some satellite.
Interactions are what the word implies… interactions between business entities. They do not get their own hub, obviously it would be a link. Hub tables are modelled off business objects, period.
3 Likes
Thanks Guys,
My thinking is along these lines…
CAMPAIGN_CONTACT_TARGET_LNK:
| CAMPAIGN_FK |
AGENT_REP_FK |
HEALTH_CARE_PROFESSIONAL_FK |
HEALTH_CARE_ORGANISATION_FK |
| 1 |
1 |
1 |
1 |
| 1 |
1 |
1 |
2 |
| 1 |
1 |
2 |
3 |
| 1 |
2 |
3 |
4 |
| 2 |
3 |
4 |
5 |
| 3 |
4 |
5 |
6 |
These are the unique participants involved as part of a targeted sales/marketing campaign
CAMPAIGN_CONTACT_INTERACTION_LNK:
| CAMPAIGN_FK |
AGENT_REP_FK |
HEALTH_CARE_PROFESSIONAL_FK |
HEALTH_CARE_ORGANISATION_FK |
| 1 |
1 |
1 |
1 |
| 1 |
1 |
1 |
2 |
| 1 |
1 |
2 |
3 |
| 1 |
2 |
3 |
4 |
| 2 |
3 |
4 |
5 |
These are the unique participants involved in at least one interaction or more. Multiple interactions related to any combination of participants are modelled in the satellite tables.
CAMPAIGN_CONTACT_INTERACTION_LNK is a subset of CAMPAIGN_CONTACT_TARGET_LNK because not all campaign targets will have been contacted or reached by our reps.
The difference between the two LNK tables modelling each business process/activity is…
| CAMPAIGN_FK |
AGENT_REP_FK |
HEALTH_CARE_PROFESSIONAL_FK |
HEALTH_CARE_ORGANISATION_FK |
| 3 |
4 |
5 |
6 |
…because the target contact has not been contacted by a rep belonging to the project/campaign.
Hopefully this makes sense. I don’t believe I am breaking up the units of work, nor am I lumping everything, or putting too much, into one big link table.
The foreign keys are business keys, and not technical surrogate keys.
Thanks.
sounds ok… but why would you not consider using a zero key for the part of the relationship that is optional?
We’ve got it configured in the Dbt Vault project. I am right in thinking that any zero keys produced in my link table will produce no more than one zero key hub record, and that key will have no satellite records, or at least will just point to a ghost record.
don’t mix ghosts and zero keys (please re-read the blog) — you will only have one in hub (hub is unique list of keys).
in AutomateDV, they labelled zero keys as null-keys, same-same
I think I’ve got it. Probably just not explained very well.
Zero Keys - Links and Hubs
Ghosts - Satellites to support PIT
It’s all about the inner joins, so it seems.
For scheduled and completed calls, what kind of satellite am I looking at? Multiple calls might be scheduled by reps per campaign HCP contact.