Error Processing on Pentaho CDE dashboard for MDX query widget

256 views
Skip to first unread message

Sumit Bansal

unread,
Apr 20, 2015, 2:03:06 PM4/20/15
to pentaho-...@googlegroups.com
Hi Group,

I am facing error processing on some widgets while loading dashboards on my application randomly.
In the Catalina.log, for one or two widgets, it is showing :
mondrian.olap.MondrianException: Mondrian Error:Internal error: Query required more than 12 iterations

and for one or two widgets, it is showing :
java.lang.NullPointerException

but if I select the same dashboard again, it'll run perfectly. It is behaving very flaky.

to fix iteration error, I did :
mondrian.rolap.evaluate.MaxEvalDepth to 50 in mondrian.properties , which is 10 by default

This got me rid of iteration error, but resulted in java.lang.NullPointerException for other two widgets permanantly except for default loading of dashboard. Due to which, I considered that the mondrian cahce is getting overflowed or corrupted.

To fix this, I did
maxElementsInMemory to 20000 for mondrian-catalog-cache in ehcache.xml ,which is 30 by default
which solved the continous occurrennce of java.lang.NullPointerException,
but situation again is same , i.e. dashboards are behaving flaky as earilier.

Also , i would like to bring in notice that it is working perfect for sql queries.
Any useful inputs from anyone ???, as i have seen on forum , many people have faced same problems.

Regards
Sumit Bansal

Nelson Sousa

unread,
Apr 20, 2015, 3:13:49 PM4/20/15
to pentaho-...@googlegroups.com

It seems that you're going over every limit in Mondrian. Those limits exist for a reason, if you hit them you may have a query that is too complex. I've seen some complex mdx queries and never hit that kind of limit.

I suggest you simplify your mdx queries, that doesn't sound healthy.

And yes, if it's a Mondrian error only, it makes sense that in sql there's no such problem.

However, that creates a new and perhaps more interesting question: what exactly are you trying to do that gets more complex in MDX than in SQL?

Regards,


Nelson Sousa
Business Intelligence Consultant


--
You received this message because you are subscribed to the Google Groups "Pentaho Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pentaho-commun...@googlegroups.com.
To post to this group, send email to pentaho-...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/pentaho-community/e083f838-575f-4638-83a6-685288013ede%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Sumit Bansal

unread,
Apr 21, 2015, 4:13:33 AM4/21/15
to pentaho-...@googlegroups.com
Hi Nelson,

Thanks for your suitable response.

we were earlier running the dashboard with default mondrain setting only when these error occured for the first time and then we tried to modify the above mentioned properties.

Also ,Here is the following query, for which I am getting nullpointerexception randomly:

WITH
    SET [select_period] AS
        ([time.financial].[fin_day].[${parDate}]:[time.financial].[fin_day].[${partoDate}])
   
    SET [cig_factory] AS
        {[organization.organization_hierarchy].[inventory_unit].[Factory1],[organization.organization_hierarchy].[inventory_unit].[Factory2],[organization.organization_hierarchy].[inventory_unit].[Factory3]}
       
    SET [batch] AS
        IIF('${parBatch}' = 'All',
                [batch_type].[batch_type].members,
                [batch_type].[batch_type].[${parBatch}]
           )

    MEMBER [Measures].[eff] AS
         ROUND((SUM([batch],[Measures].[prod_qty])/ SUM([batch],[Measures].[possible_prod]))*100,2)

SELECT
NON EMPTY {[cig_factory]} ON COLUMNS,
NON EMPTY {[select_period]}ON ROWS
FROM [production_analysis]
WHERE ([department.departmentwise].[CMD], [Measures].[eff])

Is it actually a complex query?? if so, please guide me to  optimize it or some other solution

Regards
Sumit

Nelson Sousa

unread,
Apr 21, 2015, 4:59:23 AM4/21/15
to pentaho-...@googlegroups.com

How many days do you have in that date range when you get the error?

Also, how many members do you have in [batch_type].[batch_type].members?

As for optimizing it, you don't need that [Measure].[eff], as you can do it instead by aggregating the [batch_type] members and then doing the division:


WITH 
    SET [select_period] AS
        ([time.financial].[fin_day].[${parDate}]:[time.financial].[fin_day].[${partoDate}])
    
    SET [cig_factory] AS
        {[organization.organization_hierarchy].[inventory_unit].[Factory1],[organization.organization_hierarchy].[inventory_unit].[Factory2],[organization.organization_hierarchy].[inventory_unit].[Factory3]}
        
    Member [batch_type].[Selected] AS 
    Aggregate(    IIF('${parBatch}' = 'All',

                [batch_type].[batch_type].members,
                [batch_type].[batch_type].[${parBatch}]
           )
    )

    MEMBER [Measures].[eff] AS
         ROUND([Measures].[prod_qty])/ [Measures].[possible_prod]*100,2)


