Modeling Technique

[srokes]

I am curious of how many people are modeling using a "Dimensional" modeling/star schema design. How many are still building in 3rd normal form? Any specific easily presentable benefits of one over the other?

 

[johnherman]

We are using both. There are performance gains in using the star schema with integer surrogate keys due to join speeds. In addition, certain databases (Oracle, for instance) now recognize or can be forced to recognize star schemas and optimize for them. The relational model fits well with standard (relational) SQL and Relational DBMS's. The dimensional models work exceptionally well with Multidimensional DBMS and cubes.

Remember that Kimball has stated that the dimensional model is normalized at about 1.5 NF (rather than 3rd NF for most relational models).

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[teccum]

We are using Star Schema but we have normalized on certain dimension. This we have to follow due to the cardinality between the fields in the dimension.

I would say that we are following a Normalized Star Schema(where certain dimensions are normalized as per the requirements) rather than a consolidated star schema(where all the dimensions are completely denormalized). Since it is common using surrogate keys in the dimensional model, there is performance advantage other than the schema advantage itself.