Post reply

Working with Multiple Look Ups best practice

  • June 10, 2011 at 11:06 am

    #239720

    I have a DataMart dimensional table I am populating there are 4 fields which are being populated by using a Look up component to replace the existing id field. My challenge is my data flow is looking very complicated very fast due to all the data paths which are created by all the various scenerios. I will give you an example which may be better:

    Data stream 1 -- My source --> Look up 1 (match output) --> Look up 2 (match output) --> Look up 3 ...

    Data stream 2 -- My source --> Look up 1 (No match output) --> Look up 2 (match output) --> Look up 3 ...

    Data stream 3 -- My source --> Look up 1 (No match output) --> Look up 2 (No match output) --> Look up 3 ...

    etc.

    This gets extremely complicated on a data flow when you begin to map all the various data stream paths. Now I can continue to do things this way but I am thinking there must be a better way to do this especially for future maintainability. Note all the Look ups are being done by different data sources so I can't encapsulate the look up for multiple fields in one look up component.

    Any suggestions would be greatly appreciated.

    Thanks

  • June 10, 2011 at 12:14 pm

    #1337183

    I'm not entirely sure I understand the problem as you defined it, but if I understand correctly, aren't we just talking about a series of queries with parameters? You pull back the data for the first select list and someone picks an item, so you pass that item to a query and that returns a new list (stored in a related table in the database) and so on?

    By the way, how volatile are these lists of lookup values? If they're not terribly volatile, you might want to look into setting up, or purchasing, a caching mechanism so that you keep this stuff in memory on the server and only have a referential or root copy in the database. I've seen that work much better than having to perform a round-trip to the database for each set of information. But it will only work if the data is relatively non-volatile. If it's constantly changing, you need to go to the source, the database, in order to get it.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • June 10, 2011 at 1:22 pm

    #1337222

    I'm not sure exactly what your question was, but I try to keep things fairly simple.. So let me lay out how I typically handle things.

    Don't mind the error symbols, I just threw this together to show structure. In most cases the not matched value is fixed and fed back into the pipeline, the value is usually what ever means "I don't know"..

    I've attached the jpg as well..

    CEWII

  • June 10, 2011 at 1:26 pm

    #1337224

    Thanks Grant for your response I know my problem was a little hard to understand but I appreciate your response. My data that is being looked up is fairly static but not necessarily always. I am using the LOOK UP component of SSIS I probably should of said that a bit clearer. I actually found a solution to simplifying my logic. When using the LOOK UP component you can set the result set or the paramater "Specify how to handle rows with no matching entries" to Ignore failure. This essentially sets any field value not found to a null which I can reset downstream using an expression in the derived column component. My logic in the derived component sets any null field to N/A using the following expression:

    ISNULL(my_lookup_output_field) ? "N/A" : my_lookup_output_field.

    Appreciate all those who viewed and tried to understand what I was saying. Hope this helps someone else in future.

  • June 10, 2011 at 1:29 pm

    #1337228

    Elliot thanks for your response you must have been typing the same time as me. I essentially did the same thing as you but instead of fixing everything after each look up I did this at the end. I actually like your solution better as it may be a little clearer to someone else coming behind me to maintain.

    Cheers.

  • June 10, 2011 at 4:07 pm

    #1337306

    It made things simple for me and I didn't have to try and figure out where the error occured, I know. I'm thinking the performance hit was fairly small. I haven't had any troubles with it.

    CEWII

  • June 10, 2011 at 4:18 pm

    #1337311

    Man, I couldn't have been further off track. I totally missed several key words and tricky phrases in the original question. Sorry for any confusion tossed into the mix.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply