Allan,
actually, we want to see as less errors as we can (more automation).
in the other words, we try to insert as much as we can. If the lookup
key is not found then insert it too.
the only thing I worry about is "time," it may take a long time to
distint each column and compare and then insert only un matching ones.
The flat raw data is like below table (of course way more columns). it
takes long time to distint each column (I have 7 millions records now)
and then check the lookup tables then insert the missing ones. this
may take long time.
item,custmer, address, vender
-------,-----------,------------,-----------
TV, custmer1, address1, vender1
DVD, custmer2, address1, vender1
TV, custmer1, address2, vender2
thank you!
[quoted text, click to view] On Jul 2, 10:25 pm, Allan Mitchell <a...@no-spam.sqldts.com> wrote:
> Hello Bob,
>
> I guess only your business process will tell you.
>
> It may be that your process considers this an error.
>
> It may be that your process wants you to create those rows in the lookup
> table which is common when looking up rows from a dimension table when doing
> a fact table load (Inferred Members)
>
> If you have a table of failures that you previously created due to a key
> not being found then you could always union that into the stream the next
> time you do the load so you attempt to reprocess them.
>
> Make sense?
>
> --
>
> Allan Mitchell
http://wiki.sqlis.com|
http://www.sqlis.com|
http://www.sqldts.com|
http://www.konesans.com >
>
>
> > I am wondering how does everyone handle the records if the key is not
> > found in the lookup table? I am thinking two ways:
> > 1. Distinct the raw data field first and insert to the lookup table if
> > the key is missing before running lookup;
> > 2. Run importing first and send those error records to a file. Then,
> > insert those missing keys to the lookup table and run the importing
> > again.
> > However, I think all these two methods may take a lot
> > of time since a lot of process to go through, so I just wonder if you
> > have any better way, for example, run inserting new key to the lookup
> > tables if the key is not found while in the importing process.
>
> > Thank you!- Hide quoted text -
>
> - Show quoted text -
