Dimensional keys in both parent and child fact tables

View previous topic View next topic Go down

Dimensional keys in both parent and child fact tables

Post  djphatic on Wed Nov 28, 2012 10:16 am

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?

Child Only DimensionsChild & Parent Dimensions
OperationKeyOperationKey
SessionKeySessionKey
OperationTypeOperationType
OperationConsultantOperationConsultant
OperationSpecialtyOperationSpecialty
OperationAnaestheticTypeOperationAnaestheticType
MeasuresSessionType
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

View user profile

Back to top Go down

Re: Dimensional keys in both parent and child fact tables

Post  ngalemmo on Wed Nov 28, 2012 1:19 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Dimensional keys in both parent and child fact tables

Post  djphatic on Wed Nov 28, 2012 1:38 pm

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

View user profile

Back to top Go down

Re: Dimensional keys in both parent and child fact tables

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