I have been following expressor software for some time now and they have recently released a new version of their expressor Studio desktop ETL application. I am pleased to find out that the download and installation experience of this application has been greatly simplified. expressor Studio no longer requires users to install a license key after they download and install the product. They have also eliminated a Microsoft Visio dependency from their product. Removing the license requirement and Visio dependency has made download and installation much easier.
Once you get past the download and install process, you are greeted by a clean and familiar UI, which feels natural to most users of Windows applications, such as Microsoft Office. The 3.2 release also introduces an error handling framework, a new SQL query operator, and type constraints on expressor’s metadata layer, which they calls Semantic Types. The rest of this article will focus on what these three enhancements mean to users.
A welcome addition to expressor’s ETL software is the ability to handle data validation errors related to field and record-level processing within an application. Users can predefine how to handle errors when they occur at the field or record level. There are up to five error handling options, depending on the operation. Users can simply abort the entire dataflow, skip the offending record entirely, redirect the record to a file or a different branch of the dataflow for additional processing, and skip or reject all remaining records. The addition of error handling alone is very powerful, but when combined with Semantic Constraints, you can truly appreciate what expressor brings to the data integration world.
The expressor Studio error handling framework allows users to redirect invalid records for post-processing.
Before we jump into Semantic Type constraints, let me provide you with some high level background on Semantic Types. Semantic Types are internal to expressor, and they provide a uniform way of expressing data within the application. When expressor Studio connects to a relational or flat file data source, the application automatically creates an internal Semantic Type for you. Typically, this is just a direct mapping of your external data format (schema) to their internal format. Users can also define their own Types and reuse them as shared types across one or more expressor dataflows. This feature is extremely helpful if your source data changes and you don’t want the changes to affect any subsequent transformations. Users are able to simply map in new source data to an existing Semantic Type and their application will continue to work as if the source never changed. The same is true on the target side.
Constraints are simply patterns and rules that you apply to Semantic Types. For example, you can define rules on how to validate email addresses, birthdays, phone numbers, and SSNs during the mapping process. Depending on the type of a field, you can specify a regular expression, a min or max value, or allowed values. A replacement value can be specified for use when a constraint definition is not met. When data does not conform to the constraint and a default value is not used, the error handling framework discussed above manages the erroneous data.
The expressor Semantic Type editor allows users to set constraints for each field attribute. As shown in the screenshot above, a simple Regular Expression constraint has been set for “email_address” , which rejects all records that do not include an “@” symbol and do not have a “.com” or “.edu” or “.gov” or .”info” extension.
Last but not least is the addition of an SQL Query operator. expressor now enables users to execute their own Select statements on the database prior to extracting any data. What’s interesting about their implementation is that they are still able to create a schema and a Semantic Type automatically no matter how complex the select query is. This new operator greatly improves expressor’s ability to selectively extract data from a database and even apply some transformation logic to the data during the extraction process.
The editor for the SQL Query operator also users to define SELECT statements.
Overall, I am very impressed with how fast expressor is adding functionality to their product. They are on pace to deliver significant functionally every 60 days. expressor Studio is a great application for users who want a simplified user interface for building data integration applications. If you have a data integration or ETL project that involves SQL Server or any other relational database, or if you are like me and like to explore new technologies, then definitely give expressor Studio a try. You can download by clicking this link – and best of all, It’s free.
Reference : Pinal Dave (https://blog.sqlauthority.com)
