Dimensional keys in both parent and child fact tables
2 posters
Page 1 of 1
Dimensional keys in both parent and child fact tables
I have some measures which cannot be broken down to the child level so I have created two fact tables.
My question is should the parent dimensional keys be stored in the child fact table as well as the parent fact table? Or would a join between the parent and child fact tables suffice?
For example, my parent-child relationship is theatre sessions and operations.
A session has dimensions such as session type, session scheduling type, session specialty, session consultant. SessionKey is the degenerate dimension.
An operation has dimensions such as operation type, operation consultant, operation specialty, operation anaesthetic type. OperationKey and SessionKey are degenerate dimensions.
Which of the following should the child fact table look like?
The parent fact table as well as having it's own measures would have also have aggregated child measures.
My question is should the parent dimensional keys be stored in the child fact table as well as the parent fact table? Or would a join between the parent and child fact tables suffice?
For example, my parent-child relationship is theatre sessions and operations.
A session has dimensions such as session type, session scheduling type, session specialty, session consultant. SessionKey is the degenerate dimension.
An operation has dimensions such as operation type, operation consultant, operation specialty, operation anaesthetic type. OperationKey and SessionKey are degenerate dimensions.
Which of the following should the child fact table look like?
Child Only Dimensions | Child & Parent Dimensions |
OperationKey | OperationKey |
SessionKey | SessionKey |
OperationType | OperationType |
OperationConsultant | OperationConsultant |
OperationSpecialty | OperationSpecialty |
OperationAnaestheticType | OperationAnaestheticType |
Measures | SessionType |
SessionScheduledType | |
SessionSpecialty | |
SessionConsultant | |
Measures |
The parent fact table as well as having it's own measures would have also have aggregated child measures.
djphatic- Posts : 20
Join date : 2012-04-21
Re: Dimensional keys in both parent and child fact tables
The concept of 'parent' and 'child' facts does not exist in a dimensional model. Each star schema (fact table) is a stand-alone entity. Any fact table contains all dimensions that properly represent the grain and context of the measures. The operation fact should have the sessions' dimensions as well.
Re: Dimensional keys in both parent and child fact tables
ngalemmo wrote:The operation fact should have the sessions' dimensions as well.
That's what I thought. Thanks for your response.
djphatic- Posts : 20
Join date : 2012-04-21
Similar topics
» Storing Date Keys in dimension tables versus fact tables
» Looking for advise on loading keys into factless fact tables
» Modelling parent-child relationship source tables to Fact with correct grain
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» Composite Dimension Natural Keys - Attribute Used In Multiple Dimensions as well as Fact Tables
» Looking for advise on loading keys into factless fact tables
» Modelling parent-child relationship source tables to Fact with correct grain
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» Composite Dimension Natural Keys - Attribute Used In Multiple Dimensions as well as Fact Tables
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum