2. Data warehousing fundamentals for CAE

CAE data does not behave like transactional business data. A crash simulation may produce hundreds of gigabytes of time-series output across thousands of nodes and elements, while a single solver run generates metadata that must be linked to a mesh, a material card, a load case, and a solver version. Before writing a single SQL query, the CAE data engineer must decide which storage paradigm fits which workload. This chapter establishes the architectural vocabulary — OLTP versus OLAP, star versus snowflake schemas, and dimensional modeling — and shows how these concepts map directly onto simulation result data.

OLTP and OLAP: Two Workloads, Two Designs

An OLTP (Online Transaction Processing) system is optimized for short, frequent, concurrent writes. In a CAE context, the job submission portal, the solver queue manager, and the run-tracking database are OLTP systems. They answer questions like "What is the current status of job 48213?" or "Insert a new run record for this LS-DYNA deck." Rows are narrow, normalized, and indexed for point lookups. A typical OLTP table might store one row per solver run with columns such as run_id, status, submitted_at, and solver_version.

An OLAP (Online Analytical Processing) system is optimized for large scans, aggregations, and multi-dimensional grouping. The CAE data warehouse is an OLAP system. It answers questions like "What is the average peak acceleration across all frontal crash runs for vehicle platform B, grouped by material grade and mesh size?" These queries touch millions of rows, aggregate across several dimensions, and are read-mostly. Columnar storage, partition pruning, and pre-computed aggregates are the standard tools.

The practical consequence is that you should not run heavy analytical queries against the OLTP run-tracking database, and you should not expect the OLAP warehouse to enforce transactional integrity on individual solver writes. The two systems are connected by an ETL or ELT pipeline that extracts run metadata and result summaries from the OLTP side and loads them into the warehouse.

Star Schema: The Default CAE Warehouse Layout

A star schema consists of one central fact table surrounded by dimension tables. The fact table stores measurable, numeric events — in CAE, these are simulation result metrics. The dimension tables store descriptive context — what was simulated, how, and with which model.

For a CAE warehouse, a natural fact table is fact_simulation_result, with one row per (run, node, time step) or per (run, part, result quantity) depending on granularity. Typical measures include von_mises_stress, displacement_magnitude, acceleration_x, internal_energy, and mass_scaled. The foreign keys point to dimensions such as dim_run, dim_model, dim_material, dim_load_case, dim_solver, and dim_time.

The star schema is denormalized: dimension tables are wide and contain redundant attributes. For example, dim_model might store model_id, model_name, vehicle_platform, body_style, model_year, and mesh_size_mm all in one table. This redundancy is intentional. It reduces the number of joins required for analytical queries and makes the schema easy for CAE engineers to understand without a data modeling background.

Snowflake Schema: When Normalization Matters

A snowflake schema normalizes dimension tables into multiple related tables. Instead of one wide dim_model, you might have dim_model referencing dim_platform, which references dim_vehicle_program. This reduces storage and eliminates update anomalies, but it increases join complexity.

In CAE warehousing, snowflaking is justified when a dimension has a natural hierarchy that is queried at different levels. Material data is a good example: dim_material can reference dim_material_grade, which references dim_material_family. If analysts frequently roll up results by material family, the normalized structure makes that rollup explicit and consistent. However, for dimensions that are always queried at the leaf level — such as dim_run or dim_solver — snowflaking adds joins without analytical benefit.

The pragmatic recommendation for CAE warehouses is a hybrid: use star schemas for most dimensions, and snowflake only the dimensions with genuine multi-level hierarchies that are queried at multiple levels.

Dimensional Modeling for Simulation Results

Dimensional modeling is the process of identifying the grain of the fact table and the dimensions that describe it. For CAE, the grain decision is the most consequential. Three common grains are:

• Run-level grain: one row per simulation run, with summary metrics such as max_von_mises, total_mass, wall_clock_seconds. This supports run-to-run comparison and regression tracking.
• Part-level grain: one row per (run, part), with aggregated metrics such as max_stress, min_margin_of_safety, mass. This supports design iteration analysis.
• Node-time grain: one row per (run, node, time step), with raw or downsampled field values. This supports detailed signal analysis but produces very large fact tables.

Most CAE warehouses use run-level and part-level grains as the primary analytical facts, and store node-time data in a separate, partitioned fact table or in a columnar time-series store. Mixing grains in one fact table is a modeling error: it forces NULLs and breaks aggregate consistency.

Dimensions for CAE should capture the metadata that analysts actually filter and group by. A practical dim_run includes run_id, run_name, submitted_at, completed_at, status, solver_version, and hardware_profile. A practical dim_load_case includes load_case_id, load_case_name, load_type (frontal, side, rear, pedestrian), impact_speed_kph, and barrier_type. A practical dim_model includes model_id, model_name, platform, mesh_size_mm, element_count, and model_version.

Practical Modeling Decisions and Common Pitfalls

When building the CAE warehouse, three decisions dominate the design. First, grain selection: choose the finest grain that answers the most common analytical questions without exploding storage. Run-level and part-level grains are usually sufficient for regression tracking and design comparison. Second, slowly changing dimensions: CAE models and material cards are revised frequently. Use Type 2 SCDs for dim_model and dim_material so that historical results remain linked to the model version that produced them. Third, conformed dimensions: dim_time, dim_load_case, and dim_solver should be shared across all fact tables so that results from different simulation domains — crash, NVH, CFD — can be compared consistently.

A common pitfall is storing raw nodal time-history data in the same fact table as run-level summaries. This creates a fact table with mixed grain, which breaks aggregate functions and forces analysts to filter by a grain indicator column. Keep raw field data in a separate partitioned table or a dedicated time-series store, and expose it through a view that joins to the run-level dimension.

With the schema in place, the next step is to populate it reliably. That requires a disciplined ETL process that maps solver output files, run metadata, and model definitions into the fact and dimension tables — the subject of the following chapter.