Even with a well-maintained data source and good ETL, you may end up with records that are missing data in certain fields.  These missing values can sometimes cause problems for your analysts.  Depending on your front end systems and your requirements, there are a number of ways to handle this issue. 

Leave the field empty

Analysis Services and some other front ends are very comfortable with missing values.  If you are using SSAS, values records that are missing amounts or even foreign keys will be handled automatically. In these cases, empty values may be acceptable, depending on your requirements.

Validation rules: removing the record

There are some cases where a record that is missing a value should be removed from BI altogether.  This is very rare, as most data warehouse philosophies call for the inclusion of all source system data regardless of quality.  

If you do want to remove records that are missing values for a given field, use the following steps to add a validation rule for that field that requires the field to contain a value. 

1. Right-click the field and select Field Validations.

   

    

2. In the right-hand pane, select the appropriate operator from the list at the bottom. In this case, we are choosing “Is not empty”, but “Is not null” may also be required depending on your circumstances.

   

    

Click the Add button to add the field validation, which will then be listed out beneath the field in the table.

Any record that is missing a value in that field will now be excluded. 

Transformations: providing a value

Instead of removing the record, it may be desirable to replace a missing value with a default value that can act as a placeholder. Nevertheless, developers must be extremely cautious when replacing missing values and consider carefully how they might affect any calculations.

For example, replacing a missing value with a zero may not affect totals, but it would definitely affect averages and perhaps other types of aggregations as well. This kind of substitution can also mask problems in the source system. Dimensional records, which may include optional or free-text fields, are better candidates for this technique.

Default values

TimeXtender has a Default transformation.  This will put a value you specify into any record that has no value for that field using a DEFAULT constraint.  An example is shown here:

1. Right-click on a field and select Field Transformations.

   

    

2. In the Operator drop-down, select “Default table column value”.
3. Enter a default value that will act as a placeholder.
4. Click the Add button to add the field transformation, which will then be listed out beneath the field in the table.

Fixed transformation when empty

A more robust solution is to set the field to a value when the field is empty.  This technique may have advantages over the default value technique described above. 

One advantage is that some source systems use NULL for missing text values, while others (notably AX) use an empty string as a placeholder.  The "Empty" condition will allow this substitution to work in both cases, while an empty string will defeat a Default transformation. Use the following steps to add a Fixed Value transformation to a field that includes a condition of “Is empty” for when it applies.

1. Right-click the field and select Field Transformations.

   

    

2. In the right-hand pane, select “Fixed Value” for the operator.
3. In the Value box, enter the value that will act as your placeholder.
4. Click Add to save the Field Transformation, which will be shown beneath the field in the table.

   

    

5. Right-click on the Fixed Value transformation and select “Add Condition”.

   

    

6. In the right-hand pane, select your field in the table above.
7. In the Operator box below, select “Is empty.”
8. Click the Add button to add the “Is empty” condition to the Fixed Value transformation.