Is it necessary to have define granularity for a factless fact table?
3 posters
Page 1 of 1
Is it necessary to have define granularity for a factless fact table?
We are storing the client details which holds dates. We should be able to find the number of clients active at a given point in time.
I have 2 types of date events.
Contract dates gets effective every year(many dates) which fall under Effective dates(one date)
1) Effect dates: Effective_date - End_date
Example: 2007 - 2010
2) Contract dates: Contract_Effective_date - Contract_End_date.
Example:
2007 - 2008
2008 - 2009
2009 - 2010
Question 1:
One way to do this is,
Maintaining a dimension table for each set of dates and have a factless fact table joining these 2 dimensions and holding just those dimension ids. In this scenario, each of the dimension table has different grain size and the fact table will have a row only when any of the row is populated in any of these dimension tables which results in having rows of different grain size.
As we do not have any numeric values involved for calculation, Is it OK to have rows of different grains in fact? or Not?
Question 2:
Instead of having two dimension tables, will it be better to have only 1 dimensional table to hold both of those date values?
Please feel free to ask more details
I have 2 types of date events.
Contract dates gets effective every year(many dates) which fall under Effective dates(one date)
1) Effect dates: Effective_date - End_date
Example: 2007 - 2010
2) Contract dates: Contract_Effective_date - Contract_End_date.
Example:
2007 - 2008
2008 - 2009
2009 - 2010
Question 1:
One way to do this is,
Maintaining a dimension table for each set of dates and have a factless fact table joining these 2 dimensions and holding just those dimension ids. In this scenario, each of the dimension table has different grain size and the fact table will have a row only when any of the row is populated in any of these dimension tables which results in having rows of different grain size.
As we do not have any numeric values involved for calculation, Is it OK to have rows of different grains in fact? or Not?
Question 2:
Instead of having two dimension tables, will it be better to have only 1 dimensional table to hold both of those date values?
Please feel free to ask more details
omm- Posts : 11
Join date : 2011-07-29
Re: Is it necessary to have define granularity for a factless fact table?
Could you clarify what is the relationship between contract and customer, is it that a contract can have 1 or more customers or does a contract only ever have 1 customer?
Cheers
Cheers
rob.hawken- Posts : 13
Join date : 2010-09-19
Re: Is it necessary to have define granularity for a factless fact table?
It's the other way around.
A client(Customer) has multiple contract dates.
In other words, a client will have a new contract every year. When that expires at the end of the year, a new contract will begin.
A client(Customer) has multiple contract dates.
In other words, a client will have a new contract every year. When that expires at the end of the year, a new contract will begin.
omm- Posts : 11
Join date : 2011-07-29
Re: Is it necessary to have define granularity for a factless fact table?
I don't get your granularity question... as far as the date thing goes, you have one date dimension and a FK on the fact for each date you need to represent. You don't create a dimension with two dates.
If all you need is the date without any attributes or any need to conform to other dates in the warehouse, you can just store the date as a degenerate dimension value.
If all you need is the date without any attributes or any need to conform to other dates in the warehouse, you can just store the date as a degenerate dimension value.
Similar topics
» INTERVAL TIME SUM COLUMN
» Granularity of Fact table
» Granularity - One Fact Table or Two
» Defining the granularity for a Fact Table
» Fact Table or Factless Table: Please Suggest
» Granularity of Fact table
» Granularity - One Fact Table or Two
» Defining the granularity for a Fact Table
» Fact Table or Factless Table: Please Suggest
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum