TEORÍA DE LA CODELINCUENCIA (I): AUTORÍA
2. EL CONCEPTO DE AUTOR. ESPECIAL CONSIDERACIÓN DE LOS DELITOS DOLOSOS DE ACCIÓN
Sometimes there is no need to store history of reference data changes; in this case we use a typical 3NF or 2NF type table. The nature of a data warehouse is in fact to store history, but when the business signs off on the expected “no-history requirement” then the EDW team has the go-ahead.
A no-history reference table is a structure that has no history! Imagine that!
Ok, enough kidding aside – it’s a table with no begin and no end-dates. Before I go on, I’ll say this:
reference tables can be designed as Hubs and Links, or as simple 3rd normal form tables, that is:
flat and / or wide, it’s up to you. You need to decide what’s best, and what fits – then load it and go.
What types of data might you see in a no-history reference table? Well, that all depends of course, but here are some examples of what I’ve run in to in my career:
• Medical drug prescription codes and definitions
• Stock exchange symbols
• Medical diagnosis codes
• VIN number codes and definitions (manufacturer codes)
• Calendar dates
• Calendar Times
• International currency codes
• US State code abbreviations
And so on… If you’d like to add to this list, I’d love to have your feedback. Just put the example in an email and send it off. An example of a non-history based 3rd normal form reference table is shown in Figure 8-1.
In ad
ddition to cap reference row uence numbe t’s the whole del. The COD member, that ce you can n
History Base ory based ref history of des last year, las ecially if the r ted in the fut cription was a his case, I wo
rence data.
ural keys ten cally it’s the n
icularly in the
pturing the c w was loaded ers for join pe point of refe DE is the natu a non-history o longer ask ed Reference T ference table scriptions. In st month, and reference da ture, sometim
at a particula ould strongly
However, I w d to be much natural keys e Satellites.
Figure 8-1:
ode, short de d. Sequence
erformance, erence tables ural key of the
y reference t the question Tables es are refere n other words
d so on. The ta relates to mes the busin ar point in tim urge you to c would discour h more consi which appea
Non-History escription an e numbers ar but most tim s, to use the c
e table.
table will only n: what was t
nce data with s, we or the b history may financial rep ness or the a me.
create Hubs, rage you from
stent over tim ar in the rest
Reference T d long descr e optional. T mes I simply u code, and lea
y and forever the descriptio
h a requirem business wan
become imp ports. Particu auditor wants
Links, and S m using SEQU
me (in the ca of the raw Da
Table
ription, I like t There are tim use the CODE ave the code
r show the cu on of the CA c
ment or a bus nts to track w
ortant for ce ularly when o s to see what
Satellites to h UENCE numb ase of referen ata Vault mo
to capture th mes when I us E as the prim e in place acr
urrent value code last yea
iness need to what the desc rtain referen old reports ar t the code an
house the his bers in this si nce data), an odel (EDW Mo
he date
Supe
© Da Addi code othe proc is sh
In th code (the a dif
er Charge Yo
an Linstedt 2 ing sequence es tend to be er hand, if yo ceed to use t hown in Figur
his Figure, yo e, along with
code) as the fferent appro
our Data Ware
2010-2011, a e numbers to e static (i.e., e u have a vali he sequence re 8-2:
Figur u can plainly the historica e primary key oach (using a
ehouse
all rights rese o the history b ever see the
d reason to d es all across y
re 8-2: Stand y see the prev al values of th y, but the load Hub and Sa
erved based refere
abbreviation do so – then your model.
dard History B vious code, t he previous c
d date must tellite) as a h
ence tables u for the state don’t be shy An example
Based Refere he time-line f code. We are be included f history based
P
http:/
sually adds n e of California y. Document
of a history b
ence Table for the validi e however us
for uniquene d reference ta
Page 149 of 1
//LearnData no value sinc a change?).
t the reason, based refere
ty of the prev sing the natu ess. Figure 8
able:
152
aVault.com ce the
On the and
nce table
vious ural key 8-3 shows
8.3 Code take simi uniq mad Beca EDW reas code
Code and De es and descr e the most eff
lar codes tog que “group co de-up or man
ause the gro W (generally),
sons only. Th e or a group c
Figu escriptions riptions are c
ficient route gether in to a ode” to help ufactured co up-code is sy I usually try he example in
code to use a
re 8-3: Hub/
commonly fou – that is: one a single “mas
make the un olumn (hard c
ystem genera to keep the g n Figure 8-4 a single struc
Sat History B
und in refere e that makes ster code” tab derlying code coded data in ated, and it h
group code in is made-up d cture and ho
Based Refere
nce data. If s logical sens ble. In these e unique. Of n the ETL rou has no forma
nside the ED data, but sho use all your i
ence Table
you have a lo se. I like to g e cases, I hav ften times th utine).
l business m W for joining ows how you c
nformation.
ot of codes to group many o ve to also ass e group code
meaning outsi g and uniquen
can apply a m o model of the
sign a e is a
ide of the ness master
Supe
er Charge Yo
an Linstedt 2 ou can see, t
wn you enoug rules and sta his; allowing y uld like noth stions, or com
lenges you fa u become a cerely,
Linstedt
our Data Ware
2010-2011, a this solution
fusion with in consistent, i
ons
Model and me en you break gh insight to andards that you to overco ing more tha mments. I’m ace in your d Data Vault fa
ehouse
all rights rese Figure 8-4:
has a few fla nterpretation t shouldn’t b
ethodology a k the rules an see how to a you can take ome and avo
n to help you m always inter ay to day job an along the
erved Group Code aws: if the gro n of the surro be such a pro
re highly vers nd standards apply the app e advantage
id the potent u succeed, an rested in hea b.
way, feel free
e and Descrip oup code cha ogate keys. H oblem.
satile when t s that you ca propriate and
of the years tial pitfalls an nd to hear fro aring about c
e to let me k
P
http:/
ption
anges or the However, as l
the standard n get in to tro d proper desi
of research a nd project iss om you abou ustomer suc and design I’
sues.
ut your conce cesses as we
152
INDEX
adaptability, 38, 73, 76, 83 Architectural. See Architecture Architecture, 9, 139
Basic Terminology, 10
Business Key, 4, 7, 11, 55, 57, 64, 72, 73 Business Keys, 4, 27, 58, 59, 61, 63, 71 Data Vault, 2, 3, 7, 8, 10, 15, 16, 17, 18, 19,
20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 51, 52, 53, 56, 61, 62, 63, 65, 66, 67, 68, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 87, 88, 90, 91, 92, 93, 97, 98, 99, 100, 101, 102, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 118, 119, 121, 122, 123, 124, 126, 127, 129, 137, 138, 139, 144, 145, 146, 149
Data Vault Modeling. See Data Vault
EDW, 3, 7, 33, 36, 37, 38, 39, 41, 45, 46, 48, 49, 52, 58, 76, 78, 81, 82, 83, 120, 145, 146, 148
Flexibility, 3, 4, 7, 21, 22, 76, 78 HUB, 18, 44, 50, 51, 57, 64, 140 Hubs. See Hub
Link, 4, 5, 7, 8, 18, 20, 21, 22, 24, 26, 28, 34, 43, 44, 49, 52, 57, 69, 75, 76, 77, 78, 80, 81, 82, 83, 85, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 122, 123, 139, 142
Links. See Link
Load Date, 7, 45, 46, 56, 66, 73, 105, 111, 112
Load End Date, 7, 47, 111
Record Source, 7, 9, 45, 56, 66, 73, 121, 124, 125
Reference, 6, 9, 11, 53, 144, 145, 146, 147 Satellite, 5, 6, 8, 9, 26, 34, 43, 44, 47, 48,
49, 52, 53, 56, 85, 89, 90, 92, 102, 103, 104, 105, 106, 110, 111, 112, 113, 114, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 140, 141, Satellites. See Satellite 147
Scalability, 4, 76, 84
Sequence, 3, 9, 32, 43, 44, 66, 73, 91, 112, 125, 130, 146
sequences. See Sequence SQN. See Sequence