3. Structuring CAE result tables

CAE result data rarely arrives in a form that is ready for analysis. A single solver run may produce dozens of load cases, each with multiple nonlinear iterations, and each iteration may carry hundreds of probe channels sampled at irregular intervals. When these dimensions are flattened into a spreadsheet without discipline, the result is a table where a single row mixes a load case identifier, an iteration counter, a probe location, and a scalar value — a layout that is technically readable but analytically hostile. This chapter establishes a repeatable structure for CAE result tables so that downstream pivoting, filtering, and automated reporting behave predictably.

The three dimensions of a CAE result record

Every measurement in a CAE result set can be described by three orthogonal dimensions. The load case dimension identifies the physical scenario: a static cornering load, a 3g bump, a thermal soak, or a modal excitation. The iteration dimension captures the solver's progression — for nonlinear static analyses this is the increment/iteration pair, for transient analyses it is the time step, and for optimization loops it is the design cycle. The probe dimension identifies where and what was measured: a node ID, an element centroid, a named sensor location, and the physical quantity (stress, displacement, temperature, acceleration).

A normalized table places each of these dimensions in its own column and stores exactly one measured value per row. This is the "long" or "tidy" format, and it is the only layout that survives contact with pivot tables, Power Query, and Python's pandas without special-case handling. The alternative "wide" format — one column per probe, one row per iteration — is convenient for human reading but breaks as soon as probe counts vary between load cases or as soon as a new sensor is added mid-project.

A normalized schema for CAE results

The following schema has proven robust across structural, thermal, and fatigue workflows. It is deliberately minimal; project-specific metadata can be appended as additional columns without disturbing the core structure.

Two design decisions deserve emphasis. First, step and iteration are separate integer columns rather than a combined string. This allows filtering to converged states (status = "converged") and computing per-step maxima without parsing text. Second, unit is stored per row rather than assumed globally. Mixed-unit exports — a displacement in mm alongside a stress in MPa — are common when results are assembled from multiple solver decks, and an explicit unit column prevents the silent scaling errors that plague post-processing.

Naming conventions that survive automation

Free-text load case names are the single most common cause of broken report automation. A name like Brake 3g (worst) contains a space, parentheses, and an ambiguous qualifier; it will fail as a column header, break a file path, and resist pattern matching. Adopt a fixed grammar instead:

<TYPE>_<DIRECTION>_<MAGNITUDE>_<VARIANT>

LC03_BRAKE_3G_BASELINE
LC07_CORNER_1.2G_LF
TH01_SOAK_120C_STEADY
MOD02_MODE3_FIXEDBASE

The grammar is uppercase, underscore-delimited, and free of spaces and punctuation. Each field is drawn from a controlled list documented in the project's data dictionary. The VARIANT field distinguishes baseline from sensitivity runs without requiring a separate column. Probe identifiers follow the solver's native convention (N1042 for nodes, E2087 for elements) so that traceability back to the FE model is preserved without a lookup table.

Quantity codes should also be standardized. Rather than von Mises stress, VM stress, and sigma_vm appearing in different exports, fix one code per quantity — S_VM, U_MAG, TEMP, A_MAG — and document the mapping. This is what allows a pivot table to aggregate across load cases without manual relabeling.

Data validation for engineering datasets

Validation in a CAE context is not about preventing typos; it is about catching physically impossible or analytically misleading records before they reach a report. Three validation layers are worth implementing in the workbook itself, using Excel's Data Validation and conditional formatting.

Domain validation constrains categorical columns to the controlled vocabulary. A list validation on load_case referencing a named range of approved identifiers rejects any value not in the dictionary. The same applies to quantity and unit. This catches the most damaging error class: a load case that is silently misspelled and therefore excluded from every pivot.

Range validation constrains value to physically plausible bounds per quantity. A stress probe on a steel bracket might be validated to -50 to 1200 MPa; a displacement to -500 to 500 mm. Values outside the range are flagged rather than blocked, because a genuine overload case may legitimately exceed the nominal bound. Conditional formatting with a red fill on out-of-range cells makes these visible during review.

Referential validation checks that every probe_id in the results table exists in the probe master list, and that every run_id exists in the run log. In Excel this is implemented with a COUNTIF against the master range, returning a flag column:

=IF(COUNTIF(ProbeMaster[probe_id], [@probe_id])=0, "ORPHAN", "OK")

Orphan probes typically indicate a renamed node in a model update or a truncated export, and they will silently drop out of any join-based report.

From normalized table to report-ready summary

Once the table is normalized and validated, the reporting layer becomes trivial. A pivot table with load_case on rows, quantity on columns, and MAX(value) as the value field produces the envelope summary in seconds. A second pivot with step on rows and MAX(value) filtered to a single load case produces the convergence history. Neither requires manual rearrangement, and both refresh automatically when new runs are appended.

The SVG below illustrates the transformation from a raw, mixed export to the normalized record structure, and shows where each validation layer intercepts the data flow.

Practical checklist for the first pass

When normalizing an existing export, work in this order: split any combined identifier columns into separate fields; map free-text load case names to the controlled grammar; add explicit unit and status columns; apply list validation to the categorical columns; and only then build the first pivot. Attempting to pivot before validation produces summaries that look correct but silently omit misspelled load cases or include unconverged spikes. The normalized table is the single source of truth for every downstream report, so the effort invested in its structure is repaid at every refresh cycle.