back room design

I have a question. What is the difference between Relation database Design DW(3nf, like the source system) and create a data mart in star schema from that and Dimensional DW (back room the kitchen) and then start schema for presentation.

Thanks

A 3NF data warehouse that feeds star schema data marts is the Inmon architecture. It is basically a store (in the 3NF database) and publish (into marts) approach. Users never directly access the 3NF data warehouse.

The dimensional data warehouse is the Kimball architecture. The data warehouse is made up of integrated star schema. Data warehouse and marts are one in the same. Users access the data warehouse directly, there is no requirement for a publishing tier.

Yes thanks for reply. My question is about Kimball's back room (Kichen) which is also in 3NF.

The Kimball "back room" or kitchen is primarily about process -- most significantly, the ETL process that populates the dimensional data warehouse. As part of that ETL process, most DW/BI systems include a staging area, which can be implemented as files or data tables, and is often a mixture of both of those. The Kimball architecture doesn't make strong recommendations about the data model for that staging area. Honestly, we don't care that much... the staging area exists to get the job (ETL) done. You can model it however you want. It's akin to a scratchpad, never touched by or visible to the user community.

I do want to emphasize that the staging area typically holds only the data needed for the ongoing ETL: the incremental extract, plus any decode or other lookup tables needed to do the ETL. It is NOT the same as Inmon's 3NF data warehouse, which maintains a historical store and is designed to be comprehensive (at least, that's how I read Inmon's architecture, when I can tolerate the ennui brought on by such an attempt).

People (primarily consultants) who have built a lot of data warehouses tend to use a consistent pattern in the design of their staging areas. But I doubt very many of them would get all excited and try to tell you that YOU have to design your staging area the same way they do.

Joy,

Thanks for clearing that up. I wasn't sure about the 'kitchen' reference. By the way, you've got Inmon right... (yawn!).

Thanks Jay and all, I think I am little clear now. But I still think if we design our staging(call it EDW, if you like) in 3NF then use Star schema for reporting then there not much diffreence with Inmon EDW (3nf) and have data marts in dimensional for reporting. Also in Inmon approch if department A(data mart) uses a dimension and department B (another data mart) uses same dimension then do we create the same dimension 2 times and load 2 times, one for deparmtnet A and one for B?, or Inmon also uses Conf dimension.
Thanks again for your reply.

I don't think staging area should ever be structured in 3NF. What's the point to have 3NF as it's a just a dumping ground for the convenience of using SQL for down-stream ETL activities. If you are concerned about data quality, you may have an ODS in 3NF structure before loading the data further into DDS in star format.

However there IS a difference between this ODS-DDS arrangement and Inmon's approach. In my understanding Inmon's architecture does not have a physical DDS with proper SCD and surrogate key discipline. In 3NF DW, dimensions and facts are all built off the 3NF relational model on the fly, although I am sceptical about the performance and the efficiency of its history keeping mechanism other than SCD2.

Let's put aside the argument about which method is better first. To be fair, I don't think Inmon's approach suggests loading same dimension twice in your case. On the contrary, 3NF DW would be even more disciplined about sharing data and minimising data redundancy, even at expense of performance and ease of query. And no, I don't think Inmon likes the concept of dimension conformance which requires undoing relational thinking, and starting thinking dimensionally if you seriously embrace Kimall's methodology.

The point is, don't mix the two methodologies in one DW, as they apply fundamentally different mindsets and doing so would end up with confusing and inconsistent architecture.

Thank to all for reply.

3NF in the staging areas doesn't hurt as long as you don't get too dogmatic about it. It can be handy when getting flat files and can be helpful when there is a need to create additional levels of a hierarchy.