BI Tools and SQL Queries

This page contains contains information on the BI Server’s supported tools and their usage. For information regarding administering the BI Server please see the section on Configuring the BI Server.

Page Contents

1. Introduction
2. Supported Clients
3. SQL Schema Mappings
4. Example SQL Schema Mapping
5. SQL Schema Field Mapping Options
6. SQL Schema Constraint Options
7. Auto-generated Schema Mappings
8. Mapping SPARQL Queries in a Schema Mapping
   1. Query Bindings Types
9. Reasoning and BI Server Queries
10. Setting the Dataset

---

Introduction

Stardog provides a facility to bridge the flexible schema of the graph data model to the traditional relational model required by business intelligence tools using SQL. This enables seamless use of business intelligence and visualization tools such as Tableau and Power BI.

Using this feature requires creating a schema mapping from Stardog’s data model to a relational data model. This mapping will be used to generate a relational schema. After creating the schema, the full power of SQL becomes available to relational clients.

Supported Clients

Clients connect to the BI Server using the MySQL client/server protocol. This means that a wide range of clients are supported including MySQL ODBC, JDBC and ADO.NET drivers.

Any supported client that requires the MySQL ODBC or the MySQL Connector/NET driver must use the 8.0.22 or earlier version.

Currently the following BI tools are officially supported:

Once the appropriate client driver is installed, select the option to connect to a MySQL server, enter your Stardog hostname (if running locally, use your IP address instead of localhost) and configured BI server port and provide credentials for a Stardog user.

Although not officially supported, other visualization and reporting tools should work with the BI Server. Please let us know if you’re using a different tool and have any questions or difficulties.

SQL Schema Mappings

A schema mapping defines a view over the RDF triples in a database in terms of tables in a relational schema. A schema mapping is expressed as RDF and stored in a named graph (as identified by the sql.schema.graph option which is by default set to the IRI tag:stardog:api:sql:schema) in the same database where the data to query is stored. The top-level elements in the schema mapping are table mappings. A table mapping defines the relationship between a subset of triples in the database and a set of rows conforming to a fixed schema table view of the data. A table mapping consists of a set of field mappings. Field mappings define the schema of the table as well as specify which values are present in each row.

To define a table mapping, you should add an entity of type tag:stardog:api:sql:TableMapping. Field mappings can be included in a table mapping using the tag:stardog:api:sql:hasField property. The name of the table can be provided using the tag:stardog:api:sql:tableName property. Field mappings can be freely created outside of table mappings and re-used in multiple table mappings.

The table mapping is used to match a set of triples in the database and transform them into rows in the table. Triples are joined together implicitly with a shared subject. The shared subject is mapped to a field named id. For instance, if you map a name and age property, this would give the same results as the SPARQL query:

SELECT ?id, ?name, ?age {
  ?id :name ?name ; :age ?age
}

Table mappings can include constraints. Constraints provide the ability to restrict the set of triples present in a mapped table. Constraints are similar to field mappings in that they require a property which will be linked to the shared subject. However, the object is specified as a constant. A common usage is to require the rows in a table to be mapped from instances of a given class. The tag:stardog:api:sql:hasConstraint property can be used to specify constraints for a table mapping.

Example SQL Schema Mapping

The following example is composed of several elements:

1. A Stardog database
2. A SQL schema mapping defining the schema and it’s relationship to the triples in the database
3. A SQL table schema which is auto-generated by Stardog
4. SQL queries and results

---

1. The contents of the database:

    @prefix : <http://example.com/> .
   
    :Alice  a :Person ;
            :name "Alice" ;
            :nationality :USA .
    :Bob    a :Person ;
            :name "Bob" ;
            :nationality :UK .
   

2. The SQL schema definition:

    @prefix sql: <tag:stardog:api:sql:> .
    @prefix : <http://example.com/> .
   
    :PersonTableMapping a sql:TableMapping ;
      sql:tableName "Person" ;
      sql:hasConstraint [ sql:property rdf:type ; sql:object :Person ] ;
      sql:hasField [ sql:property :name ; sql:fieldName "person_name" ] ;
      sql:hasField [ sql:property :nationality ] .
   

