Lookup Error (Spelling mistake causing 90 record losses)
3 posters
Page 1 of 1
Lookup Error (Spelling mistake causing 90 record losses)
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
Any suggestions?
Regards,
Liamptk
liamptk- Posts : 6
Join date : 2016-03-13
Re: Lookup Error (Spelling mistake causing 90 record losses)
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.
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.
Re: Lookup Error (Spelling mistake causing 90 record losses)
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?
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
Re: Lookup Error (Spelling mistake causing 90 record losses)
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- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Lookup Error (Spelling mistake causing 90 record losses)
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
Similar topics
» data puzzle - looking for a query to solve this
» ETL Subsystem 5 - Error Event Schema
» ETL Audit and Data Error log Table
» Summary Record
» Implementing audit dimension error
» ETL Subsystem 5 - Error Event Schema
» ETL Audit and Data Error log Table
» Summary Record
» Implementing audit dimension error
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum