Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Dimensional keys in both parent and child fact tables

2 posters

Go down

Dimensional keys in both parent and child fact tables Empty Dimensional keys in both parent and child fact tables

Post  djphatic 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

Back to top Go down

Dimensional keys in both parent and child fact tables Empty Re: Dimensional keys in both parent and child fact tables

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

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

http://aginity.com

Back to top Go down

Dimensional keys in both parent and child fact tables Empty Re: Dimensional keys in both parent and child fact tables

Post  djphatic 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

Back to top Go down

Dimensional keys in both parent and child fact tables Empty Re: Dimensional keys in both parent and child fact tables

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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