Differing grain dimension tables with the same transactional fact

View previous topic View next topic Go down

Differing grain dimension tables with the same transactional fact

Post  cpeterson on Sat Nov 26, 2011 1:38 pm

Is it appropriate to relate different grain dimension tables (aggregate dimensions) with the same transactional fact table?

cpeterson

Posts : 9
Join date : 2011-11-26

View user profile

Back to top Go down

Re: Differing grain dimension tables with the same transactional fact

Post  ngalemmo on Sat Nov 26, 2011 6:37 pm

Do you mean, for example, you have a 'division' dimension and a 'region' dimension? Yes, you could place them as dimensions, but if you are trying to represent a hierarchy, you are better off implementing one dimension and a bridge. You relate the fact to the lowest level entry (of the hierarchy) appropriate for the fact.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Differing grain dimension tables with the same transactional fact

Post  cpeterson on Sat Nov 26, 2011 7:22 pm

Thanks for the reply!

Let's say for simplicities sake we have three tables for an auto insurer (One fact and two dimensions)

1. Fact Table (Policy transactions) contains
a) risk/auto dim fk
b) policy dim fk
c) premium

2. dim table automobile (lower grain) contains
a) risk/auto pk
b) vin # (unique to auto/risk)
c) make
d) model

3. dim table automobile (higher grain aggregate) contains
a) surrogate key
b) make
c) model

I was wondering if the higher level dim table for auto make/model would be appropriate when looking at premium for those attributes. Doing it using the lower grain dim table could require more overhead. Is this an appropriate solution?

cpeterson

Posts : 9
Join date : 2011-11-26

View user profile

Back to top Go down

Re: Differing grain dimension tables with the same transactional fact

Post  ngalemmo on Sat Nov 26, 2011 11:26 pm

Make VIN a degenerate dimension and eliminate dimension #2, unless there are other attributes unique to VIN in the dimension. In which case, the two tables are fine.

Anyway, in terms of terminology, dimensions are not described in terms of 'grain' or 'aggregate'. They are simply dimensions. It is not unusual to store attributes in two different dimensions (the latter being a junk or 'mini' dimension), as you are in the case, when you have one really large dimension (VIN) that contains a few important attributes that are commonly queried independent of the VIN.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Differing grain dimension tables with the same transactional fact

Post  cpeterson on Sat Nov 26, 2011 11:34 pm

I think that helps explain it. I was oversimplifying the issue a bit. I wanted to illustrate that a dimension with vin,make,model would have more rows than a dimension table with just make and model. Both tables would contain more attributes that the just the ones that I listed.

I have heard the term "aggregate dimension". I thought it was a term used to describe a dimension with a strict subset of attributes from another dimension. Is that correct?

Regards.

cpeterson

Posts : 9
Join date : 2011-11-26

View user profile

Back to top Go down

Re: Differing grain dimension tables with the same transactional fact

Post  ngalemmo on Sun Nov 27, 2011 11:46 pm

There are a lot of terms for basically the same thing, a dimension whose natural key is the attributes themselves.

The only thing to be concerned about is you now have two different occurances of some collection of attributes. This can be a problem from a BI interface standpoint. Some tools handle it better/different than others. Often the end user needs to know what the differences are when choosing one version of the attributes over the other. So, make plans for training.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Differing grain dimension tables with the same transactional fact

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