Relationship between Fact and Dimension Table - Identifying or non-identifying?
4 posters
Page 1 of 1
Relationship between Fact and Dimension Table - Identifying or non-identifying?
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
Re: Relationship between Fact and Dimension Table - Identifying or non-identifying?
Yes, no, and it doesn't really matter.
Re: Relationship between Fact and Dimension Table - Identifying or non-identifying?
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.
Whether you enforce this in your physical design may depend on how you handle your ETL.
Re: Relationship between Fact and Dimension Table - Identifying or non-identifying?
A relationship is either identifying or non-identifying. It cannot be both (i.e. mutually exclusive). Both are valid in a dimensional model.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Relationship between Fact and Dimension Table - Identifying or non-identifying?
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?
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
![-](https://2img.net/i/empty.gif)
» Identifying fact grain
» Relationship between fact table and dimension tables
» many to many fact table relationship - use dimension, bridge or ?
» Relationship between view-dimension and fact table
» How to Model Fact table having 1:1 relationship with key Dimension attributes
» Relationship between fact table and dimension tables
» many to many fact table relationship - use dimension, bridge or ?
» Relationship between view-dimension and fact table
» How to Model Fact table having 1:1 relationship with key Dimension attributes
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum