search

mysticfall / pivot4j

Pivot4J provides a common API for OLAP servers which can be used to build an analytical service frontend with pivot style GUI.
Other
128 stars 101 forks source link

Boolean type in dimension #95

Closed klaudiusz223 closed 11 years ago

klaudiusz223 commented 11 years ago

At least one of the followin query should work

SELECT NON EMPTY {[Measures].[Grocery Sqft]} ON COLUMNS, NON EMPTY {Hierarchize({[Has coffee bar].[false]})} ON ROWS FROM [Store]

SELECT NON EMPTY {[Measures].[Grocery Sqft]} ON COLUMNS, NON EMPTY {Hierarchize({[Has coffee bar].[0]})} ON ROWS FROM [Store]

The first one works well via XMLA with other tools

mysticfall commented 11 years ago

I think it's a Mondrian's problem. Related stacktraces are : 

Caused by: org.olap4j.OlapException: mondrian gave exception while parsing query
    at mondrian.olap4j.MondrianOlap4jConnection$Helper.createException(MondrianOlap4jConnection.java:827)
    at mondrian.olap4j.MondrianOlap4jStatement.parseQuery(MondrianOlap4jStatement.java:375)
    at mondrian.olap4j.MondrianOlap4jStatement.executeOlapQuery(MondrianOlap4jStatement.java:346)
    at com.eyeq.pivot4j.impl.PivotModelImpl.executeMdx(PivotModelImpl.java:496)
    at com.eyeq.pivot4j.impl.PivotModelImpl.getCellSet(PivotModelImpl.java:453)
    ... 41 more
Caused by: mondrian.olap.MondrianException: Mondrian Error:Failed to parse query 'SELECT NON EMPTY {[Measures].[Grocery Sqft]} ON COLUMNS, NON EMPTY {Hierarchize({[Has coffee bar].[false]})} ON ROWS FROM [Store]'
    at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:972)
    at mondrian.olap.ConnectionBase.parseStatement(ConnectionBase.java:100)
    at mondrian.olap4j.MondrianOlap4jStatement$1.execute(MondrianOlap4jStatement.java:362)
    at mondrian.olap4j.MondrianOlap4jStatement$1.execute(MondrianOlap4jStatement.java:360)
    at mondrian.server.Locus.execute(Locus.java:87)
    at mondrian.server.Locus.execute(Locus.java:72)
    at mondrian.olap4j.MondrianOlap4jStatement.parseQuery(MondrianOlap4jStatement.java:356)
    ... 44 more
Caused by: java.sql.SQLSyntaxErrorException: Comparisons between 'SMALLINT' and 'BOOLEAN' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(Unknown Source)
    at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:205)
    at mondrian.rolap.SqlStatement.execute(SqlStatement.java:182)

I'll resolve this one as 'invalid' as we can't do anything from our side.

Thanks!

klaudiusz223 commented 11 years ago

Sorry, it was my mistake . I thought it didn't work also via XMLA connection. But it has turned out that that my xmla connection is simply Mondrian connection.

By the way is it possible to configure multiple data sources in analytics? Configuration file looks like to be prepared for that, but it doesn't work for me and I deploy multiple pivot4j instances.

mysticfall commented 11 years ago

@klaudiusz223 No problem :) By the way, you are correct about the limitation with data source configuration.

As you said, only file format and internal API is ready for supporting multiple data source configuration for now, and more work is needed (mostly UI stuffs) before they can be really used.

Actually, that is the reason why #63 is still left open. It'll be finished before the 0.8 release.