Visualizing SPARQL Query Results in Yellowfin

Using Yellowfin as a Launch Point for Semantic Web Exploration

Situation Analysis

In a typical SQL RDBMS, all Keys — whether Candidate, Primary, or Foreign — are scoped to a specific RDBMS instance. Basically, these identifiers lose their unique identification properties once you enter the realm of distributed databases.

What is a Super Key?

A Super Key is an identifier that unambiguously names its referent — the entity it identifies — regardless of the context in which it is encountered. When looked up (dereferenced), a Super Key always resolves to a description of its referent, without any operating system, RDBMS instance, platform, or other dependency.

On an HTTP-based network, whether public or private, hyperlinks (HTTP URIs) can serve as such Super Keys, and provide a powerful mechanism for enhancing Reports and Dashboards produced by Business Intelligence and Analytics oriented productivity tools.

Super Keys and Business Intelligence & Analytics

Reports and Dashboards are fundamentally insight-oriented projections of data in context. Using hyperlinks — Super Keys — as entity names enables every reader to perform additional ad-hoc exploration without the need to create yet another report.

Virtuoso can add these identification and exploration capabilities to any existing JDBC-compliant Business Intelligence or Analytics tool, through a simple JDBC connection.

The rest of this post demonstrates the use of Virtuoso to inject Super Keys into Yellowfin reports.

Enhancing Yellowfin, with Super Keys from Virtuoso

Prerequisites:

• A functional installation of Yellowfin
• A working JDBC Connection to a Virtuoso Universal Server instance which has been configured to enable SQL access via the JDBC protocol
• Virtuoso JDBC Driver classname properly added to /Yellowfin 7.35/appserver/webapps/ROOT/WEB-INF/web.xml

Getting Data from Virtuoso into Yellowfin

(1) Navigate to your running Yellowfin instance. Click on the yellow plus icon and select Data Source.

Click Database.

Select the Virtuoso JDBC driver from the JDBC Driver list. WE will be using virtuoso.jdbc4.Driver for this demonstration. Fill out your connection string and login parameters, followed by clicking on the Create View Arrow, and click Test Connection.

Upon a successful test connection, click Create View.

Virtuoso makes this work by extending SQL with SPARQL (a/k/a SPASQL); in this case, by putting a SPARQL query into the SQL FROM clause.

Here, we are querying against DBpedia (i.e., Wikipedia content in Structured Data form, that has been published in line with Linked Data principles). Select Freehand SQL and use the following SPASQL query—

SELECT DISTINCT 
   DBpedia.name        AS "Actor Label", 
   DBpedia.actor       AS "Actor ID", 
   DBpedia.school      AS "School ID", 
   DBpedia.schoolLabel AS "School Label"
FROM
(
  SPARQL
  PREFIX  dct:  <http://purl.org/dc/terms/>
  SELECT ?actor
         ?school
         ?name
         ?schoolLabel
  WHERE  
  {
    SERVICE <http://dbpedia.org/sparql> 
    {
      ?actor 
         dct:subject <http://dbpedia.org/resource/Category:Tony_Award_winners> ;
         <http://dbpedia.org/ontology/almaMater>  ?school ;
         foaf:name  ?name .
      ?school
         rdfs:label  ?schoolLabel . 
      FILTER ( LANG(?name) = "en" )
      FILTER ( LANG(?schoolLabel) = "en" )
    }
  }
  ORDER BY ASC(?school)
  LIMIT 100
) AS DBpedia

Click Validate. After successful validation, click Create Analysis.

For this example, drag, the Actor ID dimension (Relation or Entity Relationship Type) into the Columns field. In order to populate the column with data, a custom datatype will need to be established. Click on the black arrow on the right-side of the Actor ID column and select Format -> Edit.

Click on the Data tab, and change Style from default to custom.

Exit out of the menu and see the HTTP URIs appear as literal value (i.e, they aren’t live hyperlinks or references at this stage).

Right click on the right-side Actor ID arrow again, and click on Format -> Edit. Navigate to the Display tab and enter ## in the URL text box.

Exit the menu and the HTTP URI literal values will become live hyperlinks.

Using Other Columns with Hyperlinks is not a straightforward capability with yellowfin. Using HTTP URI’s is the most straightforward path.

Due to how live hyperlinks are handled in Yellowfin, you can only experience the reference lookup feature of an HTTP URI by using CTRL+RightMouse+Click (or similar) to open a Context Menu in your browser that includes a “Go to” option.

Right Click Required due to External URLs not resolving properly when clicked

Clicking on “go to…” will result in your web browser retrieving an HTML page that describes the entity identified by the selected DBpedia hyperlink (HTTP URI).

For the purpose of presenting a finished report, I added columns with literal and hyperlink values with the goal of juxtaposing two types of identifiers (literals and hyperlink-based references) associated with the entities in my report.

Click Publish, and then Save.

Your report will be generated and presented in your web browser. Report viewers now have the additional ability to drill-down for information on an entity of interest from DBpedia and the rest of the massive Linked Open Data cloud.

Remember, this boils down to using the browser’s context menu lookup feature to select “go to …” en route to looking up the description of any entity that takes your interest. In the Semantic Web and Linked Data communities, this is described as the follow-your-nose exploration pattern.

Conclusion

In this post I’ve demonstrated how Virtuoso’s JDBC Driver enhances an existing productivity tool (Yellowfin) by injecting Super Keys into SQL Query Results — dispatched over JDBC. In a nutshell, Yellowfin becomes a powerful productivity tool for non-programmers that provides Semantic Web of Linked Data navigation and exploitation.

Most important of all, JDBC, SQL, and Yellowfin are existing technology investments that are being reused in new ways (i.e., exploiting power of a Semantic Web of Linked Data) rather than being victims of expensive “rip and replace” initiatives.

Video Demonstration

Here is an embedded screencast (music background based) demonstration of what’s been covered in this post.

Related

• About Virtuoso Universal Server
• Injecting Super Keys into Microsoft Power BI Reports & Dashboards
• About LOD Cloud Connectivity License for ODBC, JDBC, and ADO.NET — provides connectivity to the massive Linked Data Cloud via our URIBurner Service
• URIBurner Service — free service provides Linked Data Cloud Access and 5-Star Linked Data Extract, Load, and Transformation (ETL) Services
• About OpenLink Data Access Drivers for ODBC, JDBC, ADO.NET, and OLE-DB