Lookup Error (Spelling mistake causing 90 record losses)

View previous topic View next topic Go down

Lookup Error (Spelling mistake causing 90 record losses)

Post  liamptk on Sat Mar 26, 2016 1:46 pm

I'm performing a lookup for a primary key, but one of the values is spelt differently and causing 90 records to error. The simple solution is obviously for me to replace the spelling within the source document. However, this is an assignment so I'm curious to know if there is another (professional) fix within the SSIS project.

Any suggestions?

Regards,
Liamptk

liamptk

Posts : 6
Join date : 2016-03-13

View user profile

Back to top Go down

Re: Lookup Error (Spelling mistake causing 90 record losses)

Post  ngalemmo on Sat Mar 26, 2016 9:02 pm

Are you loading a dimension or a fact?

A common technique for loading facts is to first validate the dimension natural keys and infer dimension rows that do not exist. That is, create the row, load the natural key info and hope dimensional information arrives at a later time. This allows you to assign a foreign key to the fact and not have to reject rows.

As far as dimensional natural keys go, they should represent the source system's primary key which should correspond to how the business identifies the entity. Since this should be the key the operational system is using, spelling mistakes should not happen.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Lookup Error (Spelling mistake causing 90 record losses)

Post  liamptk on Sun Mar 27, 2016 3:09 am

The reason the spelling mistake exists is due US English and UK English. Tyre and tire.

There are several other matches I need to make during the lookup to the dimension table. Therefore creating a column isn't the answer. Unless its acceptable to do two lookups?

liamptk

Posts : 6
Join date : 2016-03-13

View user profile

Back to top Go down

Re: Lookup Error (Spelling mistake causing 90 record losses)

Post  BoxesAndLines on Sun Mar 27, 2016 10:39 pm

Well, it's a PK or it isn't. If the database has tyre, then all of the children records will have tyre as well. If you want to cleanse and conform the data you do that before loading to get consistent data. Although, I can't ever recall cleansing a primary key column.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Lookup Error (Spelling mistake causing 90 record losses)

Post  liamptk on Mon Mar 28, 2016 1:42 am

On the source data that field is not acting as a primary key. Can you clean the data within SSIS? Or is this a process that happens prior?

liamptk

Posts : 6
Join date : 2016-03-13

View user profile

Back to top Go down

Re: Lookup Error (Spelling mistake causing 90 record losses)

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum