| Class | ActiveWarehouse::Aggregate::PipelinedRolapAggregate |
| In: |
lib/active_warehouse/aggregate/pipelined_rolap_aggregate.rb
|
| Parent: | Aggregate |
A Pipelined implementation of a ROLAP engine that stores all possible combinations of fact and dimensional values for a specific cube.
This implementation attempts to reduce the amount of work required by aggregating facts in a pipelined fashion. This means that smaller aggregates are generated from a preceding aggregate, in order to avoid having to query the entire raw data set for every aggregate.
E.g.
ABCD -> ABC -> AB -> A -> all
Build and populate the data store
# File lib/active_warehouse/aggregate/pipelined_rolap_aggregate.rb, line 21
21: def populate(options={})
22: create_and_populate_all_table
23: create_all_pipelined_agg_tables
24: create_insert_statements.each_with_index do |insert, i|
25: next if i == 0 #handled by create_and_populate_all_table
26: connection.transaction {connection.execute(insert)}
27: end
28: end
# File lib/active_warehouse/aggregate/pipelined_rolap_aggregate.rb, line 117
117: def aggregated_fact_column_sql_for_all
118: aggregate_fields.collect { |c|
119: "#{c.strategy_name}(#{c.name}) AS #{c.label_for_table}"
120: }.join(",")
121: end
# File lib/active_warehouse/aggregate/pipelined_rolap_aggregate.rb, line 123
123: def aggregated_fact_column_sql_for_rollup
124: aggregate_fields.collect { |c|
125: "#{c.strategy_name == :avg ? :avg : :sum}(#{c.label_for_table}) AS #{c.label_for_table}"
126: }.join(", ")
127: end
# File lib/active_warehouse/aggregate/pipelined_rolap_aggregate.rb, line 60
60: def create_all_pipelined_agg_tables
61: (0..dimensions_to_columns.size-1).each do |i|
62: create_rollup_cube_table(i)
63: end
64: end
build and populate a table which group by‘s all dimension columns.
# File lib/active_warehouse/aggregate/pipelined_rolap_aggregate.rb, line 33
33: def create_and_populate_all_table
34: dimension_column_names = dimensions_to_columns.collect do |c|
35: "#{c.table_alias}.#{c.name} as #{c.table_alias}_#{c.name}"
36: end
37:
38: fact_column_names = aggregate_fields.collect do |c|
39: "#{c.from_table_name}.#{c.name} as #{c.label_for_table}"
40: end
41:
42: sql = "SELECT\n\#{dimension_column_names.join(\",\")},\n\#{aggregated_fact_column_sql_for_all}\nFROM \#{tables_and_joins}\nGROUP BY\n\#{dimensions_to_columns.collect{|c| \"\#{c.table_alias}.\#{c.name}\"}.join(\",\")}\n"
43:
44: all_table_name = indexed_rollup_table_name(dimension_column_names.length)
45:
46: sql = connection.add_select_into_table(all_table_name, sql)
47:
48: connection.drop_table(all_table_name) if connection.tables.include?(all_table_name)
49: connection.transaction { connection.execute(sql) }
50: end
# File lib/active_warehouse/aggregate/pipelined_rolap_aggregate.rb, line 87
87: def create_insert_statements
88: dim_columns = dimensions_to_columns
89: template_filename = File.dirname(__FILE__) + "/templates/pipelined_rollup_#{dim_columns.length}.sql"
90: dim_columns.length.times do |i|
91: eval("@dimension_#{i} = '#{dim_columns[i].label}'")
92: end
93: @aggregate_fields_from_flat_table = aggregated_fact_column_sql_for_rollup
94: @aggregate_fields = aggregated_fact_column_sql_for_rollup
95: @flat_table_name = flat_table_name
96: @rollup_table_name = rollup_table_name
97:
98: inserts = []
99:
100: sql = ""
101: ERB.new(File.read(template_filename)).result(binding).each do |line|
102: if line.strip == ""
103: inserts << sql
104: sql = ""
105: else
106: sql += line
107: end
108: end
109:
110: inserts
111: end
Creates the rollup table
# File lib/active_warehouse/aggregate/pipelined_rolap_aggregate.rb, line 67
67: def create_rollup_cube_table(index)
68: table_name = indexed_rollup_table_name(index)
69: connection.drop_table(table_name) if connection.tables.include?(table_name)
70:
71: ActiveRecord::Base.transaction do
72: connection.create_table(table_name, :id => false) do |t|
73: dimensions_to_columns.each do |c|
74: t.column(c.label, c.column_type)
75: end
76: aggregate_fields.each do |c|
77: options = {}
78: options[:limit] = c.column_type == :integer ? 8 : c.limit
79: options[:scale] = c.scale if c.scale
80: options[:precision] = c.precision if c.precision
81: t.column(c.label_for_table, c.column_type, options)
82: end
83: end
84: end
85: end