search

activewarehouse / activewarehouse-etl

Extract-Transform-Load library from ActiveWarehouse
MIT License
240 stars 102 forks source link

SQLResolver always include the field id #119

Closed epinault closed 1 year ago

epinault commented 12 years ago

Unfortunately for me , I am trying to do a foreign_key_lookup in my transform using the SQLResolver and the field 'id' is always included in the select statement.

Looking at the code it is hard coded and is assuming that the key of a table is called id.. In some DW, they key of the table is the same name of the fact key. I can change mine but it would be nice to have an option

/home/emmanuel/talemetry/talemetry_warehouse/vendor/ruby/1.9.1/gems/activerecord-3.2.8/lib/active_record/connection_adapters/postgresql_adapter.rb:1158:in `async_exec': PG::Error: ERROR: column "id" does not exist (ActiveRecord::StatementInvalid) LINE 1: SELECT id, name FROM sources ^

thbar commented 12 years ago

If "id" is hardcoded, it is most likely because the primary goal of the foreign key lookup is to transform a given value in the corresponding surrogate key, by design.

The surrogate key (as advertised in the Ralph Kimball ETL book) is a technical key with no link to the business reality; and the ETL gem is very deeply based on the book and not encouraging to pick something else.

If you still want to use a business value instead of a technical key here, you could achieve the lookup in a block as well:

transform(:name) do |n,v,r|
  Model.where(xxxx).first.name
end

Or something similar.

We could also add more flexibility to the foreign key lookup, but it would go away from its original design though.

What do you think?

PS: I'm going offline for a couple of days (conference) - in case I don't answer quickly.

epinault commented 12 years ago

I think having the options would be nice , especially if someone does have to comply to a different set of rule in the DB. For me it s ok since I could rename it quickly Sorry took me a while to respond as I transited back home ;)