[Mondrian] Group dimension members

[Agustin Campos]

as far as i've been able to build one, I have not been able to use a SQL
query in the fact table. You know: it is not the same theory than real
live... I'm sorry, but i can't help you more.

2007/10/30, Eduardo Andrade <eduardofandrade at gmail.com>:
>
> Hello, i'm trying to build a cube that has 3 dimensions related to workers
> : Professional Category, Sex and Age. In the database we don't have the age
> but instead we have is date of birth, so i'm using this to compute his age.
> Besides that, we would like to group people by age, so i built the following
> cube :
>
>
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <Schema name="Quadro21191519310000">
>     <Cube name="Quadro2" caption="Quadro2" cache="true" enabled="true">
>         <View alias="SITUACAOPROFISSIONAL">
>             <SQL><![CDATA[SELECT A4448X1_.DTNASCIMENTO AS IPDTNASCIMENTO,
> A4449X2_.CHVP AS IPCCHVP, A4448X1_.SEXO AS IPSEXO, THIS_.CHVEIDPESSOAL AS
> SPCHVEIDPESSOAL, A4450X3_.DESIGGRUPOPESSOAL AS IPCGPDESIGGRUPOPESSOAL,
> THIS_.CHVP AS SPCHVP, A4450X3_.CHVP AS IPCGPCHVP, A4448X1_.CHVP AS IPCHVP,
> A4449X2_.CHVEGRUPOPESSOAL AS IPCCHVEGRUPOPESSOAL FROM SITUACAOPROFISSIONAL
> THIS_ LEFT OUTER JOIN IDPESSOALCARREIRA A4449X2_ ON
> THIS_.CHVEIDPESSOALCARREIRA=A4449X2_.CHVP LEFT OUTER JOIN GRUPOPESSOAL
> A4450X3_ ON A4449X2_.CHVEGRUPOPESSOAL=A4450X3_.CHVP LEFT OUTER JOIN
> IDPESSOAL A4448X1_ ON THIS_.CHVEIDPESSOAL=A4448X1_.CHVP]]></SQL>
>         </View>
>         <Dimension name="Grupos" foreignKey="IPCCHVEGRUPOPESSOAL">
>             <Hierarchy name="Total Grupos" allMemberName="Total Grupos"
> hasAll="true" primaryKey="CHVP" primaryKeyTable="GRUPOPESSOAL">
>                 <Table name="GRUPOPESSOAL" alias="GRUPOPESSOAL131" />
>                 <Level name="Grupos Profissionais" table="GRUPOPESSOAL131"
> column="DESIGGRUPOPESSOAL" uniqueMembers="false" />
>             </Hierarchy>
>         </Dimension>
>         <Dimension name="Sexos" foreignKey="SPCHVEIDPESSOAL">
>             <Hierarchy name="Total Sexos" allMemberName="Total Sexos"
> hasAll="true" primaryKey="CHVP" primaryKeyTable="IDPESSOAL">
>                 <Table name="IDPESSOAL" alias="IDPESSOAL127" />
>                 <Level name="Sexo" table="IDPESSOAL127" column="SEXO"
> uniqueMembers="false">
>                     <NameExpression>
>                         <SQL>
>                             <![CDATA[CASE WHEN SEXO=1 THEN 'M'
>                                           WHEN SEXO=2 THEN 'F'
>                                           ELSE 'vazio'
>                                           END
>                                     ]]>
>                         </SQL>
>                     </NameExpression>
>                 </Level>
>             </Hierarchy>
>         </Dimension>
>         <Dimension name="Idades" foreignKey="SPCHVEIDPESSOAL">
>             <Hierarchy name="Total Idades" allMemberName="Total Idades"
> hasAll="true" primaryKey="CHVP" primaryKeyTable="IDPESSOAL">
>                 <Table name="IDPESSOAL" alias="IDPESSOAL129" />
>                 <Level name="Idade" table="IDPESSOAL129"
> column="DTNASCIMENTO" uniqueMembers="true">
>                     <KeyExpression>
>                         <SQL>
>                             <![CDATA[CASE WHEN ((SELECT EXTRACT(YEAR FROM
> CURRENT_DATE) FROM DUAL) - extract(YEAR FROM DTNASCIMENTO)) < 18 THEN 'ate
> 18 anos'
>                                           WHEN ((SELECT EXTRACT(YEAR FROM
> CURRENT_DATE) FROM DUAL) - extract(YEAR FROM DTNASCIMENTO)) < 25 THEN
> '18-24'
>                                           WHEN ((SELECT EXTRACT(YEAR FROM
> CURRENT_DATE) FROM DUAL) - extract(YEAR FROM DTNASCIMENTO)) < 30 THEN
> '25-29'
>                                           WHEN ((SELECT EXTRACT(YEAR FROM
> CURRENT_DATE) FROM DUAL) - extract(YEAR FROM DTNASCIMENTO)) < 35 THEN
> '30-34'
>                                           WHEN ((SELECT EXTRACT(YEAR FROM
> CURRENT_DATE) FROM DUAL) - extract(YEAR FROM DTNASCIMENTO)) < 40 THEN
> '35-39'
>                                           WHEN ((SELECT EXTRACT(YEAR FROM
> CURRENT_DATE) FROM DUAL) - extract(YEAR FROM DTNASCIMENTO)) < 45 THEN
> '40-44'
>                                           WHEN ((SELECT EXTRACT(YEAR FROM
> CURRENT_DATE) FROM DUAL) - extract(YEAR FROM DTNASCIMENTO)) < 50 THEN
> '45-49'
>                                           WHEN ((SELECT EXTRACT(YEAR FROM
> CURRENT_DATE) FROM DUAL) - extract(YEAR FROM DTNASCIMENTO)) < 55 THEN
> '50-54'
>                                           WHEN ((SELECT EXTRACT(YEAR FROM
> CURRENT_DATE) FROM DUAL) - extract(YEAR FROM DTNASCIMENTO)) < 60 THEN
> '55-59'
>                                           WHEN ((SELECT EXTRACT(YEAR FROM
> CURRENT_DATE) FROM DUAL) - extract(YEAR FROM DTNASCIMENTO)) < 65 THEN
> '60-64'
>                                           WHEN ((SELECT EXTRACT(YEAR FROM
> CURRENT_DATE) FROM DUAL) - extract(YEAR FROM DTNASCIMENTO)) < 70 THEN
> '65-69'
>                                           WHEN ((SELECT EXTRACT(YEAR FROM
> CURRENT_DATE) FROM DUAL) - extract(YEAR FROM DTNASCIMENTO)) > 70 THEN '70 ou
> mais'
>                                           ELSE 'vazio'
>                                           END
>                                     ]]>
>                         </SQL>
>                     </KeyExpression>
>                 </Level>
>             </Hierarchy>
>         </Dimension>
>         <Measure column="SPCHVP" name="chvP" aggregator="count"
> formatString="###,##0" />
>     </Cube>
> </Schema>
>
> This generates a SQL with a group by clause that contains all the CASE
> expression, therefore giving a SQL ERROR. Is there a way to achieve what I
> want ?
>
> Thank you,
> Edaurdo Andrade
>
> --
> Eduardo Andrade
> eduardofandrade at gmail.com
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>


-- 
Agustín Campos Muñoz
http://www.acampos.net

Email: aguscamposENgmailPUNTOcom
           acamposENiiesPUNTOes
MSN Messenger: acampozENhotmailPUNTOcom
Skype: acamposnet
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20071030/c334e704/attachment-0001.html