3. The generated SQL table schema:

    CREATE TABLE Person (
      id varchar NOT NULL,
      person_name varchar NOT NULL,
      nationality NOT NULL,
      PRIMARY KEY (id)
    )
   

4. Example query and result:

    SELECT id, person_name, nationality FROM Person
   

    | id                       | person_name | nationality |
    |--------------------------+-------------+-------------|
    | http://example.com/Alice | Alice       | USA         |
    | http://example.com/Bob   | Bob         | UK          |
   

   If you have multiple tables, you can use tag:stardog:api:sql:refersTo to create a foreign key type relationship. For example, if your database looks like this:

    @prefix : <http://example.com/> .
   
    :Alice  a :Person ;
            :name "Alice" ;
            :nationality :USA .
    :Bob    a :Person ;
            :name "Bob" ;
            :nationality :UK .
   
    :UK a :Country ;
            :name "United Kingdom" .
    :USA a :Country ;
            :name "United States of America" .
   

   You could create a schema mapping with two tables that looks like this:

    @prefix sql: <tag:stardog:api:sql:> .
    @prefix : <http://example.com/> .
   
    :PersonTableMapping a sql:TableMapping ;
      sql:tableName "Person" ;
      sql:hasConstraint [ sql:property rdf:type ; sql:object :Person ] ;
      sql:hasField [ sql:property :name ; sql:fieldName "person_name" ] ;
      sql:hasField [ sql:property :nationality ; sql:refersTo :CountryTableMapping ] .
   
    :CountryTableMapping a sql:TableMapping ;
      sql:tableName "Country" ;
      sql:hasConstraint [ sql:property rdf:type ; sql:object :Country ] ;
      sql:hasField [ sql:property :name ; sql:fieldName "country_name" ] .
   

SQL Schema Field Mapping Options

Field mappings may contain the following properties:

SQL Schema Constraint Options

Constraints are generally expressed using the tag:stardog:api:sql:hasConstraint predicate linking the table mapping to the constraint. The constraint may contain the following properties:

To constrain table contents to be instances of the Person class, we can use the hasConstraint form as follows.

@prefix sql: <tag:stardog:api:sql:> .
@prefix : <http://example.com/> .

:PersonTableMapping a sql:TableMapping ;
  sql:tableName "Person" ;
  sql:hasConstraint [ sql:property rdf:type ; sql:object :Person ] ;
  sql:hasField [ sql:property t:name ; sql:fieldName "person_name" ] .

We can also use a constraint to include only Stardog users via the isStardogUser predicate:

@prefix sql: <tag:stardog:api:sql:> .
@prefix : <http://example.com/> .

:PersonTableMapping a sql:TableMapping ;
  sql:tableName "StardogUsers" ;
  sql:hasConstraint [ sql:property :isStardogUser ; sql:object true ] ;
  sql:hasField [ sql:property :name ; sql:fieldName "person_name" ] .

Or a constraint with inverse set if we want to find people influenced by Einstein:

@prefix sql: <tag:stardog:api:sql:> .
@prefix : <http://example.com/> .

:PersonTableMapping a sql:TableMapping ;
  sql:tableName "EinsteinPupils" ;
  sql:hasConstraint [ sql:property :influencedBy ; sql:object :Einstein ; sql:inverse true ] ;
  sql:hasField [ sql:property :name ; sql:fieldName "person_name" ] .

Restricting contents of a table to those of a specified class can be done with the shorthand predicate tag:stardog:api:sql:class. When using this, the sql:tableName property may be omitted and the table name will default to the name of the class. Here’s an example of a table mapping which includes only instances of :Person.

@prefix sql: <tag:stardog:api:sql:> .
@prefix : <http://example.com/> .

:PersonTableMapping a sql:TableMapping ;
  sql:class :Person ;
  sql:hasField [ sql:property :name ; sql:fieldName "person_name" ] .

Auto-generated Schema Mappings

If the schema mapping named graph is empty and the sql.schema.auto database option is set to true (which is the default value) a default schema mapping will be created. By default, the SQL schema mapping will be generated from the RDFS/OWL schema in the database but the sql.schema.auto.source option can be changed to use SHACL shapes definitions for schema generation.

