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.
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: