rsim / mondrian-olap Goto Github PK

Hello,

I am attempting to retrieve a list properties of a level (filtered by annotations on the property), so an admin user can select the property to filter by. But I do not want all the properties to be viewable to the user. For example, I do not want to show the StandardMemberProperties or certain other properties that I have added. For that I have added an annotation on the properties that I wish to show to the user. I retrieve the properties on the level via the first member on that level, but am unable to access the annotations from there. Do you have any ideas on how I may do this? Any help would be greatly appreciated.

Thank you!

• Herschel

Cannot establish connection with db mysql using jdbc-mysql-8.0.17, error:
Mondrian::OLAP::Error: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while creating SQL connection: Jdbc=jdbc:mysql://localhost/chu_dw?useUnicode=yes&characterEncoding=UTF-8; JdbcUser=root
wrap_native_exception at C:/jruby-9.2.12.0/lib/ruby/gems/shared/gems/mondrian-olap-1.1.0/lib/mondrian/olap/error.rb:24
connect at C:/jruby-9.2.12.0/lib/ruby/gems/shared/gems/mondrian-olap-1.1.0/lib/mondrian/olap/connection.rb:21
create at C:/jruby-9.2.12.0/lib/ruby/gems/shared/gems/mondrian-olap-1.1.0/lib/mondrian/olap/connection.rb:6
Jruby and JDK:
jruby 9.2.12.0 (2.5.7) 2020-07-01 db01a49ba6 Java HotSpot(TM) 64-Bit Server VM 25.241-b07 on 1.8.0_241-b07 +jit [mswin32-x86_64]

is there any solution for this problem?

thank you.

Hi @rsim,

Do you have plans to support Mondrian 4 in mondrian-olap?

What is the best approach to work with multiple timezones?
I have dim_calendar and dim_timer dimensions (UTC), but depending of the user, I need to change the timezone to calculate and display data.

I did using KeyExpression

EXTRACT(
 HOUR FROM (
   time at time zone 'america/sao_paulo'
 ))

But, I don't know if is the best way..

Is it possible to pass the timezone on schema, olap.execute or before run the mdx queries (SET TIMEZONE='america/sao_paulo');

Thanks

Cannot establish the DB connection if jdbc-mysql ver. 5.1.37 is used.

Hey guys! I'm new in olap, and I trying to get the dimension it include a dimension call 'Measures' is that ok?
PS The schema does not define this dimension
Also I was reading about 'dimensions', and I find out there are public and private dimensions is there a possibility that 'Measures' is a private dimension?

We are looking at rolling a gem that helps in building aggregate tables on top of mondrian-oalp but need the master branch code to have it run, but we can't put a gemspec dependency on a github branch (the way we can in the Gemfil), any chance a stable version is going to be released soon? what can we do to help make that happen? (we think it is quite stable from our use of it)

thanks,
bsd

Defining degenerate dimension like that

dimension name: "Finance Source" do
  hierarchy do
    level column: "finsource", unique_members: true
  end
end

leads to error Mondrian::OLAP::Error: mondrian.olap.MondrianException: Mondrian Error:Internal error: while parsing catalog null

everything done like that example from mondrian docs

here https://github.com/rsim/mondrian-olap/blob/master/lib/mondrian/olap/schema.rb#L142
it is said that if primary_key of hierarchy is not specified, the key of the lowest level is used, but in mondrian docs:

Note that because there is no join, the foreignKey attribute of Dimension is not necessary, and the Hierarchy element has no Table child element or primaryKey attribute.

Is there a way to define degenerate dimension?

What is the time line for upgrading to Mondrian 0.3.3? release notes http://jira.pentaho.com/secure/ReleaseNote.jspa?projectId=10167&version=10918

I believe I am encountering a bug with 0.3.0 (which is the current ver linked against with the mondrian-olap gem)

What would be required to upgrade the mondrian dependency to the latest stable release? The mondrian docs say there are no api changes, but it would be nice to have access to some of the fixes in the newer version. Is it just a matter of updating the other dependent jars?