SELECT
NON EMPTY {[cig_factory]} ON COLUMNS,
NON EMPTY {[select_period]}ON ROWS
FROM [production_analysis]
WHERE ([department.departmentwise].[CMD], [Measures].[eff], [batch_type].[Selected])


As I've written it you don't actually have to compute the batch_type set and the value of the measure for each batch_type. Assuming the two measures are aditive (aggregator="sum" in the schema), the result is the same.


Regards,

Nelson Sousa
Business Intelligence Consultant


To view this discussion on the web visit https://groups.google.com/d/msgid/pentaho-community/710b7432-8cc2-4d1e-adc9-9ded496031e9%40googlegroups.com.

Sumit Bansal

unread,
Apr 21, 2015, 5:51:00 AM4/21/15
to pentaho-...@googlegroups.com
thanks again for your such a valuable inputs.

sometimes it gives error just for selecting a single day only, and i have only 6 members in batch_type .

Now, when I run this query on saiku , it results in no data found , reason behind that , i think is ,that, for [cig_factory],  [batch_type].[Selected]  value does not exist because if I simply select [Measure].[eff] for  [batch_type].[Selected] , like:


WITH 
    SET [select_period] AS
        ([time.financial].[fin_day].[${parDate}]:[time.financial].[fin_day].[${partoDate}])
    
    SET [cig_factory] AS
        {[organization.organization_hierarchy].[inventory_unit].[Factory1],[organization.organization_hierarchy].[inventory_unit].[Factory2],[organization.organization_hierarchy].[inventory_unit].[Factory3]}
        
    Member [batch_type].[Selected] AS 
    Aggregate(    IIF('${parBatch}' = 'All',

                [batch_type].[batch_type].members,
                [batch_type].[batch_type].[${parBatch}]
           )
    )

    MEMBER [Measures].[eff] AS
         ROUND(([Measures].[prod_qty]/ [Measures].[possible_prod])*100,2)


SELECT 
NON EMPTY {[batch_type].[Selected]}ON COLUMNS,
NON EMPTY {[select_period]} ON ROWS
FROM [production_analysis]
WHERE ([department.departmentwise].[CMD],[Measures].[eff])

it'll show the result.

There might be the reason that in mondrian , member creation like this is not efficiently supported yet

Regards 
Sumit Bansal

Nelson Sousa

unread,
Apr 21, 2015, 5:53:46 AM4/21/15
to pentaho-...@googlegroups.com

Member creation in Mondrian has been around for years. I use it in almost every single query I run. That's not the issue.

But if you got it working, it's fixed.

You should take a look at the mondrian logs to debug that kind of thinkg, otherwise you're debugging blind (need to enable them in log4j.xml, though).

Regards,
Nelson

For more options, visit https://groups.google.com/d/optout.


--

Sumit Bansal

unread,
Apr 21, 2015, 6:21:20 AM4/21/15
to pentaho-...@googlegroups.com
Thanks for your instant and such an appreciable inputs and sorry for troubling you again .

But this din't worked for me as I need the result for [cig_factory]  in select and [batch_type].[Selected] in where clause , same as you created the query for me .. but that is resulting into "no data" , which is not correct. 
I am quite new to MDX and mondrian , and stuck on this point , so please guide me if possible.

I have already enabled logs but not getting any error there for the query except in catalina.log as java.lang.nullpointerexception. 
To unsubscribe from this group and stop receiving emails from it, send an email to pentaho-community+unsubscribe@googlegroups.com.
To post to this group, send email to pentaho-community@googlegroups.com.

Nelson Sousa

unread,
Apr 21, 2015, 6:27:45 AM4/21/15
to pentaho-...@googlegroups.com

If you're new to mdx and mondrian, I suggest you read the documentation first, before going any further. 

There's nothing more I can do for you now.

Regards,

Nelson Sousa
Business Intelligence Consultant


Nelson


--
Nelson Sousa
Business Intelligence Consultant


--
You received this message because you are subscribed to the Google Groups "Pentaho Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pentaho-commun...@googlegroups.com.
To post to this group, send email to pentaho-...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/pentaho-community/2ecc6f65-37b2-4ae3-accf-38e365157cd6%40googlegroups.com.

Sumit Bansal

unread,
Apr 21, 2015, 6:35:43 AM4/21/15
to pentaho-...@googlegroups.com
Thanks for giving your valuable time and I really appreciate for that.

Regards
Sumit
Nelson
To unsubscribe from this group and stop receiving emails from it, send an email to pentaho-community+unsubscribe@googlegroups.com.
To post to this group, send email to pentaho-community@googlegroups.com.


--
Nelson Sousa
Business Intelligence Consultant


Reply all
Reply to author
Forward
0 new messages