I have searched the Interweb for any solutions to the problem of creating measures that do a Count Distinct on a source values within Transformer.
I am currently using Transformer 10.1, and I am using Report Queries as source data. Within my Fact query I have a field lets call "A", then I have another field call "B" ( this is a concatenation of a "A" and "B"). I then need to get the distinct count for these 2 fields grouped by the keys ( Date, Region, Customer)
The only measure I need to build for this cube has the following formula: CountDistinct("B") / CountDistinct("A")
Unfortunately due to the amount of rows being used within the query, I am unable to return back the distinct counts within the query alone, so I was thinking if I could do the heavy lifting in Transformer. So, I was going to bring back all the values, and set the query to not summarize & group, before Transformer reads it. But here is where I am stuck, is how to use Transformer to do this???
It seems that there still is not a Rollup option for Disctinct Count??
Any suggestions??
Thanks!
Distinct counts are difficult in Transformer. The best you can do is to create a new dimension and do a category count on that. Depending on the volume of data, this can become extremely slow.