Analysis Services : How to break distinct count measures into separate measure groups ?

Having blogged last week here about the advantages of the new storage file format found in the Denali’s CTP3 for those kind of measures when they are of the string data type, remember me that maybe this little feature may need a post. This is no rocket science, just a little tip that every one would solve after spending 5 minutes on it. I even think that most of you may already know about it….

Some times ago (who says years ?), I found myself stuck with this one, as it is not really straightforward into the SSAS BIDS cube designer how to achieve it. In fact, you have to perform the steps in the correct order or you ll find yourself stuck and need to redo the thing from the beginning…

According to the msdn, a SSAS fact table can only have one measure group associated except for the distinct count measures that can be separated from other measures and have their own measure group. This allow to manage partitioning and aggregations more accurately in a manner that suites better those kind of measures.

To create distinct count measure groups, you need to proceed in two simple steps :

1) First, start with a fresh fact table that has still no measure group associated. Then drag and drop the fields from the table that you want to become distinct count measures into the measure groups metadata pane. Be sure to change their aggregation type accordingly, as for numeric measures the default aggregation type is the “sum” function.

2) Then, drag into the pane all the other measures of the fact table. This will automatically create a new measure group for the same fact table.

If you start by creating all measures and then change some of them to be distinct count measures, no new measure group will be created. You’ll end up erasing all the things and redo all your parameterization, like format, type, aggregations … which is not really handy especially when you’re not the primary developer of the cube to do some performance tuning.

I Hope this will help some of you to avoid some waste of time …



  1. Kenny Ruth said

    There is a easier method than the one listed. Right click on the cube on the Cube Structure tab and select new measure. Then select DistinctCount as the usage, then your measure to count and it will automatically be created in a new Measure Group.

    • Raphael said

      thanks for your comment, there is indeed a way to do it simply by using the wizard.

RSS feed for comments on this post · TrackBack URI

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

%d bloggers like this: