Learn how to map values of a given column to classes quickly by using Ontopic Studio
The traditional way of mapping column values to classes when building a knowledge graph is annoying - either you have to write a lot of SQL queries or you break the clear separation between data from the source and the ontology. In this article, we show you how to map a class based on the value in a given column in a single step with Ontopic Studio.
The pattern: mapping column values to classes
As in our previous posts on mapping patterns, we continue mapping data from Microsoft’s AdventureWorks sample database to classes and properties from schema.org. We as well extend the latter ontology with new classes.
The database contains information about bike parts and their components that are sold from local businesses to customers (resellers). Many people are involved with different roles in this process: employees, customer contacts, and Vendor contacts. We want to recognize each type of person involved and represent each of them as a different class.
We use the class schema:Person from schema.org and we create new sub-classes to represent each type, such as Vendor, Customer, and Employee.
The database has a table person containing information about all employees, customer contacts, and Vendor contacts. This table contains a column personType representing each type with a code.
We will map some of the values in this column to classes. For example, we will assign the value ‘VC’ to the class Vendor.
Preparing the knowledge graph using existing patterns
Starting from the table person.person in our AdventureWorks database we first create the mirror lens lenses.person.person.
Following the fundamental “mapping a table to a class” pattern, we then create the auto-suggested template data:person-person/{businessentityid} for identifying the entities, which is based on the primary key businessentityid. To complete the mapping entry, we select the class schema:Person in the drop-down menu.
Using the dictionary to create classes
Using Ontopic Studio there is no need to create one different SQL query for each class mapping entry. The novel notion of dictionary allows us to directly map column values to classes. No need to create a new lens, we reuse the lens lenses.person.person.
As for the previous mapping entry, we use the template data:person-person/{businessentityid} as the subject. This time, we cannot directly assign a “constant” class, as we want to map to the new classes only rows matching certain values, not all the rows of the lens. Instead, we select the “dictionary” option for the class and personType as a column.
Now we just have to fill the dictionary pairs. On the left side, we insert the values we want to map (‘SC’, ‘IN’, ‘SP’, ‘EM’, ‘VC’ and ‘GC’) and on the right, the classes (respectively StoreCustomer, IndividualCustomer, SalesEmployee, NonSalesEmployee, Vendor and GeneralContact). Each class is given by the rows containing the specific value that should be mapped to.
As these new classes are not present in the ontology, we create them and declare them as sub-classes of schema:Person on-the-fly while filling the dictionary pairs. Note that they are created by default with the prefix voc.
Observe that these values were not understandable without expert knowledge for decoding the acronyms.
The resulting mapping entry is the following:
Note that, like with the previous mapping patterns, no SQL query was needed.
We can test the mapping by making a query that shows the content of the knowledge graph. We can retrieve all entities belonging to the class voc:Vendor. We will be able to see that, with the current data, 156 persons belong to the voc:Vendor class.
Final remarks on mapping column values to classes
A similar pattern has been presented in the article Mapping patterns for Virtual Knowledge Graphs by Diego Calvanese et al. with the name Clustering Entity to Class (CE2C). In the book Designing and Building Enterprise knowledge graphs by the authors Juan Sequeda and Ora Lassila we also have a pattern to map data values to a class. Their pattern, called Data as Concept, defines a query that returns the specific rows through a condition on specific values of a column that satisfies the definition of the concept. The use of the term concept is equivalent to the term class used in this article.
The dictionary feature simplifies the application of this pattern by allowing us to obtain multiple classes based on the possible values of a column in a single step. It replaces cryptic codes with classes having explicit names, making data much easier to understand in the knowledge graph than it is in the source.
Ontopic Studio simplifies mapping creation by saving from having to learn new syntax (no-code approach) and by handling the “mapping column values to classes” pattern with a single entry. It allows domain experts to consider all possible cases at once, reducing the risk of forgetting some of them as when they were spread into many mapping entries. It also saves them from being exposed to technicalities such as writing custom SQL queries for each possible case.
We have seen as well in this article that we have the option to extend an existing ontology with custom classes to represent more precisely our current domain. If the content of the database changes, or a new value has been added, we can simply update the dictionary entry to address these changes.
In the next article, we will show how and when it is really necessary to use joins when building a mapping.
Have you ever worked with a mapping editor before?
You can get your demo access by filling out the form and starting mapping right away.
No time to test and would like a short demo first? You can book your demo here
Get a demo access of Ontopic Studio
Ready to do mapping with a no-code approach? Let us help you. Get a demo access:
