June 22, 2015 at 10:05 am
Scenario:
I have a person entity and Nationality Entity. The nationality may or may not be populated (can be null). If null, i want to assign the key for UNKNOWN nationality, a row i placed in the nationality table.
in my populateFactTable package i have a lookup to nationality. If it finds a match, the row moves on through the flow and gets inserted, if not, i was going to push the "no match output" to a derived column shape that contains the key for the UNKNOWN nationality, and then merge these two branches.
This does not sound optimal to me, paritcularly if you have many dimensions that can be unknown.
Generally,whats the best way to deal with this scenario?
June 22, 2015 at 10:19 am
winston Smith (6/22/2015)
Scenario:I have a person entity and Nationality Entity. The nationality may or may not be populated (can be null). If null, i want to assign the key for UNKNOWN nationality, a row i placed in the nationality table.
in my populateFactTable package i have a lookup to nationality. If it finds a match, the row moves on through the flow and gets inserted, if not, i was going to push the "no match output" to a derived column shape that contains the key for the UNKNOWN nationality, and then merge these two branches.
This does not sound optimal to me, paritcularly if you have many dimensions that can be unknown.
Generally,whats the best way to deal with this scenario?
I do this in a stored procedure, not in SSIS, but the idea is the same.
You need a variable, let's call it @Default_NationalityKey, that has the value for your "unknown" key. If the lookup fails, then assign this value. In a stored procedure, it would look like:
,[Nationality_Key] = ISNULL(Dim_Nationality.Nationality_Key, @Default_NationalityKey)
In SSIS, the derived column sounds like a good option.
Sounds like you're on the right track.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 22, 2015 at 10:45 am
In your source proc, include an isnull (column,-1) check on the column and use -1 (or whatever ) as your default member. The lookup will then succeed.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 22, 2015 at 12:29 pm
Alvin Ramard (6/22/2015)
winston Smith (6/22/2015)
Scenario:I have a person entity and Nationality Entity. The nationality may or may not be populated (can be null). If null, i want to assign the key for UNKNOWN nationality, a row i placed in the nationality table.
in my populateFactTable package i have a lookup to nationality. If it finds a match, the row moves on through the flow and gets inserted, if not, i was going to push the "no match output" to a derived column shape that contains the key for the UNKNOWN nationality, and then merge these two branches.
This does not sound optimal to me, paritcularly if you have many dimensions that can be unknown.
Generally,whats the best way to deal with this scenario?
I do this in a stored procedure, not in SSIS, but the idea is the same.
You need a variable, let's call it @Default_NationalityKey, that has the value for your "unknown" key. If the lookup fails, then assign this value. In a stored procedure, it would look like:
,[Nationality_Key] = ISNULL(Dim_Nationality.Nationality_Key, @Default_NationalityKey)
In SSIS, the derived column sounds like a good option.
Sounds like you're on the right track.
Thanks guys.
This is exactly the answer i was looking for!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply