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

How to model Self-referntial fact record

2 posters

Go down

How to model Self-referntial fact record Empty How to model Self-referntial fact record

Post  friend4allmo Fri Aug 01, 2014 8:03 am

Hi , i have a fact table like the below image
How to model Self-referntial fact record Fact12
this fact record map to case,transaction and email data where the case have many transcation and the transcation may be an email or a call

the problem for me the emai data like when it's arrived, opened , its current status , the issue email sent for and so on is handled in other diemsnions related to this fact.
now , most email in should have replies and the email in and the replies , each of them has a record in this fact, so how to model the relation between the email out and its email in ? does this mean that i need to make self referential fact table ?

Thanks,
Fr

friend4allmo

Posts : 10
Join date : 2014-05-22

Back to top Go down

How to model Self-referntial fact record Empty Re: How to model Self-referntial fact record

Post  ngalemmo Sat Aug 02, 2014 12:28 pm

You can always have a key referencing a previous transaction. It's value depends on how users intend to use this.

If they only look at the immediate parent, no problem. However, if they wish to traverse the hierarchy further, it requires some complex SQL that is not universally supported. To support the hierarchy, the relationship should be published as a bridge table. Generate the bridge based on the parent/child keys in the fact.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

How to model Self-referntial fact record Empty Re: How to model Self-referntial fact record

Post  friend4allmo Mon Aug 04, 2014 4:30 am

Thanks Nick for your reply

yes, it should not be any hierarchy. the use of it for referring the previous transactions which is email in  (in my case) not more that.
but won't this affect the performance when i need the join the fact with itself ?

Thanks,
Fr

friend4allmo

Posts : 10
Join date : 2014-05-22

Back to top Go down

How to model Self-referntial fact record Empty Re: How to model Self-referntial fact record

Post  ngalemmo Mon Aug 04, 2014 11:44 am

Yeah, sure it will affect performance. But if you need to join, what other options are there?
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

How to model Self-referntial fact record Empty Re: How to model Self-referntial fact record

Post  friend4allmo Tue Aug 05, 2014 7:48 pm

i couldn't find any other solution.


Thanks alot for you answer and your help Nick!

friend4allmo

Posts : 10
Join date : 2014-05-22

Back to top Go down

How to model Self-referntial fact record Empty Re: How to model Self-referntial fact record

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