settingsLogin | Registersettings
Show Menu

How to model multiple 'concepts' in a single source table

0 votes
We have a source system table that contains a record for each phone number and each e-mail address associated with a customer and address.  The table has its own primary key.

So the main attributes are:
Contact Number (foreign key)
Address Number (foreign key)
Communication Device Number ( the primary key to this table)
Number (which can contain a phone number or an e-mail address)
Type (which contains a code denoting whether the 'Number' is a current phone number or e-mail address, or an old number or email address)

The design is clearly a bit peculiar.  I suppose that the email and phone number ought to be attributes of a contact hub satellite, but mapping to a Raw Data Vault structure on load would be slightly tricksy (multiple rows mapping to a single row at a point in time).

Added to this, any individual record can be edited such that it changes from an e-mail address to a phone number!

We think we ought to keep the RDV structure as close to source structure as possible but I'm wondering whether this would mean one hub (equivalent to the source table) or two hubs (one for phone numbers and one for e-mails).  The latter option appears to be impossible because the source system allows editing from one to the other - thus making the ending of a satellite on one hub slightly harder.

Any thoughts on a 'preferred pattern' for this type of situation.?
asked Oct 22, 2017 in Modelling by davepons (140 points)

1 Answer

0 votes
Hi davepons,

 In the raw vault you need to ensure all data is loaded at all time.

 You could create :
 Raw Vault Hub using the Communication Device Number as BK
 Raw Vault Link between the customer and Communication Device Number
 Raw Vault Link sattelite containing Number and Type attributes

 Next you can create a business vault hub_customer satellite containing separate columns for e-mail and Phone number. Using additional logic you can load these from the raw vault data.

 Now you have preserved history of the source, and added logic to get the customer attributes.

 If there is 1-to-many relation between the customer and either Phone numbers or e-mail adresses you can create Hub_Phone number and Hub e-mail in the business vault and connect it thru links with the customer.

 Hope this helps,

 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 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. | DWH Wiki | Recent questions RSS feed | Imprint | Provided by Michael Olschimke