2. Power BI data model foundations for CAE results

CAE result data rarely arrives as a single tidy table. A typical crash or occupant-safety program produces hundreds of simulation runs, each with multiple load cases, and each load case yields a set of injury metrics per occupant. If you load those exports directly into Power BI and start writing measures, you will quickly hit two problems: relationships become ambiguous, and aggregations double-count. The fix is a deliberate star schema in which fact tables hold measurable events and dimension tables hold the descriptive context used to slice them.

This chapter builds that model for a CAE injury-metrics workflow. The goal is a structure that supports DAX measures such as peak HIC, maximum chest deflection, or the count of runs exceeding a threshold — reliably, across any combination of occupant, vehicle, and test configuration.

Choosing the grain of each fact table

The single most important decision in a star schema is the grain — what one row represents. For CAE results, three fact tables with distinct grains work well:

• FactSimulationRun — one row per simulation run. Holds run-level attributes that are genuinely numeric and additive or semi-additive: solver wall-clock time, model size (element count), mass, and a run status code. This table answers questions about simulation throughput and cost.
• FactLoadCase — one row per run × load case. A single run may be evaluated against frontal offset, side impact, and rear impact load cases. This grain captures load-case-level outcomes such as peak acceleration and pulse duration.
• FactInjuryMetric — one row per run × load case × occupant × metric. This is the finest grain and the workhorse for KPI reporting: HIC15, HIC36, chest deflection (mm), chest acceleration (g), neck tension (kN), and femur force (kN), each with its value and the applicable injury criterion limit.

Keeping these grains separate prevents the classic fan-out error. If you stored injury metrics and run-level timing in one table, summing solver time across five occupants would multiply it by five. Separate facts, each at its own grain, keep every aggregation honest.

Dimension tables and their keys

Dimensions carry the attributes you filter and group by. For this model, four dimensions cover the reporting needs:

Each dimension uses a surrogate integer key generated during ETL, not the raw source string. Surrogate keys insulate the model from source-system renames and keep relationships fast. The natural business key (for example, the occupant label "95th_Driver") is retained as a separate column for traceability but is not used in relationships.

Relationship topology

The star schema connects each fact to its dimensions through one-to-many relationships, with the dimension on the "one" side and single-direction filtering from dimension to fact. The diagram below shows the topology, including the shared dimensions that bridge the three fact tables.

Note that DimTestConfig relates to both FactSimulationRun and FactLoadCase. This is intentional: a run is executed under a configuration, and each load case within it inherits that configuration. Both relationships filter in a single direction, so a slicer on impact speed propagates to both facts without creating an ambiguous path. DimLoadCase relates to FactLoadCase and FactInjuryMetric, and DimOccupant relates only to FactInjuryMetric, because occupant identity is meaningful only at the metric grain.

Building the model in Power BI Desktop

Assume the ETL layer has produced four CSV extracts: fact_simulation_run.csv, fact_load_case.csv, fact_injury_metric.csv, and the dimension extracts. The concrete steps are:

1. Use Get Data → Text/CSV to load each file. In Power Query, set the data types explicitly — RunKey, OccupantKey, and other keys as Whole Number; MetricValue and CriterionLimit as Decimal Number; SolverTime_s as Decimal Number.
2. Rename queries to match the table names above, then Close & Apply.
3. In Model view, drag VehicleKey from DimVehicle onto VehicleKey in FactSimulationRun. Power BI should auto-detect a one-to-many relationship. Verify the cardinality reads One to many and the cross-filter direction is Single.
4. Repeat for each relationship shown in the diagram. If Power BI creates a relationship with the wrong direction, delete it and recreate it by dragging from the dimension key to the fact key.
5. Mark each dimension's date-like or sort columns appropriately, and hide all surrogate key columns from Report view so report authors cannot accidentally group by them.

Validating the model before writing DAX

A star schema is only trustworthy once you have verified row counts and aggregation behavior. Two quick checks catch most errors:

• Row-count reconciliation. Create a blank query or a table visual showing COUNTROWS(FactInjuryMetric) and compare it to the source extract's row count. A mismatch means the ETL dropped or duplicated rows.
• Fan-out test. Place SUM(FactSimulationRun[SolverTime_s]) in a card, then add DimOccupant[Percentile] to a slicer. If the total changes when you select a percentile, the model has an unintended many-to-many path — the run-level fact is being filtered by an occupant dimension it should not see.

Once both checks pass, the model is ready for measures. A representative measure at the metric grain looks like this:

Peak HIC15 =
CALCULATE (
    MAX ( FactInjuryMetric[MetricValue] ),
    FactInjuryMetric[MetricName] = "HIC15"
)

Because FactInjuryMetric sits at the finest grain and all dimensions filter it through single-direction relationships, this measure returns the correct maximum for any slicer combination — a specific occupant percentile, a vehicle program, or a load case — without manual filter logic. That reliability is the payoff of the star schema, and it is what makes the KPI layer in later chapters safe to build on.