settingsLogin | Registersettings
Scalefree
Show Menu

Hub for multiple sources

0 votes
Hello,

I have a question on how to model Hub(s) when data comes from multiple sources. We have client details coming from 3 sources (Sources A, B and C).
Some clients exist in all three source systems but with different ids. Source B and C also have corresponding Client IDs of Source A, so we can use that
to identify common clients.

Following table diagrams are formatted for fixed-width fonts and best viewed by copying and pasting on to Notepad

Eg.
Source_A:
_____________________________
|ClientID   |   ClientName  |
|===========|===============|
|A100       |   Client XXXX |
|A200       |   Client YYYY |
|A300       |   Client ZZZZ |
|A400       |   Client FFFF |

Source_B:
_______________________________________________________
|ClientID   |   Source_A ClientID   |   ClientAddress |
|===========|=======================|=================|
|B20        |   A100                | .....           |
|B30        |   A200                | .....           |
|B40        |   -                   | .....           |


Source_C:
_______________________________________________________
|ClientID   |   Source_A ClientID   |   Client Phone  |
|===========|=======================|=================|
|C55        |   A100                | .....           |
|C60        |   A300                | .....           |
|C65        |   -                   | .....           |


How do we model "HubClient" in this situation? What are the correct ways to model HubClient (or Clients) according to Data Vault modelling?

What about a design like below? Here, the combination of the 3 Source System Keys is the effective business key of the hub.
There will be 3 satellites (one for each source)

HubClient:
__________________________________________________________________
|Client Surrogate Key| Source_A ID  | Source_B ID   | Source_C ID|
|====================|==============|===============|============|
|SK001               | A100         | B20           | C55        |
|SK002               | A200         | B30           | -1         |
|SK003               | A300         | -1            | C60        |
|SK004               | A400         | -1            | -1         |
|SK005               | -1           | B40           | -1         |
|SK006               | -1           | -1            | C65        |

Does this design comply with Data Vault modelling?

Thanks
asked Oct 22, 2017 in Modelling by surm (120 points)

1 Answer

0 votes
Hi Surm,

You need to answer the question, what is the Enterprise Business Key first.

From what you show, in every source there is a common Client ID business key.
If it really is the number, the business reffers to a unique client then
my suggestion would be to load the hub_Client with the same Client ID coming from all 3 sources.

The other ID's(which are not really business keys) can be loaded into (possibly)source specific satellites as attributes.

Hope this helps,

Greets
Jacob Siemaszko
answered Oct 25, 2017 by simco_admin (660 points)
Scalefree Scalefree

Upcoming Trainings

  • July 01 to 03 - Amsterdam (English) Data Vault 2.0 Boot Camp and Certification

  • August 05 to 07 - Berlin (German) Data Vault 2.0 Boot Camp and Certification

  • September 9 to 13 - Hanover WWDVC EU

  • September 16 to 18 - Brussels (English) Data Vault 2.0 Boot Camp and Certification

  • October 21 to 23 - Hanover (German) Data Vault 2.0 Boot Camp and Certification

  • November 18 to 20 - Amsterdam (English) Data Vault 2.0 Boot Camp and Certification

  • December 02 to 04 - Dusseldorf (German) Data Vault 2.0 Boot Camp and Certification

  • December 16 to 18 - Zurich (English) Data Vault 2.0 Boot Camp and Certification

  • Contact us for inhouse training engagements.
    Visual Data Vault: Logical Modelling for Data Vault - Free Download!
    Scalefree
    DataVault.guru is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 United States License.
    Permissions beyond the scope of this license are available in our license.

    DataVault.guru | DWH Wiki | Recent questions RSS feed | Imprint | Provided by Michael Olschimke
    ...