Hubs are defined by a unique list of business keys. They are surrounded with additional technical metadata elements such as load date time stamp, last seen date (optional), record source and sequence number. Business keys may be composite (made up of more than one field), intelligent (smart-keys) – contains meaning across parts of the keys, or sequential in nature.
Though not ideal, or desirable, over time operational application developers have made the mistake of producing, displaying, and reporting on sequence numbers. The definition of a business key will be discussed in detail starting in section 4.2
Tech Tip: Meaningless sequence numbers in operational systems can be a
design/architecture hazard. If sequence numbers in operational systems are exposed to business users then they become (by default) business keys. Hub Tables are meant as a consolidation point for horizontal business functions. For example: Customer Account Numbers should span multiple lines of business. At the end of the day having a single customer account number from customer inception to delivery is what the business process needs to provide corporate level answer sets.
Unfortunately in the real-world, customer keys (as with so many other business keys) change depending on the system being used. The keys change from one state to another as the customer information passes from one system to another. These changes are typically a manual process resulting in little to no visibility at the corporate level for where a customer is in the life-cycle of business.
Supe
er Charge Yo
an Linstedt 2 gure 4-1 abo sferred from e used horizo ansfer. Wha ness key fro y also freque uence numbe
ch sequence osed, the bus ney) when the
O a w th ca
our Data Ware
2010-2011, a Figure 4 ove the key c the sales sy ontally across at business d
m one line of ntly allow thi ers as busine e numbers in siness begins e sequence n
One of the “jo nalysis - that way the busin he systems a an quickly loc
ehouse
all rights rese 4-1: Business
hanges throu stem to the p s all lines of oesn’t realize f business to
s to happen ess keys. Cle
Siebel or Pe s to use them number for th
bs” that a go t is: provide t
ess believes re collecting cate where t
erved
s Key Changin ugh an Excel procurement
business reg e is just how o the next.
by implemen early, sequen opleSoft or S m as business
he same cust
ood data war he business they are ope the data. By hey are hem
ng Across Lin managed pr t system. The gardless of th
much mone
nting off-the-s nce numbers SAP, etc… Be s keys – auto tomer differs
ehouse shou with a view o erating their
y examining t orrhaging mo
P
http:/
ne of Busines rocess when e ideal would he system of
y they are los
shelf product from Oracle ecause the se
omatically los s across mult
uld perform is of the GAP be
the custome d be for the s origin and th sing by “chan
ts which exp Financials w equence num sing traceab tiple systems
s: gap etween the nd the way will never
mbers are ility (and s.
4.1
ness keys in vidual is a dif espectively m
s have sever OAD_DTS), an
ryption key (E part of the H ryption key is t seen dates acking delete king Satellite uence numbe purpose of t rce system, b s assists in th ness to track ent state of a n they don’t ifferent conte me examples
on and Purpos is to track th d, and where a Hub shoul fferent grain modeled in tw
ral of the stan nd Record So ENCR_KEY) a ubs when th s not standar are not requ ed rows/agin es which are er, load date he Hub is to but is suppos he discovery k their inform application sy synchronize, extual data s of Hubs and
se
he first time t e it came from
ld be defined than Custom wo different H
Figure 4 ndard fields ource (REC_S and potential e data set is rd which is wh
ired, and are ng business k covered in th , and record provide a so sed to have th
of patterns a mation across ystems. The business ke sets.
their data ar
the Data Vau m. The Hub i d as the same mer Corporati
Hubs as show
4-2: Hub Exa keys. Busine he Satellite c
source.
ft-integration he same sem across system s lines of bus ese systems a
eys begin to b
re shown in F
ult sees a bus is a business e semantic g ion. Each of wn in Figure 4
mple Images quence numb special case en Date (LAST
It may be one sted in Chapt of the core ar ess keys in Hu
hapter. Req
n point of raw mantic mean ms. The Hub siness; this p
are supposed be replicated
Figure 4-3:
siness key ar s key recordin granularity. F
these types
chitecture. L ubs may be t uired in the a
w data that is ing. The res key also allo rovides a con d to synchron and worse y
rrive in the ng device. T For example:
of customers
oad Date also include S). The encry wo-part public
Last seen da tracked throu nize, but ofte yet, are then
he
Customer s should
e an yption key c key.
tes assist ugh status
are the
Supe
er Charge Yo
an Linstedt 2 he HUB_CUST errors in ent
The busin your cust the deals
en the pattern nce) that are ehouse to po ded gap anal y be broken s cess that ince
ness figures cesses of the
re are ways t archical Link s in-tact in o What is a Bu siness key is practice to h ness keys sh uence ID’s tha
esses. Busin include:
our Data Ware
2010-2011, a T_ACCT (Hub try, possibly a
ness says: “W omer numbe and getting
ns in the data e not in contra oint out the p
lysis between source system entivizes peo
out why it’s e business.
hat these ke ks and same-rder to re-con usiness Key?
s something t have unique b hould be uniq
at have been ness keys are
ehouse
all rights rese Figure b Customer A
a lack of edit
We always cre rs from contr the money.”
a are examin acts. It’s up attern. With n the busines m synchroniz ople in sales t
happening a
ys can be rol as Links is d nstitute the s
that the busin business key que, but often n released to
e supposed t
erved e 4-3: Hub Ex
ccount) it is e masks. The
eate our custo racts first bec
ned, it is clea
lled together iscussed in t source system
ness uses to ys. Business n are not. Bu business us to have mean
xample Data easy to spot e typical requ
omers in con cause they ar
r that Sales ess to figure analysis, the ents and the es, or worse:
customers.
fix it in the s
for BI report the Link chap m as necessa
track, locate keys are als usiness keys sers and now ning to the bu
P
http:/
similar patte irement in th
tracts. You w re responsibl
has produce out why; it’s data wareho source syste a loop-hole i All of this is source system
ting purposes pter (chapter ary for audita
e, and identif so known as n
may actually w are embedd usiness. Exa
Page 59 of 15
//LearnData erns, fat-finge
his case is as
will always ge e for closing
d keys (as ha the job of th ouse can pro ems. In this c
n the busine speculation ms or primary
s. The notion ded in busine amples of bus
52
case there ss
until the y
n of ata itself
n. It is a . system ess
siness
• Vehicle Identification Number (VIN)
• Auto License Plate Number
• Driver’s License Number
• Account Number
• Portfolio Number
• Part Number
• Work Order Number
• Employee Badge Number
• Invoice Number
• Ticket Number
• Bar Code
• Product Number
Each of these keys “stand-alone” in business and in the operational systems – they usually are surrounded with descriptive context to give them meaning. In data modeling terms these keys are parents, and do not require any additional keys to provide them with the grain of definition. There are times when business keys are composite keys (such as VIN numbers, or bar-codes). These are also known as intelligent keys. Business keys may also include the “natural key” and the
corresponding source system surrogate sequence key; because the business failed to make the natural key truly unique – and the source system surrogate is now needed for traceability within the EDW.