count distinct in transformer

[phanicrn]

Hi

I am doing a calucltion in framework manager and externalzing as IQD and  creating a cube in transfomer

When i test in the IQD sql in Database it give me the right count.After building the cube in transformer count varies. i dont know what to do..? Even i logged case with cognos support, no solutions..

Here's the calculation did in framework manager

if([Account Balance Model].[Account_Balance_Derived].[Member_Account_Key]  =minimum(([Account Balance Model].[Account_Balance_Derived].[Member_Account_Key]  ) for [Account Balance Model].[Account_Balance_Derived].[Member_Key]  )) then (1) else (0)


Help me guys, I am stuck here, nowhere to go

[nonooo]

the only way to do a distinct count in a cube is via "category count" and you can only do that if you have done a BIG datasource (fact table) containing all the dimension you want, in this datasource add a calculated column with a static value of 1. Then add a measure that is a sum of this static value, exclude this measure from your cube. add a measure that is a category count give the static measure as a parameter and that is all.

[phanicrn]

Hi

I have fact table as datasource, I was confused , can you explain me  in detail..

as per you word. I will created a column in fact table (with static value of 1)
new column has been created in fact table(datasource).

2)Then add a measure that is a sum of this static value ( i dont know what to do about this step..)

you want me to add column and refer to static value and define Regular rollup as (sum) is that what your saying..?   exclude this measure from your cube...?

3) coming to step3 mention static value column as category count.. is it..?

but will it be returning distinct values...

[nonooo]

2 ) yes add a measure that count, I agree this measure has no interest (this is why I advise you to exluce it from the cube) BUT it is needed to be sure that all rows in the fact table are concerned by at least one measure (if it is not the case transformer ignores some data.)

3) after launching transformer here is the step by step:
this is in the tab measure type "category count" you choose the dimension against you want to count (if you followed step 1 and have only one big fact table, you do not need to bother just choose one) and the level.
==> I was false saying you count against a measure, the measure defines the category that defines the dimension against you can count.

YES it will be distinct values.

[Cannondale23]

count measure: if the value is null in the datasource it will not count it, if the value is 0 in the datsourse transformer will count it as 1. I would decode a lot of stuff to get say all open accounts (1)

category count measure - a category is counted only if it has a non missing non zero value, if you are trying to get distinct count of say customer without double counting (say grain is by customer by product by month ) use an activity measure for the count ! have fun

[nonooo]

just to be complete your activity measure will be the static 1 rolled up as a sum.

[phanicrn]

Let me try this. I will let you know the result.
thanks guys

[phanicrn]

solved.

[phanicrn]

Hi it got worked, after doing lots of testing

doing category count will work and rollup setting changes will also work

now i can do it two ways..


Thanks for you support
phani

[phanicrn]

solved...