1) We have Mondrian Olap engine. When i tried to do fields filtering over Time dimension in column of kendo ui pivot grid, mondrian gave following error saying the MDX query pivot grid sent as a part of xml request could not be parsed. Here Mondrian could not understand SELECT statement after FROM.
Mondrian error:
Caused by: mondrian.olap.MondrianException: Mondrian Error:Internal error: While parsing SELECT NON EMPTY {CROSSJOIN({[Time].[All Time]},{{[Measures].[Revenue],[Measures].[Margin],[Measures].[Cost]}})} DIMENSION PROPERTIES CHILDREN_CARDINALITY, PARENT_UNIQUE_NAME ON COLUMNS, NON EMPTY {[Product].[All Product],[Product].[All Product].Children} DIMENSION PROPERTIES CHILDREN_CARDINALITY, PARENT_UNIQUE_NAME ON ROWS FROM (SELECT (Filter([Time].[All Time].MEMBERS, InStr([Time].[All Time].CurrentMember.MEMBER_CAPTION,"2014") > 0)) ON 0 FROM [Transaction])
at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:967) ~[MondrianResource$_Def0.class:na]
at mondrian.olap.Util.newInternal(Util.java:2400) ~[Util.class:na]
at mondrian.olap.Util.newError(Util.java:2416) ~[Util.class:na]
at mondrian.parser.JavaccParserValidatorImpl.convertException(JavaccParserValidatorImpl.java:110) ~[JavaccParserValidatorImpl.class:na]
at mondrian.parser.JavaccParserValidatorImpl.parseInternal(JavaccParserValidatorImpl.java:59) ~[JavaccParserValidatorImpl.class:na]
at mondrian.olap.ConnectionBase.parseStatement(ConnectionBase.java:96) ~[ConnectionBase.class:na]
... 88 common frames omitted
Caused by: mondrian.olap.MondrianException: Mondrian Error:Syntax error at line 1, column 332, token '('
at mondrian.parser.JavaccParserValidatorImpl.convertException(JavaccParserValidatorImpl.java:99) ~[JavaccParserValidatorImpl.class:na]
... 90 common frames omitted
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2) Pivot grid does not display members under Include Fields option as well to select.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3) Similarly I had my my Time hierarchy expanded until month level in column and product dimension expanded until department level. Now when i tried to click on Dairy in department level to drill down further, mondrian throws me an error again saying Tuple contains more than one member of hierarchy '[Time]' as follows
Mondrian error:
Caused by: mondrian.olap.MondrianException: Mondrian Error:Failed to parse query 'SELECT NON EMPTY {CROSSJOIN({CROSSJOIN({[Time].[All Time]},{[Time].[2014]})},{{[Measures].[Revenue],[Measures].[Margin],[Measures].[Cost]}}),CROSSJOIN({CROSSJOIN({[Time].[All Time].Children},{[Time].[2014]})},{{[Measures].[Revenue],[Measures].[Margin],[Measures].[Cost]}}),CROSSJOIN({CROSSJOIN({[Time].[All Time]},{[Time].[2014].Children})},{{[Measures].[Revenue],[Measures].[Margin],[Measures].[Cost]}}),CROSSJOIN({CROSSJOIN({[Time].[All Time]},{[Time].[2014].[201409].Children})},{{[Measures].[Revenue],[Measures].[Margin],[Measures].[Cost]}})} DIMENSION PROPERTIES CHILDREN_CARDINALITY, PARENT_UNIQUE_NAME ON COLUMNS, NON EMPTY {[Product].[Dairy],[Product].[Dairy].Children} DIMENSION PROPERTIES CHILDREN_CARDINALITY, PARENT_UNIQUE_NAME ON ROWS FROM [Transaction]'
at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:967) ~[MondrianResource$_Def0.class:na]
at mondrian.olap.ConnectionBase.parseStatement(ConnectionBase.java:100) ~[ConnectionBase.class:na]
at mondrian.olap4j.MondrianOlap4jStatement$1.execute(MondrianOlap4jStatement.java:362) ~[MondrianOlap4jStatement$1.class:na]
at mondrian.olap4j.MondrianOlap4jStatement$1.execute(MondrianOlap4jStatement.java:360) ~[MondrianOlap4jStatement$1.class:na]
at mondrian.server.Locus.execute(Locus.java:87) ~[Locus.class:na]
at mondrian.server.Locus.execute(Locus.java:72) ~[Locus.class:na]
at mondrian.olap4j.MondrianOlap4jStatement.parseQuery(MondrianOlap4jStatement.java:356) ~[MondrianOlap4jStatement.class:na]
... 83 common frames omitted
Caused by: mondrian.olap.MondrianException: Mondrian Error:Tuple contains more than one member of hierarchy '[Time]'.
at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:964) ~[MondrianResource$_Def0.class:na]
9 Answers, 1 is accepted
Straight up to your questions:
#1: It seems that the generated MDX query is correct. The syntax of Filter and InStr method is correct (according Mondrian reference). I am afraid that I cannot determine the cause of the exception based on the stack trace. Would it be possible to run this query directly against the cube and let us know why it is not valid:
SELECT NON EMPTY {CROSSJOIN({[Time].[All Time]},{{[Measures].[Revenue],[Measures].[Margin],[Measures].[Cost]}})} DIMENSION PROPERTIES CHILDREN_CARDINALITY, PARENT_UNIQUE_NAME ON COLUMNS, NON EMPTY {[Product].[All Product],[Product].[All Product].Children} DIMENSION PROPERTIES CHILDREN_CARDINALITY, PARENT_UNIQUE_NAME ON ROWS FROM (SELECT (Filter([Time].[All Time].MEMBERS, InStr([Time].[All Time].CurrentMember.MEMBER_CAPTION,"2014") > 0)) ON 0 FROM [Transaction])...#2: Are there any JavaScript errors? Could you elaborate a bit more on the specific actions you take and what exactly does not work?
#3: In general, the widget (PivotDataSource in particular) filters the dimension members that are from same hierarchy type. Please note that the name of the dimensions should contain the Dimension name (e.g. [Date]) followed by the member name (e.g. Calendar). You can observe this in Basic Usage demo. Notice that first tag displays "[Date].[Calendar]", not only "[Calendar]".
The widget does not support member names that does not include its Dimension (root) name. You will need to ensure that the used Cube complies to this requirement.
Regards,
Georgi Krustev
Telerik
Rank 1
For problem 1)
I have tried sending it directly to the cube and still
Mondrian says "Failed to process query from inner SELECT statement" as follows. I have attached mondrian debug log generated as well while running this query along with this.
Error from mondrian:
at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:987)
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:359)
at mondrian.server.Locus.execute(Locus.java:88)
at mondrian.server.Locus.execute(Locus.java:73)
at mondrian.olap4j.MondrianOlap4jStatement.parseQuery(MondrianOlap4jStatement.java:356)
... 8 more
Caused by: mondrian.olap.MondrianException: Mondrian Error:Internal error: While parsing SELECT NON EMPTY {CROSSJOIN({[Time].[All Time]},{{[Measures].[Revenue],[Measures].[Margin],[Measures].[Cost]}})} DIMENSION PROPERTIES CHILDREN_CARDINALITY, PARENT_UNIQUE_NAME ON COLUMNS, NON EMPTY {[Product].[All Product],[Product].[All Product].Children} DIMENSION PROPERTIES CHILDREN_CARDINALITY, PARENT_UNIQUE_NAME ON ROWS FROM (SELECT (Filter([Time].[All Time].MEMBERS, InStr([Time].[All Time].CurrentMember.MEMBER_CAPTION,"2014") > 0)) ON 0 FROM [Transaction]
at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:987)
at mondrian.olap.Util.newInternal(Util.java:2543)
at mondrian.olap.Util.newError(Util.java:2559)
at mondrian.parser.JavaccParserValidatorImpl.convertException(JavaccParserValidatorImpl.java:110)
at mondrian.parser.JavaccParserValidatorImpl.parseInternal(JavaccParserValidatorImpl.java:59)
at mondrian.olap.ConnectionBase.parseStatement(ConnectionBase.java:96)
... 13 more
Caused by: mondrian.olap.MondrianException: Mondrian Error:Syntax error at line 1, column 332, token '('
at mondrian.parser.JavaccParserValidatorImpl.convertException(JavaccParserValidatorImpl.java:99)
... 15 more
Rank 1
For problem 2)
Pivot grid does not display members under Include Fields option as well
to select. On selecting include fields for [Time].[All Time]
Pivot grid generates XMLA request as
<Discover
xmlns="urn:schemas-microsoft-com:xml-analysis"><RequestType>MDSCHEMA_MEMBERS</RequestType><Restrictions><RestrictionList><CATALOG_NAME>inventory</CATALOG_NAME><CUBE_NAME>Transaction</CUBE_NAME><LEVEL_UNIQUE_NAME>[Time].[All
Time].[(ALL)]</LEVEL_UNIQUE_NAME></RestrictionList></Restrictions><Properties><PropertyList><Catalog>inventory</Catalog></PropertyList></Properties></Discover>
while mondrian doesn't return any rows in its response below
<SOAP-ENV:Envelope SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Header>
</SOAP-ENV:Header>
<SOAP-ENV:Body>
<cxmla:DiscoverResponse>
<cxmla:return>
<root>
<xsd:schema targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" elementFormDefault="qualified">
<xsd:element name="root">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="row" type="row" minOccurs="0" maxOccurs="unbounded"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:simpleType name="uuid">
<xsd:restriction base="xsd:string">
<xsd:pattern value="[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:complexType name="row">
<xsd:sequence>
<xsd:element sql:field="CATALOG_NAME" name="CATALOG_NAME" type="xsd:string" minOccurs="0"/>
<xsd:element sql:field="SCHEMA_NAME" name="SCHEMA_NAME" type="xsd:string" minOccurs="0"/>
<xsd:element sql:field="CUBE_NAME" name="CUBE_NAME" type="xsd:string"/>
<xsd:element sql:field="DIMENSION_UNIQUE_NAME" name="DIMENSION_UNIQUE_NAME" type="xsd:string"/>
<xsd:element sql:field="HIERARCHY_UNIQUE_NAME" name="HIERARCHY_UNIQUE_NAME" type="xsd:string"/>
<xsd:element sql:field="LEVEL_UNIQUE_NAME" name="LEVEL_UNIQUE_NAME" type="xsd:string"/>
<xsd:element sql:field="LEVEL_NUMBER" name="LEVEL_NUMBER" type="xsd:unsignedInt"/>
<xsd:element sql:field="MEMBER_ORDINAL" name="MEMBER_ORDINAL" type="xsd:unsignedInt"/>
<xsd:element sql:field="MEMBER_NAME" name="MEMBER_NAME" type="xsd:string"/>
<xsd:element sql:field="MEMBER_UNIQUE_NAME" name="MEMBER_UNIQUE_NAME" type="xsd:string"/>
<xsd:element sql:field="MEMBER_TYPE" name="MEMBER_TYPE" type="xsd:int"/>
<xsd:element sql:field="MEMBER_GUID" name="MEMBER_GUID" type="uuid" minOccurs="0"/>
<xsd:element sql:field="MEMBER_CAPTION" name="MEMBER_CAPTION" type="xsd:string"/>
<xsd:element sql:field="CHILDREN_CARDINALITY" name="CHILDREN_CARDINALITY" type="xsd:unsignedInt"/>
<xsd:element sql:field="PARENT_LEVEL" name="PARENT_LEVEL" type="xsd:unsignedInt"/>
<xsd:element sql:field="PARENT_UNIQUE_NAME" name="PARENT_UNIQUE_NAME" type="xsd:string" minOccurs="0"/>
<xsd:element sql:field="PARENT_COUNT" name="PARENT_COUNT" type="xsd:unsignedInt"/>
<xsd:element sql:field="TREE_OP" name="TREE_OP" type="xsd:string" minOccurs="0"/>
<xsd:element sql:field="DEPTH" name="DEPTH" type="xsd:int" minOccurs="0"/>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
</root>
</cxmla:return>
</cxmla:DiscoverResponse>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
Rank 1
I will combine the answers of all questions in one message:
#1: Thank you for the log. I reviewed it, but I couldn't find out why it cannot parse the inner SELECT statement. I suppose is something with the configuration of the cube. Would it be possible to modify the query manually and see when the cube parses it? You can for instance run only the inner SELECT and see whether it runs (I suppose it will not). If it doesn't than you remove/modify part of it until the specific command causing the issue is pin-pointed.
#2: The purpose of the [current member].[(ALL)] request is to retrieve the member information for the top level member (ALL) member of the dimension. If the cube doesn't return anything for it then the TreeView will not be able to display anything. I would suggest you review your cube configuration and check why it does not have information about the ALL member.
#3: I am glad that you were able to resolve the issue.
Regards,
Georgi Krustev
Telerik
Rank 1
Hi Georgi,
Thanks for your suggestion.
I tried running query for problem 1 directly in the cube, but it's sad to find that mondrian does not support sub query at this point of time. I could do the same filter using following query.
"WITH MEMBER [Time].[All Time] AS 'Aggregate (Filter([Time].Members, InStr([Time].CurrentMember.Name,\"2014\") > 0))' SELECT NON EMPTY {CROSSJOIN({[Time].[All Time]},{{[Measures].[Revenue],[Measures].[Margin],[Measures].[Cost]}})} DIMENSION PROPERTIES CHILDREN_CARDINALITY, PARENT_UNIQUE_NAME ON COLUMNS, NON EMPTY {[Product].[All Product],[Product].[All Product].Children} DIMENSION PROPERTIES CHILDREN_CARDINALITY, PARENT_UNIQUE_NAME ON ROWS FROM [Transaction]";
So is there any that the generated query for filters be altered by pivotgrid?
Rank 1
For the problem 2) I found that mondrian requires top level members to be called as (All). Mondrian allows to give custom name for allLevel which i names it as (ALL).
Doing this helped in retrieving All level members. In my case for Product level it returned [All Product] successfully.
But on click of drill down next to All Product the request query is as follows,
<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
<Header/><Body><Discover xmlns="urn:schemas-microsoft-com:xml-analysis"><RequestType>MDSCHEMA_MEMBERS</RequestType><Restrictions><RestrictionList><CATALOG_NAME>inventory</CATALOG_NAME><CUBE_NAME>Transaction</CUBE_NAME><LEVEL_UNIQUE_NAME>[Product.Base Product].[(ALL)]</LEVEL_UNIQUE_NAME></RestrictionList></Restrictions><Properties><PropertyList><Catalog>inventory</Catalog></PropertyList></Properties></Discover></Body></Envelope>
but the
Rank 1
Sorry about the previous incomplete post. Please do discard it..
For the problem 2) I found that
mondrian requires top level members to be called as (All). Mondrian
allows to give custom name for allLevel which i names it as (ALL).
Doing this helped in retrieving All level members. In my case for Product level it returned [All Product] successfully.
But on click of drill down next to All Product
request query is as follows
<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/"><Header/><Body><Discover xmlns="urn:schemas-microsoft-com:xml-analysis"><RequestType>MDSCHEMA_MEMBERS</RequestType><Restrictions><RestrictionList><CATALOG_NAME>inventory</CATALOG_NAME><CUBE_NAME>Transaction</CUBE_NAME><MEMBER_UNIQUE_NAME>[Product.Base Product].[All Product]</MEMBER_UNIQUE_NAME><TREE_OP>1</TREE_OP></RestrictionList></Restrictions><Properties><PropertyList><Catalog>inventory</Catalog></PropertyList></Properties></Discover></Body></Envelope>
but mondrian returned only the following row in
xml response
<row><CATALOG_NAME>inventory</CATALOG_NAME><SCHEMA_NAME>inventory</SCHEMA_NAME><CUBE_NAME>Transaction</CUBE_NAME><DIMENSION_UNIQUE_NAME>[Product]</DIMENSION_UNIQUE_NAME><HIERARCHY_UNIQUE_NAME>[Product.Base Product]</HIERARCHY_UNIQUE_NAME><LEVEL_UNIQUE_NAME>[Product.Base Product].[(All)]</LEVEL_UNIQUE_NAME><LEVEL_NUMBER>0</LEVEL_NUMBER><MEMBER_ORDINAL>0</MEMBER_ORDINAL><MEMBER_NAME>All Product</MEMBER_NAME><MEMBER_UNIQUE_NAME>[Product.Base Product].[All Product]</MEMBER_UNIQUE_NAME><MEMBER_TYPE>2</MEMBER_TYPE><MEMBER_CAPTION>All Product</MEMBER_CAPTION><CHILDREN_CARDINALITY>8</CHILDREN_CARDINALITY><PARENT_LEVEL>0</PARENT_LEVEL><PARENT_COUNT>0</PARENT_COUNT><DEPTH>0</DEPTH></row>
Thus kendo ui is looping itself and throws following error:
error: {"faultstring":"XMLA Discover unparse results error","faultcode":"SOAP-ENV:Server.00HSBE02"}
How should the response xml be for the above request query? Any help on why mondrian does not respond with the desired response would be helpful.
I am afraid that I am an expert in Mondrian and I would not be able to provide any further details how the cube should be setup. As a reference you can refer to our online demos that uses MS SSAS and it follows the XMLA 1.1 specification.
If the Mondrian cube returns different responses, then you will need to setup it to work similarly to MS SSAS (at least regarding XMLA 1.1 standard).
Regards,
Georgi Krustev
Telerik