class Mondrian::OLAP::Query

Constants

AXIS_ALIASES
MDX_FUNCTIONS
VALID_ORDERS

Attributes

cube_name[RW]

Public Class Methods

from(connection, cube_name) click to toggle source
# File lib/mondrian/olap/query.rb, line 4
def self.from(connection, cube_name)
  query = self.new(connection)
  query.cube_name = cube_name
  query
end
new(connection) click to toggle source
# File lib/mondrian/olap/query.rb, line 12
def initialize(connection)
  @connection = connection
  @cube = nil
  @axes = []
  @where = []
  @with = []
end

Public Instance Methods

as(*params) click to toggle source

Add definition to calculated member or to named set

# File lib/mondrian/olap/query.rb, line 195
def as(*params)
  # definition of named set
  if @current_set
    if params.empty?
      raise ArgumentError, "named set cannot be empty"
    else
      raise ArgumentError, "cannot use 'as' method before with_set method" unless @current_set.empty?
      if params.length == 1 && params[0].is_a?(Array)
        @current_set.concat(params[0])
      else
        @current_set.concat(params)
      end
    end
  # definition of calculated member
  else
    member_definition = @with.last
    if params.last.is_a?(Hash)
      options = params.pop
      # if formatter does not include . then it should be ruby formatter name
      if (formatter = options[:cell_formatter]) && !formatter.include?('.')
        options = options.merge(:cell_formatter => Mondrian::OLAP::Schema::CellFormatter.new(formatter).class_name)
      end
    else
      options = nil
    end
    raise ArgumentError, "cannot use 'as' method before with_member method" unless member_definition &&
      member_definition[0] == :member && member_definition.length == 2
    raise ArgumentError, "calculated member definition should be single expression" unless params.length == 1
    member_definition << params[0]
    member_definition << options if options
  end
  self
end
axis(i, *axis_members) click to toggle source

Add new axis(i) to query or return array of axis(i) members if no arguments specified

# File lib/mondrian/olap/query.rb, line 22
def axis(i, *axis_members)
  if axis_members.empty?
    @axes[i]
  else
    @axes[i] ||= []
    @current_set = @axes[i]
    if axis_members.length == 1 && axis_members[0].is_a?(Array)
      @current_set.concat(axis_members[0])
    else
      @current_set.concat(axis_members)
    end
    self
  end
end
distinct() click to toggle source
# File lib/mondrian/olap/query.rb, line 76
def distinct
  raise ArgumentError, "cannot use distinct method before axis method" unless @current_set
  @current_set.replace [:distinct, @current_set.clone]
  self
end
except(*axis_members) click to toggle source
# File lib/mondrian/olap/query.rb, line 58
def except(*axis_members)
  raise ArgumentError, "cannot use except method before axis or with_set method" unless @current_set
  raise ArgumentError, "specify set of members for except method" if axis_members.empty?
  members = axis_members.length == 1 && axis_members[0].is_a?(Array) ? axis_members[0] : axis_members
  if [:crossjoin, :nonempty_crossjoin].include? @current_set[0]
    @current_set[2] = [:except, @current_set[2], members]
  else
    @current_set.replace [:except, @current_set.clone, members]
  end
  self
end
execute(parameters = {}) click to toggle source
# File lib/mondrian/olap/query.rb, line 238
def execute(parameters = {})
  @connection.execute to_mdx, parameters
end
execute_drill_through(options = {}) click to toggle source
# File lib/mondrian/olap/query.rb, line 242
def execute_drill_through(options = {})
  drill_through_mdx = "DRILLTHROUGH "
  drill_through_mdx << "MAXROWS #{options[:max_rows]} " if options[:max_rows]
  drill_through_mdx << to_mdx
  drill_through_mdx << " RETURN #{Array(options[:return]).join(',')}" if options[:return]
  @connection.execute_drill_through drill_through_mdx
end
filter(condition, options = {}) click to toggle source
# File lib/mondrian/olap/query.rb, line 82
def filter(condition, options = {})
  raise ArgumentError, "cannot use filter method before axis or with_set method" unless @current_set
  @current_set.replace [:filter, @current_set.clone, condition]
  @current_set << options[:as] if options[:as]
  self
end
filter_nonempty() click to toggle source
# File lib/mondrian/olap/query.rb, line 89
def filter_nonempty
  raise ArgumentError, "cannot use filter_nonempty method before axis or with_set method" unless @current_set
  condition = "NOT ISEMPTY(S.CURRENT)"
  @current_set.replace [:filter, @current_set.clone, condition, 'S']
  self
