> 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.
There are a couple ways to do this I have found.
> 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)
I think I can guess what you are trying to do here. You have one
table, wastes, which combines some general information about a waste,
such as the name and type, and then more specific facts, such as the
co2e_factor, where you might have many rows with the same waste name
and type_id (and other repeated information), but with a few fields
that vary. The common fields, you want to group and make a
waste_dimension, and the fields that vary you want to then make into
the fact table's values.
For the waste_dimension, that means you need to be doing a group by,
and probably generating a new key for each waste row, or using type_id
if there is a type per waste dimension row.
After that, when you create the fact table, you need to have some
unique value to lookup in the waste dimension (type_id?), and use a
transform in the fact etl script o get the id.
For example, if you want to lookup the date_id to link to a
date_dimension, you can use a date value in the fact data to lookup
the right date_dimension row by comparing with the date value in the
date_dimension. Here is an example of doing this using a transform in
an etl script:
transform :date_id, :foreign_key_lookup, { :resolver =>
SQLResolver.new('date_dimension', 'sql_date_stamp', 'foo_warehouse') }
Andrew Kuklewicz
On Tue, Apr 21, 2009 at 2:48 AM, Shikha Mohan <shikhamoha...@gmail.com> 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
