2 Replies Latest reply: Jan 17, 2013 10:17 AM by 985362 RSS

    External data base lookup from EDQ process

    985362
      Hi All,

      We have a requirement to look up an external database during an EDQ process. The source is a csv file with item information from legacy system. During the EDQ process, we are trying to look up the Oracle Inventory database ( ideally using a SQL query) and bring certain additional fields into EDQ. This is very similar to what the "Lookup and Return" processor would do, except that the look up should happen against an external database.

      Bringing the entire data set from Oracle as EDQ staged data is an option that we are trying to avoid since it is a constantly changing and huge database. Please let me know if you can think of any way I can achieve this without bringing a local copy of the external database into EDQ.

      Thanks,
      Romeo
        • 1. Re: External data base lookup from EDQ process
          Mike-Matthews-Oracle
          Romeo,

          This is simple to achieve. Add a Data Store connection to the database, then right-click on Reference Data in the tree, and select New Lookup, then select External Data as the source for the lookup on the first page of the wizard. You can then select which table, the lookup column or columns and the return column or columns.

          Make sure you have suitable indexes for the lookup in the DB for efficient performance.

          In general, EDQ supports three types of lookup into reference data:

          1. Using Reference Data mastered in EDQ

          This is ideal when using simple lists or maps as 'DQ rules' that you may wish to derive or add to profiling or from iterative development of DQ services.

          2. Using Staged Data not mastered in EDQ but captured and indexed for lookup purposes by EDQ

          Ideal if you have reference data in a file format, but which may change outside of your control; for example, list data from the internet

          3. Using External Data not mastered in EDQ, and looked up dynamically

          This is ideal if you need a dynamic lookup into data that changes in a database externally, or simply if you want to master all reference data outside of the DQ environment.

          See the online help for more details:

          http://www.oracle.com/webfolder/technetwork/data-quality/edqhelp/Content/concepts/reference_data.htm

          Regards,

          Mike
          • 2. Re: External data base lookup from EDQ process
            985362
            Mike,

            This is very helpful and resolves my issue. Thanks for your quick response.

            Thanks,
            Romeo