OLAP in Data Warehouse

Concept of OLAP, Multidimensional analysis, OLAP functions and applications, OLAP models – MOLAP, ROLAP and DOLAP

1. UNIT 3
Mr.T.Somasundaram
Assistant Professor
Department Of Management
Kristu Jayanti College (Autonomous), Bengaluru
OLAP in Data Warehouse

2. UNIT 3
OLAP in Data Warehouse
ConceptofOLAP,Multidimensional
analysis,OLAP functionsand
applications,OLAPmodels–MOLAP,
ROLAPandDOLAP

3. Online Analytical Processing (OLAP)
Meaning:
Theterm“OLAP”wascoinedbyE.F.Coldin1993torefertoatypeof
applicationthatallowsausertointeractivelyanalysedata.
OLAPsystemsenablemanagersandanalyststorapidlyandeasily
examinekeyperformancedataandperformpowerfulcomparisonand
trendanalyses,evenonlargedatavolumes.
OLAPisintegralpartofadatawarehousingsolution,whichisin
differentshades,dependingondatabasestructureandlocationof
majorityofanalyticalprocessing.
Itallowsmanagersandanalyststogetaninsightoftheinformation
throughfast,consistentandinteractiveaccesstoinformation.

4. Online Analytical Processing (OLAP)
Meaning:
OLAPisacategoryofsoftwarethatallowsuserstoanalyze
informationfrommultipledatabasesystemsatthesametime.
Itisatechnologythatenablesanalyststoextractandviewbusiness
datafromdifferentpointsofview.
Definition:
OLAPisamethodofanalysingthedatainamulti-dimensional
format,oftenacrossmultipletimeperiods,withtheaimofuncovering
thebusinessinformationconcealedwithinthedata.
OLAPisthedynamicsynthesis,analysisandconsolidationoflarge
volumesofmultidimensionaldata.

5. Characteristics of OLAP
OLAPdefinedinfivewords–FastAnalysisofShredMulti-Dimensional
Information(FASMI)
Characteristics
Fast
Analysis
Share
Multidimensional
Information

6. Characteristics of OLAP
1.Fact–itmeansthatthesystemtargetedtodelivermostresponsestouser
withinabout5secondandveryfewtakingmorethan20seconds.
2.Analysis–itmeansthatthesystemcancopewithanybusinesslogicand
statisticalanalysisthatitrelevantfortheapplicationandtheuser.
3.Share–itmeansthatthesystemimplementsallthesecurityrequirements
forconfidentiality,ifmultiplewriteaccessisneeded,concurrentupdate
locationatanappropriatelevelnotallapplicationsneeduserstowritedata
back.
4.Multidimensional–itisthekeyrequirementandOLAPsystemprovidea
multidimensionalconceptualviewofthedata.
5.Information–informationareallofthedataandderivedinformation
needed,relevantforapplication.

7. OLAP Concept
AtthecoreoftheOLAPconcept,isanOLAPCube.
OLAPCubeisadatastructureoptimizedforveryquickdataanalysis.
Itconsistsofnumericfactscalledmeasureswhicharecategorizedby
dimensions.
OLAPcubeisalsocalledthehypercube.
Dataoperationsandanalysisareperformedusingsimplespreadsheet,
wheredatavaluesarearrangesinrowandcolumn.
OLAPcontainsmultidimensionaldata,datausuallyobtainedfroma
differentandunrelatedsource.

8. OLAP Concept
OLAPCubeconsistsofnumeric
factscalledmeasureswhichare
categorizedbydimensions.
OLAPCubeisalsocalled
Hypercube.
Dataoperations&analysisare
performed using simple
spreadsheet.
OLAPcontainsmultidimensional
data&cubecanstoreandanalyze
multidimensionaldatainalogical
andorderlymanner.

