Data analysis using a multidimensional model and MDX
Almost all mature database applications reaches a point when it needs to start reporting back statistical and aggregated data. The data about past behavior is usually kept in a transaction history log table and you will be tempted to write some fancy SQL that selects the records in a specific timeslot and then GROUPBY type, or country, or whatever while COUNTing, SUMming and AVGing on some values like profit or whatever make sense to your company.
Well, there's a better way !
For the purpose of aggregating records, the most robust approach is to use a Multidimensional model (a.k.a OLAP).
It is usually quite simple to transform the data into a multidimensional datamart or to build it on top of the existing tables, once it is there it becomes simple to generate smart views of the data using MDX.
The best thing about it, is that it is a foundation for additional cool software such as Pivot tables, Flash charts, Dashboards and Reports.
Now some links:
Pivot table: http://en.wikipedia.org/wiki/Pivot_table
Open Source (R)OLAP Engine: http://mondrian.pentaho.org/
MDX Essentials Series: http://www.databasejournal.com/features/mssql/article.php/1495511/MDX-at-First-Glance-Introduction-to-SQL-Server-MDX-Essentials.htm