Distinct Count in a new Measure Group

Here is the situation:

I have a cube that has transaction level data and there is a control number that goes with this data, but it isn't one for one. If I have 60 million rows in the fact table, I have 50 million control numbers in a control number dimension.

I would like to be able to slice the data by date (for instance) and then do a distinct count on the control number dimension. I can do a distinct count w.o having to do a join of course because I can count the distinct instances of the key to the control number dimension.

One more note - the control number dimension is not a degenerate dimension...it is not one to one on the fact table.

I would like to use the DISTINCT COUNT measure in SSAS, but it makes me create a new measure group to do so. All of my additive measures are in one group, the distinct count is forced into another group. This forces double the processing time.

Does anyone know why this behavour happens and can someone explain why a DISTINCT COUNT measure has to go in a seperate measure group?

Thanks,

Mark

Mark,

Do you already have a DISTINCT COUNT measure in that measure group? Analysis Services will only allow you to have one DISTINCT COUNT per measure group.

|||Maybe this MDX will overcome the limitation of one unqiue count.

Distinct(Filter([Customer].[Customer Name].Members,Not IsEmpty([Measures].[Sales Amount]))).Count

Timmy
|||

Actually, I don't have one in the first measure group. The first measure group is just regular measures, but if I create a new measure that is a distinct count measure, it automatically puts it in another group.

I have now heard from other sources that this is an exptected behavior. So no biggie, just needed to know.

ps - sorry it took so long to respond.

Thanks,

Mark

http://spaces.msn.com/mgarnerbi

|||I noticed this too, does anyone know the underlying reason why this is? Is this going to be fixed in a future version?