Why Are Records Rejected in My Data Integration Load?

EPM Tech Tips · Data Integration · 06

A load that ends with a warning or a red status, with a target that doesn't match the source file, usually has one of a few causes. Rows get dropped at one of three points: during Import, when the file doesn't match the import format; during Validate, when a source value has no valid mapping; or during Export, when the target rejects the member or the value. The messages sit in the Workbench, Process Details and output files, so it's easy to fix the wrong thing. Use this fixed order.

Before you start

• Reproduce the problem. Note the integration name, POV (location, category, period) and Process ID of the failed run. Go to Application › Data Exchange, then on Data Integration select Actions › Process Details, open the Process ID and download the log. Save it now, because old process logs are purged. You need a Service Administrator or a role with Data Integration access.

• Pick your route. If the imported row count in the Workbench is already lower than the file's, check the import format (delimiter, field positions, amount column and header rows) before anything else. If Validate failed (its Workbench icon is not blue), start with steps 1 and 2. If Validate passed but the export ended with warnings or totals don't match, go to steps 3 to 5.

Step 1 · Are there unmapped source members?

What happens. Validation confirms that every source member maps to a valid target member. A new source account or cost center has no mapping yet, so validation fails and the flow stops.

How to check.

1. On the Data Integration home page, click the actions icon to the right of the integration and select Workbench.

2. Click Validate. If any dimension is unmapped, the Validation Errors page opens automatically and shows how many members are not mapped.

3. Under Dimension Name, select the dimension or member that failed.

Fix. Click Fix Maps and add the mapping on the Map Members page. Then rerun Validate. If the source adds members often, a Like mapping with a default target catches new values, but review what falls into it every period.

Step 2 · Is the wrong mapping winning?

What happens. A source value can match more than one rule. Data Integration applies mapping types in a fixed precedence: Explicit, Between, In, Multi-Dimensional, then Like. Within a type, Processing Order decides and sorts alphanumerically, so 100 runs before 20. A row can map "successfully" to the wrong member, or to ignore, and vanish silently.

How to check.

1. In the Workbench, use the Show drop-down and select Invalid Data, then Ignored Data. Ignored rows are not exported to the target.

2. Click a source amount and select View Mapping to see which rule translated each dimension.

3. In Map Members, look for overlapping Between, In or Like rules and processing orders of different lengths.

4. Target expressions replace member mapping for that dimension, and rerunning Validate in the Workbench picks up mapping rules only, not expressions. Rerun the integration from Import to apply changed expressions.

Fix. Make the rule specific (Explicit beats Like), pad processing orders to one length (010, 020, 100), and remove unintended ignore mappings.

Step 3 · Does the target member exist and accept data?

What happens. Validation checks that a mapping exists, not that the target member can store data. At export, the target rejects rows where the member doesn't exist in the outline, isn't valid for the cube you load, or is a parent or Dynamic Calc member that can't hold input. The integration ends with warnings and the rows are missing.

How to check.

1. In Options for the integration, turn on Display Validation Failure Messages (Process Details calls it Display Validation Failure Reasons). The output file then lists rejected cells, the intersection and the rejection reason, up to 100 rejections.

2. Rerun the export, then in Process Details click the Output link for that Process ID and open the .out file.

3. For each rejected member, confirm in the Dimension Editor that it exists, that it's valid for the target cube, and that its Data Storage makes it a stored, level-0 member.

Fix. Add or correct the member in the target (or in EDM, then sync), or change the mapping to point at a valid level-0 member. In Financial Consolidation and Close and Tax Reporting, you can also turn on Check Intersection to get an Intersection Check Report from the Validation Errors tab, which flags invalid intersections, cells you can't write and invalid members (administrators also need Enable Data Security for Admin Users; Full Workflow mode only).

Step 4 · Do the period and category mappings point where you think?

What happens. The POV category maps to the target Scenario, and its Frequency (Monthly, Quarterly, Yearly, Daily) decides which Target Period column is read in Period Mapping. If the period key or application mapping is wrong, data loads to the wrong period or the step fails.

How to check.

1. From the Data Integration home page, select Actions, then from the Setup actions drop-down select Period Mapping. Check the Global Mapping row for your period key, then any Application Mapping or Source Mapping that overrides it.

2. Confirm Target Period Month and Year Target match real members in the target.

3. From the same Setup actions drop-down, select Category Mapping and confirm the Target Category is the scenario you expect and the Frequency is correct.

Fix. Correct the mapping. Define global period mappings at the most granular level, and use application mappings only where a target needs a different calendar.

Step 5 · Does the load method match the data?

What happens. The Load Method in Options decides what reaches the target. Numeric loads numbers only, dropping text, Smart List and date values, and does not enforce Planning data security. All data types with security loads all data types, enforces security for non-admin users, and caps non-admins at 500,000 cells. A run by a non-admin can therefore reject cells the admin's run accepts.

How to check.

1. Open Options for the integration and check Load Method and Date format for date data.

2. Compare who ran the job (Process By in Process Details) with that user's write access on the rejected intersections.

3. Check the Export Mode you used: Merge, Replace or Accumulate (Subtract exists only for EPCM targets). Replace clears the loaded slice before writing, which can look like lost data.

Fix. Use All data types with security for non-numeric values, grant write access or run the load with the right account, and confirm the export mode with the data owner. Warning: a Replace rerun clears existing data for the loaded Year, Period, Scenario, Version and Entity, so confirm the file is complete first.

Step 6 · Did the Check step pass?

After Export, click Check in the Workbench to run the check log for the current POV. Check rules are grouped in a check rule group, built in Data Management, and assigned to the location in Edit Integration › Location Attributes. A failed rule means the data loaded but doesn't reconcile. Read the check log before calling the load done.

Quick checklist

1. Unmapped source members · Workbench › Validate › Validation Errors › Fix Maps

2. Mapping precedence and processing order · Workbench › Show › Invalid / Ignored Data, View Mapping

3. Target member exists, valid for cube, level-0 · Options › Display Validation Failure Messages, Process Details › Output

4. Period and category mapping · Actions › Setup actions › Period Mapping and Category Mapping

5. Load method, user security, export mode · Options › Load Method, Process Details › Process By

6. Check log · Workbench › Check

Pedro San Martín · Founder & Principal, Asher & Company