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

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

3 posters

Go down

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

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

Back to top Go down

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

Post  rob.hawken 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

Back to top Go down

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

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

Back to top Go down

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

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

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

http://aginity.com

Back to top Go down

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

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