9. Multidimensional Analysis of OLAP
InordertounderstandOLAParchitecturesthatcandeliverthisvalue,it
isfirstnecessarytounderstandthenatureofmultidimensionaldata.
Multidimensionalserveristoprovidefast,linearaccesstothedata
regardlessofthewaythedataisbeingrequested.
Theobjectiveistoretrievethedataequallyfast,regardlessofthe
requesteddimensions.
MostOLAPserversinfact,achievefastresponsetocomputedresults
bycomputingtheminadvance.
Onedimensionisselectedasthefactdimensionandthisdimension
formsthecolumnsofthefacttable.

10. Multidimensional Analysis of OLAP
Thedataisretrievedfromtherelationaldatabaseintotheclienttoolby
SQLqueries.
Oneoftheprimaryproblemswasmaintainingdatabaseintegrityand
ensuringconsistentdataupdates.
Databaseisconsistentlymaintainedandthattransactionupdatescanbe
performedinafastandefficientmanner.
Inordertoprovidethemultidimensionalviewsofthedata,itrequires
relationaldatabasethatthedatabeorganizedinthestarorsnowflake
schemadescribedpreviously.
TherearetwomajorOLAParchitectures,MOLAPandRAPprovide
theirownphysicalmultidimensionaldatabases.

11. OLAP Operations (Functions)
DataWarehouseusesOnlineAnalyticalProcessing(OLAP)toformulateand
executeuserqueries.
OLAPprovidesaggregatedata(measurements)alongasetofdimensions,in
which–
a)Eachdimensionisdescribedbyasetofattributes(i.e.)eachdimensiontable
includesasetofattributes.
b)Eachmeasuredependonasetofdimensionsthatprovidescontextforthe
measure(E.g.)fortheresellercompanydatawarehouse,themeasureisnumberof
soldunits,whicharedescribedbycorrespondinglocation,timeanditemtype.
c)Alldimensionsareassumedtouniquelydeterminethemeasure.(E.g.)forreseller
companythelocation,time,produceranditemtypeprovideallnecessary
informationtodeterminecontextofaparticularno.ofsoldunits.

12. OLAP Operations
TherearefivebasicOLAPOperations:
OLAP Operations
ROLL UP
DRILL DOWN
SLICE
DICE
PIVOT

13. 1.ROLLUP:
Thisoperationperformsaggregationonadatacubeinanyofthe
followingway:
i)ByClimbingupaconcepthierarchyforadimension.
ii)Bydimensionreduction.

14. (E.g.)Inthisfigure,
Rollupoperationisperformedby
climbingupaconcepthierarchyfor
thedimensionlocation.
Initiallytheconcepthierarchywas
“street<city<province<country”
Dataisaggregatedbyascendingthe
locationhierarchyfromlevelof
citytolevelofcountry.
Dataisgroupedintocitiesratherthan
countries.
Oneormoredimensionsfromdatacube
areremoved.

15. 2.DRILLDOWN:
Drill-downoperationisreverseoftheroll-up.Thisoperationis
performedbyeitherofthefollowingway:
i)Bysteppingdownaconcepthierarchyforadimension.
ii)Byintroducingnewdimension.

16. (E.g.)Inthisfigure,
Drilldownoperationisperformedby
steppingdownaconceptofhierarchy
fordimensiontable.
Initiallytheconcepthierarchywas
“day<month<quarter<year”
Ondrill-upthetimedimensionisdescended
fromthelevelquartertothelevelofmonth.
Whendrill-downoperationsisperformedthenoneor
moredimensionsfromdatacubeareadded.
Ifnavigatesthedatafromlessdetaileddata
tohighlydetaileddata..

17. 3.SLICE:
TheSliceoperationperformsselectionof
onedimensiononagivencubeandgives
usanewsubcube:
(E.g.)Inthisfigure,
Sliceoperationisperformedforthe
dimensiontimeusingthecriterion
time=“Q1”.
Itwillformanewsubcubebyselecting
oneormoredimensions.

18. 4.DICE:
Thediceoperationperformsselection
oftwoormoredimensiononagivencube
andgivesusanewsubcube:
(E.g.)Inthisfigure,
Thediceoperationonthecubebasedon
thefollowingselectioncriteriathat
involvethreedimensions:
1.(location=“Toronto”or“Vancouver”)
2.(time=“Q1”of“Q2”)
3.(item=“Mobile”or“Modem”)

