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