Wednesday, December 3, 2008

Customer Retention and other complex analysis made easy with reports over OLAP

Analysis Services and Reporting Services make it easy to create complex analysis embedded in easy to consume reports. You can embed complex queries inside a report that everyone can use. For example, see my "Customer Retention" live demonstration on It shows the retention figures for my web site over the last rolling 12 months. The algorithm for calculating retention in this demonstration is as follows
Number of Customers who were active in the preceding 2 months who have also been active in the current month
/Number of Customers who were active in the preceding 2 months

This demonstration is live and dynamic, so as you click on the report a query executes to determine the answer at the current point in time. The data is continually changing. Notice that you can also click on a particular retention figure to drill through to the customers that were active in the preceding two months but have not been active in the current month. This is just one example of a "complex" query made easy within a Reporting Services report. Imagine how difficult it would be to get the same answer by simply using the slice and dice functionality of a cube browser (albeit a powerful one) such as Excel. Combining dynamic MDX with SQL Server Reports is a great way of providing analysis to end users in a very easy to consume format.


No comments: