4. Designing a CAE data warehouse schema

A CAE data warehouse is not a generic reporting database. It must reconcile two very different worlds: the transactional, file-centric reality of simulation execution, and the analytical, cross-program reality of engineering decision-making. The schema that bridges them is almost always a star or snowflake model, with one central fact table capturing simulation runs and a set of conformed dimensions describing the vehicle program, the load case, the material, and the solver environment. Getting this schema right determines whether a query like "show me every frontal crash run on the B-segment platform where peak intrusion exceeded 120 mm" returns in seconds or requires a week of manual file archaeology.

Why a Star Schema Fits CAE Data

Simulation data is naturally event-shaped. Each solver execution is a discrete event with a timestamp, a duration, a resource cost, and a set of measured outcomes. That makes the run itself the grain of the fact table. Dimensions then provide the descriptive context: which program, which load case, which material revision, which solver version, which mesh. Because CAE analysts slice results along exactly these axes — by program, by load case, by material — a star schema aligns the physical data model with the dominant query patterns.

The alternative, a fully normalized entity-relationship model, forces every analytical query through five or six joins and makes columnar compression far less effective. For warehouses that routinely hold tens of millions of run records, that difference is decisive.

The Fact Table: Simulation Runs

The central fact table, typically named fact_simulation_run, should be declared at the grain of one row per solver execution. A single load case may be run many times — different mesh densities, different material cards, different solver builds — and each of those is a separate fact row. Declaring the grain explicitly prevents the most common modeling error in CAE warehouses: accidentally aggregating multiple runs into one row and losing the ability to trace a result back to its input deck.

Foreign keys point to each dimension. Additive and semi-additive measures sit alongside them. Typical measures include wall-clock runtime in seconds, CPU core-hours consumed, peak memory in gigabytes, license tokens checked out, and a small set of scalar result metrics extracted from the solver output — maximum von Mises stress, peak intrusion, HIC value, mass, and so on. These result metrics are the reason the warehouse exists; without them, analysts must reopen result files.

CREATE TABLE fact_simulation_run (
    run_key            BIGINT       NOT NULL,
    program_key        INT          NOT NULL,
    loadcase_key       INT          NOT NULL,
    material_key       INT          NOT NULL,
    solver_key         INT          NOT NULL,
    mesh_key           INT          NOT NULL,
    run_date_key       INT          NOT NULL,
    run_id             VARCHAR(64)  NOT NULL,
    wall_clock_sec     NUMERIC(12,2),
    cpu_core_hours     NUMERIC(12,2),
    peak_memory_gb     NUMERIC(8,2),
    max_von_mises_mpa  NUMERIC(10,3),
    peak_intrusion_mm  NUMERIC(8,3),
    hic_value          NUMERIC(8,2),
    total_mass_kg      NUMERIC(10,3),
    CONSTRAINT pk_fact_run PRIMARY KEY (run_key)
);

Note that run_id is a degenerate dimension — it carries no descriptive attributes of its own but is essential for tracing back to the source deck and result files. Keeping it in the fact table rather than a separate dimension is standard practice.

Dimension Tables

Four dimensions carry most of the analytical weight in a CAE warehouse.

Vehicle program dimension. This describes the product context: program code, platform, vehicle segment, model year, body style, and the engineering change level. It is slowly changing, because programs get renamed, platforms get reused, and model years roll forward. A Type 2 slowly changing dimension preserves history so that a run executed against the 2024 model year still reports correctly after the program moves to 2025.

Load case dimension. This is the most CAE-specific dimension and the one most often under-designed. A load case is not just a name like "Frontal 56 km/h." It has a category (crash, durability, NVH, thermal), a regulatory or internal standard it derives from, a severity level, an impact direction, and a solver deck template. Storing these as separate attributes lets analysts group runs by category or filter by regulation without string parsing.

Material dimension. Material properties are the classic slowly changing dimension in CAE. A steel grade may have its yield strength revised after coupon testing, and a run executed before that revision must still report the old value. The dimension should therefore carry the property values as attributes — density, Young's modulus, yield strength, ultimate tensile strength, hardening curve reference — along with an effective date and a revision identifier. This is a Type 2 SCD by necessity, not by preference.

Solver and mesh dimensions. These are smaller but essential for reproducibility. The solver dimension records the code name, version, and license feature. The mesh dimension records element count, element type, average element size, and mesh quality metrics. Together they let an analyst answer "did the result change because of the material or because someone refined the mesh?"

Handling Slowly Changing Dimensions in Practice

Material and program dimensions change frequently enough that a naive Type 1 overwrite will silently corrupt historical analysis. Consider a concrete scenario: a DP780 steel grade has its yield strength revised from 420 MPa to 445 MPa after coupon testing in March. A frontal crash run executed in January used the 420 MPa card. If the material dimension is overwritten, that January run will now appear to have been executed with 445 MPa material, and any correlation between yield strength and intrusion will be wrong.

The Type 2 pattern solves this by inserting a new row with a new surrogate key, an effective_start_date, an effective_end_date, and a is_current flag. The January fact row continues to point at the old material_key. The ETL process that loads new runs looks up the material by natural key and effective date, not by name alone.

-- Type 2 lookup during fact load
SELECT material_key
FROM dim_material
WHERE material_grade = :grade
  AND effective_start_date <= :run_date
  AND (effective_end_date > :run_date OR effective_end_date IS NULL);

The same pattern applies to vehicle programs when a platform is renamed mid-year, and to solver versions when a patch release changes numerical behavior. The cost is additional rows in small dimension tables; the benefit is that historical results remain interpretable years later.

Grain, Conformed Dimensions, and Query Performance

Two design rules keep the warehouse coherent as it grows. First, every fact table must declare its grain in the table comment and enforce it with a unique constraint on the natural key combination. Second, dimensions must be conformed across fact tables. If a future fact_material_test table is added for coupon test data, it should reuse dim_material rather than defining its own material list. Conformed dimensions are what allow a single query to join simulation runs with physical test results on the same material revision.

On the performance side, partition the fact table by run_date_key — typically monthly or quarterly — and cluster on program_key. Most analytical queries filter by program and date range, so this combination gives partition pruning and effective zone-map skipping. Columnar storage compresses the numeric measures well, often at ratios above 10:1 for runtime and memory columns.

A well-designed CAE warehouse schema turns simulation data from a collection of files into a queryable engineering asset. The fact table captures what happened; the dimensions capture the context that makes the numbers meaningful. When material revisions, program renames, and solver upgrades are handled through slowly changing dimensions rather than overwrites, the warehouse remains trustworthy across the multi-year lifecycle of a vehicle program.