end
generate(*axis_members) click to toggle source
# File lib/mondrian/olap/query.rb, line 96
def generate(*axis_members)
  raise ArgumentError, "cannot use generate method before axis or with_set method" unless @current_set
  all = if axis_members.last == :all
    axis_members.pop
    'ALL'
  end
  raise ArgumentError, "specify set of members for generate method" if axis_members.empty?
  members = axis_members.length == 1 && axis_members[0].is_a?(Array) ? axis_members[0] : axis_members
  @current_set.replace [:generate, @current_set.clone, members]
  @current_set << all if all
  self
end
hierarchize(order = nil, all = nil) click to toggle source
# File lib/mondrian/olap/query.rb, line 141
def hierarchize(order = nil, all = nil)
  raise ArgumentError, "cannot use hierarchize method before axis or with_set method" unless @current_set
  order = order && order.to_s.upcase
  raise ArgumentError, "invalid hierarchize order #{order.inspect}" unless order.nil? || order == 'POST'
  if all.nil? && [:crossjoin, :nonempty_crossjoin].include?(@current_set[0])
    @current_set[2] = [:hierarchize, @current_set[2]]
    @current_set[2] << order if order
  else
    @current_set.replace [:hierarchize, @current_set.clone]
    @current_set << order if order
  end
  self
end
hierarchize_all(order = nil) click to toggle source
# File lib/mondrian/olap/query.rb, line 155
def hierarchize_all(order = nil)
  hierarchize(order, :all)
end
nonempty() click to toggle source
# File lib/mondrian/olap/query.rb, line 70
def nonempty
  raise ArgumentError, "cannot use nonempty method before axis method" unless @current_set
  @current_set.replace [:nonempty, @current_set.clone]
  self
end
order(expression, direction) click to toggle source
# File lib/mondrian/olap/query.rb, line 111
def order(expression, direction)
  raise ArgumentError, "cannot use order method before axis or with_set method" unless @current_set
  direction = direction.to_s.upcase
  raise ArgumentError, "invalid order direction #{direction.inspect}," <<
    " should be one of #{VALID_ORDERS.inspect[1..-2]}" unless VALID_ORDERS.include?(direction)
  @current_set.replace [:order, @current_set.clone, expression, direction]
  self
end
to_mdx() click to toggle source
# File lib/mondrian/olap/query.rb, line 229
def to_mdx
  mdx = ""
  mdx << "WITH #{with_to_mdx}\n" unless @with.empty?
  mdx << "SELECT #{axis_to_mdx}\n"
  mdx << "FROM #{from_to_mdx}"
  mdx << "\nWHERE #{where_to_mdx}" unless @where.empty?
  mdx
end
where(*members) click to toggle source

Add new WHERE condition to query or return array of existing conditions if no arguments specified

# File lib/mondrian/olap/query.rb, line 161
def where(*members)
  if members.empty?
    @where
  else
    @current_set = @where
    if members.length == 1 && members[0].is_a?(Array)
      @where.concat(members[0])
    else
      @where.concat(members)
    end
    self
  end
end
with() click to toggle source

return array of member and set definitions

# File lib/mondrian/olap/query.rb, line 190
def with
  @with
end
with_member(member_name) click to toggle source

Add definition of calculated member

# File lib/mondrian/olap/query.rb, line 176
def with_member(member_name)
  @with << [:member, member_name]
  @current_set = nil
  self
end
with_set(set_name) click to toggle source

Add definition of named_set

# File lib/mondrian/olap/query.rb, line 183
def with_set(set_name)
  @current_set = []
  @with << [:set, set_name, @current_set]
  self
end

Private Instance Methods

axis_to_mdx() click to toggle source
# File lib/mondrian/olap/query.rb, line 279
def axis_to_mdx
  mdx = ""
  @axes.each_with_index do |axis_members, i|
    axis_name = AXIS_ALIASES[i] ? AXIS_ALIASES[i].upcase : "AXIS(#{i})"
    mdx << ",\n" if i > 0
    mdx << members_to_mdx(axis_members) << " ON " << axis_name
  end
  mdx
end
expression_to_mdx(expression) click to toggle source
# File lib/mondrian/olap/query.rb, line 339
def expression_to_mdx(expression)
  expression.is_a?(Array) ? "(#{expression.join(', ')})" : expression
end
extract_dimension_name(full_name) click to toggle source
# File lib/mondrian/olap/query.rb, line 379
def extract_dimension_name(full_name)
  # "[Foo [Bar]]].[Baz]" =>  "Foo [Bar]"
  if full_name
    full_name.gsub(/\A\[|\]\z/, '').split('].[').first.try(:gsub, ']]', ']')
  end
