Making BIM, ERP and Operational Data AI-Ready – Part 2: Canonical Models, Schema Harmonisation and Entity Resolution

Why a canonical model is non-negotiable

Without a canonical model, every integration between BIM, ERP and OT degenerates into a tangle of point-to-point mappings and duplicated business logic.

‍

A canonical model provides neutral, shared concepts: Project, Asset, Location, System, Equipment, WorkPackage, Event, Measurement; onto which specialized schemas from BIM, ERP and OT are mapped.

‍

In industrial AI lakehouses, these canonical entities typically materialize as curated silver tables that feed gold-level analytics and features.

Canonical concepts across BIM, ERP and OT

Unified Ontological Schema and Directed Acyclic Metagraph Model

‍

Defining canonical entities is necessary but not sufficient; if they live as flat tables, cross-domain joins remain structurally fragile and difficult for AI agents to traverse safely.

‍

To make joins executable for an AI agent, the canonical layer must be deployed as a Unified Ontological Schema a graph-based representation in which business concepts (Project, Asset, Location, WorkPackage, Event, Measurement) and their relationships are explicitly modeled, not just implied by foreign keys.

‍

Within this schema, we structure the domain as a Directed Acyclic Metagraph Model:

• Physical assets as immutable pivot nodes
  • Assets (equipment, structural components, systems) form the core node set of the metagraph; every other concept anchors to these nodes, making the asset graph the semantic “spine” of the data plane.

• Spatial configurations (BIM) as transient, time‑bound edges
  • BIM spaces, levels, zones and system configurations are expressed as edges and subgraphs that connect asset nodes into 3D spatial hierarchies for a given time window; as designs and as‑built conditions change, new edges are added with validity intervals, while the asset nodes remain stable.

• Financial and process structures (ERP/WBS/MES) as transient semantic edges
  • WBS elements, cost centers, work orders, job plans and MES operations become semantic edges that bind assets to financial and process contexts (e.g., “asset A is in work package W during period T with cost attribution C”). These edges are explicitly time‑bound and versioned to reflect schedule changes, reassignments and reorganizations.

• Events and measurements as edge‑attached observations
  • OT events and time-series measurements attach to asset nodes and their contextual edges (spatial and financial), forming observation subgraphs that can be traversed along multiple dimensions by asset, by location, by work package, by time.

Because the metagraph is directed and acyclic at the canonical level, AI agents and query engines can traverse from any semantic entry point (e.g., a BIM element, a work order, a tag stream) to the underlying physical asset and then outward along spatial or financial edges without encountering cycles or ambiguity.

‍

In this design, “canonical concepts across BIM, ERP and OT” are no longer just columns in tables : they are nodes and edges in a unified ontological metagraph, with assets as immutable pivots and spatial/financial/process structures as transient, time‑bounded semantics, which is what actually makes robust cross-domain joins and AI reasoning possible in heavy-industry environments.

Strategy 1: Domain-driven canonical data model

Using domain-driven design (DDD), you can structure the canonical model into bounded contexts:

• “Project Delivery” for schedule/progress.
• “Asset Lifecycle” for asset/equipment master, health and maintenance.
• “Production Operations” for throughput, quality and OEE.
• “Commercials” for contracts, costs and revenue.

Within each bounded context, define stable schemas and strong ubiquitous language; each schema is implemented as silver tables and views, with mappings from BIM/ERP/OT into these schemas.

Strategy 2: Semantic layer or knowledge graph

Alternatively (or additionally), you can implement the canonical model as a semantic layer or knowledge graph on top of your lakehouse.

• Ontologies capture classes such as Project, Asset, Space, Sensor, WorkOrder, Event and their relationships.
• Graph queries and inferencing allow advanced questions like “all pumps of class X connected to line Y with failure probability above threshold Z within six months of commissioning.”

Semantic CDE and digital twin research shows that ontology-based integration of BIM, IoT and GIS is effective for real-time analytics and decision support.

Handling BIM schemas: IFC, families and properties

BIM-specific challenges include:

• IFC vs native schema variability: IFC is an open exchange format, but project models often rely on tool-specific structures and custom properties, leading to inconsistent attributes across projects. ‍
• Families/types vs instances: repeated components (e.g., pumps, beams) must be mapped to asset classes and equipment types to support reusable analytics. ‍
• Geometric/spatial feature extraction: ML often needs derived features (volume, area, adjacency, exposure, orientation) rather than raw geometry.

