How to model number of subscribers for a certain point in time

[Bergtroll]

I have modeling problem I do not know how to handle it yet. We have a central repository listing the reported number of subscribers for a information service at a certain date. This data is complete in respect to, that it is available for every date since beginning of the service. It is our reference data and I am going to model it as a change in number in reference to the day before. For example, given that the service started at 01.01.2011:

date	number of subscribers
01.01.2011	100
02.01.2011	105
03.01.2011	103
04.01.2011	111
...	...

the fact table will be

date_key	change
20110101	100
20110102	5
20110103	-2
20110104	8
...	...

Now we have some reports, sampling the total number of subscribers only at some dates representing the state for an interval (e.g. data gathered at 03.01.2011 should be representative for January 2011). They are provided manually and are used to check if the "real" number of subscribers at this point in time is the same than that in our reference data.

for 03.01.2011 number of subscribers at our place was: 105

Unfortunately the manual delivery of this reports is not to reliable and it maybe, that older reports arrive after newer ones. I am trying to model a data structure, capable to gather these reports, no matter, when they arrive and make them comparable to the reference data. Do you have any idea how to model such a constellation?

kind regards,

Bergtroll

[ngalemmo]

I wouldn't structure the fact table that way. The problem with it is it requires summing all rows for any query. Keeping it in its original form allows for pulling as single row to get a count as well as calculating averages over periods of time. The best approach is to store both a count and a net change amount. This would support a variety of different analytics. It would also make it a lot easier to compare numbers from different reports.

Assuming the reference data is static, I do not understand the rest of your question. What is the problem comparing other analysis against the reference data?

[Bergtroll]

thanks a lot for your answer, it already helps me out. The second part of the question misled thinking, because I was not sure about the modeling of the reference data. I am going to use your approach :-)

[Sponsored content]