How do you find "code smells" in TimeXtender?

It's full-on 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 second edition we go a little less into the technical depth and look at functionality within TimeXtender that helps you find "code smells". Code smell is a term from software engineering that refers to pieces of code that could benefit from improvement through refactoring. In data warehousing, the equivalent is looking for clues that point toward efficiency or comprehensibility improvements.

Use the tools you already have: Data Profile, Reports, Logging

While you build your implementation, TimeXtender automatically handles data quality for you. Most people will encounter this via Primary Key checks: if your data source contains duplicate records for the key you've defined, TimeXtender (under default settings) will not allow those duplicates to pass through into your valid table. Key collisions are set aside in error tables where you can see which records didn't pass through and why. Normally you set up your keys and don't think about it again, but it's always good to periodically check whether your assumptions still hold. For example, if you add new data from a different source into an existing dimension, duplicates may arise: you won't encounter them in your valid table, but they will be written to the error tables.

Within TimeXtender you can view all Errors and Warnings directly via the Reports menu. So it's good, after working with keys or Field Validations, to do a quick check to see if any errors occur. If you've never looked at it, it's definitely worth taking a look: it may be catching a lot of data!

As an example, in the image below we see records that don't satisfy a Field Validation that only allows Product records through if they concern finalized products. For example, if I were to add a new source for product data and forget to correctly initialize that field, none of those records would come through. The error and warning reports show the contents of the _L and _M tables and let you filter the different types of errors.

"Known" errors that aren't actively resolved

In practice, I still occasionally come across "known" errors that aren't actively resolved. While this can work fine, it's an implicit way of dealing with a problem that's easily overlooked or forgotten during a handover. Because errors and warnings generate records in the _L and _M tables, this can also create a performance bottleneck if there are many faulty records. For example, if adding a new mapping to a table produces more duplicates than expected.

One aspect to keep an eye on when you deliberately use the PK quality checks is that the first record with a PK will pass through to the valid table, but the following records won't. Without a custom script, you can't influence the load order, and therefore you may load the wrong records into your valid table without being aware of it.

An example of such a situation is an incremental load scenario where your deltas can contain multiple rows for the same primary or business key. In such situations, you might want to keep the last row in the set instead of the first row, because the last record is the most recent. Since there's no direct control over the row order in the cleansing step between the raw and valid table, you'll need to find another way to determine the correct records. You could consider influencing the row order going into the raw table and hoping your DWH engine doesn't decide to deliver records in a different order. This will work until it doesn't; changes to the SQL engine, or perhaps migrating to a different type of engine such as Synapse or Snowflake, can have side effects in this area. A method that guarantees consistency is using an Aggregation table to pick the winner, or using a custom SQL script.

Data Profile

With the new release of TimeXtender, you have access to a handy new feature: the Data Profile. This gives you basic statistics on the contents of every column in your tables. Every time you execute your data, the statistics are updated. One of the most obvious uses is checking for NULLs without having to use the Query Tool in a Preview. For example, if you adjust your surrogate key setup, it's wise to take a quick look at the fields you're going to use for it.

Another handy option is being able to quickly see what the minimum and maximum values for a date field are: perhaps you don't expect to find dates in the future there, but they're present anyway because something changed in a business process, resulting in problems in your logic.

As a final example, there's the option to query the distribution of values in a categorical field, as shown in the image below.

Logging

When you carry out a performance analysis, the Execution Log Overview is very handy: you get insight into historical runtime, record counts, and error counts. This lets you quickly understand why your table is now taking longer to reload, or reveals that you forgot to turn Truncate Valid table before cleanse back on after disabling Incremental Load on a table.

In the image below, we see that the number of records in the valid table of the DIM_Product table in MDW has doubled in the last run compared to the previous runs. This happens because I turned off Simple Mode in the table settings because I wanted to implement a Field Validation. If you turn off Simple Mode, you need to turn Truncate valid table before data cleansing back on, since it's off by default for Simple Mode. Without checking the logging, this problem can lie dormant until it causes a problem in a report.

One of the simplest things to check is how much time each part of reloading a table takes up. From this you can conclude that a small table loads faster if the table is fully reloaded instead of incrementally.

In conclusion: TimeXtender contains plenty of options to verify that your implementation behaves as expected without dormant problems lurking. Once you have the potential issues under control, it becomes a lot easier to use generic tools such as the SQL Server Query Store to focus on your database performance.

Written by Ruairidh Smith,
Senior Consultant at E-mergo