Olap::Query allows you to use 'CrossJoin' on an axis or inside a named set (using #with_set). You sometimes need to use 'CrossJoin' in a 'where' clause, but Olap::Query does not currently support that functionality.

Example query:

olap.from('Sales').
  columns('[Measures].[Unit Sales]', '[Measures].[Store Sales]').
  rows('[Products].children').
  where('[Customers].[USA]').
  crossjoin('[Time].[2011].[Q1]', '[Time].[2011].[Q2]').
  to_mdx

Actual output:

SELECT {[Measures].[Unit Sales], [Measures].[Store Sales]} ON COLUMNS,
  CROSSJOIN([Products].children, {[Time].[2011].[Q1], [Time].[2011].[Q2]}) ON ROWS
FROM [Sales]
WHERE ([Customers].[USA])

Desired output:

SELECT {[Measures].[Unit Sales], [Measures].[Store Sales]} ON COLUMNS,
  [Products].children ON ROWS
FROM [Sales]
WHERE (CROSSJOIN([Customers].[USA], {[Time].[2011].[Q1], [Time].[2011].[Q2]}))

In its current implementation, #crossjoin always applies its arguments to @current_set:

@current_set is always the last axis or named set that was referenced when building the query. In the "actual output" section above, you can see that the 'CrossJoin' is applied to 'rows' (since it was the last axis referenced), despite the fact that #crossjoin was called immediately after #where

Hey,

it is possible to use the "ON PAGES" statement in a MDX-Query?
I've tried this and i get an error:

@result = olap.execute <<-MDX 
SELECT 
{[CallChannel].[HTTP],[CallChannel].[WEB]} ON COLUMNS, 
{[ServiceProvider].children}  ON ROWS, 
([TroubleType].children)  ON PAGES 
FROM [Sales] 
MDX 

and tried it with query builder methods but i didn't work for me. I only found one spec which use pages(), which doesn't use columns and rows in the test.

How look the result of a query with three axis?

Queries which return no results throw:

   #<NativeException: mondrian.olap.MondrianException: Mondrian Error:Failed to parse query '.....'>

Hi!

1. I love your work on mondrian-olap. Thanks for making it.

2. While performing MDX query I get following exception...

org.olap4j.OlapException: mondrian gave exception while parsing query

Can you perhaps tell me how to debug problem with query?
How can I see what exactly is wrong with my query? Where can I get more details about exception?

Thanks and have a nice day!

I'm pretty much a newbie to (j)ruby. I figured I'd try to get cube_spec.rb to run, and use that as a model during development.

When I try to run it I get the following:

$ jruby cube_spec.rb 
/usr/lib/jruby//lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `require': no such file to load -- bundler (LoadError)
    from /usr/lib/jruby//lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `require'
    from ./spec_helper.rb:2
    from ./spec_helper.rb:1:in `require'
    from cube_spec.rb:1

From my grokking, it looks like bundler is missing, yet gems claims it isn't :

$ jruby -S gem list --local

*** LOCAL GEMS ***

bundler (1.0.21)
diff-lcs (1.1.3)
json (1.6.1)
mondrian-olap (0.3.0)
nokogiri (1.5.0)
rdoc (3.11)
rspec (2.7.0)
rspec-core (2.7.1)
rspec-expectations (2.7.0)
rspec-mocks (2.7.0)

I know this is slightly OT, but I just need help getting the environment figured out (ie do I need to use rvm)?

Hi,
Are there any tips of this error? It happens only when trying to run master, 0.3.0 works fine here. It happens even on console, attempting to require the lib.

thanks,
Pedro

rake spec:mysql

/home/pedro/.rvm/rubies/jruby-1.6.7/bin/jruby -S rspec ./spec/schema_definition_spec.rb ./spec/connection_spec.rb ./spec/query_spec.rb ./spec/cube_spec.rb ./spec/mondrian_spec.rb

==> Using mysql driver
NameError: cannot initialize Java class mondrian.olap4j.MondrianOlap4jDriver
  get_proxy_or_package_under_package at org/jruby/javasupport/JavaUtilities.java:54
                      method_missing at /home/pedro/.rvm/rubies/jruby-1.6.7/lib/ruby/site_ruby/shared/builtin/javasupport/java.rb:51

Mind releasing a new gem to rubygems.org? I'm looking for a version with log4j-2.17.1.

As of Version 4 Mondrian has supported the concept of Attributes: http://mondrian.pentaho.com/schema.html#Attributes_versus_Hierarchies

I have been unable to find support for these Attributes in the code base. Is there a workaround solution or will you be able to add support for this functionality?

Hi,

By any chance, would you have any tip on the following error occuring?
Mondrian Error:Failed to load formatter class 'null' for level '[Priorities].[Priority]'.

It only happens in a production environment (trinidad). Really odd.

thanks a lot if you have nay hints on this.

Long time listener and big fan. I was wondering if it was possible to connect to Snowflake data warehouse? If possible what would this configuration look like? Any guidance would be greatly appreciated.

Hi!

Mac OS 10.11.4, jruby-9.1.1.0 [ x86_64 ], Rails 4.1.5
Gemfile:

platforms :jruby do
  gem 'jruby-openssl'
  gem 'activerecord-jdbc-adapter'
  gem 'activerecord-jdbcpostgresql-adapter'
  # gem 'activerecord-oracle_enhanced-adapter'

  gem 'mondrian-olap', :require => false
end

database.yml:

development:
  adapter: postgresql
  encoding: unicode
  database: ecrm
  pool: 20
  username: ecrm
  password: ecrm
  host: 127.0.0.1

console:

[1] pry(main)> cube = Dwh.olap.cube('Sales')
WARN  mondrian.olap.MondrianProperties  - mondrian.properties can't be found under '/Users/artempankov/RubymineProjects/ecrm/.' or classloader
WARN  mondrian.rolap.RolapUtil  - Mondrian: Warning: JDBC driver org.postgresql.Driver not found
WARN  mondrian.rolap.RolapUtil  - Mondrian: Warning: JDBC driver sun.jdbc.odbc.JdbcOdbcDriver not found
WARN  mondrian.rolap.RolapUtil  - Mondrian: Warning: JDBC driver org.hsqldb.jdbcDriver not found
WARN  mondrian.rolap.RolapUtil  - Mondrian: Warning: JDBC driver oracle.jdbc.OracleDriver not found
WARN  mondrian.rolap.RolapUtil  - Mondrian: Warning: JDBC driver com.mysql.jdbc.Driver not found
Mondrian::OLAP::Error: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while creating SQL connection: Jdbc=jdbc:postgresql://127.0.0.1/ecrm; JdbcUser=ecrm; JdbcPassword=ecrm
from root cause: java.sql.SQLException: No suitable driver found for jdbc:postgresql://127.0.0.1/ecrm

Application works good, but not Mondrian
How can i get postgres driver visible to Mondrian?

Four specs related to Coffee Script are failing:

rspec ./spec/schema_definition_spec.rb:838 # Schema definition elements User defined functions and formatters in CoffeeScript should execute user defined function
rspec ./spec/schema_definition_spec.rb:845 # Schema definition elements User defined functions and formatters in CoffeeScript should format members and properties
rspec ./spec/schema_definition_spec.rb:855 # Schema definition elements User defined functions and formatters in CoffeeScript should execute user defined property on member
rspec ./spec/schema_definition_spec.rb:864 # Schema definition elements User defined functions and formatters in CoffeeScript should execute user defined method on member

I think the root cause of this is this change in the coffee-script gem:

https://github.com/josh/ruby-coffee-script/commit/4a4bb56dca40b59bd61705ac47d31b072bec1458#diff-0

This replaced introduced ExecJS (replacing therubyracer). The latest ExecJS expects therubyrhino to implement to_ruby. That method was only introduced in 1.7.1 of therubyrhino. However, mondrian-olap has an explicit dependency on 1.7.0.

This can be fixed by changing the explicit therubyrhino dependency to 1.7.1.

Hello!

Just tried the new version with mondrian 3.5 but I get an exception right when trying to connect (code works with older commit). I didn't see a commit updating olap4j.jar or other dependencies, just wanted to make sure there is nothing missing.. Thanks!

Hi,

I have the measure (postgres, m_price, int8) like this:

  measure 'Price', column: 'm_price', aggregator: 'sum' format_string: '#,###.00'

But, the result is:

"values":[["0.149e4"],["0.873e5"],["0.19798e5"],["0.776e5"],["0.19282e7"],["0.974667e6"],["0.14370531e9"],["0.189603291e9"]]}

If I run the query on Pentaho Workbench Schema, I have the right results,

Row #0: 1.490,00
Row #1: 87.300,00
Row #2: 19.798,00
Row #3: 77.600,00
Row #4: 1.928.200,00
Row #5: 974.667,00
Row #6: 143.705.310,00
Row #7: 189.603.291,00
```

thanks,

When trying to apply a filter on a dimension, which is comprised of integers, I receive the following error.

Mondrian::OLAP::Error: org.olap4j.OlapException: mondrian gave exception while parsing query
from root cause: org.postgresql.util.PSQLException: ERROR: function upper(integer) does not exist

Any way to avoid this?

BTW fantastic work on the gem.

How does one go about setting mondrian java configuration properties? I put a mondrian.properties file in the lib/mondrian/jars directory but I can't be certain it's being used.

It would be great to have support for MDX DISTINCT operation in the query generator.

I'm trying to implement it, but I'm getting an error when running rake db:create_data MONDRIAN_DRIVER=postgresql for setting up my spec environment:

NoMethodError: undefined method `get_oid_type' for #<ActiveRecord::ConnectionAdapters::JdbcAdapter:0x3aeb625c>
/Users/manuel/.rvm/gems/jruby-1.7.19/gems/activerecord-jdbc-adapter-1.3.17/lib/arjdbc/postgresql/adapter.rb:1257:in `columns'
/Users/manuel/.rvm/gems/jruby-1.7.19/gems/activerecord-jdbc-adapter-1.3.17/lib/arjdbc/postgresql/adapter.rb:1251:in `columns'
/Users/manuel/.rvm/gems/jruby-1.7.19/gems/activerecord-4.2.3/lib/active_record/connection_adapters/schema_cache.rb:43:in `columns'
/Users/manuel/.rvm/gems/jruby-1.7.19/gems/activerecord-4.2.3/lib/active_record/attributes.rb:93:in `columns'
/Users/manuel/.rvm/gems/jruby-1.7.19/gems/activerecord-4.2.3/lib/active_record/attributes.rb:98:in `columns_hash'
/Users/manuel/.rvm/gems/jruby-1.7.19/gems/activerecord-4.2.3/lib/active_record/inheritance.rb:205:in `subclass_from_attributes?'
/Users/manuel/.rvm/gems/jruby-1.7.19/gems/activerecord-4.2.3/lib/active_record/inheritance.rb:54:in `new'
/Users/manuel/.rvm/gems/jruby-1.7.19/gems/activerecord-4.2.3/lib/active_record/persistence.rb:50:in `create!'
/Users/manuel/Work/cosas/mondrian-olap/spec/rake_tasks.rb:126:in `(root)'
/Users/manuel/Work/cosas/mondrian-olap/spec/rake_tasks.rb:125:in `(root)'
/Users/manuel/.rvm/gems/jruby-1.7.19/bin/jruby_executable_hooks:15:in `(root)'

The readme mentions shared dimensions, which in the XML schema should just be a dimension element outside of any cube element, but dimension is only supported in cube block in the ruby DSL. Are shared dimensions declared some other way in the ruby schema or does the ruby schema not support shared dimensions yet (in which case the readme should probably be clarified.)?

Does it make sense to wait for jruby 9.0 support in near future?

@rsim I've been scouring the net looking for some configurations (Mondrian properties, Tomcat memory configurations, System CPU, etc...) that will allow the olap/mondrian library to perform at it's peak against a large data warehouse. Do you have any recommendations as to what settings would help with this.

Hi, I'm having the following issue, when I try to erase the mondrian cache I use something like this:

olap_connection = Mondrian::OLAP::Connection.create(
       :driver => MONDRIAN_CREDENTIALS['driver'],
       :host => MONDRIAN_CREDENTIALS['host'],
       :database => MONDRIAN_CREDENTIALS['database'],
       :username => MONDRIAN_CREDENTIALS['username'],
       :password => MONDRIAN_CREDENTIALS['password'],
       :catalog_content => MONDRIAN_CREDENTIALS['catalog_content']
     )
olap_connection.flush_schema_cache

But, the app is still return the same data, looks like the cache is not being erased.

Do you hava any clue or idea what the problem can be?

Thanks for your help

Would it be possible to upload a new release to RubyGems (0.7.1 maybe?). There's been a number of useful fixes and dependency updates since b59e02a (last stable revision)

Glad to find this gem! We're working with Mondrian now, and would like to use properties in a hierarchy. I see that you support that in the latest commits, but not in any officially released version of the gem.

When could we look forward to a release?

I'm trying to upgrade the jars from 3.6 to 3.7 but it's throwing a strange error when I try to create the tables for the specs. I downloaded mondrian-3.7.0.0-752.zip from http://sourceforge.net/projects/mondrian/files/mondrian/mondrian-3.7.0/, unziped it and copied the jars to the jars directory.

I used bundle, created the database and when I try to create the tables:

rake db:create_data MONDRIAN_DRIVER=postgresql

This error is thrown:

==> Using postgresql driver
rake aborted!
NameError: missing class or uppercase package name (`mondrian.olap4j.MondrianOlap4jDriver'), caused by (NameError) cannot link Java class mondrian.olap4j.MondrianOlap4jDriver, probable missing dependency: mondrian/olap4j/MondrianOlap4jDriver : Unsupported major.minor version 51.0
org/jruby/javasupport/JavaUtilities.java:54:in `get_proxy_or_package_under_package'
file:/Users/jphorta/.rvm/rubies/jruby-1.7.12/lib/jruby.jar!/jruby/java/java_package_module_template.rb:14:in `method_missing'
/Users/jphorta/Projects/mondrian-olap/lib/mondrian/olap.rb:14:in `(root)'
/Users/jphorta/.rvm/gems/jruby-1.7.12/gems/activesupport-4.1.2/lib/active_support/dependencies.rb:247:in `require'
/Users/jphorta/.rvm/gems/jruby-1.7.12/gems/activesupport-4.1.2/lib/active_support/dependencies.rb:232:in `load_dependency'
/Users/jphorta/.rvm/gems/jruby-1.7.12/gems/activesupport-4.1.2/lib/active_support/dependencies.rb:247:in `require'
/Users/jphorta/Projects/mondrian-olap/spec/spec_helper.rb:1:in `(root)'
/Users/jphorta/Projects/mondrian-olap/spec/spec_helper.rb:69:in `(root)'
/Users/jphorta/Projects/mondrian-olap/spec/rake_tasks.rb:1:in `(root)'
/Users/jphorta/Projects/mondrian-olap/spec/rake_tasks.rb:3:in `(root)'
/Users/jphorta/.rvm/gems/jruby-1.7.12/bin/jruby_executable_hooks:15:in `(root)'
Tasks: TOP => db:create_data => db:create_tables => db:require_spec_helper

Am I missing something? Shouldn't be enough to replace the 3.6 jars with the 3.7?

Hi, I am getting nil values when trying to use a second heirarchy with my time dimension.
Here is what I have in the schema for the Date dimension.

dimension 'Date', foreign_key: 'created_at', type: 'TimeDimension' do
  hierarchy has_all: false, primary_key: 'id' do
    table 'dates'
    level 'Year', column: 'year', type: 'Numeric', unique_members: true, level_type: 'TimeYears'
    level 'Month', column: 'month', type: 'Numeric', unique_members: false, name_column: "month_name", level_type: 'TimeMonths'
    level 'Day', column: 'day_of_month', type: 'Numeric', unique_members: false, level_type: 'TimeMonths'
  end
  hierarchy 'Weekly', has_all: false, primary_key: 'id' do
    table 'dates'
    level 'Year', column: 'year', type: 'Numeric', unique_members: true, level_type: 'TimeYears'
    level 'Week', column: 'week', type: 'Numeric', unique_members: false, level_type: 'TimeWeeks'
  end
end

When I run following the query it returns [[nil], [nil], [nil], [nil]].

SELECT {[Measures].[Total Revenue]} ON COLUMNS,
{[Date.Weekly].[2016].[40], [Date.Weekly].[2016].[41], [Date.Weekly].[2016].[42], [Date.Weekly].[2016].[43]} ON ROWS
FROM [Sales]

If I change the schema to:

dimension 'Date', foreign_key: 'created_at', type: 'TimeDimension' do
  hierarchy  has_all: false, primary_key: 'id' do
    table 'dates'
    level 'Year', column: 'year', type: 'Numeric', unique_members: true, level_type: 'TimeYears'
    level 'Week', column: 'week', type: 'Numeric', unique_members: false, level_type: 'TimeWeeks'
  end
end

I get values when I run the query:

SELECT {[Measures].[Total Revenue]} ON COLUMNS,
{[Date].[2016].[40], [Date].[2016].[41], [Date].[2016].[42], [Date].[2016].[43]} ON ROWS
FROM [Sales]

I have tried the same type of query using the demo server it returned values, as far as I can tell the main difference between the two servers is that my database is on Amazon Redshift.

Is there something I am doing wrong in the schema? Is this an issue with Amazon Redshift?
Also, is there a way to get the SQL generated from the MDX queries?

Are bridge cubes unsupported in the schema at the moment?

The readme doesn't say nothing about it, and in the schema DimensionUsage there is no bridge_cube attribute, so the most probable answer is no.

Is this attribute something already on the way to be supported?

I've ran into the need to provide additional parameters to the connection string. In particular, I needed to provide a custom DynamicSchemaProcessor to Mondrian. I couldn't find any API method to do it, so I resorted to monkey-patching:

module Mondrian::OLAP
  class Connection
    alias_method :orig_connection_string, :connection_string

    def connection_string
      cs = orig_connection_string
      cs << ';DynamicSchemaProcessor=schema_processor_dataserver.DataserverSchemaProcessor'
      cs
    end
  end
end

Is there a cleaner way to do that?