Atlantic is the nickname for the generally available, GA, version of the Configurable Ontology to
The Enterprise Data Management Council created the Financial Industry Business Ontology (FIBO) as an industry-standard business conceptual model.
CODT transformed the FIBO into FIB-DM, the Financial Industry Business Data Model, opening the finance industry standard ontology for data architects and modelers. Over 3,000 people and more than 100 banks downloaded the FIBO data model.
The FIB-DM website provides extensive resources for the FIBO Data Model, including over 100 diagrams, videos, articles, a data dictionary of entities and packages, and an open-source version of the model for download. I recommend studying and understanding the FIB-DM transformation outcome first, and then continuing with the process described here.
CODT is a patented technology (US 12038939) that derives enterprise data models from industry-standard ontologies.
Atlantic CODT enables FIB-DM commercial licensees to transform their proprietary FIBO extensions and other ontologies into data models.
Finally, Semantics for Large Banks
Most FIB-DM users are midsize financial institutions. As the external FIBO product page on the EDM Council website states:
Many midsize EDMC members want to leverage the industry standard, but don’t have ontology tooling, databases, and the human expertise inhouse yet.
As with the EDM Council membership tiers, asset size is large, meaning it exceeds $200 billion, and is a poor proxy for sophistication in semantic technologies. Some “midsize” financial institutions are very advanced in FIBO implementation. Some global financial institutions still do not have an RDF store in production, nor have they rolled out FIBO across the enterprise.
Most financial institutions downloading FIB-DM are smaller banks—so far, twenty global banks have downloaded the FIBO data model. Most large banks have already developed their proprietary transformation and data models, which are derived from the ontology. I invite you to conduct a comparison of your FIBO Data Model with FIB-DM using PowerDesigner/ERwin. Does your model have the complete FIBO documentation? How does it handle inverse object properties and complex class restrictions?
Regardless of size, once a financial institution customizes and extends the Financial Industry Business Ontology, it must update its FIBO data model.
A “Semantics for eXtra large banks” education PowerPoint and video were released with the software update.
Semantic Enterprise Information Architecture
The Semantic Enterprise Information Architecture SEIA replaces the enterprise data (or class) model at the apex of model-driven development with the ontology.
When the Enterprise Data Management Council (EDMC) established the financial industry standard, it opted for the Ontology Web Language (OWL) over the relational model. I fully support the EDMC’s choice because the semantic model expresses business rules, including values and multiple-entity joins.
In SEIA, transformations derive the enterprise data model, FIB-DM, and object models, FIBUM, as well as, in the future, messages and process models.
We recommend using RDF/OWL, with the business-friendly FIB Concept Maps, rather than a conceptual data model to define and describe the enterprise for
You leverage the industry standard and use the same names, concepts, definitions, and design patterns across the enterprise.
As the EDM Council FIB-DM page states:
Common names and definitions across the enterprise facilitate integration between applications, RDBMS and RDF-stores.
Of course, in order to make the arrows in the pretty picture work, you want to propagate changes to your Enterprise Ontology to downstream implementation models.
Metadata Sets
The approach to derive a data model from an ontology is basic ETL: Extract, Transform, and Load.
We extract metadata from the ontology, transform it, and load it into the data modeling tool or repository. The preferred extraction method is to query an ontology on a Triple Store or development platform. We issue commands in SPARQL, the query language, and store the result sets.
The load takes files in a format consumable by the data modeling tool. PowerDesigner, for example, can import MS Excel files.
The complex part of CODT is the transformation from ontology to the Entity-Relationship metamodel.
Metadata is data that provides information about other data. A data set is a collection of data records. As defined herein, a Metadata Set is metadata stored in data sets.
An RDBMS stores information about the database schema in the system tables. For example, an RDBMS SYSCOLUMNS table contains information about the column, including COLUMN_NAME, TABLE_NAME, DATATYPE, NULLS, and other details. COLUMN_NAME and TABLE_NAME define a unique key. STSColumns is a Metadata Set.
Likewise, the CODT metadata sets are isomorphic representations of ontology, entity-relationship, and data modeling tool-specific metadata.
The transformation works in two steps:
- Transforming Ontology Metadata into generic Entity-Relationship metadata
- Transforming the Generic ER into Tool-specific metadata.
The same generic ER Metadata Set in the diagram is the Source for PowerDesigner, Sparx EA, and other tool-specific metadata sets.
MS Excel application
The patent drawing illustrates the CODT Extraction component interfacing with the Ontology Platform, Protege, TopBraid, or any RDF store, and the Load component importing metadata into the modeling tool or repository. Internally, there are Configuration, Analytics, and a User Interface component.
Any platform and programming language can implement the system, metadata sets, and methods. Microsoft Excel is the tool of choice for viewing and analyzing tabular data. Every data architect is familiar with Excel and knows how to view, search, and filter data. Hence, MS Excel is a fast prototyping tool for the CODT Metadata Sets, making the transformation easy to deploy.
Atlantic CODT installs four Excel Workbooks: Configuration, Ontology, Entity Relationship, and PowerDesigner, as well as an Excel Add-in for a few VBA functions.
In this article, we look at the core of the transformation process:
| Component | Metadata Set | Excel Workbook |
| Extraction | Ontology Metadata | Ontology MDS.xlsx |
| Transformation | Generic ER Metadata | Entity Relationship MDS.xlsx |
| Load | PowerDesigner | PowerDesigner MDS.xlsx |
In the following paragraphs, we follow the easiest transformation, ontology class to data model entity, to understand how CODT works. The PowerDesigner Metadata Set for import into the modeling tool is a simple Excel spreadsheet with columns for Entity Code, Name, Comment, lineage attributes, Prefix, Localname, and URI. PowerDesigner imports the spreadsheet and creates a data model entity for every row.
Other articles and Semantics for extra-large banks explain the complex transformation of ontology object properties into data model associative entities and the associated configuration options.
Extract SPARQL query
Some data modeling tools, such as Sparx Enterprise Architect and IBM Infosphere Data Architect, support imports of RDF/OWL files. The file import approach parses the ontology file and creates data model objects as they go along. CODT doesn’t parse files. Your ontology development platform, such as Protege or TopBraid, or your Graph, RDF store, has already implemented the parsing wheel. Hence, Atlantic CODT uses the ontology query language SPARQL on a FIBO graph to extract the metadata.
The CODT Configuration component generates a simple SPARQL query for all named classes. We query the class name, qualified name, namespace (URI), and, per the CODT FIBO configuration, the SKOS definition or class.
# Owl Classes.rq
SELECT ?class ?qname ?namespace ?skos_definition
WHERE {
?class a owl:Class .
BIND(afn:namespace(?class) AS ?namespace) .
FILTER (smf:isBound(?namespace) ).
BIND (smf:qname(?class) AS ?qname ) .
OPTIONAL { ?class skos:definition ?skos_definition }
FILTER (?class NOT IN (owl:Nothing, owl:Thing))
}
You do not want to have unmanned classes, which are equivalent to anonymous class restrictions, in your data model. Hence, we only extract classes that the EDM Council FIBO teams have named by stipulating a URI.
We also filter the ontology root classes owl:Nothing and owl:Thing that won’t become entities in a data model.
The raw query result set is a comma-separated value (CSV) text file with a tabulator as the delimiter.
The above query and result set are for the Topbraid Composer ontology development platform. You can adapt the query for your SPARQL dialect to produce the same metadata extract.
Extraction – Ontology MDS Workbook
The Ontology Metadata Set is an import of the raw CSV into MS Excel.
The ontology metadata workbook imports the raw extract and performs simple format conversions from the raw result set.
We have the Class, Qualified Name, Namespace, and SKOS definition, as well as the CODT configured main descriptive annotation property.
Notice the other Excel tabs and ontology metadata sets for Object Properties, Domain, Range, Sub-class, and Sub-property. The CODT Patent Specification provides a detailed explanation.
Entity Relationship MDS Workbook
The Entity-Relationship (ER) workbook is a generic representation of a Conceptual Data Model, independent of data modeling tool specifics.
The class-to-entity transformation is straightforward. The Entity Code must uniquely identify an entity in the ER Metadata Set. The Ontology MDS qualified name (= prefix+localname) is already unique for an ontology class. Hence, the configuration for the FIBO transformation uses the QName as the entity code without further transformation. Likewise, Prefix, Localname, and Namespace are copies of the Ontology MDS source.
The only fundamental transformation is a string conversion from the FIBO Camel Case Localname to the Conceptual/Logical Data Model naming convention. A UNCAMEL() function in the CODT add-in finds lower to uppercase changes and inserts a space. BoardAgreement becomes Board Agreement.
Data Modeling tools have prominent Comment or Definition properties for all model objects. A Configuration option tells CODT what Ontology Metadata Set column to use. All FIBO classes have a SKOS Definition.
The consistent design for all MDS features the source columns to the left and derived columns, such as the Entity Name, to the right.
Other tabs in the workbook transform supertype/subtype relations, associative entities derived from object properties, and attributes derived from data properties.
PowerDesigner MDS Workbook
The data model tool-specific MDS uses names and formats required for a direct load. In other words, PowerDesigner imports this MDS workbook.
We don’t need further transformation for the trivial import of entities. The Code, Comment, Prefix, Localname, URI, and (logical) Name are direct copies of the ER MDS values.
The other tabs, Inheritance, Inheritance Links, and Association, reflect PowerDesigner object names that differ from generic ER names.
PowerDesigner Import
Unlike CSV files, SAP PowerDesigner offers a robust metadata import feature that supports importing Microsoft Excel spreadsheets. We define a mapping from the Excel tab to the PD model object (entity) and from columns to model object properties.
The Imported File specified the path and Excel filename, and the Mapping Description lists columns and properties.
The import also facilitates orchestration. We can import several tabs within a single import job.
The screenshot below shows the outcome of the entity import. The tool created an entity for every row in the PowerDesigner MDS.
The Entity Properties show the Name, Code, and Comment of the Board Agreement.
The PowerDesigner import is performing poorly. It takes over an hour to import 2000 entities and several hours for Relationships and Relationship links.
Shortcomings of semi-manual transformations
The pre-Atlantic CODT, implemented in MS Excel, transformed the FIBO into a high-quality Enterprise Data Model. The provisional patent application claiming 2019 priority, #US16/985,414, describes the invention as offering a “working product”, CODT.
The “working product” was a prototype, and the FIBO data model is the Proof of Concept. While good enough to demonstrate the model transformation for USPTO examiners, the prototype is not
==IF(VLOOKUP(C54,'D:Local DocumentsFinancial Regulation OntologyData ModelQueryTransformation[Object Properties.xlsx]Object Properties'!$A$1:$I$495,8)<>"", VLOOKUP(C54,'D:Local DocumentsFinancial Regulation OntologyData ModelQueryTransformation[Object Properties.xlsx]Object Properties'!$A$1:$I$495,8),"")
Arcane Excel formulas and undocumented VBA code.
While fully implementing the invention, including the patented storage medium, Metadata sets, and a system comprising ETL, configuration, and analytics components, the method required too much operator manual action, copying data between metadata sets and extending formula ranges.
In short, it was a USPTO working product that was not ready for general availability.
Atlantic requirements
Just like FIB-DM is an enterprise data model, CODT remains enterprise-level software. The data modeling tool will never include a shrink-wrapped ontology-to-data model converter or a CODT import function. CODT is a transparent white box for ontologists and data architects:
- Robust
- Transparent
- Easy
- Configurable
- Extensible
Atlantic CODT is an open platform that fully discloses Metadata Sets, Excel formulas, Visual Basic for Applications (VBA), and Power Query M-language code.
Data Architects and Developers can easily extend the application by supporting additional configuration options and OWL constructs. You can easily migrate Metadata sets and code to your ETL platform.
Game-changing Microsoft Excel tables and Power Query
Tables and Power Query in the Atlantic version significantly reduced code, manual intervention, and improved readability.
Excel Tables
Excel tables organize a list of data with headers and offer additional functionality to manage and reference the data.
For Atlantic, the formulas refer to meaningful column names rather than letters, digits, and the dollar sign ($). For example, the Entity-Relationship MDS Name formula becomes: =UNCAMEL([@Localname])
Excel applies the formula automatically to the entire column. In other words, we don’t have to worry about named ranges and manually (or per macro) copy formulas.
Tables make the Metadata Set transparent and reduce errors.
Excel Power Queries
Power Query is a data connectivity and transformation technology for external data sources. While no RDF-Store connector for Power Query is available, CODT uses the technology to load the raw CSV query results and move and transform data from Ontology to Entity-Relationship onto the tool-specific metadata set.
The screenshot displays our Classes tab in the Ontology MDS, along with its Queries and Connections.
Every tab in the workbook contains an Excel Table, which populates from a Power Query on the SPARQL CSV result set.
A significant benefit of the Atlantic Text Import is the defined mapping of CSV columns to Excel Table columns and the powerful value transformation capabilities. Furthermore, we can refresh individual or all queries in the workbook if our raw ontology metadata changes.
We no longer have to open CSV files, copy values, format, and transform the data.
Let’s take a closer look at the Class query. The Power Query Editor formula bar shows our source file, the CSV Document CODT_HOME & "Ontology SourceqrClasses.txt.
The Applied steps list the Source, type changes to the text, and promote the first CSV record to table column headers.
M Programming language
Underneath the User interface lies M, a powerful ETL language. The snippet below shows the M-code of our Class query transformation.
Source = Csv.Document(File.Contents(CODT_HOME & "Ontology SourceqrClasses.txt"),[Delimiter=" ", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true])Like SQL and SPARQL, M is a declarative language. We define what we want without concern for how Power Query accomplishes the transformation.
For the Atlantic version of CODT, Power Query eliminated error-prone macros and transformation procedures.
Complex queries and transformations
Beyond simple transformations, Power Query fully supports Joins, Aggregations, and Transpositions.
The snapshot below is an outlook to upcoming Atlantic CODT articles and PowerPoints, and videos about the complex Object Property Transformations.
Ontology Object Properties are the most complex transformation challenge. The common mapping of ER Relationships is erroneous:
- Object properties, especially in the FIBO, have a sub-property hierarchy. Hence, we must transform them into associative entities, not relationships.
- Inverse object properties do not transform into two relationships or associative entities. For example, we do not have two association identifiers in a data model, and we are identified between Legal Entity and LEI. You, the data architect, must specify which object property (usually the active verb form) to transform.
- Various class restrictions must be consolidated into ER relationship cardinalities.
Queries rather than complex VLOOKUP() formulas in Atlantic make complex transformation rules transparent.
The Power Query Dependencies diagram shows the Ontology and the Entity-Relationship MDS – the arrows indicating a data source.
We visualize the query dependencies and the metadata movement from the ontology graph into the data modeling tool.
Atlantic summary
For FIB-DM licensees starting with SEIA, Atlantic provides a single-click transformation of your FIBO extensions into your FIBO Data Model. You can easily change configuration parameters to support data modeling standards and naming conventions.
Large financial institutions use the Atlantic CODT source code to develop and deploy proprietary enterprise embodiments.
Develop a Graph DB connector or source the raw Ontology metadata from your RDF store in a batch job. Replace the modeling tool import with your proprietary Enterprise Ontology and Data Model Repository synchronization.
Licensing Atlantic CODT
The Jayzed Data Models Patent Rights License Agreement (PRLA) covers the Valid Claims, CODT Software, and Products:
- Patent License to develop, make, have made, use, and import Licensed CODT Software and Products within the Licensee’s Organization or Group (PRLA #2).
- Modified Works – the licensee’s right to expand CODT, for example, to customize MDS and to develop Tool-Specific MDS for data or object modeling tools and software (PRLA #3.1).
- Secondary Works – the licensee may port the CODT Software to their ETL environments and retain the sole copyright to their code (PRLA #3.2).
- CODT Products – the licensee retains the title to Information Models that the Licensee produces with the CODT Software or Intellectual Property claimed in the CODT Patent. In other words, you have sole ownership of the models you generate with CODT.
The license is an add-on offered to licensees of the FIB-DM (Financial Industry Business Data Model). It’s a perpetual license, meaning it doesn’t expire. The license price is based on your EDMC (Enterprise Data Management Council) membership tier, currently 50% of the FIB-DM license.
Conclusion
Atlantic CODT enables the Semantic Enterprise Information Architecture.
With Atlantic, FIB-DM users transform their FIBO extensions and related proprietary or open-source ontologies into data models.
Atlantic is robust, transparent, easy to use, configurable, and extensible.
References and further reading
- NEW! Semantics for extra Large Banks – the CODT Tutorial video and PowerPoint.
- Finance Ontology transformed into an Enterprise Data Model article on FIB-DM
- Semantics for Data Architects PowerPoint online presentation
- FIBO on the Enterprise Data Management Council website:
- CODT in reverse mode – transforming data models into RDF/OWL.
Jurgen Ziemer, Ontologist and Data Architect at Jayzed Data Models
Email: jziemer@jayzed.com