Missing values (Analysis Services - data mining)

Applies to: SQL Server 2019 and earlier Analysis Services Azure Analysis Services Fabric/Power BI Premium

Handling missing values correctly is important for effective modeling. This section explains missing values and explains how to use SQL Server Analysis Services features to handle them when building data mining structures and models.

Definition of missing values in data mining

A missing value can signify a number of different things. Perhaps the field wasn't applicable, the event didn't happen, or the data wasn't available. It could be that the person who entered the data didn't know the right value, or didn't care if a field wasn't filled in.

Missing values provide important information in many data mining scenarios. The meaning of the missing values depends largely on context. For example, a missing value for the date in a list of invoices has a meaning substantially different from the lack of a date in column that indicates an employee hire date. Generally, SQL Server Analysis Services treats missing values as informative and adjusts the probabilities to incorporate the missing values into its calculations. By doing so, you can ensure that models are balanced and don't weight existing cases too heavily.

SQL Server Analysis Services provides two mechanisms for handling missing values. The first method controls null handling in the mining structure. Each algorithm implements the second method differently to process and count missing values in models that permit null values.

Specifying handling of nulls

Your data source might represent missing values as nulls, empty spreadsheet cells, N/A or another code, or an artificial value like 9999. However, for purposes of data mining, only nulls are considered missing values. If your data contains placeholder values instead of nulls, they can affect the results of the model, so you should replace them with nulls or infer correct values if possible. There are a variety of tools that you can use to infer and fill in appropriate values, such as the Lookup transformation or the Data Profiler task in SQL Server Integration Services, or the Fill By Example tool provided in the Data Mining Add-Ins for Excel.

If the task you're modeling doesn't allow missing values in a column, apply the NOT_NULL modeling flag when you define the mining structure. This flag indicates that processing should fail if a case doesn't have an appropriate value. If this error occurs when processing a model, you can log the error and take steps to correct the data that is supplied to the model.

Calculation of the missing state

Data mining algorithms treat missing values as informative. In case tables, Missing is a valid state. A data mining model can use other values to predict whether a value is missing, so a missing value isn't an error.

When you create a mining model, a Missing state is automatically added to the model for all discrete columns. For example, if the input column [Gender] contains the values Male and Female, the model adds a third value, Missing. The column's histogram includes the number of cases with the Missing state. If the Gender column is not missing any values, the histogram shows that the Missing state is found in 0 cases.

The rationale for including the Missing state by default becomes clear when you consider that your data might not have examples of all possible values, and you would not want the model to exclude the possibility just because there was no example in the data. For example, if sales data for a store showed that all customers who purchased a certain product happened to be women, you would not want to create a model that predicts that only women could purchase the product. Instead, SQL Server Analysis Services adds a placeholder for the extra unknown value, called Missing, as a way of accommodating possible other states.

For example, the following table shows the distribution of values for the (All) node in the decision tree model created for the Bike Buyer tutorial. In the example scenario, the [Bike Buyer] column is the predictable attribute, where 1 indicates "Yes" and 0 indicates "No".

The distribution shows that about half of the customers purchase a bike and half don't. Every case in this dataset has a value in the [Bike Buyer] column, so the count of Missing values is zero. If a case contains a null in the [Bike Buyer] field, SQL Server Analysis Services counts that row as a case with a Missing value.

If the input is a continuous column, the model tabulates two possible states for the attribute: Existing and Missing. In other words, either the column contains a value of some numeric data type, or it contains no value. For cases that have a value, the model calculates mean, standard deviation, and other meaningful statistics. For cases that have no value, the model provides a count of the Missing vales and adjusts predictions accordingly. The method for adjusting the prediction differs depending on the algorithm and is described in the following section.

Adjusting probability for missing states

In addition to counting values, SQL Server Analysis Services calculates the probability of any value across the data set. The same calculation applies to the Missing value. For example, the following table shows the probabilities for the cases in the previous example:

It might seem odd that the probability of the Missing value is 0.03%, when the number of cases is 0. This behavior is by design, and represents an adjustment that lets the model handle unknown values gracefully.

In general, probability is calculated as the favorable cases divided by all possible cases. In this example, the algorithm computes the sum of the cases that meet a particular condition ([Bike Buyer] = 1, or [Bike Buyer] = 0), and divides that number by the total count of rows. To account for the Missing cases, the algorithm adds 1 to the number of all possible cases. As a result, the probability for the unknown case is no longer zero, but a very small number, indicating that the state is merely improbable, not impossible.

The small Missing value doesn't change the prediction, but it improves modeling when historical data doesn't include every possible outcome.

If your data specifies every outcome, set the NOT_NULL modeling flag on the mining structure column to prevent probability adjustments.

Special handling of missing values in decision tree models

The Microsoft Decision Trees algorithm calculates missing-value probabilities differently from other algorithms. Instead of adding one to the total case count, the algorithm uses a different formula to adjust for the Missing state.

In a decision tree model, use the following formula to calculate the probability of the Missing state:

StateProbability = (NodePriorProbability)* (StateSupport + 1) / (NodeSupport + TotalStates)

The Decision Trees algorithm provides an additional adjustment that helps the algorithm compensate for the presence of filters on the model, which might exclude many states during training.

In SQL Server 2017, if a state is present during training but has zero support in a certain node, the algorithm makes the standard adjustment. However, if a state is never encountered during training, the algorithm sets the probability to exactly zero. This adjustment applies not only to the Missing state, but also to other states that exist in the training data but have zero support as a result of model filtering.

This additional adjustment results in the following formula:

StateProbability = 0.0 if that state has 0 support in the training set

ELSE StateProbability = (NodePriorProbability)* (StateSupport + 1) / (NodeSupport + TotalStatesWithNonZeroSupport)

The net effect of this adjustment is to maintain the stability of the tree.

Related tasks

These resources explain how to handle missing values.

See Also

Mining Model Content (Analysis Services - Data Mining)
Modeling Flags (Data Mining)