SLR studies emphasize the need for additional frameworks to convert BIM/IFC into structured, AI-ready datasets such as tables, graphs and time-series.

Example: extracting BIM element features to tabular form

AST-Based Spatial Extraction and Graph-Driven BIM Feature Serialization

In practice, BIM ingestion is not a simple JSON select; standard BIM files (IFC, Revit exports, vendor-specific JSON) hide relational dependencies inside deeply nested properties, type/family hierarchies and custom parameter sets.

‍

To make these models usable for AI and cross-domain joins, the ingestion engine typically performs:

• Abstract Syntax Tree (AST) spatial extraction
  • Parse BIM instances into an AST representing elements, types, families, property sets and relationships (e.g., IFCRELAGGREGATES, IFCRELCONTAINEDINSPATIALSTRUCTURE).
  • Traverse this AST to derive multi-dimensional geometric and spatial attributes (volume, area, adjacency, containment, connectivity) and to expose implicit semantics explicitly.

• Spatial-relational mapping via graph traversal
  • Build a graph representation where nodes are elements/spaces/systems and edges capture containment, adjacency, flow and system membership.
  • Use graph traversal to compute higher-order features (e.g., “number of connected spaces,” “distance to core,” “system-level path length”) before persisting to tabular form.

• Self-describing Parquet feature serialization
  • Serialize extracted features into self-describing Parquet datasets that embed schema and semantic metadata (e.g., via field naming conventions, embedded ontological tags) so downstream engines and AI agents can interpret them without reopening the original BIM files.

Conceptually, the PySpark layer then consumes these pre-parsed structures rather than raw BIM JSON.

‍

Revised, more realistic ingestion pattern (conceptual)

‍

Assume an upstream BIM parser (Speckle/ifcOpenShell/custom) has produced a “semantic element graph” in JSON, where each node contains both direct attributes and graph-derived features.

‍

from pyspark.sql import functions as F

# Load semantic BIM element graph nodes produced by the upstream AST + graph parser.
# Each record represents one BIM element with embedded geometric, spatial, relational and ontology-aligned features.
bim_semantic_nodes = spark.read.json(
   "dbfs:/semantic/bim/element_nodes/*.json"
)

bim_features = (
   bim_semantic_nodes
   .select(
       "element_id",
       "project_id",
       "ifc_class",
       "family_name",
       "type_name",
       "level_name",
       "space_id",

       # Direct geometric features extracted through the AST parser
       F.col("geometry.volume_m3").alias("volume_m3"),
       F.col("geometry.surface_area_m2").alias("surface_area_m2"),

       # Spatial position and orientation
       F.col("location.world_x").alias("x_world"),
       F.col("location.world_y").alias("y_world"),
       F.col("location.world_z").alias("z_world"),
       F.col("orientation.azimuth_deg").alias("azimuth_deg"),
       F.col("orientation.inclination_deg").alias("inclination_deg"),

       # Graph-derived relational features
       F.col("topology.adjacent_space_ids").alias("adjacent_space_ids"),
       F.col("topology.connected_system_ids").alias("connected_system_ids"),
       F.col("topology.path_length_to_core").alias("path_length_to_core"),
       F.col("topology.is_on_egress_path").alias("is_on_egress_path"),

       # Semantic enrichment fields aligned to the ontology
       F.col("ontology.material_class").alias("material_class"),
       F.col("ontology.fire_rating_class").alias("fire_rating_class"),
       F.col("ontology.semantic_labels").alias("semantic_labels")
   )
)

# Persist the reusable silver-level BIM feature table
bim_features.write.format("delta") \
   .mode("overwrite") \
   .saveAsTable("silver.bim_element_features")

‍

Here:

• The AST and graph traversal work happens before PySpark, in a specialized BIM parsing service that understands IFC/Revit semantics and constructs a knowledge-graph-like view of the model.

• The PySpark job simply projects semantic nodes into a silver-layer feature table, preserving multi-valued fields (adjacent_space_ids, connected_system_ids, semantic_labels) as arrays or maps to keep the structure self-describing and queryable.

• By storing these features in Parquet/Delta with rich column-level semantics, the lakehouse acts as a compressed, AI-friendly representation of the BIM graph, avoiding repeated heavy parsing of the original model formats.

This is closer to how production-grade BIM ingestion for digital twins and AI actually looks: AST-based spatial extraction + graph-driven relational mapping upstream, followed by semantic feature serialization into Parquet/Delta that the rest of the edge-to-cloud semantic data plane can consume.

Handling ERP schemas: WBS, materials and cost objects

