SQL SERVER – A Quick Look at expressor Data Quality Solutions

Last month I described the extension framework that allows one to easily add functionality to an expressor Studio installation.  I then used this added functionality – the input and output operators to SalesForce.com – to develop an example application.  But expressor has a second mechanism that allows you to easily enhance the functionality of your installation – reusable templates.  The idea behind this approach is that once you develop an operator that performs processing that could have value in other applications you convert this operator into a template that can be easily integrated into additional projects.  This is the approach expressor has followed to add data quality features to expressor Studio.  For those of you interested in using this functionality, you can download a Zip file containing the operator templates from www.WEBSMARTexpress.com.

So what’s the approach?

First, expressor and Melissa Data created a partnership through which expressor Studio users could obtain a key to use the Melissa Data WebSmart Suite .

Second, expressor created a collection of operator templates, one for each service in the WebSmart Suite.

And third, expressor packaged this collection of operator templates into an expressor library that you can easily integrate into any expressor library or project.

Let’s see how this all comes together.

Once you register with on the WEBSMARTexpress Web site, you are given the URL to download the expressor operator template library – WEBSMARTexpress.zip.  Do not extract this archive file.  Rather you simply import the library into an expressor workspace.

  • Open the workspace.
  • Select the Studio > Import Projects… menu item.
  • In the first window of the Import Projects wizard, browse to the file system location where you saved the operator template archive file and select the file for import.
  • In the wizard’s second window, select the LIB_WebSmart library and click Finish.  The library is added to your workspace.

If you now expand the library, you will see that it includes composite types, the operator templates, and some Datascript modules.

SQL SERVER - A Quick Look at expressor Data Quality Solutions library

Each composite type describes the return from one of the WebSmart services.  The scripting that provides the processing logic for each operator template is contained in a dedicated Datascript module.  You will not need to edit the coding within these Datascript modules but you will need to enter your Melissa Data key into the module MDVariables before you can access the WebSmart services.

Double click on the MDVariables entry under the Datascript Modules folder to open this module in the script editor and on line 13 replace num with your key and then save and close the editor.

SQL SERVER - A Quick Look at expressor Data Quality Solutions module

Now create another library or project that will include the dataflow in which you will use one of the operator templates.  Be certain to reference the LIB_WebSmart library.

SQL SERVER - A Quick Look at expressor Data Quality Solutions library_reference

When you are laying out the dataflow, note that the operator templates are listed on the Templates tab within the Transformers grouping of operators.  To use one of the operator templates, simply drag it onto the dataflow.  When you are configuring this operator, do not change any of the settings on its Properties panel, but you must open the Rules Editor.  In the screen shots that follow, I’m working with the email validation service, but usage of all the operator templates is identical.

SQL SERVER - A Quick Look at expressor Data Quality Solutions template_1

At a minimum, I must connect the incoming attribute that contains the email address to be verified to the Rule_1 input parameter Email.  The outgoing parameters are already bound to their corresponding attributes in the output record.  The structure of the output is predefined by the composite type EmailResponse, which was included in the WEBSMART library.

SQL SERVER - A Quick Look at expressor Data Quality Solutions template_2

If I also want to include other incoming attributes in the record emitted by this operator, I must drag them into the Rule_1 input parameter listing, add corresponding attributes to the Output panel, and drag the newly added output attributes into the Rule_1 output parameter listing.

SQL SERVER - A Quick Look at expressor Data Quality Solutions template_3_fullsize

Note that to handle these additional values I did not need to edit the coding in either the operator template or its associated Datascript Module.

And that’s all there is to it.  As your expressor dataflow executes, the email address in each record will be submitted to the WebSmart service for validation and the detailed information returned by the Web service will be added to the record emitted by the operator.

Through their extension framework and operator template functionality, expressor software has provided two very effective approaches for extending the functionality of an expressor Studio installation.  The extension framework provides a mechanism to add new functionality to Studio while the operator template/Datascript Module approach is a simple way to reuse logic that you have previously developed.  And the partnership with Melissa Data demonstrates how straight-forward it is to integrate expressor with functionality or services provided by partner organizations.

expressor is making this integration with Melissa Data available as part of their Desktop and Standard Editions.  You can download a trial of the Desktop Edition by clicking this link.  As always, I am impressed how quickly expressor evolves their product line and adds very valuable new capabilities to it every few months.   I encourage you to download their Desktop Edition trial.

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

SQL Utility
Previous Post
SQL SERVER – Reasons for Using Output Clause – Quiz – Puzzle – 10 of 31
Next Post
SQL SERVER – Non-Clustered Index and Automatic Rebuild – Quiz – Puzzle – 11 of 31

Related Posts

1 Comment. Leave new

  • Dear Sir
    Just one Days to go for achieving the miles stone..
    It will be great for us..


Leave a Reply