[Mondrian] Using a fact based on a varchar column withahierarchical dimension.

Julian Hyde jhyde at pentaho.com
Thu Aug 28 14:11:17 EDT 2008 

PS Bugs tend to get fixed faster if you can provide a testcase on the
foodmart schema. :)


  _____  

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On
Behalf Of Julian Hyde
Sent: Thursday, August 28, 2008 10:44 AM
To: 'Mondrian developer mailing list'
Subject: RE: [Mondrian] Using a fact based on a varchar column
withahierarchical dimension.


Yes, it's a bug. Please log it.
 
Doesn't look like we support max over string values, either as an aggregate
for rolling up fact data or as a function.
 
A workaround MAY be to use a closure table. In this case, Mondrian may be
able to generate MAX in SQL rather than using the Max function in-memory.
 
You should also upgrade to 3.0.4 - a lot of bugs have been fixed since
2.4.2.
 
Julian


  _____  

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On
Behalf Of Eduardo Andrade
Sent: Thursday, August 28, 2008 10:25 AM
To: Mondrian Mailing List
Subject: [Mondrian] Using a fact based on a varchar column with
ahierarchical dimension.


Hello,
I'm sorry to bother you guys with this, but it seemed to me appropriate to
write this one to the mailing list, just to be sure it's not a mondrian bug.

I have this mondrian xml definition with a fact table (as a sql query) and a
hierarchical dimension.
Using the cube with a Integer/Double fact column, is fine, but if I use a
VarChar column, i get an exception, but only when I expand the hierarchical
dimension root level. I am able to combine the fact (the VarChar one) with
the dimension, and see one line with the hierarchy all name and the max
value from the Varchar db column.

Is this a bug or do i have something wrong with my xml definition ?

If necessary i could try and build an example on top o foodmart example.
I'm using mondrian ver. 2.4.0.9716.

Here is the mondrian xml definition :

<?xml version="1.0" encoding="ISO-8859-1"?>

<Schema name="5_1219928473000">
  <Cube name="5" caption="Cubo Teste" cache="true" enabled="true">
    <View alias="PRODUTO">
      <SQL><![CDATA[
SELECT
   THIS_.CHVP AS PCHVP,
   THIS_.DESIGPRODUTO AS PDESIGPRODUTO
   A8676_.CHVEESTRUTURACLD AS CHVEESTRUTURACLD
   FROM PRODUTO THIS_
   INNER JOIN ESTRUTURA A8676_ ON A8676_.CHVP = THIS_.CHVP
]]></SQL>
    </View>
    <Dimension name="Produtos" foreignKey="PCHVP">
      <Hierarchy name="Total Produtos" allMemberName="Total Produtos"
hasAll="true" primaryKey="CHVP" primaryKeyTable="PRODUTO">
        <Table name="PRODUTO" alias="PRODUTO22"/>
        <Level name="Referência" table="PRODUTO22" column="REFPRODUTO"
uniqueMembers="true">
          <Property column="CHVP" name="ChvP"/>
          <Property column="DESIGPRODUTO" name="Designação"/>
        </Level>
      </Hierarchy>
    </Dimension>
    <Dimension name="Classificação" foreignKey="CHVEESTRUTURACLD">
      <Hierarchy name="Total Classificação" allMemberName="Total
Classificação" hasAll="true" primaryKey="CHVP" primaryKeyTable="ESTRUTURA">
        <Table name="ESTRUTURA" alias="ESTRUTURA18">
          <SQL><![CDATA["ESTRUTURA18".CHVECATEGORIA IN (select this_.ChvP as
y0_ from Categoria this_ where (((this_.ChvEExercicio=[$EXER] or
this_.ChvEExercicio is null) and (this_.ChvEUnidadeUtilizadora is null or
this_.ChvEUnidadeUtilizadora=[$UU])) and (lower(this_.SiglaCategoria) like
'cld' and this_.SiglaCategoria is not null))) ]]></SQL>
        </Table>
        <Level name="CCA" table="ESTRUTURA18" column="CHVP"
parentColumn="CHVEESTRUTURA" ordinalColumn="CODCOMPIESTRUTURA"
nameColumn="CODCOMPESTRUTURA" nullParentValue="NULL" uniqueMembers="true">
          <Property column="DESIGESTRUTURA" name="Designação"/>
        </Level>
      </Hierarchy>
    </Dimension>
    <Measure column="PCHVP" name="ChvP" aggregator="sum"
formatString="###,##0"/>
    <Measure column="PDESIGPRODUTO" name="DesigProduto" datatype="String"
