Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Lookup Error (Spelling mistake causing 90 record losses)

3 posters

Go down

Lookup Error (Spelling mistake causing 90 record losses) Empty Lookup Error (Spelling mistake causing 90 record losses)

Post  liamptk 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

Back to top Go down

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

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

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

Post  liamptk 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

Back to top Go down

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

Post  BoxesAndLines 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.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

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

Post  liamptk 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

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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