Imagine you're writing some sort of Web Analytics system - you're recording raw page hits along with some extra things like tagging cookies etc and then producing stats such as

  • Which pages got most traffic over a time period
  • Which referers sent most traffic
  • Goals completed (goal being a view of a particular page)
  • And more advanced things like which referers sent the most number of vistors who later hit a goal.

The naieve way of approaching this would be to throw it in a relational database and run queries over it - but that won't scale.

You could pre-calculate everything (have a queue of incoming 'hits' and use to update report tables) - but what if you later change a goal - how could you efficiently re-calculate just the data that would be effected.

Obviously this has been done before ;) so any tips on where to start, methods & examples, architecture, technologies etc.

  • Should qualify that I didn't mean the RDMS won't scale as such (Don't want to get into a RDMS v NoSQL holy war!) - but you will likely have to aggregate/pre-calculate/denormalize things at some level. – Ryan Jul 11 '12 at 8:44

First, don't assume that a RDMS isn't going to scale. It might not be the right solution, but saying it won't scale doesn't make sense unless you've considered how your data is going to come into the system, how it's going to be queried and what you eventually want to see from those queries.

Recording raw page hits may or may not be a large dataset. If you do this in a simple naive way, recording a row for every single hit, it may not scale, but this isn't necessarily the smartest way to record these things. You are likely going to be working from server logs, which will then distill them into an aggregated form.

Path tracking is likely to be the largest dataset here since you'll need the breadcrumbs from each individual user, but the querying part is important here. To do this in a sophisticated way, you'll likely be using some application logic, not a raw query.

Unless you have a large number of users, a single RDMS should be able to handle these queries. The general idea is to keep both the aggregate data and the raw, fine grained level data in different tables. The aggregates provide fast queries with indexes, etc. and the fine grained data can be used to be build new metrics.

Some databases and some BI solutions provide automated ways to do this. Oracle has aggregate persistence for example, but in my work I've found myself writing batch jobs to build aggregates.

Longer term, you'll want to learn about modeling your data dimensionally rather than relationally. Dimensional models and star schemas are more extensible than a relational model you replicate from a production system and provide a better way to manage the granularity of the data and cached aggregates.

If you have really large datasets, then you'll need to start thinking about using distributed processing, map/reduce, etc. But you'll save yourself a lot of time if you can manage to use a traditional database in an efficient manner. Performing complex analytics (i.e., more than simple aggregates such as SUM or AVE) requires a lot of more thinking and expertise in the map/reduce framework than in SQL.

  • 1
    This is a pretty comprehensive answer- modelling dimensionally sooner rather than later, and using an analytic-specialised DBMS (like Vertica or Infobright if you have no software licensing budget, or the Oracle/MS/etc data warehouse if you do) will get you a long way for a large site. Things to consider are getting clickstream into the DB (sounds like a MQ here), and extracting sessions from clickstream - session tends to be the lowest level usually required unless you're doing path analysis, but its worth keeping raw clickstream if possible. Think on DB setup for fast insert vs read only. – Jamie Jul 11 '12 at 1:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.