Pigment Null Value Handling

Overview

Null or blank values in dimension columns are one of the most common causes of push failures in planning tools. Pigment requires every dimension column to reference a valid member -- an empty department, a missing cost center, or a blank location will cause the entire row to be rejected. VersionForge's null handler detects these gaps before the push and replaces them with well-defined placeholder values.

Why Nulls Matter in Planning Models

Source systems frequently contain records with incomplete dimensional tagging:

• A newly hired employee has not been assigned to a cost center yet
• A journal entry was posted without a department segment
• A vendor bill lacks a location tag because the vendor operates remotely

If you push these records as-is, Pigment rejects them. But if you simply skip them, your planning model has gaps -- totals do not reconcile and headcount is understated. The null handler provides a middle ground: the data flows through with a clear marker that says "this dimension is unassigned."

How the Null Handler Works

1. Analyze

VersionForge scans all records being pushed and checks every dimension-mapped column (columns with a dimensionRef in the import configuration). Metric columns, date columns, and text columns are skipped -- only dimension references trigger null analysis.

For each dimension column with at least one null or blank value, the handler records:

• The column name and dimension reference
• The count of affected records
• Whether the placeholder value already exists in Pigment
• Whether Pigment-side autoCreate is enabled for this column

2. Generate Placeholders

Placeholder names follow the convention 999-No {Dimension}:

| Column Name | Placeholder Value | |---|---| | department | 999-No Department | | cost_center | 999-No Cost Center | | location | 999-No Location | | business_unit | 999-No Business Unit |

The 999- prefix sorts these placeholders to the bottom of alphabetical dimension member lists, making them easy to identify in Pigment reports and dashboards.

3. Ensure Members Exist

Before applying replacements, VersionForge checks whether each placeholder member already exists in Pigment. If it does not:

• If autoCreate is enabled on the column: no action needed -- Pigment creates the member on push
• If autoCreate is disabled: VersionForge creates the member via the Pigment metadata API (POST /api/v1/applications/{appId}/entities/{entity}/members)

4. Apply Replacements

Null and blank values in dimension columns are replaced with the corresponding placeholder. The replacement produces a new record array -- original records are never mutated.

Null Analysis Summary

After analysis, the null handler produces a summary that is surfaced in the Safety Gate review queue:

Null Replacement Summary
  Total records affected:    312
  Total field replacements:  487
  
  department:    187 records → "999-No Department" (exists in Pigment: yes)
  cost_center:   203 records → "999-No Cost Center" (exists in Pigment: no, will create)
  location:       97 records → "999-No Location" (autoCreate enabled)

This summary lets reviewers understand the scope of null replacements before approving the push.

Configuration Options

Control null handling behavior in the sync profile target configuration:

{
  "importConfigId": "cfg_abc123",
  "applicationId": "app_xyz789",
  "nullHandling": {
    "enabled": true,
    "placeholderPrefix": "999-No",
    "skipColumns": ["optional_tag"],
    "flagForReview": true
  }
}

| Option | Default | Description | |--------|---------|-------------| | enabled | true | Enable or disable null replacement. When disabled, null values are sent as-is (and will likely be rejected by Pigment). | | placeholderPrefix | 999-No | Prefix for generated placeholder names. | | skipColumns | [] | List of dimension column names to exclude from null analysis. | | flagForReview | true | When true, records with null replacements are flagged in the Safety Gate for manual review before push. |

Best Practices

Review null trends over time. If a particular dimension column consistently has high null rates, the root cause is likely a data quality issue in the source system. Work with the source system owner to improve data entry rules rather than relying on placeholders permanently.

Filter on placeholders in Pigment. After data loads, use Pigment's filtering to find all rows tagged with 999-No * placeholders. This gives you a focused view of unclassified data that needs attention.

Clean up retroactively. When a previously null dimension value gets assigned in the source system (e.g., the employee is finally given a cost center), the next VersionForge sync will update the row with the real value, removing the placeholder automatically.