Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

One Fact table - or multiple?

4 posters

Go down

One Fact table - or multiple? Empty One Fact table - or multiple?

Post  hurrican3dev 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

Back to top Go down

One Fact table - or multiple? Empty Re: One Fact table - or multiple?

Post  Jeff Smith 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

Back to top Go down

One Fact table - or multiple? Empty Re: One Fact table - or multiple?

Post  hurrican3dev 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

Back to top Go down

One Fact table - or multiple? Empty Re: One Fact table - or multiple?

Post  Jeff Smith 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

Back to top Go down

One Fact table - or multiple? Empty Re: One Fact table - or multiple?

Post  hurrican3dev 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

Back to top Go down

One Fact table - or multiple? Empty Re: One Fact table - or multiple?

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

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

http://aginity.com

Back to top Go down

One Fact table - or multiple? Empty Re: One Fact table - or multiple?

Post  ozisamur Tue Oct 28, 2014 5:45 pm

Thanks to everyone!

ozisamur

Posts : 30
Join date : 2014-10-27

Back to top Go down

One Fact table - or multiple? Empty Re: One Fact table - or multiple?

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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