# how to do a count distinct value in awm

**579054**Oct 31, 2007 11:17 AM

Hi!!!

I create a cube with 3 dimensions:

dimension1:I use the snowflake assignation type, cause I have the information of the dimension on two tables.

level1:all

level2:older or younger than 25

level3:person

level1,2 tables

ID_MAS_DE_25 DES_MAS_25_CAS TOT_MAS_DE_25 DES_TOT_MAS_DE_25

1 <= 25 TOT_MAS_DE_50 TODOS(all)

2 > 25 TOT_MAS_DE_50 TODOS(all)

3 = 25 TOT_MAS_DE_50 TODOS(all)

level3 table

ID_PERSONA DES_PER_CAS ID_MAS_MENOS(foreign key)

1 JANE 1

2 JHON 3

3 SELLY 3

4 ROBERT 2

5 ALBERT 1

6 ADAM 3

7 EVA 3

dimension2:star schema

level1:all

level2:city

ID_MUNICIPIO DES_MUNIC_CAS TOT_MUNIC DES_TOT_MUNIC

1 BILBAO TOT_MUNIC TODOS

2 LONDON TOT_MUNIC TODOS

3 PARIS TOT_MUNIC TODOS

dimension3:snowflake

level1:all

level2:car type

level3:car

ID_TIPO_COCHE DES_TC_CAS TOT_TCOCHES DES_TOT_TCOCHES

1 FORD TOT_TIPOS_COCHES TODOS

2 VOLSWAGEN TOT_TIPOS_COCHES TODOS

3 MERCEDES TOT_TIPOS_COCHES TODOS

4 RENAULT TOT_TIPOS_COCHES TODOS

5 OPEL TOT_TIPOS_COCHES TODOS

ID_REGISTRATION_NUM DES_COCHES_CAS ID_TIPO_COCHE

12454XF 12454XF 1

14585HY 14585HY 2

48796JI 48796JI 1

1489OP 1489OP 5

9874LO 9874LO 5

5975IO 5975IO 4

3659SD 3659SD 1

5697UY 5697UY 4

8963AS 8963AS 4

5687SA 5687SA 3

the cube takes the values through this fackt table

ID_PERSONA ID_MUNICI ID_REGISTRATION CANT

---------- --------- --------------- ----------

1 1 12454XF 1

1 1 14585HY 1

2 1 48796JI 1

3 3 1489OP 1

4 3 9874LO 1

5 2 5975IO 1

5 2 3659SD 1

6 2 5697UY 1

7 3 8963AS 1

7 3 5687SA 1

with this cube, I have to show these values:

How many cars are in Bilbao?

3-->"12454xf","14585hy","48796ji"

how many people that is 25 years old have a car?

4-->Eva,Adam,Selly,Jhon

how many people has a car?

7

Do anyone know how can I show all this different values in just one measure?if yes, how?

I have been thinking about how can I do a count(distinct(value)) in awm. Do you know where is that option?

also, in this cube, to show all this different values, we will have to apply different aggregation types for

each level, and I dont know if this could be possible.

thanks

*for better understanding, here are the real values of the fack table

registration Name Car City Ages

number

12454XF Jane Ford Bilbao 20

14585HY Jane Volswagen Bilbao 20

48796JI Jhon Ford Bilbao 25

1489OP Selly Opel Paris 25

9874LO Robert Opel Paris 30

5975IO Albert Renault London 20

3659SD Albert Ford London 20

5697UY Adam Renault London 25

8963AS Eva Renault Paris 25

5687SA Eva Mercedes Paris 25

best regards,

I create a cube with 3 dimensions:

dimension1:I use the snowflake assignation type, cause I have the information of the dimension on two tables.

level1:all

level2:older or younger than 25

level3:person

level1,2 tables

ID_MAS_DE_25 DES_MAS_25_CAS TOT_MAS_DE_25 DES_TOT_MAS_DE_25

1 <= 25 TOT_MAS_DE_50 TODOS(all)

2 > 25 TOT_MAS_DE_50 TODOS(all)

3 = 25 TOT_MAS_DE_50 TODOS(all)

level3 table

ID_PERSONA DES_PER_CAS ID_MAS_MENOS(foreign key)

1 JANE 1

2 JHON 3

3 SELLY 3

4 ROBERT 2

5 ALBERT 1

6 ADAM 3

7 EVA 3

dimension2:star schema

level1:all

level2:city

ID_MUNICIPIO DES_MUNIC_CAS TOT_MUNIC DES_TOT_MUNIC

1 BILBAO TOT_MUNIC TODOS

2 LONDON TOT_MUNIC TODOS

3 PARIS TOT_MUNIC TODOS

dimension3:snowflake

level1:all

level2:car type

level3:car

ID_TIPO_COCHE DES_TC_CAS TOT_TCOCHES DES_TOT_TCOCHES

1 FORD TOT_TIPOS_COCHES TODOS

2 VOLSWAGEN TOT_TIPOS_COCHES TODOS

3 MERCEDES TOT_TIPOS_COCHES TODOS

4 RENAULT TOT_TIPOS_COCHES TODOS

5 OPEL TOT_TIPOS_COCHES TODOS

ID_REGISTRATION_NUM DES_COCHES_CAS ID_TIPO_COCHE

12454XF 12454XF 1

14585HY 14585HY 2

48796JI 48796JI 1

1489OP 1489OP 5

9874LO 9874LO 5

5975IO 5975IO 4

3659SD 3659SD 1

5697UY 5697UY 4

8963AS 8963AS 4

5687SA 5687SA 3

the cube takes the values through this fackt table

ID_PERSONA ID_MUNICI ID_REGISTRATION CANT

---------- --------- --------------- ----------

1 1 12454XF 1

1 1 14585HY 1

2 1 48796JI 1

3 3 1489OP 1

4 3 9874LO 1

5 2 5975IO 1

5 2 3659SD 1

6 2 5697UY 1

7 3 8963AS 1

7 3 5687SA 1

with this cube, I have to show these values:

How many cars are in Bilbao?

3-->"12454xf","14585hy","48796ji"

how many people that is 25 years old have a car?

4-->Eva,Adam,Selly,Jhon

how many people has a car?

7

Do anyone know how can I show all this different values in just one measure?if yes, how?

I have been thinking about how can I do a count(distinct(value)) in awm. Do you know where is that option?

also, in this cube, to show all this different values, we will have to apply different aggregation types for

each level, and I dont know if this could be possible.

thanks

*for better understanding, here are the real values of the fack table

registration Name Car City Ages

number

12454XF Jane Ford Bilbao 20

14585HY Jane Volswagen Bilbao 20

48796JI Jhon Ford Bilbao 25

1489OP Selly Opel Paris 25

9874LO Robert Opel Paris 30

5975IO Albert Renault London 20

3659SD Albert Ford London 20

5697UY Adam Renault London 25

8963AS Eva Renault Paris 25

5687SA Eva Mercedes Paris 25

best regards,

- 27795 Views
- Tags: none (add)