This is a migrated thread and some comments may be shown as answers.

PivotGrid with Mondrian can't include fields and fields filter

9 Answers 373 Views
PivotGrid
This is a migrated thread and some comments may be shown as answers.
Gowtham
Top achievements
Rank 1
Gowtham asked on 28 Sep 2015, 08:36 PM

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

Sort by
0
Georgi Krustev
Telerik team
answered on 30 Sep 2015, 12:07 PM
Hello Gowtham,

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
 
Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
 
0
Gowtham
Top achievements
Rank 1
answered on 30 Sep 2015, 08:58 PM

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:

Caused by: mondrian.olap.MondrianException: Mondrian Error:Failed to parse query '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.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 

0
Gowtham
Top achievements
Rank 1
answered on 30 Sep 2015, 09:24 PM

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>

0
Gowtham
Top achievements
Rank 1
answered on 30 Sep 2015, 10:53 PM
@Georgi Krustev your suggestion for 3rd problem helped. Mondrian does not mandate giving name for hierarchy for a dimension if they have only one hierarchy within that dimension. In such case, mondrian takes name of the hierarchy same as the name of the ​dimension. Since we had only one hierarchy for [Time] and [Product] dimension, we did not give explicit name to these hierarchies. ​Now on ​setting Hierarchy names in the schema, helped fixing problem 3.
0
Georgi Krustev
Telerik team
answered on 01 Oct 2015, 01:04 PM
Hello Gowtham,

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
 
Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
 
0
Gowtham
Top achievements
Rank 1
answered on 02 Oct 2015, 04:02 PM

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?

 

0
Gowtham
Top achievements
Rank 1
answered on 06 Oct 2015, 03:52 AM

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

0
Gowtham
Top achievements
Rank 1
answered on 06 Oct 2015, 04:02 AM

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.

 

 â€‹

 

 

0
Georgi Krustev
Telerik team
answered on 06 Oct 2015, 12:10 PM
Hello Gowtham,

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
 
Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
 
Tags
PivotGrid
Asked by
Gowtham
Top achievements
Rank 1
Answers by
Georgi Krustev
Telerik team
Gowtham
Top achievements
Rank 1
Share this question
or