Wiki Home

Specific Reasons To Denormalize


Namespace: SoftwareEng
Why denormalize?
OLTP applications are typically correctly normalized, whereas OLAP ones are not.
From Flemming And Von Halle:
  • Rule RDD11.1: Consider duplicating one or a few nonvolatile columns from a parent, ancestor, or one-to-one child table to expedite table lookups in frequent or critical requests.

    norm.prg
    denorm.prg

    Queries to get invoice items for each account manager:

    SELECT Accountmanager_a.cid, Accountmanager_a.cnam, Invoiceitem_a.cdsc;
      FROM norm!accountmanager Accountmanager_a ;
        INNER JOIN norm!client;
          ON  Accountmanager_a.ikey = Client.iclnamkey ;
        INNER JOIN norm!purchaceorder;
          ON  Client.ikey = Purchaceorder.ipoclnkey ;
        INNER JOIN norm!joborder ;
          ON  Purchaceorder.ikey = Joborder.ijopokey ;
        INNER JOIN norm!timecard ;
          ON  Joborder.ikey = Timecard.itcjokey ;
        INNER JOIN norm!invoiceitem Invoiceitem_a ;
          ON  Timecard.ikey = Invoiceitem_a.iiitckey
    
    SELECT Accountmanager.cid, Accountmanager.cnam, Invoiceitem.cdsc;
       FROM  norm!accountmanager INNER JOIN norm!invoiceitem ;
         ON  Accountmanager.ikey = Invoiceitem.iiiamkey
    


    Explanation comming soon...
    Denormalization is a timesaver in a Data Warehouse if the data is frequently referenced or reported on in a particular way.


    Denormalization is essential in Dimensional Modeling. In fact, normalizing dimension tables defeats the purpose of the design entirely. This works (and works well) because data will never be modified directly in the dimensional warehouse. -- Tod Mckenna

    Contributors Carl Karsten, Cindy Winegarden
    Category Data
  • ( Topic last updated: 2007.01.22 03:35:22 PM )