One Fact table - or multiple?

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

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

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

About 50 %

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 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?

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.

Thanks to everyone!