Back to Index

 

How to: Use data objects for reference lookups

Looking up reference data as each input record is being read in is a simple task in BlueSky Integration Studio.  You simply need a matching key value for both your input record and your reference table.  You can use any database or hash data object for reference lookups.  You cannot use the Text data object though since text files are not indexed and each lookup would require a full table scan to find the matching record.

A typical job with a reference lookup might look something like this:

In this job, we are reading data in from a daily sales table.  For each input record, the Join process is looking up the corresponding product data so that we can use some additional product attributes for the final output.  Finally the data is written to a MS SQL Server table.

You can also have multiple reference lookups.  For instance, in the example above, we might want to add another reference lookup for our customer attributes.   That might look something like this:

Optimization Tip: Although you can use any database object for reference lookups, you can also use Hash tables as well.  In fact, Hash tables were designed specifically for reference lookups and can make your job run up to 10 times faster!   This is because Hash table data is pre-read into memory prior to running the job.  This means that all lookups are done in memory rather than having to run a SQL statement against the database for each input record to find the matching reference data.  Hash tables are great for lookup tables with around 25,000 records or less, any more than that and they begin to take up a lot of memory in your jobs.  

To build a job using a reference lookup:

1.  Define your input data object, join object and output data object as normal.

2.  Add a third data object which represents you lookup table and draw a line from your lookup table to the Join object. Notice that the line is Blue, which represents a reference lookup.

3.  Define your reference lookup object as normal.  The difference is that you must also define 1 or more columns as the key column(s) in the ColumnLayout property.  This is how BlueSky Integration Studio knows how to match up (or Join) your input data to the reference table.  

 

4.  Now you must tell BlueSky Integration Studio how to join the input data object to the reference table.  Double-click on the Join object in your design to get to the mapping screen shown below.  On your reference table object there is an icon which represents the joins.  Click on this icon to define the mapping between your input data object and reference data object.

 

When you click the Join icon on the reference table, you will get the following window.  Simply drag the input data object column to the reference lookup column that will find the unique match.  For instance, in the window below we are telling BlueSky Integration Studio that for each input record, find the matching reference table record based on the ITEM_NUMBER matching.

 

See also: Found Property for Reference Lookups


© 2003 - 2007 Relational Solutions, Inc. - All rights reserved