Snowflake & Factless fact

1) Going though few issues where snowflake is mentioned, few confusion arises.
Few people say that "snowflake helps when you are aggregating on higher level attributes which otherwise you would get by applying DISTINCT from the dimension."


But in case of aggregation on higher values you would be joining directly to the fact table so in this case it became a dimension ( mini dimension) in itself.. where is snowflaking here ??

Snowflaking is a thing where you break a dimension and create one more and you join both the dimension with a natural key.. am I right ?

Sometime I listen people talking about surrogate keys in snowflaked dimension, but if you are having surrogate keys then you must be connecting to the fact and in this case it becomes a dimension in itself.


WHy we don't call a snowflaked dimension a LOOKUP dimension so that there is no confusion??


2) why we call a FACT LESS FACT table a fact less fact table ?? we should call it measureless fact table because FACT is something which has happened so for example if we have a factless fact table with time and product id which shows which are the product were on promotion on a give day/month. So this factless fact table is actually giving you facts taking the defintion that "FACT --what has happened".
it doesnt hold any measure .. so these tables should be called measureless fact table.

When you write your book, you can call it whatever you like. Since Ralph wrote the book, he called it snowflake and factless fact table. Calling it something else now just adds confusion to those who read the book.

A snowflake schema has dimensions that have foreign keys to other dimensions, and that other dimension does not have a FK reference to it in the fact table.

Vishy wrote:Snowflaking is a thing where you break a dimension and create one more and you join both the dimension with a natural key.. am I right ?

Sometime I listen people talking about surrogate keys in snowflaked dimension, but if you are having surrogate keys then you must be connecting to the fact and in this case it becomes a dimension in itself.

In a pure star-schema, there are no relations between dimensions. Fact tables are highly normalized, but dimensions are denormalized.

Snowflaking breaks these dimensions into multiple tables by fullly or partially normalizing them. However surrogate keys can and should still be used.

For example, a denormalized star-schema Product dimension:

ProductKey	Product	ProductCategory
1	Recliner	Living Room Furniture
2	Sofa	Living Room Furniture
3	Bed	Bedroom Furniture

We could snowflake this dimension into the following two normalized tables:

ProductKey	Product	ProductCategoryKey
1	Recliner	1
2	Sofa	1
3	Bed	2

ProducCategorytKey	ProductCategory
1	Living Room Furniture
2	Bedroom Furniture

Dimensional modlers coming from a 3NF relational database background tend to want to do this, but it is not prefered from a dimensional modeling standpoint as it increaes complexity and hurts query performance.

However, one reason to do it would be having facts at different grains. For example, there might sales facts at the Product grain but sales forecasts at the ProductCategory grain. The snowflake model allows the forecast facts to relate directly to ProductCategory using its surrogate key (SK).