Dimensions that don't apply to every fact record

View previous topic View next topic Go down

Dimensions that don't apply to every fact record

Post  hurrican3dev on Wed Nov 28, 2012 10:31 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]. 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



hurrican3dev

Posts : 17
Join date : 2012-08-26

View user profile

Back to top Go down

Re: Dimensions that don't apply to every fact record

Post  BoxesAndLines on Wed Nov 28, 2012 2:44 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Dimensions that don't apply to every fact record

Post  Jeff Smith on Wed Nov 28, 2012 6:42 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Dimensions that don't apply to every fact record

Post  hurrican3dev on Fri Nov 30, 2012 6:02 pm

BoxesAndLines wrote: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

Posts : 17
Join date : 2012-08-26

View user profile

Back to top Go down

Re: Dimensions that don't apply to every fact record

Post  hurrican3dev on Fri Nov 30, 2012 6:04 pm

Jeff Smith wrote: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

Posts : 17
Join date : 2012-08-26

View user profile

Back to top Go down

Re: Dimensions that don't apply to every fact record

Post  hurrican3dev on Sun Dec 02, 2012 12:37 pm

Jeff Smith wrote: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?

hurrican3dev

Posts : 17
Join date : 2012-08-26

View user profile

Back to top Go down

Re: Dimensions that don't apply to every fact record

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