Data Warehousing 3
The Star and Snowfake Schema
What about changes in dimensions?
1 Thinking in Dimensions
• It is important to decide whether an atriibte owned ib a taile cobld (or cobldn’t)
ie a dimension.
• For example: look at the taile indicatnn tbrnover data.
• There are two sales channels: retail and post order. Each channel is modeled as a
separate atriibte. Sbppose that the companb starts sellinn on the Internet then
“Throbnh Internet" iecomes another atriibte.
• It is now ieter to model the three “sale channels as one dimension ieinn one
atriibte.
• Sales Channel is now an atriibte where the valbe Internet can ie easilb added.
Other new channels can easilb ie flled. Sales channel can also ie modeled as a separate
dimension in a star or snowfake schema!
• Another example: Read the followinn descripton:
• An e-commerce companb - smarterwork.com – offers throbnh a virtbal network
market a service where providers can descriie some projects and experts can react to
one of the descriied jois. Projects are ornanized ib service providers and fall within
three possiile catenories: “IT “Graphic Design or “Web Research . Each project can
onlb ie linked to one of those catenories. Fbrthermore a project
has a bniqbe identifictin numbeat a dicte it was issbed a
deicdline which the project mbst ie carried obt a patife and a
stictus (e.n. available finishei …)
• If we focbs on the dimension Project the followinn mbst ie
avoided:
1
, • Even worse is the next solbton with mini-dimensions:
• “IT “Web Design and “Graphic Design are three examples of tbpes of projects. So
create an atriibte “Kind_if_patijeft in which “IT “Web Design and “Graphic Design
are 3 valbes. The same applies to finishei and available which are aiobt the stictus if
the patijeft!
• So:
• A tp: if an enttb (inclbdinn project) can onlb possess one valbe at a tme (either
“available or “finishei either “IT or “Web Design or “Graphic ) then the chance is
hinh that it is an atriibte.
2 Slowlb Channinn Dimensions: Modelinn Historb
• Proilem:
o Dimension tailes are relatvelb “staile tailes (few channes few additons).
o A taile Client for example concerns a fxed client iase which won’t ie
channed contnbobslb.
o New clients can bet ie added or the informaton aiobt existnn clients can ie
channed (e.n. change of aiiress change in the ratng of a client etc.)
o These channes can lead to inconsistencies or omissions in the data
warehobse if it has ieen too simplb desinned.
• Example: consider the followinn sitbaton:
• Sbppose that mananement wants to know sales ib citb
• We then have:
2
The Star and Snowfake Schema
What about changes in dimensions?
1 Thinking in Dimensions
• It is important to decide whether an atriibte owned ib a taile cobld (or cobldn’t)
ie a dimension.
• For example: look at the taile indicatnn tbrnover data.
• There are two sales channels: retail and post order. Each channel is modeled as a
separate atriibte. Sbppose that the companb starts sellinn on the Internet then
“Throbnh Internet" iecomes another atriibte.
• It is now ieter to model the three “sale channels as one dimension ieinn one
atriibte.
• Sales Channel is now an atriibte where the valbe Internet can ie easilb added.
Other new channels can easilb ie flled. Sales channel can also ie modeled as a separate
dimension in a star or snowfake schema!
• Another example: Read the followinn descripton:
• An e-commerce companb - smarterwork.com – offers throbnh a virtbal network
market a service where providers can descriie some projects and experts can react to
one of the descriied jois. Projects are ornanized ib service providers and fall within
three possiile catenories: “IT “Graphic Design or “Web Research . Each project can
onlb ie linked to one of those catenories. Fbrthermore a project
has a bniqbe identifictin numbeat a dicte it was issbed a
deicdline which the project mbst ie carried obt a patife and a
stictus (e.n. available finishei …)
• If we focbs on the dimension Project the followinn mbst ie
avoided:
1
, • Even worse is the next solbton with mini-dimensions:
• “IT “Web Design and “Graphic Design are three examples of tbpes of projects. So
create an atriibte “Kind_if_patijeft in which “IT “Web Design and “Graphic Design
are 3 valbes. The same applies to finishei and available which are aiobt the stictus if
the patijeft!
• So:
• A tp: if an enttb (inclbdinn project) can onlb possess one valbe at a tme (either
“available or “finishei either “IT or “Web Design or “Graphic ) then the chance is
hinh that it is an atriibte.
2 Slowlb Channinn Dimensions: Modelinn Historb
• Proilem:
o Dimension tailes are relatvelb “staile tailes (few channes few additons).
o A taile Client for example concerns a fxed client iase which won’t ie
channed contnbobslb.
o New clients can bet ie added or the informaton aiobt existnn clients can ie
channed (e.n. change of aiiress change in the ratng of a client etc.)
o These channes can lead to inconsistencies or omissions in the data
warehobse if it has ieen too simplb desinned.
• Example: consider the followinn sitbaton:
• Sbppose that mananement wants to know sales ib citb
• We then have:
2
