online analytical processing.ppt

8/17/2019 Online Analytical Processing.ppt

1/24

nline Analytical

nline Analytical

Processing ( LAP)rocessing ( LAP)


2/24

What is LAP

hat is LAP

Basic idea:Basic idea: converting data intoconverting data into

information that decision makers needinformation that decision makers need

Concept to analyze data by multipleConcept to analyze data by multiple

dimension in a structure called data cubedimension in a structure called data cube


3/24

History

istory

In 1993, ! "! Codd came up #ith theIn 1993, ! "! Codd came up #ith the

termterm online analytical processing (OLAP)online analytical processing (OLAP)

and proposed 1$ criteria to define anand proposed 1$ criteria to define an%&'( database%&'( database

the term %&'( seems perfect to describethe term %&'( seems perfect to describe

databases designed to facilitate decisiondatabases designed to facilitate decisionmaking )analysis* in an organizationmaking )analysis* in an organization

http://proquest.safaribooksonline.com.libaccess.sjlibrary.org/0735621993/gloss01http://proquest.safaribooksonline.com.libaccess.sjlibrary.org/0735621993/gloss01http://proquest.safaribooksonline.com.libaccess.sjlibrary.org/0735621993/gloss01


4/24

Purpose of LAP

urpose of LAP

+o derive summarized information from+o derive summarized information from

large volume databaselarge volume database

+o generate automated reports for+o generate automated reports forhuman vie#human vie#


5/24

Why need LAP over

hy need LAP over

Relational Database Ielational Database I

Consistently fast responseConsistently fast response

%&'( obtains a consistently fast%&'( obtains a consistently fast

response is by prestoring calculatedresponse is by prestoring calculated

valuesvalues


6/24

Why need LAP over

hy need LAP over

Relational Database IIelational Database II

etadata-based .ueriesetadata-based .ueries

provide analysis functions that areprovide analysis functions that are

difficult or impossible to e/press in 0&difficult or impossible to e/press in 0&

0&0& #as developed primarily for#as developed primarily for

transaction systems, not for reportingtransaction systems, not for reporting

applicationsapplications


7/24

Why need LAP over

hy need LAP over

Relational Database IIIelational Database III

0preadsheet-style formulas0preadsheet-style formulas

design the data structure #ith users indesign the data structure #ith users in

mind!mind!

0preadsheets are0preadsheets are key components ofkey components of

business management because they arebusiness management because they are

intuitive to createintuitive to create


8/24

tep I

tep I

1!1! identify multidimensional dataidentify multidimensional data

measure attributemeasure attribute

)measure some value, can be)measure some value, can beaggregated upon*aggregated upon*

dimension attributedimension attribute )define the dimension and summary of)define the dimension and summary of

measure attribute*measure attribute*


9/24

(!ont")

!ont")

ach dimension is typically e/pressed asach dimension is typically e/pressed as

a 2hierarchya 2hierarchy

4ierarchy: 'nalyst is interested in4ierarchy: 'nalyst is interested indifferent level of detail of a dimensiondifferent level of detail of a dimension


10/24

tep II

tep II

$!$! 'nalyze multidimensional data into 'nalyze multidimensional data into

cross-tabulationcross-tabulation

ro# header: value for one attributero# header: value for one attribute

column header: value for another attr!column header: value for another attr!

individual cell: value aggregationindividual cell: value aggregation


11/24

tep III

tep III

3!3! 5isualize n-dimensional cube - data5isualize n-dimensional cube - data

cubecube

the #ord C6B describe #hat in thethe #ord C6B describe #hat in the

relational #orld #ould be the integrationrelational #orld #ould be the integration

of the fact table #ith dimension tablesof the fact table #ith dimension tables


12/24

tep I#

tep I#

'fter you design the cube, you #ill use 'fter you design the cube, you #ill use

the cube7s structure to build a relationalthe cube7s structure to build a relational

database )kno#n as a star schema* todatabase )kno#n as a star schema* tohouse the data for the cubehouse the data for the cube


13/24

tep #

tep #

%nce you load data into the relational%nce you load data into the relational

database, and then into the cube, you7lldatabase, and then into the cube, you7ll

be able to see ho# attributes,be able to see ho# attributes,dimensions, measures, and measuredimensions, measures, and measure

groups fit together #ithin a cube to creategroups fit together #ithin a cube to create

a po#erful analytical tool!a po#erful analytical tool!


14/24

tar che$a

tar che$a

Cubes are easily stored in relationalCubes are easily stored in relational

databases, using a denormalized datadatabases, using a denormalized data

structure called the star schema, developed bystructure called the star schema, developed by8alph imball8alph imball

starts #ith a central fact tablestarts #ith a central fact table

ach ro# in the central fact table containsach ro# in the central fact table contains

some combination of keys that makes itsome combination of keys that makes ituni.ue! +hese keys are called dimensions!uni.ue! +hese keys are called dimensions!


