An ETL (extract, transform, and load) transformation can occur in many ways. The programmer can extract intelligence from an individual field. They can do mathematical calculations to create a summary field. Also, a group of fields can be used to generate yet another value. Using the elements from the extract step the programmer can do many things utilizing derivation expressions.
Mathematical Operators in a Derivation
There are two types of operators, these are logical and mathematical. Mathematical operators are used on numeric values and usually return a number. Typical mathematical rules apply. Brackets () force the statement to be processed in a certain order, then multplication *, division /, addition + , and subtraction – are completed in the appropriate order. There is also the ability to load an array using the ampersand &.
Logical Operators in a Derivation
Logical operators return a TRUE or FALSE value. Logical operators compare values and return a single boolean response. These operators are equal =, not equal != or <>, greater than >, greater than or equal to >=, less than <, less than or equal to <=, IS – this operator tests for NULL values, IN, BETWEEN, LIKE, NOT, AND, and OR.
Operators in Action
In extracting the invoice data from the sales system, the programmer brings in the UNIT price and the QUANTITY sold. A multiplication operator would be used to calculate GROSS. UNIT * QUANTITY = GROSS.
The programmer can assign a list of codes to an array using the ampersand to separate the values, then check each associated value to see if it is in the array, building an artificial intelligence field that the business can use on reports. An example could include codes that indicate a customer is a reseller and any order for those customers could be indicated as resale orders rather than income orders.
A logical operator could be used to indicate large orders. When the GROSS exceeds a certain value the order could be flagged for a credit check. When GROSS > 123value returns TRUE, the order is flagged and an email is sent off to the finance credit department before the order is processed.
The pay rate group value could be set using a combination of the expressions. An employee whose salary is BETWEEN 10,000 and 20,000 would have a value set to GROUP1, then one who is greater than 20,000 but less than or equal to 30,000 would have the value set to GROUP2, etc.
There are many ways the expression operators can be used to build intelligence into an otherwise plain extract of data. Groupings can be generated, values can be flagged, combinations can be tested, and new information can be “made up” for the purpose of business reporting. The business determines the questions that need answers and most often the ETL programmer can build the data to provide the answers if the data needed to build the intelligence is available.
Recent Comments