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. <br><br><div><span class="gmail_quote">
2007/10/30, Eduardo Andrade <<a href="mailto:eduardofandrade@gmail.com">eduardofandrade@gmail.com</a>>:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
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 :
<br><br><br><font size="1"><font size="2"><span style="font-family: arial,sans-serif;"><?xml version="1.0" encoding="ISO-8859-1"?></span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;">
<Schema name="Quadro21191519310000"></span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> <Cube name="Quadro2" caption="Quadro2" cache="true" enabled="true">
</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> <View alias="SITUACAOPROFISSIONAL"></span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;">
<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>
</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> </View></span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> <Dimension name="Grupos" foreignKey="IPCCHVEGRUPOPESSOAL">
</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> <Hierarchy name="Total Grupos" allMemberName="Total Grupos" hasAll="true" primaryKey="CHVP" primaryKeyTable="GRUPOPESSOAL">
</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> <Table name="GRUPOPESSOAL" alias="GRUPOPESSOAL131" /></span><br style="font-family: arial,sans-serif;">
<span style="font-family: arial,sans-serif;"> <Level name="Grupos Profissionais" table="GRUPOPESSOAL131" column="DESIGGRUPOPESSOAL" uniqueMembers="false" /></span>
<br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> </Hierarchy></span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> </Dimension>
</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> <Dimension name="Sexos" foreignKey="SPCHVEIDPESSOAL"></span><br style="font-family: arial,sans-serif;">
<span style="font-family: arial,sans-serif;"> <Hierarchy name="Total Sexos" allMemberName="Total Sexos" hasAll="true" primaryKey="CHVP" primaryKeyTable="IDPESSOAL">
</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> <Table name="IDPESSOAL" alias="IDPESSOAL127" /></span><br style="font-family: arial,sans-serif;">
<span style="font-family: arial,sans-serif;"> <Level name="Sexo" table="IDPESSOAL127" column="SEXO" uniqueMembers="false"></span><br style="font-family: arial,sans-serif;">
<span style="font-family: arial,sans-serif;"> <NameExpression></span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> <SQL></span>
<br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> <![CDATA[CASE WHEN SEXO=1 THEN 'M'</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;">
WHEN SEXO=2 THEN 'F'</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> ELSE 'vazio'
</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> END</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;">
]]></span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> </SQL></span><br style="font-family: arial,sans-serif;">
<span style="font-family: arial,sans-serif;"> </NameExpression></span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> </Level></span>
<br style="font-family: arial,sans-serif;">
<span style="font-family: arial,sans-serif;"> </Hierarchy></span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> </Dimension></span><br style="font-family: arial,sans-serif;">
<span style="font-family: arial,sans-serif;"> <Dimension name="Idades" foreignKey="SPCHVEIDPESSOAL"></span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;">
<Hierarchy name="Total Idades" allMemberName="Total Idades" hasAll="true" primaryKey="CHVP" primaryKeyTable="IDPESSOAL"></span><br style="font-family: arial,sans-serif;">
<span style="font-family: arial,sans-serif;"> <Table name="IDPESSOAL" alias="IDPESSOAL129" /></span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;">
<Level name="Idade" table="IDPESSOAL129" column="DTNASCIMENTO" uniqueMembers="true"></span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;">
<KeyExpression></span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> <SQL></span><br style="font-family: arial,sans-serif;">
<span style="font-family: arial,sans-serif;"> <![CDATA[CASE WHEN ((SELECT EXTRACT(YEAR FROM CURRENT_DATE) FROM DUAL) - extract(YEAR FROM DTNASCIMENTO)) < 18 THEN 'ate 18 anos'</span>
<br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> WHEN ((SELECT EXTRACT(YEAR FROM CURRENT_DATE) FROM DUAL) - extract(YEAR FROM DTNASCIMENTO)) < 25 THEN '18-24'
</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> WHEN ((SELECT EXTRACT(YEAR FROM CURRENT_DATE) FROM DUAL) - extract(YEAR FROM DTNASCIMENTO)) < 30 THEN '25-29'
</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> WHEN ((SELECT EXTRACT(YEAR FROM CURRENT_DATE) FROM DUAL) - extract(YEAR FROM DTNASCIMENTO)) < 35 THEN '30-34'
</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> WHEN ((SELECT EXTRACT(YEAR FROM CURRENT_DATE) FROM DUAL) - extract(YEAR FROM DTNASCIMENTO)) < 40 THEN '35-39'
</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> WHEN ((SELECT EXTRACT(YEAR FROM CURRENT_DATE) FROM DUAL) - extract(YEAR FROM DTNASCIMENTO)) < 45 THEN '40-44'
</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> WHEN ((SELECT EXTRACT(YEAR FROM CURRENT_DATE) FROM DUAL) - extract(YEAR FROM DTNASCIMENTO)) < 50 THEN '45-49'
</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> WHEN ((SELECT EXTRACT(YEAR FROM CURRENT_DATE) FROM DUAL) - extract(YEAR FROM DTNASCIMENTO)) < 55 THEN '50-54'
</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> WHEN ((SELECT EXTRACT(YEAR FROM CURRENT_DATE) FROM DUAL) - extract(YEAR FROM DTNASCIMENTO)) < 60 THEN '55-59'
</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> WHEN ((SELECT EXTRACT(YEAR FROM CURRENT_DATE) FROM DUAL) - extract(YEAR FROM DTNASCIMENTO)) < 65 THEN '60-64'
</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> WHEN ((SELECT EXTRACT(YEAR FROM CURRENT_DATE) FROM DUAL) - extract(YEAR FROM DTNASCIMENTO)) < 70 THEN '65-69'
</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> WHEN ((SELECT EXTRACT(YEAR FROM CURRENT_DATE) FROM DUAL) - extract(YEAR FROM DTNASCIMENTO)) > 70 THEN '70 ou mais'
</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> ELSE 'vazio'</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;">
END</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> ]]></span><br style="font-family: arial,sans-serif;">
<span style="font-family: arial,sans-serif;"> </SQL></span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> </KeyExpression></span>
<br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> </Level></span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> </Hierarchy>
</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> </Dimension></span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> <Measure column="SPCHVP" name="chvP" aggregator="count" formatString="###,##0" />
</span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"> </Cube></span><br style="font-family: arial,sans-serif;"><span style="font-family: arial,sans-serif;"></Schema>
</span></font><br style="font-family: courier new,monospace;"></font><br>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 ?
<br><br>Thank you,<br>Edaurdo Andrade<br><span class="sg"><br>-- <br>Eduardo Andrade<br><a href="mailto:eduardofandrade@gmail.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">eduardofandrade@gmail.com
</a>
</span><br>_______________________________________________<br>Mondrian mailing list<br><a onclick="return top.js.OpenExtLink(window,event,this)" href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a><br><a onclick="return top.js.OpenExtLink(window,event,this)" href="http://lists.pentaho.org/mailman/listinfo/mondrian" target="_blank">
http://lists.pentaho.org/mailman/listinfo/mondrian</a><br><br></blockquote></div><br><br clear="all"><br>-- <br>Agustín Campos Muñoz<br><a href="http://www.acampos.net">http://www.acampos.net</a><br><br>Email: aguscamposENgmailPUNTOcom
<br> acamposENiiesPUNTOes<br>MSN Messenger: acampozENhotmailPUNTOcom<br>Skype: acamposnet