Is it necessary to have define granularity for a factless fact table?

View previous topic View next topic Go down

Is it necessary to have define granularity for a factless fact table?

Post  omm on Thu Sep 29, 2011 3:25 pm

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

omm

Posts : 11
Join date : 2011-07-29

View user profile

Back to top Go down

Re: Is it necessary to have define granularity for a factless fact table?

Post  rob.hawken on Thu Sep 29, 2011 6:12 pm

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

rob.hawken

Posts : 13
Join date : 2010-09-19

View user profile

Back to top Go down

Re: Is it necessary to have define granularity for a factless fact table?

Post  omm on Fri Sep 30, 2011 9:28 am

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.

omm

Posts : 11
Join date : 2011-07-29

View user profile

Back to top Go down

Re: Is it necessary to have define granularity for a factless fact table?

Post  ngalemmo on Fri Sep 30, 2011 3:52 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Is it necessary to have define granularity for a factless fact table?

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