SQL SERVER – Introduction to expressor 3.4 Lookup Tables

In this blog post, I am going to take a closer look at expressor’s new and extremely versatile implementation of lookup tables, which they are releasing as part of the upcoming expressor 3.4 product release.  As creation and use of the lookup table can be managed completely through simple-to-use graphical interfaces, it is very easy to utilize this feature in expressor data integration applications.  And for developers who want full control over the functionality, an API provides direct access to the table allowing their applications to read, write, update, and delete table content.  Let’s see how this all comes together!

The lookup tables themselves are yet another reusable artifact within expressor Studio.  Consequently, a user’s tables can be easily reused in multiple applications.  Moreover, the scope of the table can be limited to a single ETL dataflow, to all dataflows in a project, or to all projects across all workspaces.  Table scoping is restricted by storing the table’s underlying files within the project’s directory hierarchy.  Global scoping is managed by making the table definition available to your applications and storing the table’s underlying files in an accessible file system location.

To create a lookup table, I open a project or library and start the New Lookup Table wizard by clicking the New Lookup Table button on the expressor Studio 3.4 ribbon bar.  I can then enter a name and descriptive content and move to the next step.  I am now ready to describe the structure of the lookup table.  Each of the table’s columns is referred to as an attribute.  I can manually add attributes by clicking the appropriate button in the ribbon bar to open the Add Attribute screen.

But a far more efficient procedure is to base the table’s structure on an expressor Composite Type, which is a grouping of attributes that describe the fields in a file record or the columns in a database table.  The Composite Type Assign button at the top of the screen lets users select a type.  Since in most cases a lookup table will mirror the structure of a file or database table, you can see why this approach is so useful.

Once the structure of the table has been defined, I can select attributes as table keys.  In my example, I’ve created a unique key named Place, which is based on the attribute named place, a unique value across all the records to be stored in the table.  I’ve also created a non-unique key based on the attribute party.  My table will allow lookup of a specific US president or all presidents from a selected political party.  You may define as many non-unique keys as your application requires.

The last property I need to specify is the file system location where the database management system that implements the lookup table will store the data.  The File Connection Assign button at the bottom of the screen opens a browser from which I can make this selection.

Encapsulated within the lookup table’s definition is a description of its structure, identification of its key columns, and its location within the computer’s file system.  That is, the table definition is completely self-contained and a simple reference to the table artifact is all that is needed to use the table in an application.

With expressor 3.4’s new coding paradigm that uses a graphical control termed the Rules Editor, accessing a lookup table from within an application is as straight-forward as anything could be.  Lookups against the lookup table are performed within an expressor Transform Operator, so once I position the operator into my dataflow and connect it to the upstream operator and downstream operator, I can open the operator’s Rules Editor and add a Lookup Expression Rule.

From the Lookup dropdown control I can select the name of the lookup table, and from the Key dropdown control, select the key column.  As soon as I make this selection, the left and right panels are populated with the names of the incoming and outgoing parameters.  That is, a value for the key column place will be used to select an entry from the lookup table that contains values for lname, fname, and party.  For each incoming key value, one president’s details will be retrieved from the table.

But what happens if I use the non-unique key?

Again, the incoming and outgoing parameters appear but now the frame shows an icon that indicates that this rule is an iterative rule.  That is, possibly multiple records will be emitted for each incoming record.  This is absolutely correct as more than one US president have been members of each political party.

With just a few clicks of the mouse, I was able to access the data in a lookup table, select entries based on unique or non-unique keys, and emit one or more records from the Transform Operator.

Of course, it is possible that the lookup table does not contain a matching entry.  In this case, the Lookup Expression Rule gives me several options.

• I could output a record with nil values for the attributes representing data in the lookup table.
• I could escalate the decision as to what to do to the Transform Operator, which can be configured to either skip or reject the record, or even abort execution of the application.
• Or, I could populate the outgoing record and, if desired insert this content into the lookup table.

I can specify which approach to take in the dropdown control labeled On miss.  Output Nil is the default option and Escalate Error will push the processing decision up to the Transform Operator.  Selecting the Generate Record option displays additional controls that I could use to initialize the outgoing record and enable table writes.

Although I won’t discuss it in this blog post, if you really need to manage the contents of the lookup table, expressor 3.4 also includes an API that allows you to update and delete table entries.  With this in mind, your lookup tables can be completely self-contained.  That is, once they are initially populated, their content can remain current and there will be no requirement to periodically recreate the tables.

I am very impressed with the new functionality in expressor 3.4 and suggest you download this latest version of the software when it becomes generally available in mid-September.

Reference:  Pinal Dave (https://blog.sqlauthority.com)