Facxt Table Example

I need an example that shows how to uses multiple lookups to populate a fact table. The flow goes lilke this...

1. Read a staging table source that has source keys

2. for each source key, perform a lookup on the dimension table and return the surrogate key

3. Insert rows into the fact table with the surrogate keys

This is a standard approach that I've done many times in other ETL tools. However, I can't find any examples on how to get it to work. I have tried stringing the lookups together sequentially and using a multicast to peform the lookups in parallel. Neither approach would work. I could not find any examples on the web or in the SQL2005 samples.

Thanks,

Chris Busch

Blueprint Database

cbusch@.blueprintdatabase.com

Take a look at the samples you can download here:

http://www.msftdwtoolkit.com/ToolsandUtilities/ToolandUtilities.htm

This book is what I'd categorize as a "must have" if you're going to be spending much time with the MS BI stack.

extracting from 2 tables what doesn't exist in both

what is the best way of extracting just the rows that existing in both table
s
that are not common to both.
I guess one way might be to firstly populate another table with what does
exist in both tables and then delete from that table what does exist in
table1 and you then have what exists in table1 but not in table2, then repea
t
process for table2.
this just seems incredibly clumsy and I'm sure there is probably a far
better way to code this.
your help much appreciated.Try,
select *
from
(
select t1.c1, ..., t1.cn
from t1 left join t2
on t1.pk = t2.pk
where t2.pk is null
union
select t2.c1, ..., t2.cn
from t1 right join t2
on t1.pk = t2.pk
where t1.pk is null
)
) as t
If do not mind about duplicated rows, then use "union all" instead.
AMB
"sysbox27" wrote:

> what is the best way of extracting just the rows that existing in both tab
les
> that are not common to both.
> I guess one way might be to firstly populate another table with what does
> exist in both tables and then delete from that table what does exist in
> table1 and you then have what exists in table1 but not in table2, then rep
eat
> process for table2.
> this just seems incredibly clumsy and I'm sure there is probably a far
> better way to code this.
> your help much appreciated.
>

extracting from 2 tables what doesn't exist in both

what is the best way of extracting just the rows that existing in both tables
that are not common to both.
I guess one way might be to firstly populate another table with what does
exist in both tables and then delete from that table what does exist in
table1 and you then have what exists in table1 but not in table2, then repeat
process for table2.
this just seems incredibly clumsy and I'm sure there is probably a far
better way to code this.
your help much appreciated.
Try,
select *
from
(
select t1.c1, ..., t1.cn
from t1 left join t2
on t1.pk = t2.pk
where t2.pk is null
union
select t2.c1, ..., t2.cn
from t1 right join t2
on t1.pk = t2.pk
where t1.pk is null
)
) as t
If do not mind about duplicated rows, then use "union all" instead.
AMB
"sysbox27" wrote:

> what is the best way of extracting just the rows that existing in both tables
> that are not common to both.
> I guess one way might be to firstly populate another table with what does
> exist in both tables and then delete from that table what does exist in
> table1 and you then have what exists in table1 but not in table2, then repeat
> process for table2.
> this just seems incredibly clumsy and I'm sure there is probably a far
> better way to code this.
> your help much appreciated.
>