[Mondrian] Group dimension members

Eduardo Andrade eduardofandrade at gmail.com
Tue Oct 30 07:37:35 EDT 2007


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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20071030/50b205d7/attachment.html 


More information about the Mondrian mailing list