ERP challenges include:

• Highly normalized schemas designed for OLTP, not analytics.

• Complex WBS and cost structures that must map to Projects, Locations and WorkPackages.

• Material and asset masters that must be reconciled with BIM families/types and OT equipment identifiers.

You typically create gold-level ERP views with denormalized business entities (Asset, WorkOrder, ProjectCost, Contract) to simplify cross-domain joins.

Handling OT schemas: tag hierarchies and time-series

OT data is structured around tags and control hierarchies rather than business semantics.

• Tags often encode semantics via naming conventions (plant.line.equipment.measurement).

• Time-series must be normalized (time zones, frequency, gap handling) and associated with canonical entities.

• OT systems have strict uptime and safety requirements, so ingestion must be non-intrusive and governed.

Hybrid edge–cloud architectures collect, contextualize and stream OT data into bronze tables, then refine to harmonised silver/gold layers for AI.

Entity resolution: matching BIM, ERP and OT entities

Entity resolution (ER) ties the whole canonical model together:

• BIM element ↔ ERP asset ↔ OT equipment/tags.

• ERP WBS ↔ construction activities ↔ work orders.

• OT tags/streams ↔ sensors ↔ spaces/locations.

Because identifiers differ, ER often combines:

• Deterministic rules (ID cross-reference tables, plant codes, serial numbers).

• Heuristics (string similarity, vendor, model, capacity, location, installation date).

• ML-based record linkage for large-scale matching.

Reviews of BIM–IFC AI-readiness identify absence of robust toolchains for linking static BIM with other data sources as a primary barrier.

Example: deterministic + fuzzy matching for Asset entity resolution

Native Similarity Functions and LSH-Based Blocking for Scalable Record Linkage

‍

Production-grade Spark pipelines avoid Python UDFs for core matching logic because Catalyst cannot introspect them, which disables predicate pushdown, vectorization and numerous query plan optimizations.

‍

Instead, we use native Spark SQL similarity primitives (e.g., levenshtein) and optionally Locality-Sensitive Hashing (LSH) to build scalable blocking stages that reduce the candidate space before computing distances.

Step 1 – Deterministic matches (unchanged)

‍

We keep deterministic linkage rules (serial number + vendor) as before:

‍

from pyspark.sql import functions as F

bim_eq = spark.table("silver.bim_equipment")

erp_assets = spark.table("silver.erp_asset_master")

deterministic_matches = (

   bim_eq.alias("b")

   .join(

       erp_assets.alias("e"),

       on=[

           F.col("b.serial_number") == F.col("e.serial_number"),

           F.col("b.vendor").eqNullSafe(F.col("e.vendor"))

       ],

       how="inner"

   )

   .select(

       "b.element_id",

       "e.asset_code",

       F.lit("SERIAL_VENDOR").alias("match_rule"),

       F.lit(1.0).alias("match_score")

   )

)

‍

Step 2 – Blocking + native Levenshtein for fuzzy matches

‍

Instead of a Jaccard UDF over a full cross-join, we:

• Block records using cheap, SQL-native keys (e.g., lowercased vendor, plant, first N characters of description) to dramatically reduce candidate pairs.
• Use native levenshtein distance for string similarity, which is fully understood by Catalyst and participates in query optimization.

from pyspark.sql import functions as F

from pyspark.sql.window import Window

# Precompute blocking keys

bim_blocked = (

   bim_eq

   .withColumn("vendor_norm", F.lower(F.col("vendor")))

   .withColumn("plant_code", F.col("plant_code"))

   .withColumn("desc_prefix",

               F.substring(F.lower(F.col("description")), 1, 16))

)

erp_blocked = (

   erp_assets

   .withColumn("vendor_norm", F.lower(F.col("vendor")))

   .withColumn("plant_code", F.col("plant_code"))

   .withColumn("desc_prefix",

               F.substring(F.lower(F.col("description")), 1, 16))

)

# Blocked join on cheap keys

blocked_pairs = (

   bim_blocked.alias("b")

   .join(

       erp_blocked.alias("e"),

       on=[

           F.col("b.vendor_norm") == F.col("e.vendor_norm"),

           F.col("b.plant_code") == F.col("e.plant_code"),

           F.col("b.desc_prefix") == F.col("e.desc_prefix")

       ],

       how="inner"

   )

)

# Native Levenshtein distance and capacity check

from pyspark.sql.functions import levenshtein  # native Spark function

