2. Setting up Power BI for CAE data

CAE simulation output rarely arrives in a form that Power BI can consume directly. A typical crash or durability study produces dozens of result files per load case: nodal displacements, element stresses, energy histories, and solver log summaries, often spread across subdirectories named by configuration and iteration. Before any DAX measure can compute an injury metric such as HIC or a cumulative damage index, the raw solver output must be reshaped into a small number of well-typed tables with explicit keys. This section covers that preparation stage: how to import simulation results, how to structure them into a star-like schema, and how to establish the relationships that later DAX formulas depend on.

Choosing the import path for simulation output

Most CAE post-processors can export tabular results as CSV, and this is usually the most reliable route into Power BI. Prefer one CSV per result category rather than one file per load case, because Power BI's folder connector can then combine files with identical schemas automatically. For binary or proprietary formats (for example, an .odb or .h3d result database), export a reduced table from the solver's scripting interface first; do not attempt to parse binary result files inside Power Query.

A practical import pattern is to keep a landing folder per study, with a consistent naming convention that encodes the metadata you will later need as columns:

CAE_Export/
  HIC_Head/
    LC01_Frontal_56kph.csv
    LC02_Oblique_64kph.csv
  Chest_Deflection/
    LC01_Frontal_56kph.csv
    LC02_Oblique_64kph.csv
  Energy_Summary/
    LC01_Frontal_56kph.csv
    LC02_Oblique_64kph.csv

In Power Query, use Get Data → Folder, filter to .csv, and then Combine Files. Critically, do not accept the default combined query unchanged. Open the sample query and confirm that the promoted headers match across files; a single file with an extra column will otherwise produce a schema mismatch that silently nulls values. After combining, add a Source.Name-derived column so each row retains its originating file, then split that column into LoadCaseID and Configuration using Split Column → By Delimiter. This step is what makes the fact table self-describing and removes the need for manual per-file bookkeeping.

Structuring tables into a star schema

CAE data naturally forms a star schema if you separate what was simulated from what was measured. The dimension tables describe the test matrix; the fact tables hold the numeric results. A workable structure for injury-metric work is:

Two structural decisions matter most here. First, keep time-history data in its own fact table. A single crash pulse at 0.1 ms resolution over 150 ms yields 1,500 rows per channel; mixing those rows with scalar KPIs inflates the metric table and makes every KPI aggregation scan irrelevant detail. Second, give DimChannel a surrogate integer key rather than relying on free-text channel names, because solver exports often vary in capitalization and whitespace ("Head_Acc_X" versus "head_acc_x"), and a text join will silently drop rows.

When building DimLoadCase, derive it in Power Query by removing duplicates from the combined fact query on the metadata columns, then add an index column as the key. This guarantees referential integrity: every fact row's LoadCaseID exists in the dimension by construction. Avoid hand-maintained dimension tables for CAE work; they drift out of sync as load cases are added.

Establishing relationships

With the tables loaded, open Model view and create one-to-many relationships from each dimension's key to the corresponding fact column, with the dimension on the "one" side. Set cross-filter direction to single; bidirectional filtering is rarely needed for this schema and can produce ambiguous filter paths once both fact tables share dimensions.

Mark DimLoadCase and DimConfiguration as date-table-free dimensions and hide their key columns from report view. Then verify the model with a quick sanity check: place DimLoadCase[LoadCaseID] in a table visual and COUNTROWS(FactMetrics) beside it. Every load case should show a non-zero count. A blank or zero indicates a key mismatch, most often caused by leading zeros lost during CSV import (for example, LC01 read as text in one table and as a number in another).

Validation before moving to DAX

Before writing any measure, run three checks in Power Query or DAX Studio. First, confirm row counts: FactSignals should equal the sum of samples across all exported channels, and FactMetrics should equal the number of load case × configuration × channel combinations. Second, check for orphan keys with a left anti-join between each fact table and its dimensions; any returned rows indicate a broken relationship. Third, inspect the time axis: MIN and MAX of Time_ms per load case should match the solver's output window, and a maximum of zero usually means the time column was imported as text and sorted lexically.

Once the schema is stable and validated, the model is ready for the DAX layer: HIC integration over the acceleration time history, peak-value measures over FactMetrics, and time-windowed comparisons across load cases. The quality of those measures depends entirely on the key integrity and table separation established here, so treat this setup stage as part of the analysis rather than as preliminary housekeeping.