[Mondrian] Group dimension members
Eduardo Andrade
eduardofandrade at gmail.com
Thu Nov 8 14:54:12 EST 2007
Thank you Julian for your wise advise.
Mondrian is in fact generating everything correctly. I am working on a best
solution.
Eduardo Andrade
On 10/31/07, Julian Hyde <julianhyde at speakeasy.net> wrote:
>
> 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
>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>
--
Eduardo Andrade
eduardofandrade at gmail.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20071108/0b5a88d4/attachment.html
More information about the Mondrian
mailing list