SQL SERVER – expressor Studio 3.4 Rules Editor – ETL Graphical Coding Tool

New in the expressor Studio 3.4 release is the rules editor.  This graphical coding tool replaces the transform editor of earlier versions.  The rules editor works in concert with the newly introduced attribute propagation functionality to minimize the amount of data mapping and coding you need to provide.  The expressor folks are telling me that in a future release we will be able to save and reuse rules, which will make everyone’s  application development tasks even simpler and less prone to errors.

So what’s attribute propagation?  expressor’s starting point observation is that in any transformation most values are either copied from the input record to the output record without any changes or dropped from the output record.  What attribute propagation does is transparently perform these operations without a requirement for coding.  Consequently, within an operator performing a transformation, your only responsibility is to manipulate the values that do require modification.  And this is where the rules editor becomes involved.

Let’s consider an example.  A company wants to give any employee whose tenure with the company started before January 1, 1995 a 5% salary increase.  The application will read a list of employees from a SQL Server database table, determine if the employee’s hire date was before January 1, 1995, and if appropriate calculate a new salary.

Of course expressor offers many ways to develop this application, but I’ll just highlight one approach as it’s the rules editor that I want to discuss.

With expressor, this application can be developed using three operators: a SQL Query operator to select the qualified employees from the SQL Server database table, a Transform operator to carry out the salary calculation, and a Write Table operator to write the modified record back to the database table.  The SQL Query operator executes a SELECT statement that limits the result set to only those employees who qualify for the raise.

SELECT * FROM employees WHERE HIRE_DATE < CONVERT(VARCHAR(23), ‘1995-Jan-01’ , 121)

To continue developing the application, I open the rules editor of the Transform operator.

In the left-hand panel is a listing of the attributes in each incoming record.  The names of these attributes are identical to the table column names, although expressor allows you to easily rename if desired.

The right-hand panel shows the effect of attribute propagation.  That is, each incoming attribute has been transferred to the outgoing record.  The right-facing arrow before each attribute name indicates that the attribute will be automatically initialized and that no coding is required.

To alter the value assigned to the outgoing record’s SALARY attribute, start by using the mouse to drag a connection between the input and output attributes.  An expression rule is displayed.  The default code simply transfers the incoming value to the output.  Also note that the symbol before the output attribute’s name has changed from the right-facing arrow to a diamond, indicating that this attribute is initialized through code.

All I need to do to complete the application is modify the default code, applying the salary increase and, since all salaries are stored as whole dollars, apply a rounding, which I can easily do by clicking the Math button in the Edit tab of the ribbon bar and selecting round from the drop down list of functions.

But what happens when my processing logic is more complex, for example, I only want to give the raise to employees whose commission percentage is smaller than 0.35%.  Then I replace the expression rule with a function rule, where I have full access to the underlying code and I can use the filter helper function to enrich my logic.

In this example, I demonstrated the use of an expression rule and a function rule in the Transform operator.  These rule types are also available in the Join operator.  Within the Transform operator you can also work with a lookup expression rule and a lookup function rule.  These are the rules you use to access the lookup table artifact, another new feature of expressor Studio 3.4.  And in the Aggregate operator you can use an aggregate expression rule, which allows you to select an aggregating function from a drop down control, or an aggregate function rule that allows you to completely manage the processing logic.

While the example developed in this article was quite simple, I think you will agree that attribute propagation and the new rules editor provide a high level of support when your applications require coding.  I continue to be intrigued by the many innovations expressor is putting into their desktop ETL tool.  You can download their tool by following this link.

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