Snowflake & Factless fact

View previous topic View next topic Go down

Snowflake & Factless fact

Post  Vishy on Mon Mar 05, 2012 7:57 am


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.


Vishy

Posts : 75
Join date : 2012-02-28
Location : India

View user profile

Back to top Go down

Re: Snowflake & Factless fact

Post  BoxesAndLines on Mon Mar 05, 2012 10:08 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Snowflake & Factless fact

Post  ngalemmo on Mon Mar 05, 2012 4:11 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Snowflake & Factless fact

Post  VHF on Wed Mar 07, 2012 5:37 pm

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:

ProductKeyProductProductCategory
1ReclinerLiving Room Furniture
2SofaLiving Room Furniture
3BedBedroom Furniture

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

ProductKeyProductProductCategoryKey
1Recliner1
2Sofa1
3Bed2

ProducCategorytKeyProductCategory
1Living Room Furniture
2Bedroom 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).

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Snowflake & Factless fact

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum