Relationship between Fact and Dimension Table - Identifying or non-identifying?

View previous topic View next topic Go down

Relationship between Fact and Dimension Table - Identifying or non-identifying?

Post  kiran.mv on Wed Jun 15, 2011 4:54 am

Can the relationship between a dimension table and fact table be identifying as well as non-identfying?

kiran.mv

Posts : 13
Join date : 2011-03-10

View user profile

Back to top Go down

Re: Relationship between Fact and Dimension Table - Identifying or non-identifying?

Post  ngalemmo on Wed Jun 15, 2011 9:23 am

Yes, no, and it doesn't really matter.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Relationship between Fact and Dimension Table - Identifying or non-identifying?

Post  Bob Probst on Wed Jun 15, 2011 10:36 am

In a logical model, any dimension which is part of the fact grain would be considered identifying. Any dimension which further describes that grain would be considered non-identifying.

Whether you enforce this in your physical design may depend on how you handle your ETL.


Bob Probst

Posts : 18
Join date : 2010-05-26

View user profile http://datajuggler.blogspot.com/

Back to top Go down

Re: Relationship between Fact and Dimension Table - Identifying or non-identifying?

Post  BoxesAndLines on Wed Jun 15, 2011 12:57 pm

A relationship is either identifying or non-identifying. It cannot be both (i.e. mutually exclusive). Both are valid in a dimensional model.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Relationship between Fact and Dimension Table - Identifying or non-identifying?

Post  kiran.mv on Thu Jun 16, 2011 12:21 am

Thanks for the replies.
I understand it now.

Especially the explanation given by Bob is very helpful. I now get the concept well.
And BoxesAndLines, I understand that a relationship can either be identifying or non-identifying at a time. My question was a bit misleading. I actually thought the relationships between dimension tables and fact table should always be identifying. I now understand that it can be either.

Two clarifications I seek from you all is :
1. whether inclusion of some flags and numeric attributes apart from the FKs of dimension tables in the primary key of Fact table normal?
2. If I group these numeric attributes and flags that are part of the primary key in a junk dimension and join this junk dimension with the fact table through identifying relationship, will this be part of the key of the fact table?

kiran.mv

Posts : 13
Join date : 2011-03-10

View user profile

Back to top Go down

Re: Relationship between Fact and Dimension Table - Identifying or non-identifying?

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