Connecting Virtuoso with Tableau

[ekim9]

Connected Virtuoso to Tableau through ODBC - when I pull data into Tableau through a custom SQL query, some of the data contains characters that resemble a diamond with a question inside of it. When I use the interactive SQL function in the Virtuoso conductor, it does not have any issues with displaying the data correctly. Is there an add-in or configuration that I am missing?

Thanks

[hwilliams]

What OS are you running Tableau on ie Windows, macOS, Linux ?

What are the parameters set for the ODBC DSN being used which can be seen from the “odbc.ini” file (or registry on Windows) or provided via screenshot ?

What are the actual character being displayed incorrectly in Tableau, which you should be able to see from the correct data being returned when querying via the interactive SQL function in the Virtuoso conductor ?

Depending on the client OS being used we provide a sample ODBC application as detailed in the installation documentation ie cppdemo on Windows and iODBCDemo on macOS (found in /Applications/iODBC/ ) that can be used to make a test ODBC connection. If you make an ODBC connection with the same DSN and run the same query is the data returned correctly or with the indicated diamond chars seen in Tableau ?

What is the version, build date & gitid for the Virtuoso database server being used, which can be obtained by running virtuoso-t -? for open source and virtuoso-iodbc-t -? for commercial ?

[ekim9]

I am running Tableau 2019.4 on macOS Catalina. This was my first time working with Virtuoso so I used one of the default user account logins. I attempted to set up an ODBC connection using a demo DSN I configured and also with the Local Virtuoso DSN. Using the “DB” database in Local Virtuoso, I used a custom SQL query to retrieve the data from DBpedia. The picture below shows some of the rows with the diamond characters with some of the rows displaying normally.

[hwilliams]

You indicate querying data from DBpedia which is also implied from the query results in your screenshot. Thus I assume you are executing a SPARQL-Federated query to query such RDF data from a remote SPARQL endpoint ? Either way can you please provide the custom SQL query being executed in Tableau.

I download Tableau 2019.4 for macOS and was able to successfully run a SPARQL-Fed query against DBpedia:

So need to see specifically what query you are executing and I should be able to do the same …

[ekim9]

This is the query that I am running:

Thank you so much for your support on this matter!

[hwilliams]

Why would you provide a screenshot of a query rather than the text of it, such that it can just be copied & pasted ???

[ekim9]

My apologies, I tried to copy and paste the query as text, but the query contains multiple URLs and the forum would not let me include more than 2 links in a post since I am a new user, which is why I sent it in the form of a screenshot instead. If there is an alternative way for me to upload the query as a text here please let me know!

[hwilliams]

You can use verbatim tags to enclose text as is, like:

sparql SELECT *
WHERE {
    SERVICE <http://dbpedia.org/sparql>
    {
      select *
      where
      {
        ?s ?p ?o .
        ?o <bif:contains> "Bennington"
      }
      limit 10
    }
}

[hwilliams]

I have manually written the text of the query from your screenshot:

SELECT DISTINCT
  DBpedia.name  AS "Actor",
  DBpedia.actor  AS "Actor ID",
  DBpedia.school  AS "School ID",
  DBpedia.schoolLabel  AS "School"
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

and do indeed see the problem you report using the Local Virtuoso DSN created for the driver, which uses the Virtuoso default ANSI ODBC Driver. But as your query results contain Unicode data you should be using the available OpenLink Virtuoso ODBC Driver (Unicode) ie create an ODBC DSN with that driver with the iODBC Administrator to connect to your Virtuoso instance and Tableau will does then display the results correctly, see:

[ekim9]

This resolved the issue; thank you so much for your assistance!