One Fact table - or multiple?

View previous topic View next topic Go down

One Fact table - or multiple?

Post  hurrican3dev on Tue Jan 15, 2013 10:16 am

Good morning,

I am modeling a fact table which contains observations related to slight variations of an entity type.

For example call it [FactEvent].

The fact table contains several types of events : { EventType1, EventType2... EventType 8 }

Many of the attributes for EventType1 and EventType2... EventType8 overlap, meaning that 6/10 of the dimensions on the fact table related to all event types.

Then there are 3-4 dimensions or dates that only apply to the individual event types.

I'd like to combine all event types into one fact table, but is this approach valid/acceptable? What is the best reasonable recommendation
in terms of putting all of this data in one fact table if not all of the dimensions apply to every fact record? (event type).

This is a really important question for me to answer.

Thanks
Hurricane

hurrican3dev

Posts : 17
Join date : 2012-08-26

View user profile

Back to top Go down

Re: One Fact table - or multiple?

Post  Jeff Smith on Tue Jan 15, 2013 10:27 am

What percent of 3-4 dimension keys would have the default value in the single dimension?

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: One Fact table - or multiple?

Post  hurrican3dev on Tue Jan 15, 2013 2:06 pm

Jeff Smith wrote:What percent of 3-4 dimension keys would have the default value in the single dimension?


About 50 %

hurrican3dev

Posts : 17
Join date : 2012-08-26

View user profile

Back to top Go down

Re: One Fact table - or multiple?

Post  Jeff Smith on Tue Jan 15, 2013 2:27 pm

I would create 2 different fact tables. If the data needs to be queried as one table, I would create a union view of the 2 fact tables with the common columns.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: One Fact table - or multiple?

Post  hurrican3dev on Tue Jan 15, 2013 6:23 pm

Jeff Smith wrote:I would create 2 different fact tables. If the data needs to be queried as one table, I would create a union view of the 2 fact tables with the common columns.

Thanks

How do the Fact (View) and Dimension (Tables) FK relationships hold up?

I know that the constraints are at the table (behind the view). Does the view (in an ERD) show the same relationships to each dimension?

hurrican3dev

Posts : 17
Join date : 2012-08-26

View user profile

Back to top Go down

Re: One Fact table - or multiple?

Post  ngalemmo on Tue Jan 15, 2013 7:17 pm

I would probably create multiple facts as well.

As far as the view goes, constraints are enforced during update or insert, not in queries. If all the view is doing is a UNION ALL of selected columns (including dimension FKs) from different fact tables, the view itself would perform no different than a hand-coded query doing explicit joins within each SELECT.

As far as drawing it goes, your success would depend on the tool. If you can't do it, a work around would be to create a logical-only entity that contains the columns in the view, then draw FK relationships to the surrounding dimensions. It's not really what is happening, but it does allow you to draw a reasonable picture of what the view represents.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: One Fact table - or multiple?

Post  ozisamur on Tue Oct 28, 2014 5:45 pm

Thanks to everyone!

ozisamur

Posts : 30
Join date : 2014-10-27

View user profile

Back to top Go down

Re: One Fact table - or multiple?

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