How to join two facts.

Chaitanya

Hi Team,

Recently, I have attended couple of interviews. In Each Interview, they are asking How to Join Two Facts? I said, No.we cannot join. But they are asking if requirement comes like this, how can you do it?

Can anybody please answer this question with steps?

Regards,

C.

Robert Angel

Hi,

Case 1: Both facts have a key that means that they can join directly without altering their data granularity and without loss of data. In this less than normal case you can join directly, usually by dragging one physical table on to the logical table source of the other in the BM layer.

Case 2: Both facts share one or more foreign keys. Here you join by having both facts joining to the same dimension(s) in the BM and then you can use both facts in a single query provided only conformed dimensions are present in the query or you have set the non-conformed dimensions to total level for the fact table(s) that they do not join to.

Hope this helps,

Robert.

Chaitanya

Thanks Robert.

Regards,

C.

Joel

I would be asking the interviewer why the data model warrants the joining of 2 facts.  If the grain is the same then there should just be a single fact.

Martin van Donselaar

Case 1 Probably means your model is not correct or at least not optimally modeled (as it should probably be a single fact table and that is how you should model it in the BMM).

So you should model conformed dimensions (Case 2) , That you can even join facts that are on different dimensional level if you set the content levels correctly

Robert Angel

Agreed on case 1 - but believe it or not I have been presented with exactly what I described from a 'Big 5' consultancy, sold to the client on a country wide basis as 'off the peg'. Reality sure is sub-optimal sometimes!

On case 2 that presumes that the shared keys exist at the correct granularity to facilitate the join as you describe, and I was trying to keep the answer succinct, as long and heavily caveated answers in interviews are often (wrongly) read as someone who does not know what he is talking about...

Martin van Donselaar

True, that happens sometimes. Also, you will have to deal with similar problems if your physical model is not a star model.

BTW, I just wanted to provide some more information not invalidate your answer in any way.

Robert Angel

No offence taken, apologies if it read that way!

And yes, 3NF modelling is heavily sub-optimal by its very nature!

[Deleted User]

For Robert's case 1 I agree with bpth Robert and Martin - this can and will exist depending on who built the physical model. There's definitely occurrences of this and you don't always have the right to change it. Sometimes you have to make the best out of a poor situation.

Of course there's always a lot more to it than what we all write in 2-3 sentences as much is implicit in our thoughts behind the statement.

@Chaitanya So what was your reply so far?

Chaitanya

Ok. Thanks for your time and reply robert. It is helpful answer.

Regards,

C.

Chaitanya

Thank you Martin for your reply. It is also helpful answer.

Regards,

C.