Multiple fact types in one fact table

View previous topic View next topic Go down

Multiple fact types in one fact table

Post  MaureenSy on Mon Feb 18, 2013 1:51 am

Hi,

I'd like to get guidance on the best way to set-up a datawarehouse, with 3 different fact types.
These fact types have 95% of their fields as the same, ie product, customer etc.

To build the reports, I also need to link 2 fact types at a time.

Question is: should i load these 3 diff fact types to one table or have each fact table have it's own table.
What are the pros and cons?

Am thinking more of having separate fact tables - as i will be joining data later on within the same table. ie. sales and inventory joins.

Thanks!
Maureen


MaureenSy

Posts : 2
Join date : 2013-02-18

View user profile

Back to top Go down

Re: Multiple fact types in one fact table

Post  ngalemmo on Mon Feb 18, 2013 3:37 am

Generally it is a really bad idea to try to put disparate information in the same fact table. Especially if you are talking about things like sales and inventory. Keep them separate.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Multiple fact types in one fact table

Post  MaureenSy on Mon Feb 18, 2013 5:09 am

Thanks for the response!
Just to make sure i can rationalize the decision, given that they will be joined to produce derived measures. (so business might say the data is not exactly unrelated)
1. if i extend the column to hold the additional measures for the same set of products - this will complicate loading process with respect to joins for column population
2. if i extend the rows, to have several fact types within the same table - this will complicate the report creation due to join within the same table and impact performance

Did i get the Cons correctly? are there other keys considerations?

MaureenSy

Posts : 2
Join date : 2013-02-18

View user profile

Back to top Go down

Re: Multiple fact types in one fact table

Post  ngalemmo on Mon Feb 18, 2013 3:07 pm

Yes, those are cons. I am sure there are a whole lot more. The thing is, there are no 'pros' for doing it.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Multiple fact types in one fact table

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