The first thing you need to answer is whether you'll do data processing on select, on insert, or as a mix of both.
On select, you only insert raw data (like an access log). You can then pick one of two ways to generate your reports and transform your data. You can do them in real time - when a user requests the data. Or you can do it as a batch process and do the whole OLTP to OLAP offline.
On insert you calculate everything as each hit comes in and fill in your reporting structure directly.
Both the select with real-time report generation and the insert approach give users access to real time data. Select with an offline transformation script will provide cached data until the batch process is run again.
Imma guess that you're users want as-real-time as possible, with some data having to be more real-time than other. This is where you adopt some form of hybrid/mix.
As for actual implementation...First, can you afford to lose data? Now don't jump the gun and immediately say "no". A lot (but certainly not all) of analysis of this type of data works on averages and general trends. If you happen to lose a couple hundred rows in a couple hundred thousands, it doesn't really change the outcome that much. Anyways, it's important to know this because some storage engines support non-durable writes (writes to memory) which are stupid fast, but might result in lost data (or you can possibly write your own buffering logic in the app/web code to spin off another thread and write every 500 hits in a bulk-insert).
Technologies. MongoDB has a couple things going for it. First, writes are fast, and they can either be done to memory (stupid fast) or to disk (or to X replicas). Even writes to disk are pretty damn fast (to X replicas will largely depend on network latency). The other thing MongoDB has going for it is MapReduce...sadly it's single threaded, but whether you are doing real-time reporting on an offline OLTP->OLAP transformation, MapReduce is significantly more powerful than OrderBy.
In chapter 6 of the (free) Little MongoDB Book (http://openmymind.net/mongodb.pdf) I actually outlined the initial OLTP to OLAP process we were using for a very modest reporting tool.
Since then, we've switched to Redis and do it all on insert to provide real-time analytics. However, we are only tracking 3 very basic incrementing statistics. You can see the code here: https://github.com/mogade/mogade-server/blob/master/app/mode... (the hit method).
Of course, why don't you just use Google Analytics?
EDIT:
If your data processing needs are truly huge, you'll likely end up with something like Hadoop (which I haven't had the opportunity/pleasure to need, so I can only mention it in passing).
Comments
My $0.03
The first thing you need to answer is whether you'll do data processing on select, on insert, or as a mix of both.
On select, you only insert raw data (like an access log). You can then pick one of two ways to generate your reports and transform your data. You can do them in real time - when a user requests the data. Or you can do it as a batch process and do the whole OLTP to OLAP offline.
On insert you calculate everything as each hit comes in and fill in your reporting structure directly.
Both the select with real-time report generation and the insert approach give users access to real time data. Select with an offline transformation script will provide cached data until the batch process is run again.
Imma guess that you're users want as-real-time as possible, with some data having to be more real-time than other. This is where you adopt some form of hybrid/mix.
As for actual implementation...First, can you afford to lose data? Now don't jump the gun and immediately say "no". A lot (but certainly not all) of analysis of this type of data works on averages and general trends. If you happen to lose a couple hundred rows in a couple hundred thousands, it doesn't really change the outcome that much. Anyways, it's important to know this because some storage engines support non-durable writes (writes to memory) which are stupid fast, but might result in lost data (or you can possibly write your own buffering logic in the app/web code to spin off another thread and write every 500 hits in a bulk-insert).
Technologies. MongoDB has a couple things going for it. First, writes are fast, and they can either be done to memory (stupid fast) or to disk (or to X replicas). Even writes to disk are pretty damn fast (to X replicas will largely depend on network latency). The other thing MongoDB has going for it is MapReduce...sadly it's single threaded, but whether you are doing real-time reporting on an offline OLTP->OLAP transformation, MapReduce is significantly more powerful than OrderBy.
In chapter 6 of the (free) Little MongoDB Book (http://openmymind.net/mongodb.pdf) I actually outlined the initial OLTP to OLAP process we were using for a very modest reporting tool.
Since then, we've switched to Redis and do it all on insert to provide real-time analytics. However, we are only tracking 3 very basic incrementing statistics. You can see the code here: https://github.com/mogade/mogade-server/blob/master/app/mode... (the hit method).
Of course, why don't you just use Google Analytics?
EDIT:
If your data processing needs are truly huge, you'll likely end up with something like Hadoop (which I haven't had the opportunity/pleasure to need, so I can only mention it in passing).
Thanks - much to consider!
I can't use Google Analytics because the data needs to be tied to the user id of the logged in user.