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

Sure, a data warehouse can have a normalized design, but, in a data warehouse, that's probably NOT a good thing.

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.

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

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.

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.

This is very helpful. Thanks

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.

I don't agree, blindman. I've worked on extremely large star schemas. They worked and they definitely weren't datamarts.

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.

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.