Two fact tables sharing the same dimensions

View previous topic View next topic Go down

Two fact tables sharing the same dimensions

Post  amir2 on Wed Nov 09, 2011 9:54 am

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!

amir2

Posts : 29
Join date : 2010-07-29

View user profile

Back to top Go down

Re: Two fact tables sharing the same dimensions

Post  ngalemmo on Wed Nov 09, 2011 11:08 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Two fact tables sharing the same dimensions

Post  amir2 on Wed Nov 09, 2011 11:15 am

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.

amir2

Posts : 29
Join date : 2010-07-29

View user profile

Back to top Go down

Re: Two fact tables sharing the same dimensions

Post  ngalemmo on Wed Nov 09, 2011 11:33 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Two fact tables sharing the same dimensions

Post  amir2 on Wed Nov 09, 2011 11:44 am

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.

amir2

Posts : 29
Join date : 2010-07-29

View user profile

Back to top Go down

Re: Two fact tables sharing the same dimensions

Post  vijayrc on Thu Dec 08, 2011 8:57 pm

ngalemmo wrote: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?

vijayrc

Posts : 3
Join date : 2011-10-17

View user profile

Back to top Go down

Re: Two fact tables sharing the same dimensions

Post  ngalemmo on Fri Dec 09, 2011 3:34 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Two fact tables sharing the same dimensions

Post  vijayrc on Sat Dec 10, 2011 5:57 pm

ngalemmo wrote: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'?

vijayrc

Posts : 3
Join date : 2011-10-17

View user profile

Back to top Go down

Re: Two fact tables sharing the same dimensions

Post  ngalemmo on Mon Dec 12, 2011 11:09 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Two fact tables sharing the same dimensions

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum