[Mondrian] Group dimension members

Julian Hyde julianhyde at speakeasy.net
Wed Oct 31 16:06:10 EDT 2007


What database are you using?
 
Can you post the SQL that is generated? If it looks correct and the database
just can't handle it, you can't really blame mondrian - it's the database's
problem. I know some databases can't handle expressions in the group by
clause.
 
Finally, I have to point out that while this approach (fact table based on a
view, and level based on a complex expression) may work, it is asking a lot
of the database. In a traditional star schema, age range would be a column
in the worker dimension table, and populated by the etl process. And things
would work a lot smoother.
 
Julian 


  _____  

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On
Behalf Of Eduardo Andrade
Sent: Tuesday, October 30, 2007 4:38 AM
To: Mondrian Mailing List
Subject: [Mondrian] Group dimension members


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/20071031/48b1b460/attachment.html 


More information about the Mondrian mailing list