19. 5.PIVOT:
Thisisavisualizationoperationthat
rotatesthedataaxesinanalternative
presentationofdata.
Pivotoperationisalsoknownasrotation.
Itrotatesthedataaxesinviewinorderto
provideanalternativepresentationofdata.
(E.g.)Inthisfigure,
Inthistheitemandlocationaxesin2-D
slicearerotated.

20. Implementation Steps of OLAP
ThemajorstepsoractivitiesforimplementationofOLAPare–
Dimensionalmodelling
DesignandbuildingoftheMultidimensionaldatabase(MDDB)
SelectionofthedatatobemovedintotheOLAPsystem
DataacquisitionorextractionfortheOLAPsystem
DataloadingintotheOLAPserver
Computationofdataaggregationandderiveddata
Implementationofapplicationonthedesktop
ProvisionofUsertraining

21. Few Companies OLAP Implementation
TheWorldBank–performscomplexstatisticalanalysisonamassof
worldwideeconometricdata.
HP–providesspeedyoperationalreportsusingadesktopOLAPover
thecorporateintranettonumeroususers.
BritishAirways–reducesprocessingcoststhroughbetteranalysis
usingOLAPdatabasestiedtonewgeneralledger.
BarclaysBank–managesriskonloansformaximumprofit.
IBMFinance–providesfinanceportalforcost-effectiveanalysis,
reportingandperformancemanagement.
DeluxeCorp–performsmoreaccurateforecastingthroughplanning
andanalysisapplications.

22. Advantages & Disadvantages of OLAP
Advantages:
1.Flexibility:
ItmeansbusinessusersofOLAPapplicationscanbecomemoreself-
sufficient.
Itaccesstostrategicinformationequalsmoreeffectivedecisionmaking.
2.FasterService:
ItispossibletobuildanOLAPsystemusingsoftwaredesignedfor
transactionprocessingordatacollection.
Developerscandeliverapplicationstobusinessusersfaster,providing
betterserviceanditreducestheapplicationsbacklog.

23. 3.Reductionofapplicationbacklog:
Itreducestheapplicationsbacklogbymakingbusinessusersself-sufficientenough
tobuildtheirownmodels.
OLAPapplicationsaredependentondatawarehousesandtransactionprocessing
systemstorefreshtheirsourceleveldata.
Itgainsmoreself-sufficientuserswithoutrelinquishingcontroloverintegrityof
data.
4.ReductionofNetworkTraffic:
ITalsorealizesmoreefficientoperationsthroughOLAP.
ITreducesthequerydragandnetworktrafficontransactionsystemsordata
warehouse.
5.EfficientUseofResources:
Itprovidetheabilitytomodelrealbusinessproblemsandamoreefficientuseof
peopleresources,OLAPenablestheorganizationtorespondmorequicklytomarket
demands.

24. Disadvantages:
1.ComplexitytoAdminister:
OLAPsystemsprovideacomfortableenvironmentfortheenduser.
Complexityincreasesthelevelofknowledgerequiredtocreatebusiness
intelligencesystems.
2.DataMartrequired:
OLAPsystemrequiresadatamartwithastarorsnowflakelayout.
DatamustbecopiedfromtheOLTPsystemsintothedatamart.
3.Latency:
DatamartisrequiredbyOLAP,thereisautomaticallysomelatencyinthe
businessintelligence.
4.Read-Only:
OLAPdataisread-onlyandifdoesn’tsupport,thenchangeswillreflectinOLAP
data.

25. Applications of OLAP
OLAPapplicationsareusedbyavarietyofthefunctionsofanorganization-
1.FinanceandAccounting:
Budgeting.
Financialperformanceanalysis.
2.SalesandMarketing:
Salesanalysisandforecasting.
Promotionanalysis.
MarketandCustomersegmentation.
3.Production:
ProductionPlanning.
Activity-basedcosting.
Financialmodelling.
Marketresearchanalysis.
Customeranalysis.
Defectanalysis.

