Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Optimising Distinct Count

leave a comment »

Great blog entry from Denny Lee on optmising distinct count measures here:
I’d seen a ppt deck of a presentation he did at PASS a few years ago where he first set out these ideas, but it looks like he’s now updated them for AS2005.
On a similar topic, I’ve been working with a customer recently where we needed to optimise a calculated measure which did a count on a distinct list of customers (>1 million in the dimension) who met a certain criteria. The filter couldn’t be designed into the cube or dimension completely so it had to be done with a filter function, and similar to what Denny describes here I was seeing in Profiler that the data was being read of the disk pretty quickly but it was the filter/count that was taking the time because only one CPU was being utilised. Unfortunately I found out that there’s no way to design parallelism into the formula engine in the same way that Denny’s technique above designs parallelism into the work the storage engine has to do – apparently AS prevents two queries executing at the same time unless one of them is a Cancel, and despite experimenting with sprocs to do this I had no luck because none of the code in the formula engine is thread safe. I was able to pass the raw data into a sproc using SetToArray and do the work outside the formula engine but that performed even slower. Hohum…

Written by Chris Webb

November 19, 2006 at 11:49 am

Posted in On the internet

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

Join 4,003 other followers

%d bloggers like this: