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 …