Null member causing "parse" error?

[John Gordon]

Ran into a situation where the mondrian olap gem is reporting a parse
error when executing a query. The issue seems to be when a member of
a level is specified in the WHERE clause of an mdx query when the
member doesn't exist:

mondrian.olap.MondrianException: Mondrian Error:Failed to parse query
'
with
member [Request Method].[Total] as 'Sum([Request Method].[All
Request Methods].Children)'
member [Topic].[Member Name] as [Encounter].CurrentMember.name
select {[Request Method].Children, [Request Method].[Total]} ON
COLUMNS,
TopCount({[Topic].Children}, 10.0, [Request Method].[Total])
ON ROWS
from [Encounters]

WHERE ([Encounter].[RFI],[Time End].[2011],[Location].[SOMERSET],
[Organization].[All Organizations])
'

I ran the same query in the pentaho bi-server. It ran successfully,
returning no data back. Here's the query run in the bi-server,
captured in the log4j log I have set up. Notice the #null in the
[Location] dimension:

with member [Request Method].[Total] as 'Sum([Request Method].[All
Request Methods].Children)'
member [Topic].[Member Name] as '[Encounter].CurrentMember.Name'
select {[Request Method].Children, [Request Method].[Total]} ON
COLUMNS,
TopCount({[Topic].Children}, 10.0, [Request Method].[Total]) ON ROWS
from [Encounters]
where ([Encounter].[RFI], [Time End].[2011], [Location].[#null],
[Organization].[All Organizations])

Do you know of a way to handle the issue of running a query when a
member doesn't exist?


Special Note: instead of using a star schema, the data has been
consolidated into one "facts" table. The Location dimension is
contained within it.

Thanks!

[Raimonds Simanovskis]

Yes, Mondrian error messages sometimes are not very explanatory - if you use some member full name in query that does not exist then it gives "failed to parse query" error.

I assume that Pentaho BI server sets mondrian.rolap.ignoreInvalidMembers property to true (see description of all properties at http://mondrian.pentaho.com/documentation/configuration.php) which tells Mondrian to ignore missing members. So to achive that you should do

java.lang.System.setProperty("mondrian.rolap.ignoreInvalidMembers", "true")

before requiring "mondrian-olap" in your application (don't know exactly at which point Mondrian is reading these properties therefore the safest way is to set them before requiring it).

Raimonds

[John Gordon]

Excellent. Thanks for the info.  I added these settings to application.rb and now things are running fine when invalid members are passed.  For posterity, this is my code:

#file config/application.rb

require File.expand_path('../boot', __FILE__)

require 'rails/all'

require 'java'

....

Bundler.require(:default, Rails.env) if defined?(Bundler)

module MhlrcRails

  class Application < Rails::Application

.....

  java.lang.System.setProperty("mondrian.rolap.ignoreInvalidMembers", "true")

  java.lang.System.setProperty("mondrian.rolap.ignoreInvalidMembersDuringQuery", "true")

  end

end

I'm pretty new to rails, so this may not be the most ideal place to put these settings.  Regardless, it works!

Thanks again!

John

-- 
John Gordon

[Raimonds Simanovskis]

I'm doing in the following way. In Gemfile I specify

gem 'mondrian-olap', :git => 'git://github.com/rsim/mondrian-olap.git', :require => false

which means that mondrian-olap will be added to load path by Bundler but will not be yet required.

And then I have separate initializer file config/initializers/mondrian.rb and there you can put e.g.

require "java"

{

  "mondrian.rolap.ignoreInvalidMembers" => "true",

  "mondrian.rolap.ignoreInvalidMembersDuringQuery" => "true"

}.each do |key, value|

  java.lang.System.setProperty(key, value)

end

require "mondrian-olap"

Which will set all necessary properties and then will load mondrian-olap.

This is more in Rails recommended way how to group different initialization settings into separate initializers.

BTW in my application I am setting mondrian.native.filter.enable to false as a workaround for bug http://jira.pentaho.com/browse/MONDRIAN-932

Raimonds

[John Gordon]

Implemented!  Thanks!

-- 
John Gordon

[ELKHATTABI Imane]

Hello everyone,

Excuse me for commenting here, this discussion has few years now.

But i am facing the same problem with pentaho schema workbench. I managed to design the cube and publish it.

But one i want to do a mdx  query. it displays this message  ; ERROR pasing mdx query '...' Null!

I would like to knwo the exact file where i can modify the "ingnoreInvalidMembers" property.

Could anyone please help?

Thanks.

[Raimonds Simanovskis]

Hi

Set the corresponding Java properties before requiring "mondrian-olap" and creating mondrian-olap connection:

java.lang.System.setProperty "mondrian.rolap.ignoreInvalidMembers", "true"

java.lang.System.setProperty "ignoreInvalidMembersDuringQuery", "true"

In our Rails application in Gemfile we specify

gem 'mondrian-olap', git: 'git://github.com/rsim/mondrian-olap.git', require: false

and then we have a mondrian.rb initializer file where we set all necessary Mondrian options and then require "mondrian-olap":

  {

    # If set to true, during schema load, invalid members are ignored and will be treated as a null member if they are later referenced in a query

    "mondrian.rolap.ignoreInvalidMembers" => true,

    # If set to true, during query validation, invalid members are ignored and will be treated as a null member.

    "mondrian.rolap.ignoreInvalidMembersDuringQuery" => true,

    # ... other options ...

  }.each do |key, value|

    java.lang.System.setProperty(key, value.to_s)

  end

  # set JRuby verbose option to see Java stack trace if mondrian-olap fails to load

  enable_warnings do

    require "mondrian-olap"

  end

Kind regards,

Raimonds