Sunday, December 18, 2005

My first foray into the music biz!

A good mate of mine got chatting over the weekend about a new business idea for the music industry. What started as a casual discussion suddenly turned out some top ideas. We are now thinking of making some of these ideas happen. Won't say too much yet but it looks promising :)

More soon!

Wednesday, December 14, 2005

NetFox nears next release...

Well my baby - that which I have slaved over for three years is about to realise its true potential. We have only a few weeks left on the development schedule for NetFox 3.8. Yay! Hopefully I still have some hair left after all of this.

The data cube

So I looked at one of my customers Netfox web logs the other day and there were over 22 million entries! This is one of my largest sites but there will be bigger. Now the problem is some of the queries that I run on that data are *c0mpl3x*. I started thinking about how to improve speed by "aggregating" some common reports. Ok, so say you've got hits and bytes grouped by website in a report for a certain date range. This takes a lot to compute so you pregenerate this report and add it to a table with an id column. The id column references a periods table which contains start and end dates for different report intervals. Ok, great! I've just reduced my query time by perhaps 100 times but what if I want to limit the results only to a particular user or group?

Well, I realised, when thinking about this that I was essentially building a data cube. To be able to filter a prebuilt report each "aggregate table" must have one or more conditions tables. For example for users each row in the aggregate table has a condition id. Then in a table called say condition_users, mapped against that condition id is a list of all the user ids which match the condition. Then when reporting I just join the aggregate to the condition table and filter by id - simple huh?! :)

I need a drink.