Dimensions that don't apply to every fact record

[hurrican3dev]

Good morning,

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

For example call it [FactEvent]. Let's say it has 10 related Dimensions.

The fact table contains two types of events : { EventType1, EventType2 }

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

The other 4/10 Dimensions only relate to EventType1. I would use a default dimension record key for those dimensions on the Fact table where the
row represented an EventType2.

If we rule out the possibility of using a seperate fact table for each event type, is this approach valid/acceptable?

Thanks
Hurricane

[BoxesAndLines]

Not really. One of the guiding principles is ease of use. Your design has dimensions that are dependent on the type of metric. This requires "tribal knowledge" in order to understand how to correctly query your star schema.

[Jeff Smith]

I think more info is needed. I suspect it's a situation that each event type has 5 common dimensions and has several dimensions unique to the event type. Take transactions for banking. You got teller transactions, ATM transaction, Online transactions, telephone transactions, etc. Each transaction type has many of the same dimensions (account, customer, product) as well as unique dimensions.

You could create 1 fact table for each event type with only the dimensions that are needed and then create a union view with the common fields. Or you could combine the dimensions that are unique to the event types - a bit of a junk dimension. If fields A, B, C apply to event type 1 and D, E and F apply to event type 2, create a dimension with fields A thru F.

[hurrican3dev]

Not really. One of the guiding principles is ease of use. Your design has dimensions that are dependent on the type of metric. This requires "tribal knowledge" in order to understand how to correctly query your star schema.

I agree 100%.

This is one of those situations where I think I am going to have to require some tribal knowledge, as most of the common dimensions are the "important" ones.

The dimensions that don't apply to both entity types are a little less likely to be queried.

[hurrican3dev]

I think more info is needed. I suspect it's a situation that each event type has 5 common dimensions and has several dimensions unique to the event type. Take transactions for banking. You got teller transactions, ATM transaction, Online transactions, telephone transactions, etc. Each transaction type has many of the same dimensions (account, customer, product) as well as unique dimensions.

You could create 1 fact table for each event type with only the dimensions that are needed and then create a union view with the common fields. Or you could combine the dimensions that are unique to the event types - a bit of a junk dimension. If fields A, B, C apply to event type 1 and D, E and F apply to event type 2, create a dimension with fields A thru F.

great feedback. Your analogy is very accurate to the situation I am working with.

The idea of having a "all entity, only common dimensions" table is something I'll have to noodle.

I'm not ruling out the possibilty of having an all entity fact table, along with a table for each specific entity type.

[hurrican3dev]

Or you could combine the dimensions that are unique to the event types - a bit of a junk dimension. If fields A, B, C apply to event type 1 and D, E and F apply to event type 2, create a dimension with fields A thru F.

Could you elaborate a little more on this? Did you mean to say that the types specific to each event could be pooled into junk dimensions? Doesn't the situation you described above then all for two Junk dimensions, one carrying ABC and the other carrying DEF?

[Sponsored content]