New To DTS could use a hand with Ref Integrity

Question

Post reply

New To DTS could use a hand with Ref Integrity

aaron otto

SSC Rookie

Points: 45
More actions
April 7, 2004 at 9:18 pm

#59823

I'm new to DTS and trying to understand how to impliment FK and Ref Integrity with tables when loading Data Warehouse from DTS. My challenge is figuring out how to create my Fact Table and have the FKS automatically populate with the PK from their associated Dim Tables.

So far I've just gone into Query analyzer and updated columes
ie
update facttable set datekey = 1
where inputdate = 010104

Any help is much appreciated

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65143 More actions · Answer 1

Assuming the Dim tables are populated correctly then get the keys/ids when you select data for the fact table

insert into facttable

select s.col1,

s.col2,

d.datekey,

s.coln

from sourcetable s

inner join dimtable d on d.inputdate = s.inputdate

you can specifiy either a table or sql query for the data source of a data pump in DTS

Far away is close at hand in the images of elsewhere.
Anon.

aaron otto SSC Rookie Points: 45 More actions · Answer 2

The Dim Tables are solid and I understand your join, but I'm affraid I'm still a little grey. The Inner Join should go in the Sql Qry and then I reference Pump I would like to use. Would I then use the Dim table as a look up table and add active X scripting ? Or am I on the wrong track. I didn't see anything set to a param in the join qry?

Thanks,

Sorry I'm still trying to figure DTS out.

David Burrows SSC Guru Points: 65143 More actions · Answer 3

What I do in my dts is to have several data transformation tasks, to build each dim table and then one to build the fact table.

As I said you can specify a sql query in each transformation source to extract the data in the format required and join the dim tables if you need to get their PK.

You only need to access the dim tables if you are converting data to a PK, if the value of the data column is the same as the PK of the dim table then you do not need to join that table.

Without examples of you database ddl, it is difficult to see what the problem is.

Far away is close at hand in the images of elsewhere.
Anon.

aaron otto SSC Rookie Points: 45 More actions · Answer 4

Dave,

Most likely the problem is just me, I'm a technical resource for a group and I've had to self -teach SQL so my learning curve is a little......slow.

Thanks for your help,