end
from_to_mdx() click to toggle source
# File lib/mondrian/olap/query.rb, line 343
def from_to_mdx
  "[#{@cube_name}]"
end
members_to_mdx(members) click to toggle source
# File lib/mondrian/olap/query.rb, line 298
def members_to_mdx(members)
  members ||= []
  # if only one member which does not end with ] or .Item(...)
  # then assume it is expression which returns set
  # TODO: maybe always include also single expressions in {...} to avoid some edge cases?
  if members.length == 1 && members[0] !~ /(\]|\.Item\(\d+\))\z/i
    members[0]
  elsif members[0].is_a?(Symbol)
    case members[0]
    when :crossjoin
      "CROSSJOIN(#{members_to_mdx(members[1])}, #{members_to_mdx(members[2])})"
    when :nonempty_crossjoin
      "NONEMPTYCROSSJOIN(#{members_to_mdx(members[1])}, #{members_to_mdx(members[2])})"
    when :except
      "EXCEPT(#{members_to_mdx(members[1])}, #{members_to_mdx(members[2])})"
    when :nonempty
      "NON EMPTY #{members_to_mdx(members[1])}"
    when :distinct
      "DISTINCT(#{members_to_mdx(members[1])})"
    when :filter
      as_alias = members[3] ? " AS #{members[3]}" : nil
      "FILTER(#{members_to_mdx(members[1])}#{as_alias}, #{members[2]})"
    when :generate
      "GENERATE(#{members_to_mdx(members[1])}, #{members_to_mdx(members[2])}#{members[3] && ", #{members[3]}"})"
    when :order
      "ORDER(#{members_to_mdx(members[1])}, #{expression_to_mdx(members[2])}, #{members[3]})"
    when :top_count, :bottom_count
      mdx = "#{MDX_FUNCTIONS[members[0]]}(#{members_to_mdx(members[1])}, #{members[2]}"
      mdx << (members[3] ? ", #{expression_to_mdx(members[3])})" : ")")
    when :top_percent, :top_sum, :bottom_percent, :bottom_sum
      "#{MDX_FUNCTIONS[members[0]]}(#{members_to_mdx(members[1])}, #{members[2]}, #{expression_to_mdx(members[3])})"
    when :hierarchize
      "HIERARCHIZE(#{members_to_mdx(members[1])}#{members[2] && ", #{members[2]}"})"
    else
      raise ArgumentError, "Cannot generate MDX for invalid set operation #{members[0].inspect}"
    end
  else
    "{#{members.join(', ')}}"
  end
end
quote_value(value) click to toggle source
# File lib/mondrian/olap/query.rb, line 366
def quote_value(value)
  case value
  when String
    "'#{value.gsub("'", "''")}'"
  when TrueClass, FalseClass
    value ? 'TRUE' : 'FALSE'
  when NilClass
    'NULL'
  else
    "#{value}"
  end
end
where_to_mdx() click to toggle source
# File lib/mondrian/olap/query.rb, line 347
def where_to_mdx
  # generate set MDX expression
  if @where[0].is_a?(Symbol) ||
      @where.length > 1 && @where.map{|full_name| extract_dimension_name(full_name)}.uniq.length == 1
    members_to_mdx(@where)
  # generate tuple MDX expression
  else
    where_to_mdx_tuple
  end
end
where_to_mdx_tuple() click to toggle source
# File lib/mondrian/olap/query.rb, line 358
def where_to_mdx_tuple
  mdx = '('
  mdx << @where.map do |condition|
    condition
  end.join(', ')
  mdx << ')'
end
with_to_mdx() click to toggle source

FIXME: keep original order of WITH MEMBER and WITH SET defitions

# File lib/mondrian/olap/query.rb, line 253
def with_to_mdx
  @with.map do |definition|
    case definition[0]
    when :member
      member_name = definition[1]
      expression = definition[2]
      options = definition[3]
      options_string = ''
      options && options.each do |option, value|
        option_name = case option
        when :caption
          '$caption'
        else
          option.to_s.upcase
        end
        options_string << ", #{option_name} = #{quote_value(value)}"
      end
      "MEMBER #{member_name} AS #{quote_value(expression)}#{options_string}"
    when :set
      set_name = definition[1]
      set_members = definition[2]
      "SET #{set_name} AS #{quote_value(members_to_mdx(set_members))}"
    end
  end.join("\n")
end