Count Distinct Options in Transformer

[mrcool]

Hi Guru's,

Could you please suggest few best options to do a count distinct in transformer.We are using Cognos 8.4
We have claim number in the fact table but the fact has lower granularity than Claim Number.
I want to create a measure Claim Count with distinct Claim Numbers.
I am considering an option of creating a dimension with Claim number as a column and perform category count on that field. Is that a better option?
Appreciate your quick response.

Cheers,
MC

[prikala]

I think category count is your only option, unfortunately (things get ugly when you have lots of categories).

[mrcool]

Thank you prikala..
But I have lot of categories..Should see how it performs.

Thanks,
MC

[ykud]

You can use external rollup measure and count it in the database prior to populating the cube. We usually did this for performance.