count distinct in transformer!!!!

[biswasarijit84]

hey arsenal can u plzzzzzzzz help with a detail information about how u made the distinct count in transformer? where u write the manual sql? in database or in FM?

[phanicrn]

how r u getting iqds. is it framework manager..
if its fm.. try create count distinct calculation in fm & bring to transformer ,set rollup setting, check with less date.
it worked for me..

phani

[MMcBride]

One way you can create a count distinct is to utilize a Count Dimension to create a Measure and simply hide this dimension from the Consumers.

We recently faced this issue with Claim Counts, a Claim could be assigned to multiple People but at the aggregate levels (Team, Branch) we did not want to count these claims multiple times, regardless of how many people are assigned.

By creating a Dimension that contained "Branch, Team, Claim Number" and enforcing uniqueness to the Claim Number category we were able to create a Claim Count measure from this that displays Unique counts at the Aggregate levels and still gives us a very accurate view of all claims assigned to individuals.
We then used namespace security to Hide this Dimension so we could hide the dimension from everyone while still using it to determine an accurate distinct Count of Claims.

I realise this isn't what you asked for but this is how we handled creating a distinct count in one of our Cubes, so I thought it might help

The only issue we have is a training issue with the end users on the data, which considering they created the business logic wasn't very difficult.
The training issue involves.

If you create a Crosstab that displays Branch, Team, User in nested Rows with Claim Details in the Columns and one of the Measures in the Crosstab is "Claim Count" then you cannot simply add up all the claims for each user and match the Aggregate.

So for example,
User_01 is assigned Claims 1, 2, 3, 4, 5
User_02 is assigned Claims 5, 6, 7, 8, 9

The claim count for each user will be 5
The claim count for the team they both work in will be 9

Name	Claim Count
User_01	5
User_02	5
Team Total	9

I realise this might be a little confusing when looking at it but this is the way the business wanted the data to be displayed.

We created this exact Crosstab in Report Studio against the relational Data with a simple Count Distinct applied to the count and the results from the Cube and Relational source match perfectly, the cube is just 10 times faster...