Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2003
    Posts
    95

    Can a Data Warehouse Have a Normalized Design?

    I only have a basic understanding of data warehouse design. I've always thought a data warehouse is primarily a denormalized model, but recently we have been speaking with a data warehouse vendor who says their product is normalized and is not a Star or Snowflake schema. This sounds more like a data store. Can a data warehouse have a primarily normalized model?

    Thanks, Dave

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Sure, a data warehouse can have a normalized design, but, in a data warehouse, that's probably NOT a good thing.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Actually, I would RECOMMEND a normalized schema for a data warehouse, with additional tables for commonly aggregated data. I would use a Star or Snowflake schema for datamarts that are spun off of the data warehouse.
    It sounds like your vendor is an adherant of Inmon's philosophy of data warehousing, rather than Kimball's "one-size star schema fits all" approach.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2003
    Posts
    95
    I read a few articles this morning on the Internet about Enterprise Data Warehouses and how the designs are typically normalized vs. a Data Warehouse, where the model is denormalized. It looks like we are wanting an Enterprise Data Warehouse.

    Thanks, Dave

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    It is very easy to create non-normalized data from normalized data.
    It is very difficult to create normalized data from non-normalized data.
    Lesson: Store your data in a normalized schema. Create additional non-normalized and pre-aggregated tables if processing speed becomes an issue.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    There is another angle to it.

    A dimensional (denormalized) data warehouse is the product of a series of translations.

    1. Data Sources are analyzed.
    2. A normalized model called the Operational Data Store is created to represent the enterprise.
    3. A Dimensional Data Store is created based on the normalized Operational Data Store.
    4. ETL routines are written to move data from the Source Systems to the Operational Data Store
    5. ETL routines are written to move data from the Operational Data Store to the Dimensional Data Store
    6. Data is then extracted transformed and loaded from the Data Sources into the Operational Data Store
    7. Data is then extracted transformed and loaded from the Operational Data Store into the Dimensional Data Store
    8. Analytical tools are used against the Dimensional Data Store for reporting
    9. The two step ETL process (6, 7 & 8) is repeated on a schedule as frequently as every day

    This is the Kimball method.

    Similar processes are used to create subsets that function as Data Marts.

    I can't comment on Inmon's strategy except that he is in favor of normalized models (Operational Data Stores or even properly normalized Source Systems) being used without modification as processing power increases and makes the performance barriers of multiple joins go down. Kimball's rebuttal is that dimensional model is easier to grasp by the end user than a complex normalized data model.
    Last edited by certus; 09-15-06 at 14:47.

  7. #7
    Join Date
    Jan 2003
    Posts
    95
    This is very helpful. Thanks

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Kimball doesn't do data warehouses. He sells datamarts and calls them datawarehouses.
    Actually, he sells seminars.
    And telling people that they can create a datawarehouse just by using a simple star schema is a great way to get people who don't know jack about data warehousing to pay to attend your seminar.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    I don't agree, blindman. I've worked on extremely large star schemas. They worked and they definitely weren't datamarts.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    There is simply a limit to the complexity of relationships that can be established with a fixed star schema. Any logical design that can be boiled down to a single fact table and associated dimensions is a datamart, not a datawarehouse. How large it is, as in "number of records", is irrelevant. What matters is the number of tables, the complexity of the relationships, and the variety of data from different areas of the business enterprise that it is able to store.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Kimball does not limit the number of fact tables to one. You can have as many fact tables and dimensions as you need. A dimension can bridge more than one fact table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •