ural Defin
2 Link-To-Link nk-to-Link rel
e, it is equiva k is below, in
this example son are both se in complex hildren of ea
ucing tempor attributes w ng the Link E
AT** The on le – in other data set canno
(Parent/Child ationship ind alent to a nes
Figure 5-19.
Figu e, Hub Produc parents to Li xity in loading ach Link.
rality as a rea ithin the Link ntity should n
ly time a Link words, it is a ot be update
Relationships dicates a par sted relations
ure 5-19: Exa ct and Hub S ink B. Link B g and queryin
ason to produ k table exhib
never happe
k may contai transactiona d or changed
s)
rent-child arra ship with diffe
mple of Link upplier are b B and Hub Te ng, and it bec
uce a new se it the exact s n.
n temporality al Link where d in any way
angement, o erent levels o
k To Link Rela both parents erritory are bo
comes most
equence in th same cause a
y is when it f the data is t (legally by th
r a hierarchy of grain. An e
ationships to Link A. Li oth parents t evident when
he Link and t and effect. T
unctions as a time-stamped he business).
y of some sor example of a
nk A and Hu to Link C. Th n Satellites a
reating This
a d,
rt. In this a
Link-To-b Sales is forces are found
Supe
er Charge Yo
an Linstedt 2 a modelers o
tionships are ents’ Hubs we pler to mainta em changes…
ttened” hiera
our Data Ware
2010-2011, a ften feel the e interesting a m a logical sta
chitecture. Im uring the load m the physica
fine to logicall d in the physica
flattened out, e (born by th
s the ETL to lo ll the time speaking, if w
e can repres ain (going fo
…) and, the s rchy, and the
Figure
ehouse
all rights rese need to put and may be e andpoint it is mplementing ding cycle. Th
l implementa ly model Link t al data model , and the hiera
e loading pro
ad sequential
we “denorma ent the same rward, it is “f structure is e e new structu
e 5-20: Step he following i ation.
to Link relatio . To avert issu archy depende ocesses) of p
lly to each chil
alized” the Lin e data set in future proof”
asier to load ure.
1, Flattening
k relationshi logically, the the parent-c model is diffi is a discussio
nships; the pr ues and proble encies remove ey are extrem child relations
icult because on on best pr
roblem is when ems, the Link ed.
plementing t
oves the abilit
s so each Lin manner. The
if the relatio uery. Figure
k Hierarchy
Page 97 of 15
//LearnData del. While the mely difficult t ships when m e of the pare
ractices for r
n they are structures
his type of st
ty to load all Li
nk is connect e structure b onship in the
tructure is
inks in
ted to its becomes source
the first
The
below shows
final step un normalized).
ct access to t ks, it will be a ords in LINK C
exist in Link A is absolutely time (within s ent records fo
Please no the logica relationsh
to remove Lin to Link B. Th Remember, L r to simplify t
ved or flatten the complete
Figure nhooks the de This new str the data set vailable base C which do n
A.
y vital to havi scope). If the or the entries
ote: the proce al model has hips have bee
nk A sequenc his moved Li Link B always
he model, th ned. Now, to ed Link Struc
e 5-21: Step ependency o ucture allows through the ed on direct r
ot exist in Lin
ing a Data W e other depe s in Link B an
ess of removi been built, as en established
ce from Link nk B to a pee s had the foll
e loading an o finish the jo
ctures which
2, Flattening f Link C to Li
owing grain:
d querying p ob, the proces
should be im
g Link-To-Link nk B. The Li be loaded in means that if lso has the f can be recor
apable of abs ere in place, w
t to load the
Link relationsh to accomplish
nd step, is to Link A, howev Product by S rocesses – t ss is repeate mplemented
k Hierarchy nks are now n parallel, an f the queries following effe rds which ex
sorbing 100%
we would be appropriate
hips should h h once the co
o put the Hub ver, Link B is Supplier by S
he dependen ed for Link C.
physically.
successfully d allows all q need access ect: There can
ist in Link B w
5-y flattened queries
Super Charge Your Data Warehouse Page 99 of 152
© Dan Linstedt 2010-2011, all rights reserved http://LearnDataVault.com 5.13 Link Applications
Link structures are all defined the same way. There are several different applications of Links which require discussion, introduction, and definition. It is these types of applications that are discussed in the following sections. The different types of Links include the following:
• Hierarchical Links
• Same-As Links
• Transactional Links
• Exploration Links
• Low-Value Links
• Computed and Aggregate Links
In some cases, the data within the Links is derived or computed by one or more business processes – thus resulting in a Link which contains non-auditable data; or at the very least, data which never existed in the source system. If this is the case – mark those rows with the appropriate “system generated” record source, or process name. Some of these cases include utilizing a Data Quality engine to produce “similarity” across names and households, business names, product names, etc… Other cases include using aggregate functions to produce corporate vision information that is used to drive the business in a day-to-day decision making function.
A majority of the time, these computations and aggregations or results of processing belong in a business Data Vault which is defined in the upcoming book: Quick Start Guide to Business Data Vaults.
5.14 Hierarchical Links
Hierarchical Links are just what their name implies: a Link structure which contains N levels of hierarchical data from the same Hub. For example, consider the case of an employee who reports to a manager. Their manager is also an employee – who happens to report to a director, and so on.
The Hierarchical Link allows roll-ups and aggregation of lower level data into a tree topology, or tree-like organization.
Hierarchies are a form of ontologies. Let’s look at an Organizational chart, and an example of a Hierarchical Link. Remember, the Hierarchical Link is an application of the standard Link structure.
It does not change the Link structure nor violate the rules in any fashion.
The
•
•
•
• In th exec
The the o man relat hiera
following ass
• Each divis
• The execu
• Each “offi
• Each “sto his case, diffe
cutive office.
Hierarchical office sequen ny office roll-u tionship is th archy today m
F sumptions ar sion office is n utive office is n
ce” has its ow re” has its ow erent stores r The Data Va
Link is show nce twice; on ups as neede hat: relationsh
may not be th
Figure 5-22: E re made abo not a store-fron
not a store-fro wn office busin n store busine report to diffe ault model w
Figure 5-23 wn in purple a nce for the ro ed. The reaso
hips in busin he same as y
Example Org ut the organi nt
nt
ness key ident ess key identif erent division ould appear
3: Hierarchica above the Hu oot office, onc
on for extrap ness change o
yesterday or e
anization Str ization shown
ifier fier
nal offices, th as Figure 5-2
al Link for Off b Office table ce for the “pa polating the h
over time. S even the sam
ructure n in Figure 5
he division of 23:
fices e. The hiera arent” office.
hierarchy to a So the represe
me as tomorr -22:
ffices report
rchical Link c . There can b a many-to-ma entation of th row.
to an
contains be as any
he
Supe
er Charge Yo
an Linstedt 2
The Data
ther example e, all employe ld look very s del for this str
member: the a ew use for the lication of the he Data Vault
nges).
5 Same-As Lin