26. Basis OLTP OLAP
Source of DataOperational data, OLTPs are the
originalsource of data
Consolidation data, OLAP data comes from
thevarious OLTP databases
Purposeof DataTo control and run fundamental
business tasks
To help with planning, problem solving and
decision support
What the Data
Reveals
A snapshot ofongoing businesses
processes
Multi-dimensional views of various kinds of
business activities
Insertsand
Updates
Short and fast inserts and updates
initiated by end users
Periodic long-running batch jobs refresh the
data
Queries Relatively standardized and simple
queries returning relatively few
records
Often complexqueries involving aggregations
Processing
Speed
Typically veryfast Depends on the amountof data involved, batch
data refreshes and complex queries may take
many hours, query speed can be improved by
creating indexes
OLTP Vs OLAP

27. Basis OLTP OLAP
Space
requirements
Can be relatively small if historical data is
achieved
Larger dueto the existence of
aggregation structures and history data,
requires more indexes than OLTP
Data base
Design
Highly normalized with many tablesTypicallyde-normalized with fewer
tables, use of star or snowflake schemas
Backup and
Recovery
Backup religiously,operational data is
critical to run the business, data loss is
likely to entail significant monetary loss and
legal liability
Instead of regular backups,some
environments may consider simply
reloading the OLTP data as a recovery
method
OLTP Vs OLAP

28. OLAP Models
TheOLAPserverunderstandshowdataisorganizedinthedatabase
andhasspecialfunctionsforanalysingthedata.
ThethreemajoralternativesforimplementingOLAPapplicationsare:
OLAP Models (Tools)
Multi-
dimensional
OLAP (MOLAP)
Relational
OLAP
(ROLAP)
Desktop OLAP
(DOLAP)

29. 1.MultidimensionalOLAP(MOLAP):
InMOLAPmodel,dataforanalysisisstoredinmultidimensionaldatabases.
Specializeddatastructures(multidimensional)needtoorganize,navigate
andanalyzethedatainaggregatedform.
Largemultidimensionalarraysformthestoragestructures.
(E.g.)Tostoreno.of500unitsforProductAinJanmonth2009,instoreS1
willbestoredinanarrayrepresentedbythevalues(ProductA,2009/01,Store
S1).
Thearrayvalueindicatesthelocationofthecellsanditisintersectionofthe
valuesofthedimensionattributes.
Itprovidecapabilitytoconsolidateandfabricatesummarizedcubesduring
theprocessthatloadsdataintoMDDBsfrommaindatawarehouse.

30. MOLAP Architecture

31. Advantages:
Itprovidesexcellentperformancewhendataisaccessedasintendedat
designtime.
Itsupportspre-definedanalysisoftrendsovertime(E.g.)salesby
customertypebygeographicarea).
Allcalculationshavebeenpre-generatedwhencubeiscreated.
Complexcalculationsarenotonlyfeasible,buttheyreturnquickly.
Thedataforanalysisisstoredandmaintainedinapersistentstructure,
whichreducestheoverheadofperformingcalculationsandbuilding
aggregationsduringqueryprocessing.

32. Disadvantages:
Theinflexiblenatureofthemultidimensionhierarchylimitstheabilitytosupport
multiplebusinessareasfromtheonedatabase(i.e.)differentviewsofsamedata.
Whendimensionrequirementschange,thedatacubemayneedtobereorganized.
Theaggregatednatureofdatamakesitdifficultto‘drilldown’todatarequiredby
manyanalysisapplications.
Itrequirenewskillsandtoolkitstobuildandmaintainmultidimensional
databases,thusincreasingthedevelopmentandmaintenancecost.
AstheuserbaseismuchlowerthanforRDBMStherearefewertoolsand
utilitiestosupportmulti-dimensionalDBMS.
Thereisaheavyprocessingrequirementtore-calculatealloftheaggregations
everytimethatnewdataisaddedtothecube.
Theyarenotsuitedtolargedatabases(i.e.)>10GB.

