Fact to Fact relationship ?

[beinownow]

Hello to all,

I've been reading posts here and there and found out the following:
- never join 2 fact tables together !
- you can join 2 fact tables only if absolutely necessary
- bypass joining 2 fact tables directly by creating new Conformed dimension

We have been given the relationship  info between the tables in a documentation that we received.

I must say I'm a bit confused.

This is what we have so far in our database Layer in FRAME WORK manager:

                                                  FACT TRANSACTION BATCH                       
                                                                        0..1                                             
                                                                            |                                                 
                                                                            |                                                 
                                                                         1..1                                                   
DIM BANK OTHER 1..1___0..n FACT TRANSACTION 0..n___1..1DIM TRANSACT DATE
        1..1                                                          0..n     
           |                                                               |       
           |                                                               |
           |                                                               | 
           |                                                               |
        0..n                                                            |                                                                                                               
DIM BANK 1..1__________________________|                                                 

Questions:
- Then when is it OK to join 2 fact tables together ??
- How in my case would you suggest bypassing joining 2 fact tables by creating Conformed dimension ??
- As we can see in my (simulation of) diaggram, my FACT TRANSACTION is not a true FACT as for it has 1..1 as CARDINALITY on its side when joined with the ohter fact table. Am I obliged to get rid of that join ?
- Also DIM BANK and DIM BANK OTHER are linked together. Is that OK ? Are we supposed to allow the join between 2 dimensions ?

Thanks to anyone willing to help

[bdbits]

In my opinion, you should never join fact tables. If you have modeled correctly, there is no need to do so.

There is not enough information below, especially what makes up the relationship between FACT TRANSACTION BATCH and FACT TRANSACTION. I will venture a guess and say that you are missing a dimension, call it DIM BATCH. This dimension would be a foreign key in FACT TRANSACTION. If FACT TRANSACTION BATCH actually has measures that are not aggregates of FACT TRANSACTION, then it could still be a fact table with a foreign key from DIM BATCH.

Your bank dims might be appropriate. You could probably present this as a hierarchy, if you are using a dimensional model (for example, cubes or a DMR).

[beinownow]

Hi bdbits and thank your for ha INC taken the time to respond,

When you say:"There is not enough information below, especially what makes up the relationship between FACT TRANSACTION "

==> Those 2 fact tables are a tually beeing linked by a query subject named TransactionBatch#



When you say: " if you are using a dimensional model (for example, cubes or a DMR)."

== Nope, relational db tables from OLTP (no data warehouse here unfortunately and they don't seem to want to invest more $$ for this and want us to model over the OLTP)

Thanks again for your reply!

[bdbits]

Based on what you have posted, I think my guess was pretty much what you want to do. Make a DIM BATCH table as a dimension to FACT TRANSACTION, and if there are measures for FACT TRANSACTION BATCH then keep it as a fact table but make DIM BATCH a dimension to it. That will be the conformed dimension you will want to use (and maybe others depending on data content).

You should use database views and/or Cognos modelling techniques to mimic star schemas as much as possible. I know people hit transactional databases directly, but in my experience like most BI reporting/analytic tools, Cognos works infinitely better with star schemas, even if they are "faked" with views/modelling.