In the default mapping, each RDFS/OWL class will be mapped to a table. Any property whose rdfs:domain is set to that class will be added to the table as a field. The rdfs:range defined for the class will be used as the type of the column which will be a primitive SQL type if the range is a datatype and a foreign key reference if the range is a class.

If sql.schema.auto.source is set to shacl then any node shape with a sh:targetClass will be mapped to a table. The property shapes defined on the shape will be added as a field if the path for the property shape is a predicate. The range defined for the property via sh:class, sh:datatype, or sh:node will be used as the type of the field. An example of mappings generated from SHACL constraints can be found in the Stardog tutorials.

The auto-generated mappings will be updated automatically as the database gets updated unless sql.server.commit.invalidates.schema option is set to false. In either case, auto-generated mappings are never materialized in the special graph identified by the sql.schema.graph option. The schema information will still be available to the SQL tools. If you would like to inspect the auto-generated mappings you can generate the mappings using the following command:

$ stardog data model --input owl --output sql DB

If you would like to customize the mappings then you can save the output in a file, make changes to the mappings and load the file into the BI/SQL named graph.

Mapping SPARQL Queries in a Schema Mapping

In addition to table mappings, stored SPARQL SELECT queries can be treated as tables. They will automatically be added to the schema if visible from the database. The following restrictions apply:

1. The FROM and FROM NAMED clauses, which specify the named graphs to include in the query’s dataset, are ignored
2. The reasoning setting that is saved with the query is ignored

Query Bindings Types

While a mapping is not required for a stored query to be accessible as a table, by default, all the bindings from the query will be mapped to fields of type string. To override this default you must specify a mapping for the stored query. Mappings for stored queries support for following tag:stardog:api:sql:TableMapping properties:

… and the following field-mapping properties:

As an example, here is a stored query named aml:

PREFIX : <tag:aml:>

SELECT ?acct ?amount {
  ?acct a :Account ;
        :hasAmount ?amount ;
}

The ?acct binding is an IRI and the ?amount binding is an xsd:decimal. The mapping for this query is:

@PREFIX : <tag:aml:mapping:> .
@PREFIX qm: <tag:stardog:api:sql:> .

:amlQueryMapping a qm:TableMapping ;
                   qm:queryName "aml" ;
                   qm:hasField :acctField, :amountField .

:acctField qm:binding "acct" ;
           qm:type xsd:anyURI .

:amountField qm:binding "amount" ;
             qm:type xsd:decimal .

Reasoning and BI Server Queries

Stardog’s reasoning capabilities are available to BI Server queries. The reasoning schema (see Reasoning with Multiple Schemas) can be selected at any time during a connection by setting the reasoning_schema session variable.

To use the default reasoning schema execute:

set @@reasoning_schema = 'default'

or you can explicitly set a schema by executing:

set @@reasoning_schema = 'other_schema'

The reasoning schema can be set at the connection level in various ways:

1. The initstmt option can be set on the MySQL Connector/ODBC connection.
2. The sessionVariables option can be set on a MySQL Connector/J (JDBC) connection. This can be added directly to the connection URL, e.g. jdbc:mysql://localhost:5806/db?sessionVariables=reasoning_schema='default'.
3. The Tableau MySQL connection dialog has an option in the bottom left called Initial SQL.... Clicking this option will open a dialog where the statement to set the reasoning schema can be provided.
4. The Power BI MySQL connection dialog has an “Advanced Options” section which can be expanded to reveal a “SQL statement” option.

Setting a reasoning schema from Power BI Desktop will result in an error. At this time, there is no workaround, so users are advised to use Tableau or MySQL Workbench instead if using reasoning is a requirement.

Setting the Dataset

By default, BI Server queries are run against all the named graphs, both local and virtual, that are visible to the user. This default can be changed per database by setting the sql.data.graphs database option. The dataset can also be changed at the connection level by setting the data_graph session variable. For example, to configure a connection to query only the database’s local graphs from MySQL Workbench, you would execute set @@data_graph = 'tag:stardog:api:context:local';.

---