11 Replies Latest reply on Sep 14, 2011 4:27 PM by TonyScalese

    lookup dimension- plz help !!

      Hi all,

      Can anyone help me with the concept of " lookup dimension " ? How to use lookup dimension ..?

      My requirement is that, I need to use looked up value in import script, I do not want mapping to be applied at Validation stage, I want mapping at import script itself. Can anyone help me in using lookup dimesion.

      Thank you in adavance !!
        • 1. Re: lookup dimension- plz help !!

          Lookup dimensions are used to store relationships that can be used in scripting. You cannot import data into a mapping dimension but you can use the stored relationship in other scripting needs.

          Think of a lookup dimension as another pseudo table in the FDM database that your scripts can leverage.
          1 person found this helpful
          • 2. Re: lookup dimension- plz help !!

            Please take a moment to review this posting:

            Forum Posting Tips & Etiquette

            There are a number of people on this forum that are happy to help. When someone takes the time to help you, it would be nice if you indicated that their replies were helpful. This serves 2 purposes, allows other forum users to identify answers which are correct and recognizes the efforts of those that are sharing their knowledge with you and the community.
            • 3. Re: lookup dimension- plz help !!
              Thank You for your reply !!

              Its really helpful. If possible, Could you please provide me an example script..! How can this functionality is used in scripting? This would be really helpful for me.

              Thank you for the information!!

              Edited by: james on Sep 13, 2011 8:16 AM
              • 4. Re: lookup dimension- plz help !!
                Robb Salzmann

                Here is an example exactly how to do a lookup:
                Function DoLookup(strField, strRecord)
                   Dim objCN, strConnection, strSQLQuery, objRS, strLookupVal
                   strLookupVal = "MyDefaultMember"
                   Set objCN = CreateObject("ADODB.Connection")
                   strConnection = "Driver={SQL Server};Server=LOOKUPSVR;Database=LOOKUPDB;Trusted_Connection=TRUE"
                   objCN.Open strConnection
                   strSQLQuery = "Select * from MyLookupTable where LOOKUP_KEY = '" & strField & "'"
                   Set objRS=CreateObject("ADODB.Recordset")
                   Set objRS = objCN.Execute(strSQLQuery)
                   If NOT objRS.EOF Then
                      strLookupVal  = objRS.Fields("LOOKUP_VALUE")
                   End If
                   DoLookup = strLookupVal
                End Function
                Robb Salzmann
                • 5. Re: lookup dimension- plz help !!

                  A little food for thought. While it is highly unlikely to happen, you should always account for what-if's in the code. The inline SQL offered in your example is prime for a SQL injection and/or buffer overrun attack.

                  What happens in the following scenarios for the field strField? :

                  - strField = ; DROP TABLE tPOVPartition (Assuming referential integrity did not prevent it, the table would be dropped from the database!)
                  - strField = ; Update tSecUser set AppSecLevel = 1 (Would set all users to highest level of security)

                  Things could get even uglier using xp_cmdshell or xp_regwrite as you could actually exectute other programs on the machine, update the registry, even create local accounts....

                  When working with data coming from an unknown source, I would strongly recommend sanitizing(constraining) the input to ensure this isn't a possibility.

                  a.) If you expect the field to be numeric, ensure all data is numeric
                  b.) If data input is text, ensure it does not exceed the number of characters expected. (prevent overrun opportunities)
                  c.) strip characters out which could lead to injection attacks ' and ; are the bit ones. Also

                  Additionally, you may want to consider using stored procedures where possible as it will :

                  a.) Automatically enforce type constraints as you have to pass parameterized values
                  b.) Depending on the version of SQL Server, Stored Procedures offer a performance benefit (pre-compiled execution steps). For something that is going to execute multipel times, you will want all the performance you can get.
                  1 person found this helpful
                  • 6. Re: lookup dimension- plz help !!
                    Robb Salzmann
                    Thanks for the feedback Beyerch2.

                    My intent was to provide a simple, yet working example, not to code production ready $cripts.

                    Robb Salzmann
                    • 7. Re: lookup dimension- plz help !!
                      Understood Robb. I just know from history that they will run with what samples we provide and providing the pitfalls in advance is optimal. I would wager that 9,999 times out of 10,000 there's absolutely nothing wrong with the sample provided here.

                      I also stayed at a Holiday Inn Express and watched Hackers last night too... (JOKING!)

                      • 8. Re: lookup dimension- plz help !!
                        Robb Salzmann
                        LOL, Busy night. Hopefully the OP will take the code and your sage advice together :)

                        Robb Salzmann
                        • 9. Re: lookup dimension- plz help !!
                          Thank you both for your answers !!

                          Well, If I want to lookup for a value within the same table, then how can I do that ?

                          To say clearly, I need to get the value to COLUMN A from COLUMN X based on some input string value. Hope this is clear.

                          input string is : ferrari

                          Col A | Col x | Col Y (corresponding value to col X)
                          567 | ferrari | 567

                          So, here based on my string "ferrari" I need to lookup in column Y and assign that value in col Y (ex: 567) to Col A (ex: 567)

                          Thank you in advance !
                          • 10. Re: lookup dimension- plz help !!
                            Robb Salzmann
                            Hi James,

                            The example I provided above will do just that. I doubt it will even need to be changed much.

                            If you're in need of help doing the implementation, I know a good consultant.... ;)

                            also, please review this post:

                            Robb Salzmann
                            • 11. Re: lookup dimension- plz help !!
                              And his name is Tony Scalese :)