mondrian-olap JRuby library

Raimonds Simanovskis

github.com/rsim

@rsim

Mondrian!is nice…

But I don’t like Java and XML

so much…

And I like!Ruby!

object-orienteddynamic

programming language

simple from outside

powerful insideYukihiro

Matsumoto or “Matz”

What is!mondrian-olap!JRuby gem?

http://github.com/rsim/mondrian-olap

Mondrian 3.x schema definitionschema = Mondrian::OLAP::Schema.define do

cube 'Sales' do table 'sales' dimension 'Customers', foreign_key: 'customer_id' do hierarchy has_all: true, all_member_name: 'All Customers', primary_key: 'id' do table 'customers' level 'Country', column: 'country', unique_members: true level 'State Province', column: 'state_province', unique_members: true level 'City', column: 'city', unique_members: false level 'Name', column: 'fullname', unique_members: false end end dimension 'Time', foreign_key: 'time_id', type: 'TimeDimension' do hierarchy has_all: false, primary_key: 'id' do table 'time' level 'Year', column: 'the_year', type: 'Numeric', unique_members: true, level_type: 'TimeYears' level 'Quarter', column: 'quarter', unique_members: false, level_type: 'TimeQuarters' level 'Month', column: 'month_of_year', type: 'Numeric', unique_members: false, level_type: 'TimeMonths' end end measure 'Unit Sales', column: 'unit_sales', aggregator: 'sum' measure 'Store Sales', column: 'store_sales', aggregator: 'sum' end end

Mondrian connection

olap = Mondrian::OLAP::Connection.create( driver: 'mysql', host: 'localhost', database: 'mondrian_test', username: 'mondrian_user', password: 'secret', schema: schema )

MDX queriesresult = olap.execute <<-MDX SELECT {[Measures].[Unit Sales], [Measures].[Store Sales]} ON COLUMNS, {[Products].children} ON ROWS FROM [Sales] WHERE ([Time].[2010].[Q1], [Customers].[USA].[CA]) MDX !result.axes_count # => 2 result.column_names # => ["Unit Sales", "Store Sales"] result.column_full_names # => ["[Measures].[Unit Sales]", # "[Measures].[Store Sales]"] result.row_names # => e.g. ["Drink", "Food", "Non-Consumable"] result.row_full_names # => e.g. ["[Products].[Drink]", "[Products].[Food]", # "[Products].[Non-Consumable]"] result.values # => [[..., ...], [..., ...], [..., ...]] # (three rows, each row containing value for # "unit sales" and "store sales")

Query builder

olap.from('Sales'). columns('[Measures].[Unit Sales]', '[Measures].[Store Sales]'). rows('[Products].children'). where('[Time].[2010].[Q1]', '[Customers].[USA].[CA]'). execute

Query builder

olap.from('Sales'). with_member('[Measures].[ProfitPct]'). as('Val((Measures.[Store Sales] - Measures.[Store Cost]) / Measures.[Store Sales])', format_string: 'Percent'). columns('[Measures].[Store Sales]', '[Measures].[ProfitPct]'). rows('[Products].children'). crossjoin('[Customers].[Canada]', '[Customers].[USA]'). top_count(50, '[Measures].[Store Sales]'). where('[Time].[2010].[Q1]'). execute

Cube and member queries

cube = olap.cube('Sales') cube.dimension_names # => ['Measures', 'Customers', 'Products', # 'Time'] cube.dimensions # => array of dimension objects cube.dimension('Customers') # => customers dimension object cube.dimension('Time').hierarchy_names # => ['Time', 'Time.Weekly'] cube.dimension('Time').hierarchies # => array of hierarchy objects cube.dimension('Customers').hierarchy # => default customers dimension hierarchy cube.dimension('Customers').hierarchy.level_names # => ['(All)', 'Country', 'State Province', # 'City', 'Name'] cube.dimension('Customers').hierarchy.levels # => array of hierarchy level objects cube.dimension('Customers').hierarchy.level('Country').members # => array of all level members cube.member('[Customers].[USA].[CA]') # => lookup member by full name cube.member('[Customers].[USA].[CA]').children # => get all children of member in deeper # hierarchy level cube.member('[Customers].[USA]').descendants_at_level('City') # => get all descendants of member in specified # hierarchy level

