UNII-
Data ularehouse and OLAP Technclog
Aatn Wavehouse- A dota NClYehouse is a
reposltorg
infovmation colected from multiple Sources,
edoved under a
unutued scnema, and
usuatly 1esiding
at a Snqle site
Data warehouses are Construtted via a poes
Aato cleaning, data neqration, data transfo7m-
of
ation, data toading and periodic data 1efreshing
Data ware house ' a relationat datalbase manaqement
uctem CRDBMS) Construct to meet the Teuirement
processing systems t can be toosely
f transaction
descri6ed as any centralized data neporitony khich
Can 6e aueried for busines beneft.
Data ware house environment contains an extvacton
ransportation ant oading C¬TL) Solution an onune
ana.yticac processing (olAP) enine, customer analy
toote and other appications that handCle the
process of 9atherng information and deliveing
it to business Users
A data Warehouse agroup of data specific
to the entire organizatuon,not enlyto part
Cular roP users
TOr
a s u r e s
~ation
in ve
ve ctigatue
stigative
database designed -for in
9t i a
vavíous applicatOnd
tacks, using data trom
historical
data to povide
current and
9 t includes n.
a
historical pexqutive of trfovmatio
s reacl-Sntens i ve-
Itc us aqo
tablec-
t to ntains
a few larqe
Sata &oure
in chicog
clean
Eata
Alew yok Integate
Wareh-
Pueny&
analisis
Tranaform OUse
Load L tods
Refresh
Toronto
(cient) (dient)
Vancouver
A MultiDimenional Bocta Model
The muti- Simeni onal Data Modef k a method
which is usedfor Ordering data in the database
along with ood arangement and anembling
ethe Content ín t6e doctabae
The Muti-imemional 9ata model al! Os
Customos to
Snteogale analytical questíon
associated wih markel or busineg«, bends,
1elational databases which alows customes
unike
unike
allow
to accesc data in They
theform of quenes.They
us ers to Tapidty eceive ansnersto 1he 1eueak
hich hemade by Creating and examiningthe
data comparativety -ast
uces mutti
multi
data Warehouseing
and
OAP
OlAP
databases. t is Used to show multiple
dimensional
usep.
dimersiors
o h e tata b
aOlatin0
Muti dimensional Data Model
OLAP
P Operattons on
perati +the multi-limenmional Made
There
are 4types ei OLAP pevahons
Rll-up
Drit-down
3 suce
3
Dice
4
Pivot Chotab)
5
the data in the
cdata model gives
Muti-dimensional
d a t a cube
fo1m e
viewed in mu Hiple
Hipe
to be
alto s h e data
data Cube
&inensiS
Roll-up as dill-up
0peraion
alsocalled
Th agqreqation
on a
operation performs
Tell-p Concept hierachy
The
T he
upa
uP a concept of hieraxh
either cmbing
a t a cube ution.
jeductjon.
fOn 9jed
aa dimension
dimens
dimersión
01 by
Tr a
Shows the rolup operaion
belouw fig, Jt
the the
Consider
cumbinq up
central
cub
by
e
Performed
on locafion a dofined
the imens io
or
hiesarcht
Conept
Kauntry
"Syeet
eitykstate
the data
aggregates
he
ne oll-up operation
-he
locartion hieraTchy from
ascending
the level o countrny
to
the level o ity
k perfned y dinea
when the rel-up operation
dumens10n
a r e qemoved
mOTe
Teduction o n e or
rom h e given Cube
cotatn tud
a sales data Cuhe
Consider
time.
Rol P Operaiy
Rofl up Operdtian
location and
dimensfons:
the tine dmensjon,Terat
Temovíng
i perfprmed by
he todal
dates by locztion.
agAregaton
an
rof1-up Ope
rol-up afin
paaiu
thie jereke
OAI-Doon-
on a data tube either
B peotfomm
Oill-doOn operati00
concept hleTarchy for
a
a
own
teppin sepping or introducing
addition
damenaion
daimengion cu
on t h Central
operation peuhor
med
elown dimeniot
9itl the
concept
hierarchy for
down a .
bStepping
ume dekned a
QuarteT yeay
do menth
day
operatih þerfomcd
b4 tetpingdou
oi-doon evel
h e level o Quarler t
ept biera chy fom be details the
details
month then the jesutng data cube
month rather than by quarter
toa s al per
prill
own can be performed by addinq new dime
nai on
data Cube such a customer qrou
Sice Dice
and Dlte
Selection on ene
he slice eperaHOn pEtform a
dimensioN G a
iven Cube esuttng tn a Sub cube
wheie
the below fq shows a stice opesation
Selected from the
he Sajey ata aTe
quaskr
central cube -foY the dimens ion time usin
usinq aikr
a seledtion on two
Brce opeiation performs
the
qi ven cube 1esultihg in a
01 mOTe
mensíons o the
Sub cube
a dice operadíon On
on the
shows
The bel oud fa
criteria
CYiteria that
basod on the fpjlooína
central Cube
mee 3 dimensiong
Pivot retate):-
pivets a visualization operation t6 at otate
he data ax in a view in ordey to Províde
an alkoinative Presentation othe data.
he blowf showc a pwot Operation where
he item and lcation ax in a -D Sice ate rotale
peation(aty
chao44
Newyork
As20
Vancoe
/315
6osGos as 40o
Tbme
(quarex)
3
Movie Compukr aeTotid
Phone
EnterHainment
CJten Eoa
roll-up
on loca tion
fom ciiu to countrie)
1
USA
C a n a
1000
Time
Puartos) 9 2
m- Co1 ph yadio
(-Hem-gouP
drl down on Time
Cfrom Quarler-to moni) .
ei
locakion) Dewyat
Toronto/
Varovev
Jan
100
s
Mar
Apr
Ma
(Time)
JUnL
ur
Dct
pov
9e
CAcrngeu
Stce fo lime-Q
ice
chito locatoD- Jrasfo or vercae
l o s n o n
TbrDo Tr
JaswwT5es|ras |4o vacad
6os
compcomp frdio
CJtemou
m- Comp
Pivet
6os
eoP
ph
fadko
Dala CAnve house Archtrehre
ata Cunre house
C o l l e c t o n
ahebogenious
o disercat Cqanized vnder C
uniicd Schema
A
A cala tuave house archikedore 5 a mefhod of
dechonq e Ovciall avchileche cf data
Comn-
Unication ctcss Et prsentation tech
There are re
tpes o Ackikres!
,
Sirqle lor) one
Ti
, wo Tiev
Tkore Tic
And
2Tia Hehitshom sei.
tsS clont
de rchouse
and deba
Soices
availabe
Spits phystay
s not expancdable csucvo
ege
encl use
T s a't Suppot latge lomber
4 s easy o Maintai olvakanco
is fst comnurcak
NeSonsSLa
SOU
S
Data
Comming -to hvee tiex ackikehtove
10 declave it as a fre per archtechture
beCase ofit's rre Ti piesent nthe
Avchiteck tue.
O Tottom Tier The botton tic ot an Cuchi
avcki
echtuve is e dota warehoUse daka base
SeveY
I is a velatbnal data base ystcm.
w e Use back End tools (ov) ETL Tools
totecd data in to data base.
he ETL Perfoms
Excthat ion.
Tvanstor m
Loa d
clean &
Retves h.tinc tions
Middle lies n middle ter we hav OlAP
Sexvers of wo type
, Relatioml OLAP (ROLAP)
Jt maps he operabons on Mulibime
nsonal hra to Standar d elationa
Operabions
, MutidimenSiona olAP (MOLAP)
T t oirctimplements e multoien
Sional dorta and opesotions
Top ierThis tier is the on-End
cliest laye.This laye holcds e Qve
tools &veporting tool aolybis tools
ata mining tools
.
CitCCktore
Q/Rort Amlysis Dara min
Micdle Tire
Oulput
Servev wer
hous e Bottom Tic
Dta a v e
Monitoin
Aiministhahon
melasbba
bac
Clea
Transtom Data Swce
Loa d
Refresh
ptraboral calaas Eotera bates
D a a Narehouse Impkmeration
the data
There ae various sepc to Jmplement
warehouse
Reqwemens analysis and Capatity planntnq.
Aardare ntegralio
aModelling
physicol modethng
5 Sources
6 ¬TL
7Popuate he data kavehone
8 user appitations
9 Roll-Out the warehouse and applicationu.
Miin:rtd miíng
a phoc Seo
l
ktydctiog ad
Reguirement analyis and capaily Plonnn
invowes
in data warehousing
ihe
frst procect
charchilectqyes
needs, defining
deftning enterpke
planning and seleclinq the
,
Caning out capatily
Ths Sep will (ontain
nardware softuaye -took
and
managtment, a well athe
Consulting senio
difforent slakeholde
Jlardua 1e gndegvalion
Once the havd ware and 8oftwaye has bee
Setected he aequeto be put by integafing
e servess +Re storage method , and the ugON
Sotbware tools.
s a stgmfficant &4age that
Moddbng:- Modetung
the wayehoule schema and
tnvelvu desiqning
1h s may contain usin g a modeling toof t
view
Wajehouue avje so
phisticated
the data
woehouyer
the dala
hsical modelung-for
medelinq is
perfonmiietly. Phyical
to
contat desigming the ghysicoal dota
needed. h is
wwje house iganizaton doda placement plato
and
Patition ing, deeidbng on acceu echnique
ndexing.
the data
5Sucesth rformation fo
coalehouse í Sevejal dada
ukely to Come fom
coweas. This &tep Conlaunk ident ana
Conneng e couw Using the galo o { O
dives, o anethen rapey.
ll
TL The data fom the Sowce syustem
eque to
go though an ET pHase The pr0ce
the eri phase
destging and mpementing
a suitable £TLtool
nad cotain delning
vendors and pchasing ond nplemenfng the
foo h s may Contaun ustomize the toel
ult the need ofthe entoprde
Warehouse once the
PopulaTe the data
the
took have been agreed upon tesfng
will be needed perhaps uSinG a
staging
tool
1ea Once eything worting adequakly,
tha
be used in populaung
the ETL
took ma
schema and view defomkón
arehoues qiven He
the dala warehouse
cer applicatkonuHor
thee must be end-uye apsticatio.
to Se he?ful,
and
ns-Th step Contauns desming
oppicatione teauied bythe
implementing
end-us eK
ot-Out the arehouie &_appücationg
Once The data twanehowe ha been popwa
and the end-client applications uted , the
Wavehone 3ytem and the apeations moy
be ated velled out for the users Community
+0 use
OLTP OLAP
G 4stoicas pioce sSing
Day day pocsSing
DBA
USed by dafa amhsts
T t vscd by
G >tocus*s o igomko.oot
T toaks cnenrtinin Soou
ased ER Mode| bad basdm schma
cla ta
eatds qie a cieased aen
ae
accessed amongr
Recoids
tens data nk ckfa
usel to tuo the bos_ie Aha the..bsn
T
SUmmkd dafa .
prmtivc and deaile d dale
Mae dho 1aw GB
Si 1-/00 g
fhl:lky -hgh
