mondrian-olap

require "rubygems"
require "mondrian-olap"

schema = 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: true
      end
    end
    dimension 'Products', foreign_key: 'product_id' do
      hierarchy has_all: true, all_member_name: 'All Products',
                primary_key: 'id', primary_key_table: 'products' do
        join left_key: 'product_class_id', right_key: 'id' do
          table 'products'
          table 'product_classes'
        end
        level 'Product Family', table: 'product_classes', column: 'product_family', unique_members: true
        level 'Brand Name', table: 'products', column: 'brand_name', unique_members: false
        level 'Product Name', table: 'products', column: 'product_name', unique_members: true
      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
      hierarchy 'Weekly', has_all: false, primary_key: 'id' do
        table 'time'
        level 'Year', column: 'the_year', type: 'Numeric', unique_members: true, level_type: 'TimeYears'
        level 'Week', column: 'week_of_year', type: 'Numeric', unique_members: false, level_type: 'TimeWeeks'
      end
    end
    measure 'Unit Sales', column: 'unit_sales', aggregator: 'sum'
    measure 'Store Sales', column: 'store_sales', aggregator: 'sum'
  end
end

require "jdbc/mysql"

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

result = 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")

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

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 = 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

schema = 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

schema = Mondrian::OLAP::Schema.define do
  # ... cube definitions ...
  role 'California manager' do
    schema_grant access: 'none' do
      cube_grant cube: 'Sales', access: 'all' do
        dimension_grant dimension: '[Measures]', access: 'all'
        hierarchy_grant hierarchy: '[Customers]', access: 'custom',
                        top_level: '[Customers].[State Province]', bottom_level: '[Customers].[City]' do
          member_grant member: '[Customers].[USA].[CA]', access: 'all'
          member_grant member: '[Customers].[USA].[CA].[Los Angeles]', access: 'none'
        end
      end
    end
  end
end