aggregator="max" formatString="###,##0"/>
  </Cube>
</Schema>


When i issue this query :
select {[Measures].[DesigProduto]} ON COLUMNS,
Hierarchize(Union({[Classificação.Total Classificação].[Total
Classificação]}, [Classificação.Total Classificação].[Total
Classificação].Children)) ON ROWS
from [5]

I obtain this exception :
"
Cause:
class
com.tonbeller.jpivot.olap.model.OlapException:mondrian.olap.MondrianExceptio
n: Mondrian Error:Internal error: Error while executing query [select
{[Measures].[DesigProduto]} ON COLUMNS,
Hierarchize(Union({[Classificação.Total Classificação].[Total
Classificação]}, [Classificação.Total Classificação].[Total
Classificação].Children)) ON ROWS from [5] ]

com.tonbeller.jpivot.mondrian.MondrianModel.getResult(MondrianModel.java:305
)
com.tonbeller.jpivot.mondrian.MondrianModel.getCurrentMdx(MondrianModel.java
:856)
pt.gedi.bi.services.rep.ad.Explorador_ServiceImpl.handleMondrianResult(Explo
rador_ServiceImpl.java:598)
pt.gedi.bi.services.rep.ad.Explorador_ServiceImpl.getMondrianResult(Explorad
or_ServiceImpl.java:314)
pt.gedi.bi.control.actions.rep.ad.ExploradorCuboExtendedAction.getArealistHe
aderConfig(ExploradorCuboExtendedAction.java:1456)
sun.reflect.GeneratedMethodAccessor624.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
.java:25)
java.lang.reflect.Method.invoke(Method.java:597)
pt.gedi.base.control.actions.BaseAction.processAskServer(BaseAction.java:217
5)
pt.gedi.base.control.actions.BaseAction.execute(BaseAction.java:1641)
...
Cause:
class mondrian.olap.MondrianException:Mondrian Error:Internal error: Error
while executing query [select {[Measures].[DesigProduto]} ON COLUMNS,
Hierarchize(Union({[Classificação.Total Classificação].[Total
Classificação]}, [Classificação.Total Classificação].[Total
Classificação].Children)) ON ROWS from [5] ]

mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:755)
mondrian.olap.Util.newInternal(Util.java:1104)
mondrian.olap.Util.newError(Util.java:1120)
mondrian.rolap.RolapConnection.execute(RolapConnection.java:442)
com.tonbeller.jpivot.mondrian.MondrianModel.getResult(MondrianModel.java:279
)
com.tonbeller.jpivot.mondrian.MondrianModel.getCurrentMdx(MondrianModel.java
:856)
pt.gedi.bi.services.rep.ad.Explorador_ServiceImpl.handleMondrianResult(Explo
rador_ServiceImpl.java:598)
pt.gedi.bi.services.rep.ad.Explorador_ServiceImpl.getMondrianResult(Explorad
or_ServiceImpl.java:314)
pt.gedi.bi.control.actions.rep.ad.ExploradorCuboExtendedAction.getArealistHe
aderConfig(ExploradorCuboExtendedAction.java:1456)
sun.reflect.GeneratedMethodAccessor624.invoke(Unknown Source)
...
Cause:
class java.lang.ClassCastException:java.lang.String cannot be cast to
java.lang.Double

mondrian.olap.fun.FunUtil.max(FunUtil.java:945)
mondrian.rolap.RolapAggregator$4.aggregate(RolapAggregator.java:58)
mondrian.olap.fun.BuiltinFunTable$22$1.aggregateChildren(BuiltinFunTable.jav
a:655)
mondrian.olap.fun.BuiltinFunTable$22$1$1.evaluate(BuiltinFunTable.java:630)
mondrian.rolap.RolapEvaluator.evaluateCurrent(RolapEvaluator.java:494)
mondrian.calc.impl.ValueCalc.evaluate(ValueCalc.java:31)
mondrian.olap.fun.FunUtil.evaluateSet(FunUtil.java:1223)
mondrian.olap.fun.FunUtil.max(FunUtil.java:937)
mondrian.rolap.RolapAggregator$4.aggregate(RolapAggregator.java:58)
mondrian.olap.fun.BuiltinFunTable$22$1.aggregateChildren(BuiltinFunTable.jav
a:655)
...
"

Thank you in advance.

Best wishes,

-- 
Eduardo Andrade
eduardofandrade at gmail.com


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20080828/5c75ca6c/attachment.html

More information about the Mondrian mailing list