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

Differing grain dimension tables with the same transactional fact

2 posters

Go down

Differing grain dimension tables with the same transactional fact Empty Differing grain dimension tables with the same transactional fact

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

Back to top Go down

Differing grain dimension tables with the same transactional fact Empty Re: Differing grain dimension tables with the same transactional fact

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

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

http://aginity.com

Back to top Go down

Differing grain dimension tables with the same transactional fact Empty Re: Differing grain dimension tables with the same transactional fact

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

Back to top Go down

Differing grain dimension tables with the same transactional fact Empty Re: Differing grain dimension tables with the same transactional fact

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

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

http://aginity.com

Back to top Go down

Differing grain dimension tables with the same transactional fact Empty Re: Differing grain dimension tables with the same transactional fact

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

Back to top Go down

Differing grain dimension tables with the same transactional fact Empty Re: Differing grain dimension tables with the same transactional fact

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

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

http://aginity.com

Back to top Go down

Differing grain dimension tables with the same transactional fact Empty Re: Differing grain dimension tables with the same transactional fact

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