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

View previous topic View next topic Go down

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

Post  Bergtroll on Wed Aug 24, 2011 8:32 am

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

Bergtroll

Posts : 15
Join date : 2011-02-02

View user profile

Back to top Go down

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

Post  ngalemmo on Wed Aug 24, 2011 11:59 am

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

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

View user profile http://aginity.com

Back to top Go down

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

Post  Bergtroll on Thu Sep 01, 2011 5:13 am

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 :-)

Bergtroll

Posts : 15
Join date : 2011-02-02

View user profile

Back to top Go down

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

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