If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Creating Distinct Count Measures???

Started by torre2011, 01 May 2013 01:23:57 PM

Previous topic - Next topic

torre2011

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!


CognosPaul

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.