We have two different systems that provide Opportunity Data. The Business Keys that identify an Opportunity is very different in the two systems. One system uses OPP_NUM + OPP_MONTH + OPP_YEAR as the Business Key and the other one uses OPP_NUM + BUSINESS_OFFICE_NUM + OPP_DATE as the Business Key.
Our HUB is currently integrating data from the first System. Now we need to integrate data from the Second system as well. What is the best way to approach this? It looks like the HUB needs to updated to hold Business Keys from the second system.
What are the best practices to achieve this using dbtvault?
@saqib - Does your current HUB store each part of the key in separate fields? Something like…?
HUB_OPP_HK
RSRC
LDTS
OPP_NUM
OPP_MONTH
OPP_YEAR
ABC123
OPPSRC1
1/1/2022
1
1
2022
I’m just wondering a little more about details. I don’t know the best practices with regard to dbtvault.
I also have a few “rabbit-hole” or “shiny thing” questions:
Does the OPP_NUM start anew each MONTH+YEAR, or is it contiguous apart from the associated date?
Given the two apparent “NUM” fields in system 2, is there an opportunity there to express that combination as a LINK? That would then alter what the actual business key of system 2 is, and, in that case, be more closely related to the business key from system 1.
Sorry for not answering the question. I too am interested in the answer. I just had a few questions of my own to ride on this one.
Yes, the OPP_NUM restarts at a regular interval. So we need to have the MONTH and YEAR as part of the BK
Not following you here. Both systems can use the same OPP_NUM for completely different Opportunities, as such we will need to create a Same as Link for the same Opportunities that exist in both systems (which does happen quite often). Same OPP_NUM in both system does not imply same Opportunity.
Note: In the System 2, the same OPP_NUM can be used by different BUSINESS_OFFICE for completely different Opportunities. That is why we need to have the BUSINESS_OFFICE_NUM as part of the Business Key. In addition to that, the BUSINESS_OFFICE_NUM helps in defining the “Type” of Opportunity.
So, the OPP_NUM cannot stand on its own (like a dependent key, almost )? Does your RV have a HUB_BUSINESS_OFFICE (or whatever the business concept name is)?
How does the business talk about their opportunities?
Hey, I have opportunity #35 in July, 2022 that will send our stock prices soaring!!
I’m being a bit facetious, of course; however, I’m wondering more along the lines of how the business really identifies the opportunities. Is it by a given name? Or is it truly by a surrogate numeric identifier? What other attributes (i.e., potential satellite fields) are available in each system? Are there truly no other potential identifiers as a business key? Are opportunities really more like transactional items? “Opportunity” implies, to me, that any one of those items could “convert” to an actual fulfilled transaction of some sort, whether that is a new client or account or…whatever else. Perhaps a synonym would be “lead?”
Am I unnecessarily creating a “square peg round hole” situation?
Anyway, I’m waaaaay off base with regard to the original question.
I am keenly interested in the final approach to this. I’m sure this isn’t a unique situation.
@Nicruzer . Good questions. And it is helping me think through things myself.
Typically the process is Lead -> Opportunities -> Quotes -> Orders
The Opportunities do not particularly have a name. They have a description, but those are free-text field that can not uniquely identify an Opportunity.
The BUSINESS_OFFICE is a Business Concept by itself.
But it also plays a part in uniquely identifying an OPPORTUNITY as a unique Business Concept. If there was a way to uniquely identify an OPPORTUNITY without the BUSINESS_OFFICE, we could have had LINK for the OPPORTUNITY, BUSINESS_OFFICE, and CUSTOMER.
And this is also where I am not 100% sure and would like community feedback
I think you might be confusing a Primary Key with a Business Key — they are not the same.
Keep the BK in the Hub but those dependent-child keys in the sats.
It looks like OPP_NUM is the BK and the other columns that make it unique by source are dependent-child keys. Each to load to their own RV-SAT and thus no issue arrises with loading or uniqueness.
In dbtvault you can include the those columns you identified as the PK for the SAT, it should keep those unique
If Business_Office is a independent business concept that means it is a Hub right? Then I assume Business_Office_Num is the BK for that Hub? If so then Opportunity is NOT a Hub but rather a Link - you should never have a column (or BK) from one Hub show up as part of a BK in another Hub.
Seems like something is missing here. If OPP_NUM (in both systems) is a system assigned sequence, that can repeat over time, it really is not a good candidate BK. So either your have yet to really find the true business key for Opportunity or you are trying to treat aa Link like ti is a Hub. Make sense? And a Patrick stated, a Date should never be part of the BK of a Hub (though it may occasioanlly be part of the BK of a non-historicized/transaction link).
The reason we have to use the Business Office as part of the OPPORTUNITY BK is because all the Business Offices are autonomous when it comes to registering the Opportunities. The same OPP_NAME / OPP_NUM will be used by multiple Business Offices to refer to completely different OPPORTUNITIES. And this is how the Business references to the Opportunities i.e. being tied to a Business Office. A OPP_NUM / OPP_NAME doesn’t have a Business meaning if it is not tied to a Business Office.
Okay, then Opportunity is a Link not a Hub. The link would have a FK to Business Office Hub. Your definition makes it clear to me that, for this source anyway, the business rules indicate that an Opportunity is defined IN RELATION TO a Business Office (hence it is a Link as you cannot have a FK between Hubs).
That does leave me in a quandry over your other source. It seems that the business definition of opportunity in Source 1 is very different from Source 2 so that may indeed be its own Hub and you will tie the two together via a Same-as-Link (but that leads to a Link table between a Hub and a Link). Unless somewhere in Source 1 there is a tie to Business Office that is just not evident from the source table you are currently loading.
Note: It is very common to have the same Opportunity registered in the two systems, in which case we will need to use Same-as-Link to map the Opportunity to the corresponding Opportunity in the other system.
)? Does your RV have a HUB_BUSINESS_OFFICE (or whatever the business concept name is)?
