Optimizing your distinct count calculation in Analysis Services (By Denny Lee)
A distinct count (e.g. unique user or visitor counts) provides valuable information but it is not an easy problem to resolve. An example of this problem is your grocery "market basket" scenario where a single visitor purchased various dairy, meat, and vegetable items. While there may be eight (for example) items purchased, these items were all purchased by one person. Understanding that one person purchased many items can be very revealing in the context of business intelligence. Using a web analytics example, if you have a high activity / visitor web site that has a lot of traffic in comparison to visitors (e.g. a financial market site where a set of number of users are viewing many web pages to do market research), you have a very "sticky" web site with relatively loyal users. On the other hand, if you have a low activity / visitor web site that has a lot of users but not so much traffic in comparison (e.g. an auto site where many visitors view many different auto web site to find the best deal in cars), you have a less “sticky” web site with relatively un-loyal visitors. Both business scenarios reveal different insight about the types of visitors to your web site business allowing you to make some actionable business items.
Tags: mdx, performance, distinct count