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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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