Scribd Logo
Upload

Welcome to Scribd!

  • 143 views

    Data Warehousing - CS614 Power Point Slides Lecture 07

    Uploaded by

    N Gull
    De-normalization involves combining or splitting tables in a data warehouse to improve query performance, while maintaining data integrity. It reduces the number of joins and rows retrieved, speeding up queries. However, it introduces redundant data, so the level of de-normalization must be carefully considered. Common techniques include collapsing tables in a one-to-one or many-to-many relationship, splitting tables horizontally or vertically, pre-joining tables, and adding redundant or derived columns. Guidelines for de-normalization include performing a cost-benefit analysis, data requirement analysis, and weighing maintenance issues of redundant data.

    Copyright:

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

    Data Warehousing - CS614 Power Point Slides Lecture 07

    Uploaded by

    N Gull
    143 views9 pages
    De-normalization involves combining or splitting tables in a data warehouse to improve query performance, while maintaining data integrity. It reduces the number of joins and rows retrieved, speeding up queries. However, it introduces redundant data, so the level of de-normalization must be carefully considered. Common techniques include collapsing tables in a one-to-one or many-to-many relationship, splitting tables horizontally or vertically, pre-joining tables, and adding redundant or derived columns. Guidelines for de-normalization include performing a cost-benefit analysis, data requirement analysis, and weighing maintenance issues of redundant data.

    Original Description:

    Data Warehousing - CS614 Power Point Slides Lecture 07

    Copyright

    © © All Rights Reserved

    Available Formats

    PPT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    De-normalization involves combining or splitting tables in a data warehouse to improve query performance, while maintaining data integrity. It reduces the number of joins and rows retrieved, speeding up queries. However, it introduces redundant data, so the level of de-normalization must be carefully considered. Common techniques include collapsing tables in a one-to-one or many-to-many relationship, splitting tables horizontally or vertically, pre-joining tables, and adding redundant or derived columns. Guidelines for de-normalization include performing a cost-benefit analysis, data requirement analysis, and weighing maintenance issues of redundant data.

    Copyright:

    © All Rights Reserved
    Download as PPT, PDF, TXT or read online from Scribd
    Download as ppt, pdf, or txt
    143 views9 pages

    Data Warehousing - CS614 Power Point Slides Lecture 07

    Uploaded by

    N Gull
    De-normalization involves combining or splitting tables in a data warehouse to improve query performance, while maintaining data integrity. It reduces the number of joins and rows retrieved, speeding up queries. However, it introduces redundant data, so the level of de-normalization must be carefully considered. Common techniques include collapsing tables in a one-to-one or many-to-many relationship, splitting tables horizontally or vertically, pre-joining tables, and adding redundant or derived columns. Guidelines for de-normalization include performing a cost-benefit analysis, data requirement analysis, and weighing maintenance issues of redundant data.

    Copyright:

    © All Rights Reserved
    Download as PPT, PDF, TXT or read online from Scribd
    Download as ppt, pdf, or txt
    You are on page 1of 9

    Data Warehousing

    Lecture-7
    De-normalization

    1
    De-normalization

    2
    Striking a balance between “good” & “evil”
    De-normalization Normalization
    Too many tables
    4+ Normal Forms

    3rd Normal Form

    2nd Normal Form

    Data Cubes 1st Normal Form

    Data Lists

    Flat Table One big flat file

    3
    What is De-normalization?
     It is not chaos, more like a “controlled crash”
    with the aim of performance enhancement
    without loss of information.

     Normalization is a rule of thumb in DBMS,


    but in DSS ease of use is achieved by way of
    denormalization.

     De-normalization comes in many flavors,


    such as combining tables, splitting tables,
    adding data etc., but all done very carefully. 4
    Why De-normalization In DSS?
     Bringing “close” dispersed but related data
    items.

     Query performance in DSS significantly


    dependent on physical data model.

     Very early studies showed performance


    difference in orders of magnitude for different
    number de-normalized tables and rows per table.

     The level of de-normalization should be


    carefully considered. 5
    How De-normalization improves performance?
    De-normalization specifically improves
    performance by either:

     Reducing the number of tables and hence the


    reliance on joins, which consequently speeds up
    performance.

     Reducing the number of joins required during


    query execution, or

     Reducing the number of rows to be retrieved from


    the Primary Data Table.
    6
    4 Guidelines for De-normalization
    1. Carefully do a cost-benefit analysis
    (frequency of use, additional storage,
    join time).
    2. Do a data requirement and storage
    analysis.
    3. Weigh against the maintenance issue
    of the redundant data (triggers used).
    4. When in doubt, don’t denormalize.
    7
    Five principal De-normalization techniques
    1. Collapsing Tables.
    - Two entities with a One-to-One relationship.
    - Two entities with a Many-to-Many relationship.

    2. Splitting Tables (Horizontal/Vertical Splitting).

    3. Pre-Joining.

    4. Adding Redundant Columns (Reference Data).

    5. Derived Attributes (Summary, Total, Balance etc).

    8
    Collapsing Tables
    ColA ColB
    denormalized

    ColA ColB ColC


    normalized

    ColA ColC

     Reduced storage space.

     Reduced update time.

     Does not changes business view.

     Reduced foreign keys.

     Reduced indexing.
    9

    You might also like