Tabular views of data with tables, columns and rows as the key concepts are still a popular basis for data analysis and storage, used in relational database management systems and spreadsheet software. Graph based approaches are a superset of the tabular view and use vertices and edges/properties as the key concepts to manage the data. A common way to store graph data is using subject, predicate and object triples in a “triple store”. For quite a few use cases, transforming the triple store data to a tabular view is needed since tabular systems are still widespread. The straightforward approach to generating such data using a “naive” query will, however, create unexpected results or even fail because of conceptual diferences between the relational and the graph approaches regarding the handling of the cardinality/multiplicity of properties. This work shows a systematic approach to analyze the property cardinalities of the graph data in an RDF/SPARQL triple store and to extract “truly tabular” data (with cardinalities of 1 in each column) by automatically generating appropriate queries. We propose a SPARQL query builder that simplifies the generation of queries that limit the result set to such “truly tabular” data.
In recent years Wikidata [
The work discussed in this paper is motivated by the frustration we experienced when trying to extract academic conference data from RDF datasets as supplied by the German National Library (in its GND dataset) or by Wikidata. Our experience with the GND dump1 as shown in Table 1 shows that the RDF dataset has content that creates surprising efects when using a straightforward/naive query to select the intended data. E.g., of the 731K conference entries in the dataset, only 3206 have a unique acronym, only 0.4% of the records have acronyms at all, and some conferences have up to 4 diferent ones. When querying the dataset with a “naive” query 2, we had single conferences having 1 query solution (known as a “binding set”), while others had up to 576 query solutions. We learned that we had to analyze our data for cardinalities and adapt the SPARQL query accordingly.
The problem showed up again when querying Wikidata, and the research question arose, “How could duplicate query solutions be avoided and queries be generated for tabular target systems?”
To better describe the problem, consider a novice user with a “tabular” (SQL) background
attempting to query Wikidata to extract a table full of information of interest - data related
to “Game of Throne” characters. Very likely, they are unfamiliar with the details of SPARQL
multiset semantics (as outlined by Angles [
In Wikidata, a “Game of Thrones” character (such as Jon Snow3) would be linked via an “instance of”4 property to the type “Game of Thrones” character5. Retrieving all instances of Game of Thrones characters would simply check for all items of that type (117 at the time of writing this work). It is natural to now create a query to get a table of all Game of Thrones characters with columns for their most interesting aspects.
We might start using the Wikidata Query Service [
We might thus create a “naive” query7 without aggregates as it would work in SQL out of the box. The result of executing this query appears to be nicely tabular and can be exported to CSV to then be imported to our favorite relational database or spreadsheet. However, when 2https://wiki.bitplan.com/index.php/GOTExample2022#NaiveGOTQuery2 3https://www.wikidata.org/wiki/Q3183235 4https://www.wikidata.org/wiki/Property:P31 5https://www.wikidata.org/wiki/Q20086263 6https://www.wikidata.org/wiki/Wikidata:SPARQL_query_service/queries/examples 7https://wiki.bitplan.com/index.php/GOTExample2022#NaiveGOTQuery1 examining the results, the table does not contain 117 entries (all the documented Game of Thrones characters) but only 28.
SPARQL queries do not return NULL values as a SQL/relational database query language [
Next, suppose a bit more information is needed and 3 more properties are requested. The result of this third naive query9 leads to a new surprise for the novice. Suddenly the query result has 304 rows for our 117 characters. There are duplicate rows for each character and strange combinations of entries. A troublesome example seems to be Jorah Mormont10 having 8 diferent values for “occupation”.
Given this problem statement, we explore the basic concepts upon which this work is based in Section 2. Sections 3 and 4 overview how property cardinality analyses are performed (and can be automated), as well as the results of analyzing various Wikidata item types. Section 5 discusses related work; Section 6 concludes.
Viewing the world in terms of tables vs. graphs makes a major diference in your approach to data management.
A tabular design is more straightforward than a graph design since it forces dealing with multi-valued properties in a very explicit manner. In each row, a column has exactly one entry. In a graph, this restriction is relaxed and multiple values per property (i.e., column) are possible. This calls for an implicit 1 : relation for the property’s domain and range. Unfortunately, introducing the 1 : relation is often not an explicit design decision but happens accidentally (due to multi-valued data being entered by chance) or might be motivated by specific requirements. Note that the use of multi-valued properties can be a legitimate design choice.
Choosing a tabular design to avoid complexity is not easy in a graph environment, especially
if data and schema are not well documented or controlled. This is the case with many datasets
in an RDF/SPARQL based system, such as Wikidata or GND [
Using a “naive” approach for handling data in RDF/SPARQL directly translates SQL thinking to
SPARQL. However, as discussed above, this leads to surprising results. Following the “principle
of least astonishment” [
8https://wiki.bitplan.com/index.php/GOTExample2022#NaiveGOTQuery2 9https://wiki.bitplan.com/index.php/GOTExample2022#NaiveGOTQuery3 10https://www.wikidata.org/wiki/Q3810007 11The main catalog of the German National Library has embraced an RDF ontology from 2012 12If a feature is accidentally misapplied by the user and causes what appears to him to be an unpredictable result, that feature has a high astonishment factor and is therefore undesirable. If a feature has a high astonishment factor, it may be necessary to redesign the feature
Relational databases based on tables, columns and rows were proposed by E. F. Codd in 1970 [
• A table is a subset of the cartesian product 1 × · · · × . has attributes 1 . . . .
Each attribute has a domain , and any given row of is an -tuple (1, . . . , ) such that ∈ . • A table is in first normal form (1NF) if all domains are atomic, i.e., no domain is composed of other atoms.
A graph = (, ) has a set of vertices = {1, 2, . . . , } and a set of edges =
{1, 2, . . . , } where each edge is a pair (, ) defining the connected vertices. Graph
data may be stored in RDF [
The concepts of classes (also known as types) are defined in the W3C specifications for RDF:
Resources may be divided into groups called classes. The members of a class are known as instances
of the class. Classes are themselves resources [
Wikidata uses the Property P31/“instance of” to assign classes to instances. Properties are the edges of a graph. SPARQL allows querying instances of a specific class as well as querying other properties.
A SPARQL query result is a solutions sequence (or “binding sets”), which might be unordered [
In the context of this work the concept of the cardinality (also called multiplicity or frequency) of properties is relevant. We define the cardinality of a property (also called max frequency, maxf) with respect to an instance as the maximum number of RDF triples selected by the basic graph pattern, <instanceIdentifier> <propertyIdentifier> ?value . (See GND Query to analyze multiplicity13: for a concrete SPARQL example.) In general, a SPARQL graph pattern is written as <subjIdentifierOrVariableName> <propertyIdentifierOrVariableName> <objectIdentifierOrVariableName>.
Expanding on this pattern, all instances of a particular class can be queried using ?item a <classIdentifier> . However, the problem is a bit more complex. You may want all instances of the class AND any of its subclasses. This requires using SPARQL property paths, and in Wikidata is written as ?item wdt:P31/wdt:P279* <classIdentifier> (see Wikidata basic membership properties14). The query result is then grouped by the ?item to count the property values per class instance.
A Truly tabular SPARQL SELECT query result is a solution sequence where • each RDF term (column) is functionally dependent on a property of a specific class • each tuple of RDF terms (row) represents a single instance of a class so that the cardinality / entry count is limited by: ∏︁ max(entrycount (table, column)) = 1 col=0 I.e., for each row (an instance for which data is provided) and column (a property that has some value) in a table, there is only one value15).
For example, for a list of instances of people (class: human/Q5), there might be properties indicating their date of birth, their country of residence, a contact phone number and their marital status. Properties such as date of birth, country of residence and marital status should be single-valued for any specific person, whereas there may be multiple contact phone numbers. In this case, the single-valued properties would lead to “truly tabular” query results while the list of contact phone numbers column needs to be specified by its own RDF term in the solution mapping using the SPARQL GROUP_CONCAT aggregate to avoid multiple rows per person.
Obtaining such tabular query results directly is much easier than having to post-process the data to make it compatible with systems expecting tabular data.
Since often only a few instances of a specific class have multi-valued attributes (such as persons with multiple genders), it is simpler to handle the standard cases with single-valued attributes and account for the special cases with extra aggregate columns such as COUNT, MIN, MAX and GROUP_CONCAT.
Combining the tabular standard view on data with the aggregate view containing the special cases allows handling the special cases with standard tabular tools such as spreadsheets.
Our definition of “truly tabular” corresponds to the first normal form of relational database theory. In practice it is not clear whether the first normal form (1NF) is violated if an attribute holds a value that has a datatype that is non relational such as a string but the content is 13https://wiki.bitplan.com/index.php/Truly_Tabular_RDF/GND 14https://www.wikidata.org/wiki/Help:Basic_membership_properties 15Note that the value does not have to be atomic in the pure sense - see comments below SELECT ?count (COUNT(?count) AS ?frequency) WHERE {
SELECT ?item ?itemLabel (COUNT (?value) AS ?count) WHERE { # instance of Game of Thrones character ?item wdt:P31 wd:Q20086263. ?item rdfs:label ?itemLabel.
FILTER (LANG(?itemLabel) = "en"). # cause of death ?item wdt:P509 ?value.
} GROUP BY ?item ?itemLabel } GROUP BY ?count ORDER BY DESC (?frequency)
Listing 1: SPARQL Query to count property data availability multi-valued by using a delimiter separated list of entries. "A,B,C" could be interpreted as an atomic value or as a list of three items. If the latter, the table would not be in 1NF any more.
IBM DB2 designer Christopher J. Date states that "The 1970 paper fails to define the term
atomic value adequately. This failure led to a massive misunderstanding in the database
community at large as to what exactly it means to say a relation is in first normal form — a
misunderstanding that persists, widely, to the present day." [
Therefore, the "true tabularity" of a table is in the eyes of the observer.
Analyzing the cardinalities of the properties related to a class is done in three steps. First a count query determines the total number of instances in the class, then a property count query selects all properties that any instance of the class might use along with the number of instances that actually provide data for that property. In the third step, a query per property is performed that reports the actual cardinality statistics per property. Listing 1 shows an example for the property “cause of death” of the class “Game of Thrones” character. (Note that the property P279 is not necessary since we know that there are no subclasses defined for the class Q20086263.)
To automate the cardinality analysis, we created a tool that will be enhanced to become a SPARQL query builder: http://wikidata.bitplan.com16.
Figure 1 shows an exemplary screen shot after starting the analysis for the class Game of Thrones Character(Q20086263).
The two figures 2, 3 show how “truly tabular” the instances of the classes scientific conference series (Q47258130)17 and academic conference (Q2020153)18 are. The examples are in the focus of the ConfIDent[15] project that partly funded this work. The explanation of the details of the columns of the property statistics are available on a wiki page19. 11 properties are available for more than 20% of the 4244 scientific conference series instances. The external IDs are not unique for a small percentage of corner cases of less than 0.5%. There may be up to three diferent titles in 6 cases.
13 properties are available for more than 20% of the 7765 academic conference instances. The external IDs are not unique for a small percentage of corner cases of less than 0.2%. There may be up to 14 diferent locations in 30 cases and multiple start and end dates and titles in less than 0.3% of the cases.
The distribution of the cardinality of properties for classes is a data quality indicator for how well the instance data of this class is curated. Table 2 shows a table of classes and the number of instances available in Wikidata as of 2022-07. The columns total, 80%, 20% and tail denote the number of properties in total and the number of instances where the percentage of instances where at least one value is available for the property is higher than 80% versus 20% according to the pareto principle. The tail column shows the percentage of properties being available in 17https://www.wikidata.org/wiki/Q47258130 18https://www.wikidata.org/wiki/Q2020153 19https://wiki.bitplan.com/index.php/Truly_Tabular_RDF/Info less than 20% of the cases.
Typically the encyclopedic classes such as country or continent with a small number of instances to curate have a long list of well defined properties. Alternately, a concept such as “single” (a record with a single or just a few music items) is an example of a probably crowdsourced class that has a lot of instances but only a few well curated properties. Human(Q5) is one of the classes with the highest number of instances. Only two properties P31/instance of and P21/sex or gender are available in more than 80% of the cases. There are only 12 properties which are available in 20% of the cases but 4647 properties in total that makes 99.7% of properties are rarely available.
A recent survey by Fiorelli [16] covers several systems for the triplification of tabular data. Such systems are an alternative to make sure that “truly tabular” results are imported.
Gleim [17] applies a compact trie-based representation of property and type co-occurrences to recommend properties to be used for items. This means that the recommender proposes columns that should be filled. The tabularity will only be influenced if more than one entry is added for the recommended property. The inclusion of the values of properties and especially their multiplicity are not considered in the property recommendation.
Lisena [18] handles the case when the number of properties that have multiple values grows (e.g., multilingual names, multilingual descriptions, a set of images, ...). A SPARQL query of these properties would return many results, one for each combination of values. A merging procedure is proposed such that the resulting JSON has a tree structure. The intended “list” aggregate option of our approach creates an equivalent result.
Property cardinality analysis is necessary to obtain SPARQL query results appropriate for tabular view target systems such as relational databases or spreadsheets. Property cardinality analysis can be useful for various applications such as the development of semantic alignments using OpenRefine 25, the analysis of the completeness of the description of an entity using tools such as Recoin[19], and the adjustment of the definition of Wikidata statements
We propose a property cardinality analysis and SPARQL query builder tool that simplifies the generation of queries to limit the result set to “truly tabular” data.
Even in its current experimental state, the analysis and query builder results are a strong basis for future work. Potential extensions include data quality analysis, instance-level ontology 25https://github.com/OpenRefine/OpenRefine validation and benchmarking of SPARQL endpoints.
Further details and example are made available at https://wiki.bitplan.com/index.php/Truly_ Tabular_RDF.
Acknowledgements. This research has been partly funded by a grant of the Deutsche Forschungsgemeinschaft (DFG). 26
26ConfIDent project; see https://gepris.dfg.de/gepris/projekt/426477583 [15] Technische Informationsbibliothek (TIB), ConfIDent – a service for open research information on conferences, 2020. URL: https://projects.tib.eu/en/confident/. [16] M. Fiorelli, A. Stellato, Lifting tabular data to RDF: A survey, in: Metadata and Semantic Research, Springer International Publishing, 2021, pp. 85–96. doi:10.1007/ 978-3-030-71903-6\_9. [17] L. C. Gleim, R. Schimassek, D. Hüser, M. Peters, C. Krämer, M. Cochez, S. Decker, SchemaTree: Maximum-likelihood property recommendation for wikidata, in: The Semantic Web, Springer International Publishing, 2020, pp. 179–195. doi:10.1007/ 978-3-030-49461-2\_11. [18] P. Lisena, A. Meroño-Peñuela, T. Kuhn, R. Troncy, Easy web API development with SPARQL Transformer, in: C. Ghidini, O. Hartig, M. Maleshkova, V. Svátek, I. Cruz, A. Hogan, J. Song, M. Lefrançois, F. Gandon (Eds.), The Semantic Web – ISWC 2019, Springer International Publishing, Cham, 2019, pp. 454–470. doi:10.1007/978-3-030-30796-7\_28. [19] V. Balaraman, S. Razniewski, W. Nutt, Recoin, in: Companion of the The Web Conference 2018 on The Web Conference 2018 - WWW '18, ACM Press, 2018, p. 1787.–1792. doi:10. 1145/3184558.3191641.