Differing grain dimension tables with the same transactional fact
2 posters
Page 1 of 1
Differing grain dimension tables with the same transactional fact
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
Re: Differing grain dimension tables with the same transactional fact
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.
Re: Differing grain dimension tables with the same transactional fact
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?
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
Re: Differing grain dimension tables with the same transactional fact
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.
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.
Re: Differing grain dimension tables with the same transactional fact
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.
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
Re: Differing grain dimension tables with the same transactional fact
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.
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.
Similar topics
» conformed dimension for two fact tables which are at different grain
» calendar grain on both dimension and fact tables
» Using a dimension in multiple fact tables with different grain and support SCD
» Linking two Fact tables with different grain through a hierarchy dimension
» Geography Dimension with diffirent grain in fact tables
» calendar grain on both dimension and fact tables
» Using a dimension in multiple fact tables with different grain and support SCD
» Linking two Fact tables with different grain through a hierarchy dimension
» Geography Dimension with diffirent grain in fact tables
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum