4. Importing and shaping CAE result data

Crash simulation output is not a tidy table. An LS-DYNA run produces d3plot state databases, nodout and elout ASCII files, and binout binaries; PAM-CRASH writes DSY and TH time-history files; Abaqus emits .odb databases plus .dat and .fil text. None of these formats is directly consumable by Power BI. The DAX measures you will write in later chapters — HIC, 3 ms clip, chest deflection, femur force — all depend on a clean, correctly typed, correctly oriented injury-metric table. Power Query is where that table is built, and the decisions made here determine whether the downstream DAX is trustworthy or quietly wrong.

This chapter walks through a reproducible pipeline: loading solver output, normalizing time-history curves into a long fact table, attaching channel metadata, and validating the result before it reaches the model.

Know the shape of the source before you connect

Each solver family stores time histories differently, and the import strategy follows from that structure.

• LS-DYNA: nodout and similar ASCII files are block-structured — a header line, then rows of node/element IDs and result components, repeated per time step. binout is binary and requires a converter (LS-PrePost export, or lasso-python) to CSV before Power Query can read it.
• PAM-CRASH: TH files are typically whitespace-delimited with a time column followed by one column per sensor/channel; the channel legend usually sits in a companion .des or header block.
• Abaqus: history output from .odb is best exported via a Python script (odbAccess) to CSV; .dat files are fixed-width and need explicit column splitting.

The practical rule: convert binary or fixed-width formats to delimited text at the source, keep the raw export immutable in a landing folder, and let Power Query handle only parsing, reshaping, and typing. This preserves traceability — if a HIC value looks wrong, you can re-open the original export and compare.

Building the long-format time-history fact table

Wide time-history exports (one column per channel) are convenient for plotting in the solver but hostile to DAX. The target structure is a long fact table with one row per (run, channel, time) observation:

To unpivot a wide export in Power Query: select the Time column, choose Transform → Unpivot Other Columns. Power Query produces Attribute (channel name) and Value columns. Rename them to ChannelID and Value, then set Time_ms and Value to Decimal Number. Critically, set the locale explicitly on the type change step — European solver exports often use a comma decimal separator, and a silent misparse turns 78,31 into 7831.

// M excerpt: unpivot and type a wide PAM-CRASH TH export
let
    Source = Csv.Document(File.Contents("C:\CAE\Run_A\TH_export.csv"),
                [Delimiter=",", Columns=null, Encoding=65001]),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    Unpivoted = Table.UnpivotOtherColumns(Promoted, {"Time"},
                    "ChannelID", "Value"),
    Typed = Table.TransformColumnTypes(Unpivoted, {
                {"Time", type number},
                {"Value", type number}}, "en-US"),
    Renamed = Table.RenameColumns(Typed, {{"Time", "Time_ms"}})
in
    Renamed

For LS-DYNA nodout blocks, the same logic applies but the unpivot happens after a block-splitting step: filter rows containing the time-step header, extract the timestamp with Column From Examples, then fill down so every result row carries its time value before unpivoting the component columns.

Attaching channel metadata and units

A long fact table without units is a liability. Build a channel dimension table — one row per ChannelID — carrying the physical quantity, unit, sign convention, and the filter class used later for CFC filtering and injury-metric eligibility.

Merge this dimension into the fact query on ChannelID (or keep it separate and relate it in the model — the latter is preferred for a star schema). The SignConvention column matters: a femur force recorded as tension-positive will produce a negative "compression" peak unless you normalize the sign at load time. Decide the convention once, document it, and apply it consistently in both the fact table and every DAX measure.

Time alignment and resampling

Different channels in the same run may be sampled at different rates, and different runs may have different end times. For cross-run comparison and for windowed metrics (HIC is evaluated over a 36 ms or 15 ms window), align time bases before loading. Two practical options:

1. Keep native sampling and let DAX handle windowing with FILTER over Time_ms. Simplest, but windowed measures become expensive on large fact tables.
2. Resample to a common grid (e.g., 0.1 ms) in Power Query using Table.Range plus interpolation, or pre-resample in the solver export. Faster measures, larger table.

For a typical frontal impact with 150 ms duration at 0.1 ms resolution, one channel yields ~1500 rows; 40 channels across 20 runs gives 1.2 million rows — well within Power BI's comfort zone. Resampling is usually worth it.

Validation before the model loads

Add a validation query that surfaces the failure modes you will otherwise discover only when a KPI looks implausible:

• Row count per channel — a channel with zero rows means a silent parse failure.
• Time monotonicity — non-increasing Time_ms within a channel indicates a block-split error.
• Null and sentinel scan — solver exports use 1.0E+30 or -999 for "no data"; filter these out explicitly.
• Peak sanity check — compare the max Value per channel against the expected order of magnitude from the channel dimension.

Pipeline at a glance

The diagram below shows the full path from solver output to the injury-metric fact table, including the branch where binary formats are converted before Power Query touches them.

What "done" looks like

A correctly shaped dataset satisfies four conditions before any DAX is written. First, the fact table is long-format with a stable grain of one row per run, channel, and time. Second, every Value is typed as Decimal Number under an explicit locale, with sentinel values removed. Third, each ChannelID resolves to exactly one row in the channel dimension carrying unit and sign convention. Fourth, the validation query reports no zero-row channels, no non-monotonic time series, and no peaks outside the expected order of magnitude. When those four hold, the HIC, clip, and deflection measures in the following chapters operate on data whose provenance and physical meaning are both defensible — which is the only basis on which a CAE performance KPI can be trusted in a review.