Thursday, October 2, 2008

Web Analytics using SQL Server and PerformancePoint

The default page for my live demonstrations is currently showing a PerformancePoint dashboard of the web statistics of the site. SQL Server Integration Services is regularly (aprox every 15 minutes) incrementally loading new web log records into SQL Server and incrementally processing the OLAP cube. The PerformancePoint dashboard is continuously available and will show the latest (0-15 mintes of real-time) web analytics information.

For example this is the scorecard showing key information for the last 3 days. This image shows the web data is up to and including the 12th hour of October 2nd. Note, that is Sydney Australia time, so if you are looking from Europe or America, it will appear to be in the future. This isn't the future, it is the current time in Sydney.

The key metrics shown include the number of web hits, average response time for .aspx resources, error rate, number of distinct client sessions, MB transferred and cpu utilisation on my two machines.

There are several other charts and tables on the dashboard. A couple of my favourites are the (bottom left) table of Cities by Hour for the last 3 days. This table shows the top city for each hour over the last 3 days. For example, on this day, New York generated the most activity to my site from 9AM to 12AM, with Bologna, Dublin, Sacramento, Toronto and others featuring earlier on this day.

Another one of my favourite charts on this dashboard is the Top Referrers Last 3 Months. This table shows an ordered list of the sites that refer the most traffic to my site.

Many of the charts and tables are interactive, so you can right click on numbers, heading, columns, lines etc to drill down, drill up, drill across, drill through, change chart type, pivot etc. Essentially, when the chart/table is interactive, you can perform ad hoc queries as you would in any thin client cube browser. Try it out. Every few minutes, you will also see that the data is incrementally changing.

The purpose behind this demonstration is not to show you web analytics of my site (although I find that useful) it is to show you how powerful SQL Server and PerformancePoint are in creating a dynamic and useful business intelligence solution. The only software employed on this site is Microsoft SQL Server, Microsoft PerformancePoint and Microsoft SharePoint.


Julian said...

Quite cool Richard.

What did you use to parse the log file?


Richard Lees said...

SQL Server Integration Services calls Logparser 2.2 (freely downloaded from