Mondrian Schema - No measures data returned on MySQL query

Question

Hi Team,

Hpp 2018, i have an issue with my mondrian schema. All sounds to be okay untill i try to drill down the cube. I only get the sumarised measures (min, med and max) for all, but when i try to dril down the cube no data is returned for the rest dateils. 

Here is my schema,

<?xml version="1.0"?>

<Schema name="mic_infocom">

<!-- Cubes -->

<Cube name="Produto">

<Table name="table_name"/>

<!--Private dimensions Produtos-->

<Dimension name="Produto" foreignKey="codprod">

<Hierarchy hasAll="true" allMemberName="Todos Produtos">

<View alias="geral">

<SQL dialect="generic">

SELECT

T.`produto` AS Produto

FROM `table_name` T

</SQL>

</View>

<Level name="Produto" column="Produto" uniqueMem

.bers="true"/>

</Hierarchy>

</Dimension>

<!--Private dimensions Local-->

<Dimension name="Local" foreignKey="codprov">

<Hierarchy hasAll="true" allMemberName="Todas Provincias">

<View alias="geral">

<SQL dialect="generic">

SELECT

S.`local` AS Local,

S.`distrito` AS Distrito,

S.`provincia` AS Provincia

FROM `shops` S, `table_name` T

WHERE T.codprov=S.codprov AND T.coddist=S.coddist AND T.codloca=S.codloca

</SQL>

</View>

<Level name="Provincia" column="Provincia" uniqueMembers="false"/>

<Level name="Distrito" column="Distrito" uniqueMembers="false"/>

<Level name="Local" column="Local" uniqueMembers="true"/>

</Hierarchy>

</Dimension>

<!-- basic measures -->

<Measure name="Min" aggregator="min" formatString="#,###.00">

<MeasureExpression>

<SQL dialect="generic">table_name.precounit</SQL>

</MeasureExpression>

</Measure>

<Measure name="Med" aggregator="avg" formatString="#,###.00">

<MeasureExpression>

<SQL dialect="generic">table_name.precounit</SQL>

</MeasureExpression>

</Measure>

<Measure name="Max" aggregator="max" formatString="#,###.00">

<MeasureExpression>

<SQL dialect="generic">table_name.precounit</SQL>

</MeasureExpression>

</Measure>

</Cube>

</Schema>

Comments

Hi Arlindo, we'll help you asap. Thank you for the patience.

Paolo

Answer 1

Dear
I usually define dimension outside cube definition, I have a simple doubt about ; foreign Key="codprod".
Can you check if there are some errors inside log files ?

Best regards
Angelo

Comments

Hi Angelo,

Thanks for your replay. About the foreign Key i defined it wrong.

Inside the logfile i found this error...

Caused by: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while loading segment; sql=[select `geral_1`.`Provincia` as `c0`, min(table_name.precounit) as `m0`, avg(table_name.precounit) as `m1`, max(table_name.precounit) as `m2` from (SELECT

S.`local` AS Local,

S.`distrito` AS Distrito,

S.`provincia` AS Provincia

FROM `shops` S, `table_name` T

WHERE T.codprov=S.codprov AND T.coddist=S.coddist AND T.codloca=S.codloca) as `geral_1`, `table_name` as `table_name` where `table_name`.`codprov` = `geral_1`.`codprov` group by `geral_1`.`Provincia`]

at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:977)

at mondrian.olap.Util.newInternal(Util.java:2404)

at mondrian.olap.Util.newError(Util.java:2420)

at mondrian.rolap.SqlStatement.handle(SqlStatement.java:361)

at mondrian.rolap.SqlStatement.execute(SqlStatement.java:252)

at mondrian.rolap.RolapUtil.executeQuery(RolapUtil.java:350)

at mondrian.rolap.agg.SegmentLoader.createExecuteSql(SegmentLoader.java:625)

... 8 more

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'geral_1.codprov' in 'where clause'

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)

at java.lang.reflect.Constructor.newInstance(Unknown Source)

at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)

at com.mysql.jdbc.Util.getInstance(Util.java:360)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)

at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1446)

at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:205)

at mondrian.rolap.SqlStatement.execute(SqlStatement.java:199)

... 10 more

