Chapter 41. Handling Prep Builder Errors
Errors in software suck. Who hasn’t spent time crying into their keyboard or banging their head against their screen asking the computer gods to solve their problems? Agreed—no one! As I have used Prep Builder a lot, I have developed a good sense of the common mistakes people make with the tool. This chapter will address the following trouble spots and pose some potential solutions:
-
Parameter errors
-
Blank Profile and Data panes
-
Errors within a Calculated Field
Parameter Errors
You may often see an error message including the word parameters. For example, the error message in Figure 41-1 reads Function 'DATEADD' can't accept parameters: (string, integer, string).
In this case, parameters are the data fields, numbers, and strings you are entering into your calculations. The calculation expects a certain data type in these fields and can’t accept a different one. In this specific case, my colleague was using a date in a string field. The DATEADD() function specifically needs a valid date for the calculation to increment.
Figure 41-1. A common error message regarding parameters in Prep
Be sure to check the data types that you are using in the calculation against what the syntax expects. Remember, you can see an example of each function’s syntax to the right of the Reference list in the Edit Field dialog.
Blank Profile Panes or Data Panes
There are multiple causes for blank Profile or Data panes, but I’ve found a few common culprits.
Changing a Calculation or Removing a Data Field Downstream
In Figure 41-2 I have created a duplicate NPS field, but I removed the original field in an earlier step.
Figure 41-2. Example flow to show the effect of changing fields
Obviously, this breaks the flow, but I’ve done it to show the source of the issue. In Figure 41-3, the red exclamation point above the duplicate NPS field shows where the error prevents the flow from running, but this isn’t where you would actually fix the flow; you need to do that in the earlier step where the original field was removed.
Figure 41-3. Example flow indicating the error
Use the Changes pane in Prep to work your way back through the flow to find where that original field was deleted. The error icon in the top-right corner of your flow allows you to read the error description, but the “View in flow” option in the bottom corner of this dialog box takes you to the error (Figure 41-4).
Figure 41-4. Example error with option to view the error in the flow
There’s no easy solution here, but at least this might help you find where to start working your way backward.
The Data Source Has Changed
When you are using any data tool, if you change the data source in any way other than adding more rows of data, you are likely to get errors. Prep Builder is no exception. Common changes to check for are:
-
The data source being used has been moved.
-
The data source has been deleted.
-
The columns used in calculations have been removed.
-
The columns used in calculations have been renamed.
All of these changes will likely result in Prep Builder returning a blank Profile pane, as no data load will be possible, or data fields will appear in the Clean step with only null or blank values (Figure 41-5).
Figure 41-5. Blank Clean step resulting from an error
Errors Within a Calculated Field
When errors occur in Prep Builder, they may not be as catastrophic as losing all of your data as in the case just shown. Calculated Field errors are the most common mistakes you will make, but generally they are very easy to fix when you know how to spot them.
Incomplete Calculations
When writing calculations, you will very quickly see when the calculation is not in the format that Prep Builder is expecting. For example, the calculation in Figure 41-6 is erroring because the IF statement does not have an END condition yet.
Figure 41-6. Error caused by a calculation not matching the expected syntax
By clicking the “1 error” arrow at the bottom of the Calculation Editor, you can quickly see what is missing in your calculation. When building IF statements, I often add the END condition early and then edit the calculation between the words IF and END so I can see when each condition is complete as the error disappears (Figure 41-7).
Figure 41-7. Non-erroring calculation in Prep Builder
Unsupported Functions
If you have worked with other data software before, sometimes you find yourself using tried-and-tested methods elsewhere. This is especially true for Tableau Desktop users who use Prep Builder. Using functions that are supported elsewhere but unsupported by Prep Builder—either in a certain area or altogether—can cause you a lot of frustration, even for simple functions like sum(), as shown in Figure 41-8.
Figure 41-8. Error caused by an unsupported function
To resolve this error, you would need to use either an Aggregate step or a Level of Detail (LOD) calculation, as these are the areas where Prep Builder does support calculating totals.
Summary
Errors occurring in software when you least expect them can be very frustrating. The time you spend decoding them is time lost from the analysis and production of other pieces of work. Blank Data panes are a sign that something is amiss within Prep, and the warning symbols can get you only so far, but hopefully this chapter has given you a starting point to approach these challenges.