15/24

licing % Dicing

licing % Dicing

'dditional "unctionality that can be 'dditional "unctionality that can be

thought of as vie#ing a slice of the datathought of as vie#ing a slice of the data

cube, particularly #hen values forcube, particularly #hen values formultiple dimensions are fi/ed!multiple dimensions are fi/ed!

0licing;icing simply consists of selecting0licing;icing simply consists of selecting

specific values for these attributes, #hichspecific values for these attributes, #hichare then displayed on top of the cross-are then displayed on top of the cross-

tabtab


16/24


17/24

Rollup % Drill&do'n

ollup % Drill&do'n

%&'( permit users to vie# data at ay%&'( permit users to vie# data at ay

desired level of granularity!desired level of granularity!

8ollup: moving from finer-granularity data8ollup: moving from finer-granularity datato coarser granularityto coarser granularity

;rill-do#n: opposite to 8ollup;rill-do#n: opposite to 8ollup


18/24

LAP Inple$entation

LAP Inple$entation

ultidimensional %&'( )%&'(*ultidimensional %&'( )%&'(*

8elational %&'( )8%&'(*8elational %&'( )8%&'(*

4ybrid %&'( )4%&'(*4ybrid %&'( )4%&'(*


19/24

LAP

LAP

+he database is stored in a special, usually+he database is stored in a special, usually

proprietary, structure that is optimized forproprietary, structure that is optimized for

multidimensional analysis!multidimensional analysis!

< : very fast .uery response time because data< : very fast .uery response time because data

is mostly pre-calculatedis mostly pre-calculated

-:-: practical limit on the size becausepractical limit on the size because the timethe time

taken to calculate the database and the spacetaken to calculate the database and the spacere.uired to holdre.uired to hold these pre-calculated valuesthese pre-calculated values


20/24

R LAP

LAP

+he database is a standard relational database+he database is a standard relational database

and the database model is a multidimensionaland the database model is a multidimensional

model, often referred to as a star or sno#flakemodel, often referred to as a star or sno#flake

model or schema!model or schema!


21/24

H LAP

LAP

a hybrid of 8%&'(a hybrid of 8%&'( and %&'(and %&'(

can be thought of as a virtual databasecan be thought of as a virtual database

#hereby the higher levels of the#hereby the higher levels of thedatabase are implemented as %&'(database are implemented as %&'(

and the lo#er levels of theand the lo#er levels of the database asdatabase as

8%&'(8%&'(


22/24

D LAP

LAP

+he previous terms are used to refer to+he previous terms are used to refer to

server based %&'( technologiesserver based %&'( technologies

;%&'( );esktop %&'(*;%&'( );esktop %&'(* ;%&'( enables;%&'( enables users to .uickly pullusers to .uickly pull

together small cubes that run on theirtogether small cubes that run on their

desktops or laptopsdesktops or laptops


23/24

!onclusion

onclusion

%&'( is a significant improvement over%&'( is a significant improvement over

.uery systems.uery systems

%&'( is an interactive system to sho#%&'( is an interactive system to sho#different summaries of multidimensionaldifferent summaries of multidimensional

data by interactively selecting thedata by interactively selecting the

attributes in a multidimensional dataattributes in a multidimensional datacubecube


24/24

References

eferences

IB 8edbooks!IB 8edbooks! ;B$ Cube 5ie#s: ' (rimer!;B$ Cube 5ie#s: ' (rimer! ;urham, >C,;urham, >C,60': IB, $??3! ebrary collections! 0an @ose 0tate60': IB, $??3! ebrary collections! 0an @ose 0tate6niversity! A6niversity! Ahttp:site!ebrary!comlibs=su;ochttp:site!ebrary!comlibs=su;ocid1?113?1DEppgF3id1?113?1DEppgF3GG

@acobson, 8eed,@acobson, 8eed, icrosoftH 0& 0erver $??J 'nalysisicrosoftH 0& 0erver $??J 'nalysis0ervices 0tep by 0tep0ervices 0tep by 0tep! icrosoft (ress!! icrosoft (ress!

Berry, ichael @! '!Berry, ichael @! '! ;ata ining +echni.ues : "or arketing,;ata ining +echni.ues : "or arketing,

0ales, and Customer 8elationship anagement!0ales, and Customer 8elationship anagement! 4oboken,4oboken,>@, 60': @ohn Kiley E 0ons, Incorporated, $??F! ebrary>@, 60': @ohn Kiley E 0ons, Incorporated, $??F! ebrarycollections! 0an @ose 0tate 6niversity!collections! 0an @ose 0tate 6niversity!AAhttp:site!ebrary!comlibs=su;ocid1?11F$LMEppgJ$$http:site!ebrary!comlibs=su;ocid1?11F$LMEppgJ$$ G!G!

online analytical processing.ppt

Documents