Modernising Engineering Datasheets a bCLEARer® project A case study in migrating from legacy engineering standards Chris Partridge, BORO Solutions Overview Provide a sense of: What happens when a bCLEARer project is faced with data in the FORM paradigm See how the implicit FORM syntax can be made explicit Walk through a sanitised case history Based upon real project but both simplified and sanitised Sample of actual work not intended as an example of best practice! Show some typical approaches and challenges faced Particularly at the early alpha stage Background BORO - bCLEARer What is BORO? 1. Introduction The Business Object Reference Ontology BORO has chosen to adopt a closer integration with philosophy than other ontologies in the information systems domain … also, unlike them, it emerged from and was developed in commercial projects rather than in academia BORO includes a foundational (or upper) ontology and a closely intertwined methodology for information systems (IS) re-engineering (Partridge, 1996), hence the term BORO refers to both the ontology and the methodology . BORO was originally conceived in the late 1980s to address a particular need for a solid legacy re-engineering process and then evolved to address a wider need for developing enterprise systems in a ‘better way’; in other words, in a way that … enable[ed] higher levels of reuse and, as a consequence, capable of reducing the effort and cost of (re-)developing, maintaining and interoperating enterprise systems. It was eventually publicly documented in (Partridge, 1996) de Cesare, S. and Partridge, C. 2016. BORO as a Foundation to Enterprise Ontology. Journal of Information Systems. 30 (2), pp. 83-112. https://doi.org/10.2308/isys-51428 Partridge, C. 1996. Business Objects: Re-Engineering for Re-Use, Butterworth-Heinemann. 4 style.visibility style.visibility style.visibility What is BORO? BORO has two closely intertwined components BORO Foundational Ontology a foundational (or upper) ontology bCLEARer a methodology systematically mining (re-engineering) the semantics from information systems The two frameworks validate and inform each other top-down BORO Foundational Ontology guides the bottom-up framework bottom-up bCLEARer framework validates the whole model 5 BORO FO - top-down framework bCLEARer - bottom-up framework Components deployed across various exploitation routes transform assess build has the application already been deployed? is there a commercial application available in the market? has it already been selected? standard needs to be implemented in application? sustain target requires a standard? requirement semantics composition mereology classification external identifiers content standardise configure 6 IES Ongoing BORO development 2010 Mapping relative interest in the standards community: reveals ‘corporate’ preferences (myopia?) 1990 2000 2020 OMG UPDM 2 UAF 2025 DODAF DM2 bCLEARer MODAF/MODEM IDEAS ISO 15926 : Part 2 BORO Foundational Ontology 7 bCLEARer Methodology 8 The user perspective 9 bCLEARer’s five stages {5C22544A-7EE6-4342-B048-85BDC9FD1C3A} stages Collect Collect the datasets Establish the broad scope of the process Load Select the data in scope Translate the data into the cells Evolve Reveal the underlying semantics Mine the ontology Assimilate Merge the run into the full model Establish a single integrated model Reuse Export into applications and (re-)use 10 collect load evolve assimilate reuse Multiple inputs, multiple runs, integrated 11 a repeated sequence of automated processes: a scalable way to systematically improve semantic maturity data 2 increasing semantic maturity reuse reuse Foundational ontology collect data 1 data 3 collect collect integrated into a single foundational ontology Schematic view of one bCLEARer engine 12 Increase maturity in pragmatic steps 13 increasing semantic maturity evolve Entity repository project 1 project 2 project 3 entification O-O repository object-orientation Ontological repository ontologisation (or ontologification) style.visibility style.visibility style.visibility style.visibility Datasheets Why datasheets? One part of the legacy engineering standards territory Need to start somewhere A good first slice when approaching the overall territory Another way (slice) would be to start with the application systems themselves Will eventually need to consider these Can be seen as the start of the data trail (in some sense) 15 What is a datasheet? A datasheet, data sheet, or spec sheet is a document that summarizes the performance and other characteristics of a product, machine, component (e.g., an electronic component), material, subsystem (e.g., a power supply), or software in sufficient detail that allows a buyer to understand what the product is and a design engineer to understand the role of the component in the overall system. Typically, a datasheet is created by the manufacturer … The ideal datasheet specifies characteristics in a formal structure, according to a strict taxonomy, that allows the information to be processed by a machine. Such machine readable descriptions can facilitate information retrieval, display, design, testing, interfacing, verification, system discovery, and e-commerce. https://en.wikipedia.org/wiki/Datasheet 16 Example: centrifugal pump datasheet 17 Intriguingly, the explanation of the datasheet does not tell the reader about the FORMs implicit structure. Presumably, this is human(-engineer)-readable if not machine readable What kinds of issues? Specifications are heterogenous too many standards covering the same topics under different governance models and for different purposes Low level of digitalisation where a lot of the specifications are in paper (PDF) form Hard to develop a single interoperable model of these that can be implemented in computer systems Legacy data does not obviously conform to any standard or it is hard to establish conformance, and typically, little progress in aligning legacy data to these specifications Different organisations adopt different standards and hard to enforce any one of these as the common standards because of governance issues 18 Datasheets as FORM paradigm bCLEARer Collect ‘paradigm’ stages Collect rule of thumb ‘paradigm’ data stages Can generally be allocated one of these three: TEXT – unstructured data FORM – semi-structured data DATA - structured data (includes O-O, etc.) Where there is a lack of explicit machine-readable structure It need to be added 20 Datasheets are in FORM paradigm FORMs as grid with implicit structure TABLES have just simple explicit grid structure. FORMs have an implicit property model reflected in the organisation of the form The attributes/properties are laid out in sections and sub-sections of varying sizes and positions with the aim of capturing a variety of relations in the model Currently, there are no established methods for extracting the property model from this structure There are a complex range of strategies applied to organise the properties on the page 21 FORMs have been around for a while … 22 Table of the Animal Kingdom (Regnum Animale ) from the 1st edition of Systema Naturæ (1735) Digitalisation of datasheets in FORM structure Staged approach to datasheet slice 23 Stage approach - tips ‘Lean start up’ a good mindset Stage approach - agile a good model alpha beta live Alpha stage - exploring possibilities minimise cost to run maximize evolutions/pivots Beta stage – start with a clearer picture still minimise cost to run still maximize evolutions/pivots Aim for soup to nuts (MVP) as soon as possible Then evolve that 24 Alpha stage - tips In alpha often look to use a no-code data preparation tool: Alteryx, Talend, Knime - good in early stages can go straight to Python (or similar) - later should consider going to Python Even at alpha stage, aim for soup to nuts (AFAP) Aim to clean syntax before exposing semantics 25 Broad pipeline structure 26 Aims to make the implicit structure in the dataset specification explicit and machine readable, before the (populated) datasheets are consumed Fully automated pipeline Datasheet Specification Merge (Populated) Datasheets ISO Quantities Neo4J Neo4J Neo4J Alteryx Backbone (very) Broad technical architecture Keep a simple common backbone, Branch from the backbone for specific functions such as visualisation Consider pipeline orchestration 28 Orchestration in a clean nested structure improves quality and reduces costs. While not as easy in a no-code environment, is still well-worth doing (Clean coding: Martin, Robert C. 2012. Clean Code: A Handbook of Agile Software Craftsmanship.) Collect 29 Potential Sources for Collect Two good sources for standards: American Petroleum Institute https://www.api.org/products-and-services/standards/ e.g. API 676 - Rotary positive displacement pumps ISO e.g. ISO 9905 - Technical specifications for centrifugal pumps https://www.iso.org/standard/17788.html 30 Datasheet Specifications (Standards): example volume analysis 31 Digitalisation of datasheets in FORM Load 32 Context Case history: reporting on the bCLEARer team’s progress still at an early alpha stage Expect significant further evolution/development including major pivots 33 LOAD (FORM) Initially, treat the specification FORM as a simple grid Add identifiers for the specification, its columns and rows and its cells Record the relations between these Which specification rows and columns are in which row and column a cell is in. 34 grid row grid column LOAD: Identify the FORM content 35 COLLECT (FORM) unstructured excel sheets LOAD (FORM) id content Digitalisation of datasheets (Form to Table Paradigm shift) EVOLVE 1: FORM 2 TABLE 36 EVOLVE 1: FORM 2 TABLE: Evolve to the (unified) table paradigm 37 VISUALIZE EVOLVE 1 – FORM 2 TABLE 2 EVOLVE 1 – FORM 2 TABLE 2 format as a (unified) table cell type classification scheme classify cells evolve classifications LOAD (FORM) id content EVOLVE 1 – FORM 2 TABLE 1 classify (syntactic) content Go from FORMs as grid with implicit structure To (unified) TABLES with simple explicit grid structure. Evolve 1: Form-2-Table 38 An early basic model Form Syntax Deconstruction EVOLVE 1: FORM 2 TABLE Early Classification 39 Design decisions Current design – as simple as possible Assume a ‘table’ grid pattern Only allow cell to cell parent-child links Cells links can be classified parent-child links can be inferred Proposed future design pivots (lessons identified) Allow for larger cell blocks – with child cells Recognise merged cells Recognise borders Cell Classification Types (Early Sample) 41 {5C22544A-7EE6-4342-B048-85BDC9FD1C3A} Cell Type Description data_sheet_title Title of the whole sheet is value input datasheet_row_number A number to identify the row on a datasheet units_of_measure A unit of measure property A property name sub_property A property that is a under a main property sub_property_range name of sub-property as range 'Operating Range' value_input A blank value input cell value_input_calculated A value input which is calculated on the basis of value input in one or mutiple cells property_value_list A list of values for a property property_value_list_value_composite single-selection value in a "property_value_list" & is given with '/' to enter mulitple values facetted_property_group A set of properties that are subject to a matrix facet group sub_property_facet A property facet that is part of a facetted property group and is used to construct a facetted property element sub_property_facet_boolean A property facet that is part of a facetted property group and has boolean input facetted_property_element A property that is constructed in a matrix of two facets & is blank. facetted_property_element_range_max A property as max value for range that is constructed in a matrix of two facets & is blank. sub_register_name Name of sub-register, sometimes it may or may not be present in the sheet e.g. sometimes implicit by reading the name of columns sub_register_property_with_unit_of_measure Name of columns of sub-register with uom mentioned along with it. sub_register_property_value value input for the sub-register columns Cell Classification Types – An early distribution report 42 Cell Classification Types – Visualised 43 data_sheet_titl e purpose property sub_property Classifying cell roles: types of headers – early attempt 44 Datasheets specifications typically contain headers The headers seemed to be arranged in a hierarchy of levels; Where the lower level is dependent upon the higher level There are a limited variety of possible lower levels Initially had some broad classifications – see diagram Trials with the data revealed shortcomings Classifying cell roles: facetted properties 45 facetted_property_group The title of the facet matrix sub_property_facet The columns and rows of the matrix sub_property_facet_element The elements of the matrix Facetted properties, where a property is constructed by combining a number of facets For example, in the diagram: the stator and rotor (main parts) can both have countries of origin and total quantities the cell is related to an internal facet row and column in an internal facet matrix NB: This is a good example of implicit structure The combination ‘stator’ x ‘country of origin’ is not explicitly mentioned anywhere Classifying cell roles: Partial Property Names 46 partial_property_name Multiple cells containing part of a property name. Sometimes property names are spread between multiple cells, these need to be extracted and merged into a single property. Note the use of borders as markings. This is probably bad form design; the cells should be merged to reflect the border. Probably the standards committee was uninterested in machine readability. Set of Cells in a Border vs Individual Cell property_value_list_with_default_and_qualifier_list_of_values Classifying cell roles: Property value lists 47 A list of values for a property that includes a standard set of values and empty cells for qualifier values. sub_property_with_value_input The sub-property and value input are in the same cell facetted_property_element_boolean Facetted_property_element having boolean input value_input_boolean An input requiring boolean input, could be select button or radio button Classifying cell roles: Value Inputs 48 Value inputs are fields for the user to type their entries Value input relates to a property or sub-property for which information has to be filled in the blanks. Classifying cell roles: composite 49 sub_property_composite value_input_composite sub_property_composite - A property that is a under a main property and contains multiple properties in same cell e.g. ‘Manufacturer/Model’ value_input_composite - A blank value input cell & is given with '/' etc or without any seperator to enter multiple values for which unit of measurement is not applicable Semantics Hiding under the syntax The composite property implies an underlying syntactic structure (with a semantics) that needs to be exposed. 50 Form Syntax Deconstruction Challenges EVOLVE 1: FORM 2 TABLE Challenges 51 Early-stage issues and resolution Sample of issues identified and reported by the bCLEARer team and their proposed resolution (at this stage) 52 Resolution Define checkbox cell type as facetted_property_element_boolean Cell Types: Facetted Properties 53 Issue What cell type should be given for selecting the boxes of a facetted_property_element ? facetted_property_element_boolean Cell Types: Embedded Table/Register 54 sub_register_property sub_register_property_value Resolution Use the categories: ' sub_register_name ' for register (not in this example) ' sub_register_property ' for columns ' sub_register_property_value ' for the value input Issue How to handle tables/registers inside the forms? Resolution Classify cells as property_value_list & property_value_list_value as shown in the image. property_value_list are linked up Issue Datasheet contains list of values to choose from in associated cells off the printable sheet. property_value_list property_value_list_value Cell Types: Values 55 Cell Types: Encoding icons 56 Resolution Use icons from the legend as parents. Issue 8 How to capture the relationships of the ‘icons’ (e.g. Circle for Purchaser, Square for Supplier etc ) Used to identify the section to be filled by appropriate personnel. icons value_input_boolean property_value_list_value property_value_list Cell Types: Multiple Boolean presentations in a single cell 57 Resolution Mark "yes" as ' value_input_boolean ' - if only 'Yes' or 'No' is available as the single option But if both 'Yes' and 'No' are available with or without checkbox then use ' property_value_list_value ' and if available like 'Yes/No' then use ' property_value_list ' Issue Sub-property and value input boolean are present in the same cell property_value_list property_value_list_value Cell Types: Series organised as sentence 58 Resolution Considering we have to select only one of the choices, Top, Bottom & side will be the ' property_value_list_value ' of the ' property_value_list ' . ‘ property_value_list ' can be sometime absent then mark parent as whichever sub_property they are under. For items where you have to select multiple values use ' property_value_list_value_multiple ’. Issue Sub-property, Boolean value input & sub property group together to form a sentence. You cannot inject from the bottom of a vertical pipe! Resolution Make Operating Range as ' sub_property_facet_range ' When Min, Max, Normal and uom all parts of range are in the same cell, then use ' facetted_property_element_range ' If they all are in the separate cells then use the categories shown below: Issue Inside facetted element there are ranges with min, max values and UoM all together in same cell Cell Types: min, max values and UoM in a single cell 59 sub_property_facet_range facetted_property_element_range facetted_property_element_range_uom facetted_property_element_range_max Syntactic Checks 60 Check referential integrity 61 These identifiers need to exist in the register All foreign keys should be checked for referential integrity - automatically Exact coincidence of Cell Values Cell values are used to extract a list of unique cell strings (cell value types) we can hash the unique strings, along with the uuid of the cell where they are used, to provide inspectability back to the underlying cell where that string is inscribed. This can be used for further processing in a transparent manner 62 {5C22544A-7EE6-4342-B048-85BDC9FD1C3A} AUTO AUTO AUTO AUTO. AUTO. {5C22544A-7EE6-4342-B048-85BDC9FD1C3A} AUTO {5C22544A-7EE6-4342-B048-85BDC9FD1C3A} AUTO. Properties Semantic Checks and Analysis 63 Stage 1: Properties (clean names and classify) 64 expanding the short forms and abbreviations stripping and separating the section numbers assigning property types to capture the relations semantically establishing relations with physical quantities Rationalize and expand the contents of each cell (filtered and done as per respective cell types) Strip out the section numbers in the cells and relate them to the parent cell Assign property_types to each cell to capture the semantic relations Relate the property_types to a physical_quantity Correct the misclassified cell_types and change in the meta model global naming conventions – first pass Fully qualified names, (no abbreviations). Spelling UK English Use of special characters parentheses hyphens as separators ( temperature – concentration) hyphens as word components (flow-rate) apostrophes (e.g. pump manufacturer's rated capacity) quotes (for denoting inches vs other purposes) slashes (for separating lists of values/options) 65 global naming conventions - second pass -Avoid underscores and special characters in property names. Use spaces for word separation. - Use lowercase letters in all property names. - Spell out all words in full, avoiding abbreviations. - Ensure property names are clear, descriptive, and accurately represent their data. - Each property name should be unique. Rename or combine duplicates if necessary. - No special characters like &, %, #, etc. in property names.() - Include units of measure in parentheses for clarity and precision.(abbreviations and units) -If a slash (“/”) is used to indicate multiple options or elements, replace it with the word “or”. If the slash is used to indicate a division or ratio, replace it with the word “per”. If the slash is part of a standard industry term or code, it should be retained. 66 Physical quantities 67 ‘Physical property’ types are mapped to physical quantities. This means that each physical property type measures a specific physical quantity. For example, temperature, time. If a word's meaning (semantics) can be mapped to a physical quantity, it should be mapped to the most fundamental quantity that it measures. For example, boiler dimensions, tube diameter, and wall thickness are all classified under "length" because length is the most fundamental quantity that all three are directly or indirectly measuring. physical quantities Digitalization of ISO 80000 68 Digitization of ISO 80000 - Parts 1 to 9 Work initially of a sample Extract unit of measure tables from ISO 80000 documents Converted them into table paradigm Entify quantity names and unit of measure Compile all ISO parts into a single file to use & validate with DEP quantities and UOM. 69 {5C22544A-7EE6-4342-B048-85BDC9FD1C3A} ISO 80000-3: Space and time ISO 80000-4: Mechanics ISO 80000-5: Thermodynamics ISO 80000-6: Electromagnetism ISO 80000-7: Light and radiation ISO 80000-8: Acoustics ISO 80000-9: Physical chemistry and molecular physics ISO 80000 ISO 80000-1: General ISO 80000 71 entified_iso_80000_quantity_names entified_iso_80000_uom e_iso_80000_part_1_to_part_9_consolidated_uom UOM to physical quantity relations UOM Evolve – Link to Physical Quantities Assign property_types to each cell to capture the semantic relations Relate the property_types to a physical_quantity Correct the misclassified cell_types and change in the meta model UOM Evolve – Rationalising Names 73 cleaning and rationalizing UOM separating extensions in UOM correcting misclassified cell_types stripping and separating the additional information Rationalize and expand the contents of each cell (filtered and done as per respective cell types) Strip out the section numbers in the cells and relate them to the parent cell Units of Measure-Visualised in Neo4J 74 Property Model Graph Visualisation 75 Graph visualisation process Node Types (Labels) workbooks, sheets, cell values, cell types, property types, Standard unit of measures, Physical quantity relations workbooks to sheet (whole part) sheet to cell value (whole part) cell value to cell type (type instance) cell type to property type (placeable type) property type to property type supertype (super subtype) property type to physical quantity (placeable type) cell type to cell type super type (super sub type) cell value to standard unit of measure (placeable type) standard unit of measure to physical quantity (placeable type) 76 Property Model Graph: Mechanical centrifugal pump data sheet 77 Property Model Graph: Electrical Low voltage switch gear data sheet 78 Property Model Graph: ‘Meta Model’ 79 Very early version of the graph meta model Alternative forms of automated data extraction 80 LLM based extraction 81 Early results are promising Key issues are reliability and consistency – picks the wrong values because it assumes certain general patterns (e.g. maximum is greater than minimum) 82 questions 83