0 Replies Latest reply on Feb 14, 2020 6:44 PM by Doug Newton

    Lookup inside a User-Defined Function

    Doug Newton Seasoned Veteran

      Enhancement request, I think...  Having unconnected lookups in a mapping is cleaner than having a dozen inline Lookup widgets.  But even then, it makes for a lot of visual clutter.

       

      In a Data Mart scenario, you're always doing the same lookups over and over again to common dimensions like Customer, Product, etc. 

       

      It would be nice if you could create, essentially a unconnected lookup inside a UDF.  So inside your UDF, you'd have a new function like:

       

        db_lookup( connection, tablename, match_criteria, result_column)

       

      So I could create a lookup called udf_GetCustomerDimID to my Customer Dim like:

       

      db_lookup( 'CustomerDb', 'CUSTOMER_DIM', CUSTKEY = in_Variable, CUST_ID)

       

      I'd pass in the customer lookup key value in "in_Variable", and it would pull the dimension surrogate key ID and return it as an integer, in this case.

       

       

      With this, you could have an Expression step in your main Mapping, that simply calls udf_GetCustomerDimID( CUSTKEY) and it would return the ID.  Nice and simple, no visual clutter.

       

      Behind the scenes, Informatica would have to treat this "db_lookup" function as a sort of unconnected, reusable (cached) lookup inside the mapping.