Join Two fact tables that has different grain

I have two fact tables that share the same dimension (consumer) but have different grains ..

Fact A:

Consumer_key, Episode_key, Episode_Begin_date_key,assessment_key

Fact B:
Consumer_key, Episode_key, Service_begin_date_key, Sum_of_Cost



There is 1:M relationshiop between Fact A and Fact B...

IS it a bad idea to join two fact tables to get a result ?
DO I have to always use a common dimension table , in this case, DimConsumer to join to fact tables?

jung1975Asked:
Who is Participating?
 
imitchieCommented:
If some cases you have to join them together, then there's no other choice so there's no good/bad idea. However, you should reconsider alternate routes to arrive at your facts, possibly Fact C, if performance becomes an issue.
For joining, yes you do need a common dimension table.
0
 
nicolasdiogoCommented:
i suppose i will have to say depends, you could use the more detailed fact to support deeper business investigations.

if you want to allow users to check customers' assessments using FACT A  and then drill through into FACT B on a second report would make sense and keep performance good.

BUT you could also amalgamate both FACTs into a single table if performance becomes an issue.

AND you will always need shared dimensions to be able to bridge between fact tables to make them work properly.


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.