Scribd Logo
Upload

Welcome to Scribd!

  • 17 views

    Understanding Multi Dimensional Database: Prepared By: Amit Sharma Hyperion/OBIEE Trainer

    Uploaded by

    sarv2k
    This document provides an overview of multi-dimensional databases and OLAP concepts. It discusses star schemas with fact and dimension tables, and how data can be aggregated, drilled down, rolled up, sliced and diced across dimensions. Common OLAP operations like aggregation, drilling down and rolling up allow analyzing data at different levels of granularity. Hierarchies within dimension tables enable hierarchical summarization of data.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Understanding Multi Dimensional Database: Prepared By: Amit Sharma Hyperion/OBIEE Trainer

    Uploaded by

    sarv2k
    17 views34 pages
    This document provides an overview of multi-dimensional databases and OLAP concepts. It discusses star schemas with fact and dimension tables, and how data can be aggregated, drilled down, rolled up, sliced and diced across dimensions. Common OLAP operations like aggregation, drilling down and rolling up allow analyzing data at different levels of granularity. Hierarchies within dimension tables enable hierarchical summarization of data.

    Original Description:

    DWH Concept

    Original Title

    35878451-DHW-Models

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document provides an overview of multi-dimensional databases and OLAP concepts. It discusses star schemas with fact and dimension tables, and how data can be aggregated, drilled down, rolled up, sliced and diced across dimensions. Common OLAP operations like aggregation, drilling down and rolling up allow analyzing data at different levels of granularity. Hierarchies within dimension tables enable hierarchical summarization of data.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    17 views34 pages

    Understanding Multi Dimensional Database: Prepared By: Amit Sharma Hyperion/OBIEE Trainer

    Uploaded by

    sarv2k
    This document provides an overview of multi-dimensional databases and OLAP concepts. It discusses star schemas with fact and dimension tables, and how data can be aggregated, drilled down, rolled up, sliced and diced across dimensions. Common OLAP operations like aggregation, drilling down and rolling up allow analyzing data at different levels of granularity. Hierarchies within dimension tables enable hierarchical summarization of data.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 34

    Understanding

    Multi Dimensional Database


    Prepared By : Amit Sharma
    Hyperion/OBIEE rainer
    learnhyperion!"ordpress!#om
    $e%ie"

    Architecture Architecture

    Characteristics Characteristics

    Relational OLAP Relational OLAP

    Multidimensional OLAP Multidimensional OLAP

    ROLAP VS. MOLAP ROLAP VS. MOLAP


    oday Star S#hema
    oday Star S#hema

    Fact table

    Dimensions

    Drilling Down & Roll u

    Slicing & Dicing


    &a#t
    De!inition " Facts are numeric measurements #$alues% that
    reresent a seci!ic business acti$it&.
    Facts are stored in a FAC' table (.e. the center o! the star
    schema.
    Facts are used in business data anal&sis) are units) cost)
    rices and re$enues
    *+amle" sales !igures are numeric measurements that
    reresent roduct and,or ser$ice sales.
    .
    &a#t able
    Central table

    Mostl& raw numeric items

    -arrow rows) a !ew columns at most

    Large number o! rows #millions to a


    billion%

    Access $ia dimensions


    &a#t able
    De!inition "'he centrali.ed table in a star schema is called as
    FAC' table) that contains !acts and connected to dimensions. A
    !act table t&icall& has two t&es o! columns"
    Contain !acts and
    Foreign /e&s to dimension tables.
    'he rimar& /e& o! a !act table is usuall& a comosite /e& that is
    made u o! all o! its !oreign /e&s.
    A !act table might contain either detail le$el !acts or !acts that
    ha$e been aggregated #!act tables that contain aggregated
    !acts are o!ten instead called summar& tables%. A !act table
    usuall& contains !acts with the same le$el o! aggregation.
    Dimension
    De!inition " 0uali!&ing characteristics that ro$ide additional
    ersecti$e to a gi$en !act.
    *+amle" sales might be comared b& roduct !rom region to region
    and !rom one time eriod to the ne+t.
    1ere sales ha$e roduct) location and time dimensions.
    Such dimensions are stored in D(M*-S(O-AL 'A2L*.
    Dimension ables
    De!inition" 'he dimensions o! the !act table are !urther described with
    dimension tables
    Fact table"
    Sales #Mar/et3id) Product3(d) 'ime3(d)
    Sales3Amt%
    Dimension 'ables"
    Mar/et #Mar/et3(d) Cit&) State) Region%
    Product #Product3(d) -ame) Categor&) Price%
    'ime #'ime3(d) 4ee/) Month) 0uarter%
    De!inition" Star Schema is a relational database schema !or
    reresenting multidimensional data. (t is the simlest !orm o! data
    warehouse schema that contains one or more dimensions and !act
    tables.
    (t is called a star schema because the entit&5relationshi diagram
    between dimensions and !act tables resembles a star where one
    !act table is connected to multile dimensions.
    'he center o! the star schema consists o! a large !act table and it
    oints towards the dimension tables.
    The advantage of star schema are slicing down, performance
    increase and easy understanding of data.
    'hat is Star S#hema(
    Steps in designing Star S#hema

    (denti!& a business rocess !or anal&sis#li/e sales%.

    (denti!& measures or !acts #sales dollar%.

    (denti!& dimensions !or !acts#roduct dimension) location dimension)


    time dimension) organi.ation dimension%.

    List the columns that describe each dimension.#region name) branch


    name) region name%.

    Determine the lowest le$el o! summar& in a !act table#sales dollar%.

    (n a star schema e$er& dimension will ha$e a rimar& /e&.

    (n a star schema) a dimension table will not ha$e an& arent table.
    4hereas in a snow !la/e schema) a dimension table will ha$e one or
    more arent tables.

    1ierarchies !or the dimensions are stored in the dimensional table itsel!
    in star schema.

    4hereas hierarchies are bro/en into searate tables in snow !la/e


    schema. 'hese hierarchies hels to drill down the data !rom tomost
    hierarchies to the lowermost hierarchies.
    Attributes
    *ach dimension table contain attributes.
    6sed to search) !ilter and classi!& !acts.
    *+amle) Sales) we can identi!& some attributes !or each
    dimension"

    Product Dimension" roduct (D) descrition)


    roduct t&e

    Location Dimension" region) state) cit&.

    'ime Dimension" &ear 7uarter) month) wee/


    and date.
    Attributes Hierar#hy
    De!inition " A1 ro$ides a to5down data organi.ation
    6sed !or aggregation and drill5down,roll5u data anal&sis.
    *+amle) location dimension attributes can be organi.ed
    in a hierarch& b& region) state and cit&.
    A1 ro$ides the caabilit& to er!orm drill5down and roll5
    u searches.
    Allows the D4 and OLAP s&stems to to ha$e de!ined
    ath.
    A )on#ept Hierar#hy: Dimension
    *lo#ation+
    all
    Europe North_America
    Mexico Canada Spain Germany
    Vancouver
    M. Wind L. Chan
    ...
    ... ...
    ...
    ...
    ...
    all
    reion
    o!!ice
    country
    "oronto #ran$!urt city
    A )on#ept Hierar#hy: Dimension
    *lo#ation+
    Produ#t ,ine Produ#t &amily Produ#t
    )ategory
    Produ#t -ame
    Boo.s Arts and Music Architecture 1igh 'o Almonds
    Boo.s Audioboo/s 2iograhies 4ild Swans" 'hree
    Daughters o! China
    Boo.s Audioboo/s Childrens 'he 1obbit
    Boo.s Audioboo/s Childrens 4innie 'he Pooh
    Boo.s Audioboo/s Fiction 'he Ad$entures o!
    1uc/leberr& Finn
    %roduct_Line&'%roduct_#amily&'%roduct_Cateory&'%roduct_Name
    Multidimensional Data
    Sales $olume as a !unction o! roduct) month) and
    region
    %
    r
    o
    d
    u
    c
    t
    (
    e

    i
    o
    n
    Month
    Dimensions: Product, Location, Time
    Hierarchical summarization paths
    Industry Region Year
    Category Country Quarter
    Product City onth !ee"
    #$$ice Day
    A Sample Data )ube
    Total annual sales
    o$ T% in &'(')'
    Date
    P
    r
    o
    d
    u
    c
    t
    C
    o
    u
    n
    t
    r
    y
    sum
    sum
    "V
    VC(
    %C
    )*tr
    +*tr
    ,*tr
    -*tr
    ..S.A
    Canada
    Mexico
    sum
    A Sample Data )ube
    Total annual sales
    o$ T% in &'(')'
    Date
    P
    r
    o
    d
    u
    c
    t
    C
    o
    u
    n
    t
    r
    y
    sum
    sum
    "V
    VC(
    %C
    )*tr+*tr
    ,*tr-*tr
    ..S.A
    Canada
    Mexico
    sum
    E//0a/e
    Sale/ Manaer *tr) *tr+ *tr, *tr-
    (ales (ales (ales (ales
    *ohn Ne1 2or$ )333 )333 )333 )333
    Cali!ornia +33 +33 +33 +33
    "exa/ 4,--4 4,--4 4,--4 4,--4
    5hio 44,, 44,, 44,, 44,,
    6llnoi/ ,-44 ,-44 ,-44 ,-44
    *tr) *tr+ *tr, *tr-
    ac Ne1 2or$
    Cali!ornia
    "exa/
    5hio ,33
    6llnoi/
    Star S#hema
    A single !act table and !or each dimension one dimension table
    Does not cature hierarchies directl&
    *+amle o! Star Schema" Figure 8.9




    (n the e+amle) sales !act table is connected to dimensions
    location) roduct) time and organi.ation. (t shows that data can be
    sliced across all dimensions and again it is ossible !or the data to
    be aggregated across multile dimensions. :Sales dollar: in sales
    !act table can be calculated across all dimensions indeendentl& or
    in a combined manner which is e+lained below.
    Sales dollar $alue !or a articular roduct
    Sales dollar $alue !or a roduct in a location
    Sales dollar $alue !or a roduct in a &ear within a location
    Sales dollar $alue !or a roduct in a &ear within a location sold
    or ser$iced b& an emlo&ee
    E/ample o0 Star S#hema

    time_$ey
    day
    day_o!_the_1ee$
    month
    7uarter
    year
    time
    location_$ey
    /treet
    city
    province_or_/treet
    country
    location
    Sale/ #act "a0le
    time_$ey
    item_$ey
    0ranch_$ey
    location_$ey
    unit/_/old
    dollar/_/old
    av_/ale/
    Mea/ure
    /
    item_$ey
    item_name
    0rand
    type
    /upplier_type
    item
    0ranch_$ey
    0ranch_name
    0ranch_type
    0ranch
    Aggregation
    Man& OLAP 7ueries in$ol$e aggregation o! the data in the !act
    table
    For e+amle) to !ind the total sales #o$er time% o! each roduct in
    each mar/et) we might use
    S*L*C' S.Mar/et3(d) S.Product3(d) S6M #S.Sales3Amt%
    FROM Sales S
    ;RO6P 2< S.Mar/et3(d) S.Product3(d
    'he aggregation is o$er the entire time dimension and thus
    roduces a two5dimensional $iew o! the data
    Aggregation O%er ime
    'he outut o! the re$ious 7uer&
    S6M#Sales_Amt% M1 M2 M3 M4
    P1
    3553 1653
    7
    P2
    8553 2452
    7
    P3
    4653 3
    7
    P4
    9653 9555
    7
    P6 7 7 7
    Market_Id
    P
    r
    o
    d
    u
    c
    t
    _
    I
    d
    ypi#al O,AP
    Operations
    Roll u #drill5u%" summari.e data

    b& climbing u hierarch& or b& dimension reduction


    Drill down #roll down%" re$erse o! roll5u
    !rom higher le$el summar& to lower le$el summar& or
    detailed data) or introducing new dimensions
    Slice and dice"
    ro=ect and select
    Pi$ot #rotate%"

    reorient the cube) $isuali.ation) >D to series o! ?D lanes.


    Other oerations
    drill across" in$ol$ing #across% more than one !act table
    drill through" through the bottom le$el o! the cube to its
    bac/5end relational tables #using S0L%
    Drilling Do"n and $olling
    Up
    Some dimension tables !orm an aggregation hierarch&
    Mar/et3(d Cit& State Region
    *+ecuting a series o! 7ueries that mo$es down a hierarch& #e.g.) !rom
    aggregation o$er regions to that o$er states% is called drilling down

    Re7uires the use o! the !act table or in!ormation more seci!ic


    than the re7uested aggregation #e.g.) cities%
    *+ecuting a series o! 7ueries that mo$es u the hierarch& #e.g.) !rom
    states to regions% is called rolling u
    Drilling down on mar/et" !rom Region to State
    Sales #Mar/et3(d) Product3(d) 'ime3(d) Sales3Amt%
    Mar/et #Mar/et3(d) Cit&) State) Region%
    S*L*C' S.Product3(d) M.Region) S6M #S.Sales3Amt%
    FROM Sales S) Mar/et M
    41*R* M.Mar/et3(d @ S.Mar/et3(d
    ;RO6P 2< S.Product3(d) M.Region
    S*L*C' S.Product3(d) M.State) S6M #S.Sales3Amt%
    FROM Sales S) Mar/et M
    41*R* M.Mar/et3(d @ S.Mar/et3(d
    ;RO6P 2< S.Product3(d) M.State)
    Drilling Do"n
    $olling Up
    Rolling u on mar/et) !rom State to Region
    (! we ha$e alread& created a table) State3Sales) using
    8. S*L*C' S.Product3(d) M.State) S6M #S.Sales3Amt%
    FROM Sales S) Mar/et M
    41*R* M.Mar/et3(d @ S.Mar/et3(d
    ;RO6P 2< S.Product3(d) M.State
    then we can roll u !rom there to"
    ?. S*L*C' '.Product3(d) M.Region) S6M #'.Sales3Amt%
    FROM State3Sales ') Mar/et M
    41*R* M.State @ '.State
    ;RO6P 2< '.Product3(d) M.Region
    $oll:up and Drill Do"n

    Sales Channel

    Region

    Country

    State

    Location Address

    Sales Representative
    R
    o
    l
    l

    6

    1igher Le$el o!
    Aggregation
    Low5le$el
    Details
    D
    r
    i
    l
    l
    5
    D
    o
    w
    n
    ;Sli#ing and Di#ing<
    Produ#t
    Sales )hannel
    $
    e
    g
    i
    o
    n
    s
    Retail Direct Secial
    1ousehold
    'elecomm
    Video
    Audio
    (ndia
    Far *ast
    *uroe
    'he 'elecomm Slice
    Sno"0la.e S#hema
    A snow!la/e schema is a term that describes a star schema
    structure normali.ed through the use o! outrigger tables. i.e
    dimension table hierarchies are bro/en into simler tables. (n
    star schema e+amle we had A dimensions li/e location) roduct)
    time) organi.ation and a !act table #sales%
    Sno"0la.e s#hema
    Reresent dimensional hierarch& directl& b& normali.ing tables.
    *as& to maintain and sa$es storage
    *+amle o! Snow!la/e
    Schema



    E/ample o0 Sno"0la.e S#hema
    time_$ey
    day
    day_o!_the_1ee$
    month
    7uarter
    year
    time
    location_$ey
    /treet
    city_$ey
    location
    Sale/ #act "a0le
    time_$ey
    item_$ey
    0ranch_$ey
    location_$ey
    unit/_/old
    dollar/_/old
    av_/ale/
    Mea/ure
    /
    item_$ey
    item_name
    0rand
    type
    /upplier_$ey
    item
    0ranch_$ey
    0ranch_name
    0ranch_type
    0ranch
    /upplier_$ey
    /upplier_type
    /upplier
    city_$ey
    city
    province_or_/treet
    country
    city
    THANK YOU
    o learn more about hyperion
    please %isit
    http://learnhyperion!"ordpress!#o
    m

    You might also like