In reference to my blog on Distinct Count Optimization at http://denster.spaces.live.com/blog/cns!125D53A08EC75357!1156.entry
, I would like to expand it slightly specifically for Analysis Services 2005. Based on customer visits and more extensive testing, I have found that following most of the advice in my original blog entry will help improve distinct counts.
But, for Analysis Services 2005, it will be better to partition where the distinct value (e.g. users) are placed in the same partition. This is different from Analysis Services 2000 where the partitioning strategy was to take that same distinct value and spread it across as many partitions as possible. For AS2k5, there was significant improvement when using this new partitioning strategy. Over the next couple of months, I’ll update this blog entry when I finally get a best practices whitepaper to provide more details on this.