Reasons to use a 3NF design over a Dimensional Model design for an EDW - Discussion

twofivepie wrote:

PHough wrote:... in a Star schema the fact table is "always" fully normalised and optimised....

Hi Paul,

I have been reading through this very interesting thread and noticed this comment from you and I wondered a lot about it. My understanding is that a fact table is DE normalised in the sense that it repeats information. For example if you had a fact table of order line items you would have the order key and the customer key in each order line item row. In a normalised form you would have a customer order_header table and a customer order_line_items table, joined on order number and the customer key would only exist once, in the orders header table.

In what sense were you using the term 'normalised'?

regards

Simon

It's irrelevant, other than academicly, to describe a star schema in terms of normal forms, since normalization techniques are not used to develop the model.

However, if one was to desribe a fact table (and only a fact table) in terms of its normal form, it would usually be 3NF, as the contents of the row are bound by its key (i.e. the dimension keys make up the primary key) and only the key.

ngalemmo, I guess it could be argued that is academic, but the title of the post is '3NF vs dimensional modeling', so I just wanted to clarify my understanding.

I maintain that a fact table is not in 3nf normal form, because it contains redundancy in potentially several keys - a sales order fact table may have many dimension keys not required for unique key in to the fact, after sales order number and line number, you may have order date, sold-to, salesman, payment terms that will all be characteristics of the order, but which will be repeated on each row. If this was 3NF, you would have an order header table and and order lines table. Or have I misunderstood 3NF in this context?

regards

Simon

ok - now read Chapter 6 - Introducing Dimensional Modeling, "The Data Warehouse Lifecycle toolkit" and Kimabll does indeed say "...fact tables are typically normalised to 3NF in a dimensional model because the related context is removed to dimension tables." - hmm...

Maybe I am wrong, but as far as I can remember from one of Kimball's books, fact tables are in 3NF and dimension tables 2NF.

My understanding about 3NF is the repeated textual attributes should be removed and put in another table and then have FK in the subject table. So repeating keys instead of contents is 3NF thinking.

Another guiding principle about dimensional modeling is normalised facts and denormalised dimensions. However when we talk about applying 3NF for data warehouse system is really about snow-flaking/normalising dimensions. As far as fact tables are concerned, true dimensional modeling practitioners always advocate normalisation more vigorously than any relational thinkers in an OLTP system.

The key objectives on the dimensional modeling is, normalised fact will deliver the best performance and denormalised dimensions deliver the easy of use (and possibly performance as well). In layman's term, big (deep) fact tables should be normalised while small dimension (shallow) tables should be denormalised.

However the guidelines should never be used as dogmatic rules dictating your design. Kimball has pointed out, in his books and articles, many scenarios in which you should be more flexible about the guidelines. For instance, a bridge table needs to be introduced for multivalued dimensions, a monster dimension should be normalised to avoid performance and storage issues. I noticed in both relational and dimensional camps, many people go from one extreme to another and rarely try to take advantages from other side, or even worse, applying the methodology in wrong situations.

twofivepie wrote:ngalemmo, I guess it could be argued that is academic, but the title of the post is '3NF vs dimensional modeling', so I just wanted to clarify my understanding.

I maintain that a fact table is not in 3nf normal form, because it contains redundancy in potentially several keys - a sales order fact table may have many dimension keys not required for unique key in to the fact, after sales order number and line number, you may have order date, sold-to, salesman, payment terms that will all be characteristics of the order, but which will be repeated on each row. If this was 3NF, you would have an order header table and and order lines table. Or have I misunderstood 3NF in this context?

regards

Simon

If you were applying normalization you would not have a dimensional model. I do not describe dimensional models in terms of normal forms. Wither one thinks a fact table is one normal form or another doesn't matter because it is a fact table, not a table or entity in the ER modeling sense. It is just that people like to give labels to things and most of the time a fact table is considered to be in 3NF or something close to it.

As I said, its irrelevant.

The thread is not... it started out as a discussion as to wither a normalized data store (Inmon) is more/less appropriate than a dimensional data store (Kimball).

As far as fact tables are concerned, true dimensional modeling practitioners always advocate normalisation more vigorously than any relational thinkers in an OLTP system.

Really? When I'm desiging a dimensional model, normalization is the farthest thing from my mind. When I teach the subject I always recommend that any ER modelers forget everything they've learned before...

ngalemmo wrote:

As far as fact tables are concerned, true dimensional modeling practitioners always advocate normalisation more vigorously than any relational thinkers in an OLTP system.

Really? When I'm desiging a dimensional model, normalization is the farthest thing from my mind. When I teach the subject I always recommend that any ER modelers forget everything they've learned before...

That's because normalization is ingrained into your thought processes. After normalizing models for years, you don't actually think about normalization, you just know how the attributes should be modeled. I find this is still a valuable skill in teaching how to appropriately model a dimension. The beginner dimensional modelers all struggle with the attribution of their dimensions because Kimball never provides a methodology for attributing dimensions.

That's because normalization is ingrained into your thought processes.

...not my thought process. I started designing databases before relational databases became available. Normalization was just a topic of a research paper then. When I design star schema I think in terms of business events, states, and context.

For example, an employee belongs to a department. Is it one dimension or two? Normalization demands it be two tables, no question. In dimensional modeling, on the other hand, it depends on what that relationship means to the business... its it just an attribute or part of a more complex organizational structure?

But 3nf is useful in the staging area. It certainly makes creating the Dimension tables easier.