Two fact tables sharing the same dimensions

[amir2]

Hello

We have a FactDailyBalanceSnapshot table that has links to dimensions such as DimAccount, DimCustomer, DimRelationshipManager, DimDate, ...

We now need to introduce a new fact table called FactTermInstructions which will store information such as renewal instructions for an account that is about to mature say. This fact table also needs to be linked to the same dimensions as the FactDailyBalanceSnapshot.

Is it better to:

1. Link the new FactTermInstructions to the existing FactDailyBalanceSnapshot and use the latter to get the balance (on matruity) as well as dimension data (i.e. indirectly get to the dimensions via another fact) OR

2. Link the dimensions directly to the new fact table.

Please note the that for the balance on maturity, we need to traverse from FactTermInstructions to FactDailyBalanceSnapshot. Or, should we avoid this by putting the balance on the Term Instruction?

Thanks!

[ngalemmo]

NEVER #1, ALWAYS #2.

If you know the balance at maturity at the time you load the fact, then sure, add it to the table. It makes queries much simpler.

[amir2]

Thanks ngalemmo.

What about the dimension tables? Shall I link the new fact table to the dimension directly or go via the main fact table (which has existing link to all of the dimensions)?

I am guessing the answer is direct link (to make queries simpler), but I want to be sure as I am new to this stuff.

[ngalemmo]

You listed 2 options in your original post. Option #2 was to link directly from the fact. You always do it that way... that is, by definition, the basic form for a star schema. You never get to dimensions by linking through another fact.

[amir2]

Ah, I just spotted your Never #1, always Number #2.

Thanks for the clarification - it is good to know I am not barking up the wrong tree.

[vijayrc]

NEVER #1, ALWAYS #2.

If you know the balance at maturity at the time you load the fact, then sure, add it to the table. It makes queries much simpler.

Question on the same topic:
We do have a simple star schema with a FACT table having detailed transactional data DTL_FACT1.
We would also want to have two other FACT table summarized at Account Level SUMM_ACCT and Center level SUMM_CENT.
So ideally this is being done for performance as well as for other business reason to generate other files out of it etc.
90% of users go against SUMM_CENT which has some 7million/week; In case of analysis is when users would go to SUMM_ACCT which has some 25million rows/week while DTL_FACT1 would have 220million rows/week.
Question is: Can the three FACTs be interchangeable used as they share teh same DIM?

[ngalemmo]

Yes. Some BI tools support 'aggregate awareness'. If you have three facts at different levels of aggregation, the tool will chose the most appropriate fact based on the dimensions involved in the query.

[vijayrc]

Yes. Some BI tools support 'aggregate awareness'. If you have three facts at different levels of aggregation, the tool will chose the most appropriate fact based on the dimensions involved in the query.

Thx. Are you aware by any chance if OBIEE supports 'aggregate awareness'?

[ngalemmo]

I haven't looked at OBIEE (aka Siebel Analytics) in a long time. I've worked with it from the back end (ETL, Data Model). What they do with the front-end these days, I don't know.

[Sponsored content]