There is a "general_1" table that i dont know from where it come from...look like the system apply it by default

---

Dear

I see:

"Unknown column 'geral_1.codprov' in 'where clause'"

This is an error about mondrian mapping.... check if the "codprov" column exists

BestRegards

A.

---

Dear Angelo,

I have 2 tables, one is my fact_table and contain the products and unitprices and other is shops and contain the province, district and location description.

The folowing schema is working, my issue is how to get the province, distrit and location  description wich are in the shops table. when i use mySQL dialect i get the description but no measures are returned.

<?xml version="1.0"?>

<Schema name="mic_infocom">

<!-- Cubes -->

<Cube name="Produto">

<Table name="table_name"/>

<!--Private dimensions Produtos-->

<Dimension name="Produto" foreignKey="codprod">

<Hierarchy hasAll="true" allMemberName="Todos Produtos">

<Level name="Produto" column="produto" uniqueMembers="true"/>

</Hierarchy>

</Dimension>

<!--Private dimensions Local-->

<Dimension name="Local" foreignKey="codprov">

<Hierarchy hasAll="true" allMemberName="Todas Provincias">

<Level name="Provincia" column="codprov" uniqueMembers="false"/>

<Level name="Distrito" column="coddist" uniqueMembers="false"/>

<Level name="Local" column="codloca" uniqueMembers="true"/>

</Hierarchy>

</Dimension>

<!-- basic measures -->

<Measure name="Min" column="precounit" aggregator="min" formatString="#,###.00"/>

<Measure name="Med" column="precounit" aggregator="avg" formatString="#,###.00"/>

<Measure name="Max" column="precounit" aggregator="max" formatString="#,###.00"/>

</Cube>

</Schema>

---

My Schema 

---

<?xml version="1.0"?>

<Schema name="mic_infocom">

<!-- Cubes -->

<Cube name="Produto">

<Table name="table_name"/>

<!--Private dimensions Produtos-->

<Dimension name="Produto" foreignKey="codprod">

<Hierarchy hasAll="true" allMemberName="Todos Produtos">

<Level name="Produto" column="produto" uniqueMembers="true"/>

</Hierarchy>

</Dimension>

<!--Private dimensions Local -->

<Dimension name="Local" foreignKey="codprov">

<Hierarchy hasAll="true" allMemberName="Todas Provincias">

<View alias="shops">

<SQL dialect="generic">

SELECT

S.`local` AS Local,

S.`distrito` AS Distrito,

S.`provincia` AS Provincia,

T.codloca AS Local1,

T.coddist AS Distrito1,

T.codprov AS Provincia1

FROM `shops` S, `table_name` T

WHERE T.codprov=S.codprov AND T.coddist=S.coddist AND T.codloca=S.codloca

</SQL>

</View>

<Level name="Provincia" column="provincia" uniqueMembers="false"/>

<Level name="Distrito" column="distrito" uniqueMembers="false"/>

<Level name="Local" column="local" uniqueMembers="true"/>

</Hierarchy>

</Dimension>

<!-- basic measures -->

<Measure name="Min" column="precounit" aggregator="min" formatString="#,###.00"/>

<Measure name="Med" column="precounit" aggregator="avg" formatString="#,###.00"/>

<Measure name="Max" column="precounit" aggregator="max" formatString="#,###.00"/>

</Cube>

</Schema>

---

My problem now is :

1 - In the SQL code for Local dimension when i query for the local descriptions i only get the sumarized values for all Local:

Ex:

SELECT

S.`local` AS local,

S.`distrito` AS distrito,

S.`provincia` AS provincia

FROM `shops` S, `table_name` T

WHERE T.codprov=S.codprov AND T.coddist=S.coddist AND T.codloca=S.codloca

but if i query for the local codes i get the measures needed

SELECT

T.codloca AS local,

T.coddist AS distrito,

T.codprov AS provincia

FROM `shops` S, `table_name` T

WHERE T.codprov=S.codprov AND T.coddist=S.coddist AND T.codloca=S.codloca

I dont see what i am doing wrong here.

Here are 2 screenshots

---

Dear

You can show the column that contain the description using "caption" attributes, on dimension definition.

You can find information on mondrian documentation.

Best Regards

A.