33. 2.RelationalOLAP(ROLAP):
ROLAPsupportslargedatabaseswhileenablinggoodperformance,
platformportability,exploitationofhardwareadvanceslikeparallel
processing,robustsecurity,multi-userconcurrentaccessandopennessto
multiplevendor’stools.
ROLAPisbasedonfamiliar,provenandalreadyselectedtechnologies.
Themetadatalayersupportsthemappingofdimensionstotherelational
tables.(i.e.)supportssummarizationsandaggregations.
ROLAPhasthreedistinctcharacteristics-
SupportsallthebasicOLAPfeaturesandfunctions.
Storesdatainarelationalform.
Supportssomeformofaggregation.

34. ROLAP Architecture

35. Advantages:
Itperformmorerobusttypesofanalysisthanmulti-dimensionalOLAP,
incorporatingawiderrangeofdataviews.
Itsupportsflexibleanalysis(i.e.)analysiscanexplorethedata.
Itallowsmulti-dimensionalviewsoverthesamedatatables,tosupport
differentbusinessareas.
Itcansupportlargevolumesofdetaileddatafor‘drilldownanalysis.
Theprocessingloadcanbeshiftedontoaserverplatform,allowingto
usewithnetworkcomputers.
Thereisnorequirementtoreturnaggregationswhenadditionaldatais
addedtotherelationaldatabases.

36. Disadvantages:
Usingrelationaldatabasesformulti-dimensionalanalysiscanleadto
performanceissueswhendataisextractedfromthedatabases,if
databaseisnotwelldesigned.
Thedenormalizeddatabasedesigngivesrisetosubstantialdata
redundancy.
ManyrelationalOLAPproductsdon’tprovidesamedegreeofdata
visualizationfoundinmulti-dimensionalOLAPtools.
RelationaldatastructuresrequiremoretimeforsimpleOLAPanalysis
thanmulti-dimensionalOLAPtoolsbecausethedatamustbeextracted
priortoanalysis.

37. 3.DesktopOLAP(DOLAP):
DOLAPmodelallowsausertodownloadapieceofdatafromadatabaseor
sourceandworkwithitlocallyorontheirdesktop.
DOLAPissingle-tier,desktop-basedOLAPtechnology.
Itisabletodownloadarelativelysmallhypercubefromacentralpoint,
usuallyfromdatamartordatawarehouseandperformmultidimensional
analyses.
Datasetsarelimitedtoboundariesdefinedbyuserwithnoaccessto
granulardata.
Cubescontainsummarizeddata,organizedinafixedstructureof
dimensions.
Itisidealforwell-understood,recurringanalyticquestionsandreporting.

38. Advantages:
Userfriendly–usercanpivotandmanipulatedatalocallyfromthe
returnedresultsetstoredondesktop.
Excellentqueryperformance–itcollects,aggregatesandcalculates
datainadvanceofanalysis.
Lowcostperseatandmaintenance.
Usefulformobileuserswhocan’talwaysconnecttothedata
warehouse.
EasiesttodeployamongallOLAPapproaches.
Disadvantages:
Limitedfunctionalityanddatacapacity.

39. OthertypesofOLAP:
4.HOLAP–HybridOLAPModelisanapplicationthatcombinesMOLAP
andROLAP’sgreatestcharacteristicsintoasinglearchitecture.HOLAP
systemsstorealargeramountofdetaileddatainrelationaltables,while
aggregationsaresavedinpre-calculatedcubes.AhybridOLAPmodelis
providedbyMicrosoftSQLServer2000.
5.Web-EnabledOLAPModels(WOLAP)–itreferstoanOLAPprogram
thatmaybeaccessedthroughawebbrowser.WOLAPisathree-tiered
architecturethatconsistsofthreecomponents:aclient,middleware,and
databaseserver,asopposedtostandardclient/serverOLAPsystems.
6.SpatialOLAPModels(SOLAP)-combinesthecapabilitiesofbothGIS
andOLAPintoasingleuserinterface.Ithelpswithbothspatialandnon-
spatialdatamanagement.SpatialOLAP,forexample,canbeusedtoanalyze
regionalweathertrends.