The Data Vault model is built for extreme flexibility and extreme scalability. The Link table separates the relationships from the business key structures (the Hubs). The Link table provides for the representation of the relationship to change over time. The Satellites provide the descriptive characteristics about the Hubs or Links as they change over time.
For instance, suppose you own a car and you are the registered driver. You currently have two relationships to the car: one as a driver, and one as an owner. Now suppose you hired a driver.
Well, you still own the car right? Now, you have one relationship with the car as the owner, but the person you hired now has a relationship with the car as the driver. However, the description of the car has not changed.
What if you sold the car to someone else? Then your relationship with the car as an owner would END, and the buyer’s relationship with the car would begin. This information about the relationship between business keys is what we keep in the Link structures. Again, the basic description of the car remains unchanged so the Satellite data is untouched.
The Link table may also be applied to information association discovery. Business changes frequently – redefining relationships and cardinality of relationships. The Data Vault model
approach responds favorably because the designer can quickly change the Link tables with little to no impact to the surrounding data model and load routines.
MAJOR FUNDAMENTAL TENANT: THE DATA VAULT MODEL IS FLEXIBLE IN ITS CORE DESIGN.IF THE DESIGN OR THE ARCHITECTURE IS COMPROMISED (THE STANDARDS/RULES ARE BROKEN) THEN THE MODEL BECOMES INFLEXIBLE AND BRITTLE. BY BREAKING THE STANDARDS/RULES AND CHANGING THE ARCHITECTURE, RE-ENGINEERING BECOMES NECESSARY IN ORDER TO HANDLE BUSINESS CHANGES. ONCE THIS HAPPENS, TOTAL COST OF OWNERSHIP OVER THE LIFECYCLE OF THE DATA WAREHOUSE RISES, COMPLEXITY RISES, AND THE ENTIRE VALUE PROPOSITION OF APPLYING THE DATA VAULT CONCEPTS BREAKS DOWN.
For example, suppose a data warehouse is constructed to house parts – then after 3 months in operation the business would like to track suppliers. The Data Vault can quickly be adapted by adding a Supplier Hub, Supplier Satellites, followed by a Link table between parts and suppliers - the impact is minimal (if any) to existing loading routines and existing history held within.
Supe
© Da In Fi oran ware com One repr pare Rela by e Whe need An e
er Charge Yo
an Linstedt 2 gure 1-6 we nge (to the rig
ehouse desig ponents.
difference b esent associ ent keys to ch ationships su
mbedding th en the busine ds, the mode example of a
our Data Ware
2010-2011, a see the exist ght of the red gn to be flexib
between the D ations acros hild tables di ch as 1 to M he parent fiel ess rules cha el is altered a 3rd normal fo
ehouse
all rights rese Figure 1-6: F ting data war d dotted line) ble in this ma
Data Vault m s concepts.
rectly (withou any, Many to ds in the chil nge and the as is the oper orm model is
erved Flexibility of A
rehouse in pu ). Placing the anner – wher
odel and a 3 3rd Normal fo ut an extrapo o 1, and 1 to ld tables. Thi cardinality o rational appli s shown below
Adapting to C urple and the e association
re new comp
3rd normal for orm represen olated associ
1 are repres is leads to inf
f the data m ication using w in Figure 1
P
http:/
hange e new section ns in a Link ta ponents do no
rm model is t nts most rela iation table).
sented in 3rd flexibility of t ust change t g the model.
1-7.
Page 23 of 15
//LearnData ns in yellow a able enables
ot affect exis
the use of Lin ationships by
normal form the model.
to meet busin 52
aVault.com and
s the data sting
nks to y tying
directly
ness
In Fi plier”, which ordingly. Whe n one supplie
le this appea rational syste data wareho ehouse that c acts. In othe gle child table
• You have
• You have
• You have end result is m le with larger
Th Product can h
e this, the bu means that t en or if the b er words, any e. The end re
to rebuild ALL to rebuild ALL to re-model A massive re-eng and larger dat
his is the #1 topped, halte nd already h rchitectural d model! Don’t
p-front.
1-7: 3rd Norm have 1 and o usiness rule m
the operation business chan application m mall change it ly if the produ t Data Vaults eign keys em changes ma esult?
L your ETL load L your Queries LL your parent gineering effor
ta warehouse
reason why ed, burned, a igh cost of re design and d let this happ
al Form Prod only 1 supplie may be: “a Pr nal system th nges its rule must change, t may affect uct is a PARE ) this structu bedded in ch ade to parent
ding routines against the d t and child tab rts, and that’s
models.
Data Wareho and ripped ap e-engineering ependencies pen to you! U
duct and Sup er, but a Supp
roduct can on hat collects th to say: “a pro as must the
all kinds of u ENT to other t re leads to e hild tables, th t keys will cas
ependent stru bles
not all! The p
ouse/BI Proje part” or label g which is cau s built in to yo Use a Data Va
pplier Exampl plier can sup nly be suppli he informatio oduct can be underlying d underlying inf
tables.
even more co his leads to c scade all the
uctures
problem gets e
ects are “torn led failures!
used by poor our data war ault and avoi
le
pply many pro ied by a singl on is coded e supplied by data model st
formation in
omplexity. In
Supe
er Charge Yo
an Linstedt 2 is evident w del did not ex hitecture and tionships. Th ardless of car
Data Vault B Data Vault is a within). Thi
structing a bu of the found cific point in t ness key and re is a law in ned below:
In mathema result. It is a commutativ given a nam mathematic
basic notion lication, and any point in t in the Data V ussion on Hu
our Data Ware
2010-2011, a hen loading xist in the pas
loading proc he Data Vault rdinality.
Basis of Comm s based on ra
s is common usiness base a fundamental vity of simple op me or attributed cs. http://en.w
is that A = B B = staging a ime containe Vault – while ub based bus
ehouse
all rights rese history to a d st. Data ware cess, if the en t model mitig
mutative Prope aw data sets nly referred to ed Data Vault es of the Data ata Vault ach ping the load s called the c
tivity is the abili property in mos perations was fo d until the 19th wikipedia.org/wik
B = C at a spe area, and C = ed within C.
offering bas siness keys).
erved data warehou
ehousing tea nterprise dat gates this ris
rties and Set arriving at th o as “the raw t that will be a Vault is: en hieves this by cycles with t commutative
ity to change th st branches of m
or many years im century when m ki/Commutative
ecific point in
= enterprise This preserv e level integr
use where th ams run a hig
ta warehouse k by providin
Based Math he warehouse w data wareho discussed la able re-creat y loading raw the arrival da e property. T
e order of some mathematics an
mplicitly assum mathematicians
e
n time, where Data Vault; s ves the audita
ration across
P
http:/
e cardinality gh risk of re-e e model enfo ng Link tables
e (with little t ouse.” There ater in this bo tion of a sour w data, passiv ates of the da he commuta
ething without c nd many proofs ed and the prop began to forma
e A = a source such that A ca ability of the s lines of bus
Page 25 of 15
//LearnData that exists in engineering t orces current s for all relati
to no alterati e is a notion ook.
rce system d vely integratin ata set.
ative property
changing the en depend on it. T perty was not alize the theory
e system/so an be recons data set hou siness (see p
52
To fi ind out more iness of Data ther founding h. The Hubs ded based on ding processe ndard set the Set theory mathemat objects, su mathemat in primary objects. El context. M undergrad
n some cases evel star sche usiness what e between th pplications.
f the identifie about gap a a Vault Mode g principle be
and Links a n delta chang
es for restart eory is define y, formalized u tics. The langu uch as functio tics curriculum
school, along lementary ope he business r Information ed gaps.
nalysis, plea eling.
ehind the Dat re loaded ba ges inclusive ability, scala d as follows:
sing first-orde uage of set the
ns, and conce m. Elementary g with Venn dia erations such a d concepts suc atics curriculu
esent the Dat level star sc
systems are rules, busine quality (IQ) c
se read the b
ta Vault arch sed on union of the union ability, and pa
er logic, is the eory is used in epts of set theo
facts about se agrams, to stu
as set union a ch as cardinal um. http://en.w
ta Vault while hemas are u collecting, a ess operation can be improv
book: The Ne
hitecture is th n sets of info
functionality artitioning of
most common the definition ory are integra ets and set me dy collections and intersectio ity are a stand wikipedia.org/w
e C represen tilized to sho nd where the ns, and sourc
ved through
ext Business
he use of set rmation, whi y. Set logic is
the compon
n foundational ns of nearly all ated througho embership can s of commonpl on can be stud dard part of th wiki/Set_theory le the Satelli s applied to t
ents.
l system for l mathematica out the
n be introduce lace physical died in this
Supe
er Charge Yo
an Linstedt 2 he Data Vault ding routines
set theory is rted) is appli rder to the ta Data Vault a main purpos rovide you wi f. The archit neered with cepts are fou poses of the d he backgroun
implement y Data Vault M cessing). Ma deling compo ical partitioni is a base pa del is to split t
our Data Ware
2010-2011, a t approach, s is depicted i
Fig applied aga
ed or loaded arget table wh
nd Parallel Pro se to introduc
ith a glimpse tecture is not specific toler undational to design eleme nd reasons as our Data Vau Modeling com ssively Paral onents make ing. The vert rt of the arch the work, so
ehouse
all rights rese set theory is a n Figure 1-8:
gure 1-8: App in for Hub an d. Set-based here they hav ocessing Math cing set-theo e of the actua
t merely “just rance levels understandi ents are. I ho
s to why you ult.
mponents are lel Systems t use of this d tical partition hitecture. Th that the data
erved applied to inc :
plied Set Theo nd Link loadin logic is appl ven’t yet bee hematics
ry concepts a al engineering t another des so that it can ing why the a ope you find should stick
e based on pa typically use
esign techniq ning of data is e objective o abase engine
coming data
ory for the Da ng where onl
ied when sin en loaded.
and the math g effort behin sign” of table n scale, and b of vertical par
es can optim
P
http:/
sets. The se
ata Vault y new data ( gle distinct li
hematics beh nd the Data V es strung tog
be flexible as s designed, a enlightening ginal structur
ssing mathem thing design.
data in a vert Hub, Link an rtitioning with
ize the follow
Page 27 of 15
//LearnData et theory app
not previous ists of keys a
hind the Data Vault archite gether – no, it s necessary.
and what the g, as it explain res (unmodif
matics (versu The Data V tical format: a
d Satellite st hin the Data
are loaded
a Vault is cture t is These e specific ns some
•
•
•
• If yo can Figu
The behi Para The star, Busi
• Index Cov
• Data Red
• Parallel Q
• Resource u are not fam read about g
re 1-9 is a gr
principles at ind the Data allel Data Pro topology of t , ring, tree, h iness Keys, t
verage undancy (mini
uery
Utilization (sp miliar with the generic paral
raph drawn f
Im
t work expres Vault Model ocessing, Par the computin yper-cube, fa he relationsh
imize this) plit over hardw
e mathemati lel processin rom Wikiped
Figure 1-9:
mage: http://en
ss themselve can be foun rallel Task Pro ng cluster (da
at hyper-cube hips (associa
ware platforms ical principles ng rules and p ia that introd
Parallel Com
.wikipedia.org/w
s in the form d by reading ocessing, an atabase engin
e, or n-dimen tions), and th
s)
s of Massive performance duces the con
mputing Simp
wiki/Parallel_co
m of design an about parall d MPP syste ne) can be an nsional mesh
he descriptiv
ely Parallel Pr e speed up (ta
ncepts of Pa
lified
omputing
nd processin lel processing
ms design an ny of the des
. The Data Va ve data (repe
rocessing (M asking) on W rallel Process
g. The math g. Specifical nd architectu sired pieces i
ault splits ou etitive).
PP) you Wikipedia.
sing:
hematics lly:
ure.
ncluding:
ut the
Supe
er Charge Yo
an Linstedt 2 iness keys by matically. Bu
ker. The Hub ance) to split ical partitioni is the nature w as large as
elation to the forms, and a m for this is: “ is just one w tree, all the trees are no ducing a “cub a Vault in a H
Image
our Data Ware
2010-2011, a y nature are g usiness keys
bs therefore t different Hu ing at the ha way down to othing more t be-like” struc Hyper Cube –
e: http://clanbase.g
ehouse
all rights rese generally non by nature are act as indepe ubs across dif
rdware level.
d is known as t or the busin e hardware).
es replicated
”.
he Data Vault the individu han more Hu cture if desire it might look
Figure 1-10:
ggl.com/img.php?url
erved n-repetitive, t e also specif endent sourc al table struc ubs, Links, an ed. An examp k something
Logical Data
=fc07.deviantart.co
therefore inc ic to a set or ces of inform puting platfor
” Scale-out te s while keep r association othing environ
essentially b ctures built w nd Satellites ple of a logic
like this:
a Vault Hyper
om/fs14/i/2007/07 ping near line ns also follow nments for e
based on the within the mo within the Da ex coverage
a as an ident ng it easy (for er words, app
lows the mod ear performa w across mult ease of joins.
e principles of odel. Multiple ata Vault, thu conceptual v
_by_Meninx.jpg
52
In th asso they Hub Hype para
This data SAN The e
he physical m ociation. In t y are not dire s provide the er Cubes can allelism is in
is where it st set is not ye or a NAS driv end-result (to
model, Hubs a he physical D ctly related.
e keys, while n be created Figure 1-11:
Figure tarts, quite s et large enoug
ve. When the o an extreme
are connecte Data Vault M This is a con Satellites aro as can trees
e 1-11: Physic imple enoug gh. All of the e data set gr e) might be as
ed to Link stru odel nodes a nceptual bas ound Hubs “ . A simpler v
cal Data Vau h – no real p e tables go th ows, physica s shown in Fi
uctures; Link are connecte sis for establi describe” the vision or view
lt Layout (Sta partitioning of hrough one, t al partitioning
igure 1-12:
ks become a d through Lin ishing the pre e key for any w of the Data
arting point) f the data be two, or three g (or split-off
physical noti nks to each o emise of the y given point
Vault Model
ecause the si I/O connecti of tables) ca
ion for an other,
vision.
in time.
split for
ze of the ions to a n occur.
Supe
er Charge Yo
an Linstedt 2 his case, each d 0+1 format
tional databa endencies ac ormance. Th ching the MP
Data Vault M
our Data Ware
2010-2011, a Figur h table has I/
t. This is the ase engines t cross each ta he next step
P level of arc Model follows ee network is a cally. That is, t es for large val 2 < ? < 3, alth networks are ree, including
ehouse
all rights rese re 1-12: Phys
/O channels ultimate in s to operate th able. Truly in might be sep chitecture in s a scale-free a network who the fraction P(
ues of k as P(
hough occasio noteworthy b the protein ne
erved sical Data Va
bound to it, a separation fo
eir “parallel dependent h parating the p
hardware.
e topology. S ose degree dis (k) of nodes in k) ~ k-? where onally it may lie ecause many etworks, citatio
ult Layout (P along with de r relational d query engine e outside thes empirically ob on networks, a
P
http:/
Partitioned) edicated disk database eng es” without d
els can achie ower out into
pology is defin ws a power la having k conn ant whose valu se bounds.
bserved netwo and some soc
Page 31 of 15
//LearnData k (DASD) sitti gines. This a disk wait state
eve very high o different no
ned as follow w, at least ections to oth ue is typically i
orks appear to ial networks.[
The built grap the n cent 1.10 This inter intro cont Curr of th
Source: ht mathematics t from the Da ph, or a weigh nodes are th tralized withi 0 Introduction
topic is reall rest of time, oduction to th
tribution to lo rent data war he following p
ttp://en.wikipe s behind sca ata Vault prin hted graph in e “most impo n the graph, to Complexity ly deserving o and due to th his concept.
owering the o rehousing sys problems in a
edia.org/wiki/
le-free netwo ciples will ca n either 2 dim ortant” in the and have the y and the Data
of an entire c he fact that t The Data Va overall compl stems try to d a single load
/Scale-free_ne orks applies t arry the same mensions or 3 e Data Mode
e most neigh a Vault
chapter, perh this concept ault model an
lexity of the s do too much pattern:
etwork to the Data V e mathematic 3 dimensions l. The most i hbors.
haps even an must be brou nd methodolo
systems invo in their load
Vault Model.
cal propertie s, it becomes interconnect
n entire book ught to light, ogy make a t
lved in data w ding cycle. Th
Any physica s. Using a sp s apparent w ted nodes are
k. However, i I will make a remendous warehousing hey try to add
l model pring-which of
e
n the a small g.
dress ALL
Super Charge Your Data Warehouse Page 33 of 152
© Dan Linstedt 2010-2011, all rights reserved http://LearnDataVault.com
• Sourcing Problems:
o Synchronization / Source Data Availability time windows o Cross-System Joins
o Cross-System Filters o Cross-System aggregates
o Indexing issues, leading to performance problems o Disjoint or missing source data sets
o Missing source keys
o Bad source data, out of range source data o Source system password issues
o Source system Availability for loading windows o Source system CPU, RAM, and Disk Load o Source System structure complexity o Source system I/O performance
o Source System transactional record locks
• Transformation problems – often IN STREAM o Cleansing
o Quality and Alignment
o Quality and Alignment