candidate_matches = (

   blocked_pairs

   .withColumn(

       "desc_distance",

       levenshtein(F.col("b.description"), F.col("e.description"))

   )

   .withColumn(

       "capacity_diff",

       F.abs(F.col("b.capacity") - F.col("e.capacity"))

   )

   .where(

       (F.col("desc_distance") <= F.lit(5)) &  # configurable threshold

       (F.col("capacity_diff") < F.lit(1e-3))

   )

   .select(

       F.col("b.element_id").alias("element_id"),

       F.col("e.asset_code").alias("asset_code"),

       F.lit("BLOCKED_LEVENSHTEIN_CAPACITY").alias("match_rule"),

       # convert distance into a score (example: inverse normalized)

       (1.0 / (1.0 + F.col("desc_distance"))).alias("match_score")

   )

)

‍

This approach keeps everything inside Spark’s expression engine, allowing Catalyst to optimize joins, filters and projections.

Step 3 – Optional LSH-based similarity join for very large catalogs

‍

For very large, high-cardinality asset catalogs where even blocked joins are heavy, you can use Locality-Sensitive Hashing (LSH) to build an approximate similarity join.

‍

Pattern (conceptual):

• Vectorize normalized descriptions (e.g., via bag-of-words or character n‑grams).
• Apply MinHashLSH or other LSH models to compute hashes.
• Use approxSimilarityJoin between BIM and ERP feature sets to produce candidate pairs with high probability of similarity, then apply Levenshtein/capacity filters for final scoring.

Pseudo-code sketch:

‍

from pyspark.ml.feature import HashingTF, MinHashLSH

from pyspark.ml.linalg import Vectors

# Build text features for descriptions (simplified)

# In practice, you’d tokenize and vectorize properly.

bim_desc = bim_blocked.select("element_id", "description")

erp_desc = erp_blocked.select("asset_code", "description")

# Example: character 3-gram hashing (placeholder)

hashing_tf = HashingTF(inputCol="tokens", outputCol="features", numFeatures=1 << 18)

# After tokenization, fit MinHashLSH

lsh = MinHashLSH(inputCol="features", outputCol="hashes", numHashTables=5)

bim_model = lsh.fit(bim_features_df)

erp_model = lsh.fit(erp_features_df)

# Approximate similarity join (returns candidate pairs)

approx_pairs = bim_model.approxSimilarityJoin(

   bim_features_df, erp_features_df, 0.8  # similarity threshold

)

‍

# … then refine with native Levenshtein/capacity filters as above

‍

You don’t need to show all of this in the blog, but it’s good to mention the pattern so readers know they can scale beyond simple blocking when asset catalogs reach tens of millions of records.

Step 4 – Persist unified candidate set

‍

Finally, unify deterministic and fuzzy candidates:

‍

matches = deterministic_matches.unionByName(candidate_matches)

matches.write.mode("overwrite").saveAsTable("silver.asset_link_candidates")

Pattern: ID graph for cross-system identity

An ID graph stores relationships between canonical IDs and source-system IDs:

• Nodes: canonical entities (Project, Asset, Location, WorkPackage).

• Edges: assertions that a source ID corresponds to a canonical entity, with rule, confidence and provenance.

Implementation options:

• Graph DB or RDF triple store storing semantic relationships.

• Lakehouse tables modeling mappings with effective/expiry times and metadata.

Downstream analytics and ML then operate purely on canonical IDs, reducing coupling to source-system volatility.

Spatial-based entity alignment

Spatial alignment complements ID-based and fuzzy matching:

• BIM contains precise 3D geometry and space definitions.

• IoT/OT devices can be geo-tagged or assigned to spaces/areas.

By intersecting coordinates or using building/plant zoning, you can validate that a sensor belongs to a particular BIM element or space and enrich telemetry with structural context (e.g., “north-facing facade on top floor”).

Handling conflicts and drifts

Even with canonical models and ID graphs, changes and misalignments occur:

• BIM updated but ERP/OT not updated.

• Assets replaced or relocated without documentation.

• Tags renamed or reassigned.

To manage this, you need:

• Versioned mappings with effective-from/effective-to timestamps.

• Data quality rules and anomaly detection to flag inconsistencies (e.g., telemetry from non-existent assets).

• Governance workflows to route issues to data stewards and system owners.

This combination of semantic modeling, entity resolution and governance is what turns a data swamp into an AI-ready backbone.

‍

In Part 3, we will operationalize these concepts into an AI-ready data platform with medallion layers, governance and real cross-domain analytics and AI patterns.