can we join one fact table to another fact table

[inu]

Hi
1. can we join two fact tables to each other, will there be any trouble in generating reports
2. can we join two dimension tables to each other, will there be any trouble in generating reports

regards
inam

[RobsWalker68]

Hi Inam,

In a star schema design (facts & Dimensions) you should never join a fact to fact but instead use a conformed (shared) dimension to link the facts.  This allows the Cognos query engine to perform stitch queries so you don't lose data.

If you can join two dimensions together on a 1:1 basis then you should consider making them one dimensions instead.

Kind Regards

Rob

[blom0344]

2 joined dimension tables would make sense when you are dealing with a snowflake schema. But, then it would make more sense to merge them into a new model query subject..

[navissar]

The answer for both is "Yes, you can", but then also "No, you shouldn't".
Joining fact tables is a big no-no for four main reasons:
1. Fact tables tend to have several keys (FK), and each join scenario will require the use of  different keys. On one case you might want to join them based on date key, and on another you would want to join them based on product and date...
2. Fact tables tend not to have data that matches exactly, which means that if you don't want to have data missing, you'll have to create a full outer join. Which brings me to
3. Fact tables are big. Typically they're the largest ones in your model. Outer joining them will be a time consuming task.
4. Fact tables can sometimes be in different levels of granularity (For instance, a sales target fact might be monthly, and a sales fact would be daily). This will cause multiplication of data. You can't use a determinant on a fact table because it will aggregate your measure as MIN(), so there's no real solution.
What you want to do is to model each fact with its dimensions (Star scheme style). If you select values from two facts that have a common dimension, Cognos will outer join the result of (f1 join dim) and (f2 join dim) automatically, so that would be the way to go.

Creating snowflakey structures (fact joined to dim, dim joined to another dim) is possible, but might affect performance. I'd rather toss the joined data in a table or materialized/indexed view as one dim, or, in lack of another choice, model it as suggested by blom0344.

Here's a tip though: If you must join the facts together and there's no common dimension (This can happen if the join field is a field in the fact that doesn't correspond with a dimension) - add to each fact table a field that describes the table, and union the facts.  This will create a long result which will have all data, and could easily be filetered by table.