[Mondrian] Questions regarding measures and ordinalColumn attribute of Level element

Eduardo Andrade eduardofandrade at gmail.com
Wed Jul 4 08:40:53 EDT 2007


Hello everyone, i'm using mondrian and basically i have a star schema with a
fact table joining with multiple dimensions, some of them are described by
parent/child hierarquies with no closure.

I would like to know the answer to a couple of questions :

- Why does jPivot shows a sum of every record of the fact table and not just
the sum of the records that have a foreign key to the dimension ? (i.e. by
analising the query that mondrian produces i see that it does a sum() on the
fact column but does not have a join condition with the dimension table. Why
is that ?

- The other issue is that the members of the dimensions are not ordered as i
would like them to be. The attribute ordinalColumn on the <Level> element is
being used and the records are fetched correctly with the order by clause
(i'm seeing this on the query logs of mysql), but they're not showing up
correctly in the pivot table. Any ideas ?

I'm using mondrian 2.1.1 and jPivot 1.5, and I can't upgrade because
parent/child hierarchy without closure is broken and I can't figure out if
this is a jPivot or Mondrian problem (altough i'm convinced it is a jPivot
issue and logged a bug request for it).

Thank you.

Best regards,

PS:
Cube definition:

<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE Schema SYSTEM "mondrian.dtd">
<Schema name="ODL1183547800000">
    <Cube name="ODL" caption="ODL" cache="true" enabled="true">
        <Table name="ORCAMENTODESPLINHA" />
        <Dimension name="CCA FF" foreignKey="CHVEESTRUTURAFF">
            <Hierarchy name="FF" allMemberName="FF" hasAll="true"
primaryKey="CHVP" primaryKeyTable="ESTRUTURA">
                <Table name="ESTRUTURA">
                    <SQL>
                        <![CDATA[CHVECATEGORIA IN (SELECT CHVP FROM
CATEGORIA WHERE SIGLACATEGORIA='FF')]]>
                    </SQL>
                </Table>
                <Level name="Fonte de Financiamento" table="ESTRUTURA"
column="CHVP" uniqueMembers="true" parentColumn="CHVEESTRUTURA"
ordinalColumn="CODCOMPIESTRUTURA" nameColumn="CODCOMPESTRUTURA"
nullParentValue="NULL" />
            </Hierarchy>
        </Dimension>
        <Dimension name="CCA CLP" foreignKey="CHVEESTRUTURACLP">
            <Hierarchy name="CLP" allMemberName="CLP" hasAll="true"
primaryKey="CHVP" primaryKeyTable="ESTRUTURA">
                <Table name="ESTRUTURA">
                    <SQL>
                        <![CDATA[CHVECATEGORIA IN (SELECT CHVP FROM
CATEGORIA WHERE SIGLACATEGORIA='CLP')]]>
                    </SQL>
                </Table>
                <Level name="Classificação Económica do Programa"
table="ESTRUTURA" column="CHVP" uniqueMembers="true"
parentColumn="CHVEESTRUTURA" ordinalColumn="CODCOMPIESTRUTURA"
nameColumn="CODCOMPESTRUTURA" nullParentValue="NULL" />
            </Hierarchy>
        </Dimension>
        <Measure column="VALLIQUIDADO" name="valLiquidado" aggregator="sum"
formatString="###,##0.00" />
        <Measure column="VALCABIMENTOS" name="valCabimentos"
aggregator="sum" formatString="###,##0.00" />
        <Measure column="VALPAGO" name="valPago" aggregator="sum"
formatString="###,##0.00" />
    </Cube>
</Schema>

-- 
Eduardo Andrade
eduardofandrade at gmail.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20070704/982bd41a/attachment.html 


More information about the Mondrian mailing list