How do you avoid hand-written CASE statements in TimeXtender?

It's almost spring, and that's a good time for a spring clean of your TimeXtender implementations. In this series of blogs, Senior TimeXtender Consultant Ruairidh Smith zooms in on typical things you can improve, especially for long-running implementations or implementations set up by multiple developers. These are things that are usually seen as "details" that are less important than delivering new data. In this blog he discusses how you avoid hand-written CASE statements.

Technical Debt

There's an English saying: "but in this world nothing can be said to be certain, except death and taxes" (Benjamin Franklin). To these two "certainties" I'd like to add a third: Technical Debt. Technical Debt can be described as future rework costs caused by applying a simple or quick solution to a problem that could better be solved in another, more time-consuming way. Just like entropy or a loan, Technical Debt carries "interest", causing the problem to grow over time and become increasingly difficult to address. At some point, all those "we'll do that properly later" items conspire to spectacularly bring your production environment to a halt.

Although the automation TimeXtender does for you already helps prevent a lot of Technical Debt, you'll always implement business logic yourself in SQL. By reviewing this code and keeping it as simple as possible, you keep your environment maintainable going forward. Often, if you're new to TimeXtender, this means you need to adjust the instincts you have from other tools, because TimeXtender automates certain things for you directly that you would otherwise solve with manual code.

How do you avoid hand-written CASE statements?

The first topic I want to touch on is one I come across very often: CASE statements in Field Transformations that determine which value should be stored in a field. Usually, in such a case, you'll want to create a solution that follows IF-THEN-ELSE logic. In the example we use here, we look at the Class field in the AdventureWorks Product table: this field has four values:

• NULL: unknown class
• H: high product
• M: medium product
• L: low product

A typical approach, especially for the SQL connoisseurs among us, is to write a CASE statement that picks up the description that belongs to the code. You'll find an example of such syntax in Figure 1. In this specific case you'll probably think: "the code documents itself", but in real situations the expressions are often more complex. Since there's a reasonable chance the logic will change over time, you'll need to maintain this code, and developers will need to read the code to understand why something happens that they didn't expect.

Conditional Field Transformations

In TimeXtender it's possible to automate this kind of transformation: Conditional Field Transformations. This approach makes it possible to implement the same logic, but in a way that's easier to read and maintain. See Figure 2 for an example that implements the setup of the original CASE statement.

The big shift in thinking you need to make is that you use Fixed Transformations for all values, with conditions for all situations that aren't the default (the ELSE in the IF-THEN-ELSE). You first create a Transformation for the ELSE, without a condition. Then, for each IF-THEN, you add a Fixed Transformation with a Condition attached that represents the IF.

As long as your CASEs don't overlap in terms of conditions, the order of the transformations with conditions doesn't matter much (as long as you have the ELSE at the top). If there's overlap between conditions, you need to put the condition that "wins" at the bottom.

The result is easier to understand for non-SQL developers (in my experience) and prevents syntax variations that can occur because different developers have different coding styles. You can also drag the transformations and conditions between fields, allowing you to quickly apply the same IF-THEN-ELSE to multiple fields.

Although this may seem trivial, it makes your business logic explicitly visible instead of hiding it in a Custom Transformation that you first have to open to read it in full. I'd recommend everyone implement new logic this way and set aside some time to go through the existing transformations. This way you prevent getting a visit, some future Friday afternoon at 16:58, from the proverbial Technical Debt Collector forcing you to fix a 1000-line CASE statement that you cobbled together a year ago from three Stack Overflow posts you ran through ChatGPT.

Written by Ruairidh Smith,
Senior Consultant at E-mergo