Fact with non-additive measures

[SnowShine429]

Hi Everyone,

I have a table that stores our customers information such as Number of Employees, Primary Officer Name etc. This table has a start date and end date which means, customer ABC may have 100 employees from 1/1/2011 to 12/31/2011 and then 120 employees from 1/1/2012 to 12/31/2012. Since the facts in this table aren't additive, how should I handle this in the Data Warehouse? Also, how should I join it with my date table as it has a date range. Please advise and thanks a bunch in advance.

[ngalemmo]

Its a snapshot. Its common.

The 'date range' is misleading, or it should be. Normally stuff like this is as of a particular date. It is unreasonable to expect that over a period of a year a company had X employees, no more, no less. You need to choose one of the dates to represent the value. Normally this is the end date.

As far as the measures go, they are semi-additive. It would make no sense to sum the values across time (although you could do averages across time), but it could make sense for other sums within the same time period.