Querying XML files with Tableau via Flexter

Uli Bethke Tableau, XML, XSD

Tutorial - Querying XML files with Tableau

Tableau is a great visualisation tool that works with a large number of different file and data formats. One format that is not supported is data in XML files. There are some workarounds, e.g. using Excel to flatten the XML file. However, with deeply nested files or XMLs with a complex schema this approach fails. Excel won’t work either if you have multiple XML files. For those scenarios or if you don’t own a copy of Excel you can use the free online version of Flexter Data Liberator.

In the tutorial below I will walk you through the process of querying XML files with Tableau step by step.

Sample Data - Clinical Studies from clinicaltrials.gov

We will use some sample data from the  www.clinicaltrials.gov  website. Data exchange standards in health care and life sciences are predominantly based on XML. The clinicaltrials.gov website is maintained by the National Library of Medicine (NLM) at the National Institutes of Health (NIH). Information on ClinicalTrials.gov is provided and updated by the sponsor or principal investigator of the clinical study.

Create target schema and CSVs

In a first step we download the XML schema file (XSD)  from the clinicaltrials.gov website. If you would like find out more about the various download options you can visit the Downloading Content for Analysis page.

We then download the XML files about the clinical studies we are interested in, e.g. trials about the Ebola virus.

https://clinicaltrials.gov/search?term=ebola&resultsxml=true

Once we have downloaded both the XSD and XML files we can process them through Flexter into tab or comma separated files, which is a format that Tableau can consume. Flexter supports many other data formats, e.g. any relational database such as Oracle, SQL Server, MySQL or big data formats such as Parquet.

Let’s first go to the Flexter upload page .

In step one we upload our XSD file.

dsa

In step two we upload our XML files. We also register our account so that we can come back to process other XML files

flexter1

 

Next we activate our account

flexter2

Once you have activated your account by following the link in your welcome e-mail you will be able to browse the source schema

flexter3

Drill into the source schema

flexter4

Browse the optimized target schema

flexter5

Drill into the target schema

flexter6

…and most important of all, download your data for querying in Tableau.

Note: We can see that 21 tables of our target schema were populated with data from our sample trials.

flexter7

Once you have downloaded the data and extracted it to a folder on the PC where you have Tableau installed you can import it. Flexter automatically creates primary and foreign keys as part of the transformation from XML to TSV. Those keys let you join the data.

flexter8

 

flexter9

We first import the main text file clinical_study.tsv, which sits at the root of our schema. For each output file, Flexter auto-generates a primary key and where relevant a foreign key. In this case it creates the primary key PK clinical study.

flexter10

We change the data type of the primary key to String to get around a Tableau limitation when dealing with very large numbers.

flexter11

Next we add two more text files, e.g. collaborator.tsv and authority.tsv. Both data sources contain foreign keys to the clinical_study.tsv file. Both foreign keys were auto-generated by Flexter so that we can join the various output files on common keys.

flexter12

flexter13

Once again we change the data type to String

flexter14

We are now ready to query the data by going to the worksheet

flexter15

We convert each primary and foreign key to a Tableau Dimension

flexter16

flexter17

flexter18

…and define relationships between our text files

flexter19

Select Custom and Click the Add… button

flexter20

We map the primary key of clinical study to the foreign keys in authority and collaborator

flexter21

flexter22

Finally we are ready to commence our analysis.

flexter23

Number of clinical studies by phase

flexter24

Number of Ebola studies by party type

flexter25

Which data formats apart from XML also give you the heebie jeebies and need to be liberated? Please leave a comment below or reach out to us.