User defined MDX functionsschema = Mondrian::OLAP::Schema.define do # ... cube definitions ... user_defined_function 'Factorial' do ruby do parameters :numeric returns :numeric def call(n) n <= 1 ? 1 : n * call(n - 1) end end end user_defined_function 'UpperName' do ruby do parameters :member returns :string syntax :property def call(member) member.getName.upcase end end end end

UDF in JavaScriptschema = Mondrian::OLAP::Schema.define do # ... cube definitions ... user_defined_function 'Factorial' do javascript <<-JS function getParameterTypes() { return new Array( new mondrian.olap.type.NumericType()); } function getReturnType(parameterTypes) { return new mondrian.olap.type.NumericType(); } function execute(evaluator, arguments) { var n = arguments[0].evaluateScalar(evaluator); return factorial(n); } function factorial(n) { return n <= 1 ? 1 : n * factorial(n - 1); } JS end end

JavaScript is OK … but

CoffeeScript is better!

Sample CoffeeScript# Assignment:number = 42 opposite = true# Conditions:number = -42 if opposite# Functions:square = (x) -> x * x# Arrays:list = [1, 2, 3, 4, 5] # Objects:math = root: Math.sqrt square: square cube: (x) -> x * square x

# Splats:race = (winner, runners...) -> print winner, runners# Existence:alert "I knew it!" if elvis? # Array comprehensions:cubes = (math.cube num for num in list)

UDF in CoffeeScript

schema = Mondrian::OLAP::Schema.define do # ... cube definitions ... user_defined_function 'Factorial' do coffeescript <<-JS parameters: ["Numeric"] returns: "Numeric" execute: (n) -> if n <= 1 then 1 else n * @execute(n - 1) JS end end

Cell, property, member formatters in

Ruby and CoffeeScriptcell_formatter { ruby {|value| "%020d" % value} } property_formatter do ruby do |member, property_name, property_value| property_value.upcase end end member_formatter { ruby {|member| member.getName().upcase } } !cell_formatter do coffeescript <<-JS s = value.toString() s = "0" + s while s.length < 20 s JS end member_formatter { coffeescript "member.getName().toUpperCase()" } property_formatter { coffeescript "propertyValue.toUpperCase()" }

Next idea:!More

CoffeeScript

Next idea: Mondrian schema

in CoffeeScript@Schema -> @Cube 'Sales', -> @Table 'sales' @Dimension 'Customers', foreignKey: 'customer_id', -> @Hierarchy hasAll: true, allMemberName: 'All Customers', primaryKey: 'id', -> @Table 'customers' @Level 'Country', column: 'country', uniqueMembers: true @Level 'State Province', column: 'state_province', uniqueMembers: true @Level 'City', column: 'city', uniqueMembers: false @Level 'Name', column: 'fullname', uniqueMembers: false @Dimension 'Time', foreignKey: 'time_id', type: 'TimeDimension', -> @Hierarchy hasAll: false, primaryKey: 'id', -> @Table 'time' @Level 'Year', column: 'the_year', type: 'Numeric', uniqueMembers: true, \ levelType: 'TimeYears' @Level 'Quarter', column: 'quarter', uniqueMembers: false, \ levelType: 'TimeQuarters' @Level 'Month', column: 'month_of_year', type: 'Numeric', uniqueMembers: false, \ levelType: 'TimeMonths' @Measure 'Unit Sales', column: 'unit_sales', aggregator: 'sum' @Measure 'Store Sales', column: 'store_sales', aggregator: 'sum'

Questions?