How should one design an analytics system for an online multiplayer game?

Question

For my simple MMO, the MySQL database stores things like user experience, user level, user coins, health points, etc.

We wanna have an analytics system that can show me trends through daily averages, medians, totals of different stats and actions of the game's user base.

Some answers that need to be answered by this analytics system:

• Average health points over time

• How many users are what level over time

• How many quests were started by users over time vs how many were completed

• Cohort analysis on certain groups of users

I was looking into BI systems and thought maybe building an OLAP database with a star schema might be appropriate. But I'm not sure what are good dimensions to use for this and how to deal with changes to the transactional database over time. And it seems like a lot of work to build out a ETL and reporting system as well.

Anything else that's simpler and yet still robust? Are key-value datastores a good option? What are some commonly used systems for doing this type of analytics?

Answer 1

I wouldn't think key/value stores are a good solution for this - they're not very good at calculating over many records.

In my experience, there are 3 common routes:

• run your reporting queries directly against the production database. This is the easiest option, but can affect performance - so, for a game, probably not that useful.
• duplicate your production database - e.g. through replication - and run your reporting queries against the copy. This has minimal impact on the production database (replication has a slight overhead), is relatively easy to set up, and uses your knowledge of the existing schema. It may not scale to huge amounts of data, though; to mitigate this, the first step is to pre-calculate commonly needed data. For instance, you might run a nightly batch to calculate daily statistics, a weekly batch for weekly stats etc.
  • create a proper data warehouse with a schema customized for OLAP. This supports huge data sets, and allows you to use high-end reporting tools allowing non-technical folk to create very sophisticated queries (Cognos and the like).

I wouldn't recommend jumping straight to the data warehouse option - it's a whole new world, designing star schemas is a specialist skill, and you will get far better results by implementing your reports using "traditional" SQL queries, and then moving to the data warehouse when you really, really need to.

Answer 2

I would go the OLAP way (maybe because I'm a bit biased as working for icCube) as this is a solid foundation. You're sure to be able to evolve the system in the future.

You do not need to be a specialist to setup a model with a few dimensions to start with. You could create some dedicated SELECT statements (or VIEWS) to create the cubes and once loaded into icCube, your prod DB won't be used anymore (this is an in-memory OLAP server). Later you could decide to create a dedicated DB if required. I do not see requirements for an ETL in your case.

For the reporting side, we're providing a Google Visualization DataSource for very easy access from Javascript to create dedicated dashboards that does exactly what you want the way you want. Or use existing tools connecting using XMLA (Excel, ...).