Shikha
I did it with the virtual attribute handler (dont know if thats how
its called) and a PROC that can reference any attribute, I dont
remember where I scavenged that but IT WAS REALLY hard to figure out
hehehe
I''ll just put all the etl file, hope its self explanatory, just mind
that this is the etl for a fact table wich has two child dimensions
linked by lugar_id and robo_negocio_id
cheers
PAco
require 'active_record/connection_adapters/sqlserver_adapter'
require 'active_record/connection_adapters/sqlite3_adapter'
#ActiveRecord::Base.logger = Logger.new(STDOUT)
source :in, {
:database => "Delitos_ZM",
:type => :database,
:target => :operational ,
:table => "union_robo_a_negocio",
:select => "replace(CONVERT(varchar(20), FECHA_DE_LEVANTAMIENTO,
111),'/','-') as date_id, MUNICIPIO as municipio,
COLONIAS as colonia, ZONAS_ZMG as zona, REGIONES as
region,
GIRO_COMERCIAL,
VIOLENCIA, MODUS_OPERANDI, MEDIO_DE_COMISION, DIA,
HORA"
},
[
:date_id
, :municipio
, :region
, :colonia
, :zona
, :GIRO_COMERCIAL
, :VIOLENCIA, :MODUS_OPERANDI, :MEDIO_DE_COMISION, :DIA, :HORA
]
transform :municipio, :default, :default_value => "SE IGNORA"
transform :region, :default, :default_value => "SE IGNORA"
transform :colonia, :default, :default_value => "SE IGNORA"
transform :zona, :default, :default_value => "SE IGNORA"
transform :municipio, :trim
transform :region, :trim
transform :colonia, :trim
transform :zona, :trim
transform :GIRO_COMERCIAL, :default, :default_value => "SE IGNORA"
transform :VIOLENCIA, :default, :default_value => "SE IGNORA"
transform :MODUS_OPERANDI, :default, :default_value => "SE IGNORA"
transform :MEDIO_DE_COMISION, :default, :default_value => "SE IGNORA"
transform :DIA, :default, :default_value => "SE IGNORA"
transform :HORA, :default, :default_value => "SE IGNORA"
transform :GIRO_COMERCIAL, :trim
transform :VIOLENCIA, :trim
transform :MODUS_OPERANDI, :trim
transform :MEDIO_DE_COMISION, :trim
transform :DIA, :trim
transform :HORA, :trim
transform :date_id, :foreign_key_lookup,{
:resolver => SQLResolver.new(
"date_dimension", "sql_date_stamp", :datawarehouse
)
}
destination :out, {
:type => :database,
:target => :datawarehouse,
:table => 'robo_negocios_facts',
:truncate => true,
:unique => false
},
{
:order => [:date_id, :lugar_id, :robo_negocio_id],
:virtual => {
:lugar_id => (Proc.new do |r|
ETL::Engine.connection(:datawarehouse).select_value("SELECT id
FROM lugar_dimension WHERE municipio = '#{r[:municipio]}' AND colonia
= '#{r[:colonia]}' AND zona = '#{r[:zona]}' AND region =
'#{r[:region]}' ")
end),
:robo_negocio_id => (Proc.new do |r|
ETL::Engine.connection(:datawarehouse).select_value("SELECT id
FROM robo_negocio_dimension WHERE
GIRO_COMERCIAL = '#{r[:GIRO_COMERCIAL]}' AND
violencia = '#{r[:VIOLENCIA]}' AND
modus_operandi = '#{r[:MODUS_OPERANDI]}' AND
medio_de_comision = '#{r[:MEDIO_DE_COMISION]}' AND
dia = '#{r[:DIA]}' AND
rango_de_horas = '#{r[:HORA]}'
")
end)
}
}
On Apr 21, 2009, at 1:48 AM, Shikha Mohan wrote:
Hi,
I am new to this datawarehouse concept. I am trying to create a dw.
I have one dimension and one fact, namely: waste_dimension.rb,
waste_fact.rb
Location of elt is db/elt
My database.yml is something like:
etl_execution:
adapter: mysql
database: rails_warehouse_etl_execution
username: root
password: password
host: localhost
warehouse:
adapter: mysql
database: for_demo
username: root
password: password
host: localhost
I am using database for extract and load.
I am able to create a simple dimension table using following code:
source :in, {
:database => "for_demo",
:target => :warehouse,
:table => "wastes",
:select => "wastes.name, wastes.type_id",
:order => "wastes.name"
},
[
:name,
:type_id
]
transform :name, :default, :default_value => "No Name"
destination :out, {
:database => "rails_warehouse_etl_execution",
:target => :etl_execution,
:table => 'waste_dimension'
},
{
:order => [
:name,
:type_id
]
}
Now I need to populate the watse_facts table. I am not sure how to
go about it. How do I add waste_id which is a foreign key from
waste_dimesions table. Also I wanted to add additional information
from wastes table to waste_facts table like co2e_factor (which I
have not included in waste_dimensions table)
Structure for waste_dimension and waste_facts table is as follows:
class CreateWasteDimension < ActiveRecord::Migration
def self.up
fields = {
:name => :string,
:type_id => :integer
}
create_table :waste_dimension do |t|
fields.each do |name,type|
t.column name, type
end
end
fields.each do |name,type|
add_index :waste_dimension, name unless type == :text
end
end
def self.down
drop_table :waste_dimension
end
end
class CreateWasteFacts < ActiveRecord::Migration
def self.up
# you should add indexes for each foreign key, but don't add
# the foreign key itself unless you really know what you are
doing.
create_table :waste_facts do |t|
t.column :waste_id, :integer, :null => false
t.column :co2e_factor, :integer, :default => 1
end
add_index :waste_facts, :waste_id
end
def self.down
drop_table :waste_facts
end
end
As I could not get any information on net hence mailing on this
list. Please help me resolve this problem. Thanks!
_______________________________________________
Activewarehouse-discuss mailing list
Activewarehouse-discuss@rubyforge.org
http://rubyforge.org/mailman/listinfo/activewarehouse-discuss
_______________________________________________
Activewarehouse-discuss mailing list
Activewarehouse-discuss@rubyforge.org
http://rubyforge.org/mailman/listinfo/activewarehouse-discuss
