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 deal with 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, just add a validation rule for that field that requires it to contain a value, as shown here:

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

Transformations: providing a value

In some cases, if a value is missing, it's safe to replace it with a placeholder.  Use extreme caution when replacing missing fact values - using a zero for missing values may not throw off your totals, but it can definitely affect averages and other calculations.  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:

Fixed transformation when empty

A more robust solution is to set the field to a value when the field is empty.  This technique has a number of advantages. 

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. 

An example of the transformation and condition solution is shown here: