Thursday, May 6, 2010

How to change the data source in a workbook within Tableau Software

In the tableau forum I was trying to find a solution for making Tableau workbooks point to a different data source without having to create all the sheets. Without success, so I started hacking myself. With success.

Here's how you can do it.

First, make sure you have a good editor, such as notepad++.
Now open the workbook with your editor. It's an XML file, so in notepad++ you want to select Language, XML to view the file in that language.

Collapsing all root elements should give you a view that looks like this:

Notice how the basic structure consists of Preferences, Data Sources, Worksheets, WIndows and Thumbnails. (my workbook did not have a Dashboard yet, otherwise you should have seen that too).

If you expand the DataSources element, you'll see an element for each data source:
My workbooks has 7 data sources that all connect to a PostgreSQL backend.

Let's take a look at the first data source named 'namelookup_cachepool'.
If you expand the element you will see an element named .

Here's how mine was specified (I had to remove the '<' and '>'symbols to make it display on my blog):

'      connection class='postgres' dbname='d2dna' port='5432' server='d2host' username=''
'        relation name='TableauSQL' type='text'select server, ts, value from ibmsur0001_dwh.statrep_entry
'      /connection

Basically, this data source has two dimensions (servers and ts), and measures stored in a column named 'value'.

I created a Tableau Extract file from my data source to Postgres, and created a new workbook that now connects to this extract file. The connection element in the workbook file now looks different from my first example:  

'    datasource inline='true' name='NAMELOOKUPCACHEPOOL Extract' version='7.7'
'      connection class='firebird' dbname='C:\Documents and Settings\wouter\My Documents\My Tableau Repository\Datasources\NAMELOOKUPCACHEPOOL.TDE' tablename='TableauExtract' username='SYSDBA'
'        relation join='inner' type='join'
'          clause type='join'
'            expression op='='
'              expression op='[TableauExtract].[fk:ts:0]'
'              /expression
'              expression op='[ts].[pk:ts:0]'
'              /expression
'            /expression
'          /clause
'          relation join='inner' type='join'
'            clause type='join'
'              expression op='='
'                expression op='[TableauExtract].[fk:server:0]'
'                /expression
'                expression op='[server].[pk:server:0]'
'                /expression
'              /expression
'            /clause
'            relation name='TableauExtract' table='[TableauExtract]' type='table' /
'            relation name='server' table='[server]' type='table' /
'          /relation
'          relation name='ts' table='[ts]' type='table' /
'        /relation
'        calculations
'          calculation column='[Number of Records]' formula='1' /
'        /calculations
'      /connection

Notice how the two dimensions (server and ts) are defined.

I did some testing by replacing everything in the element. It seems to work, as long as you make it point to data of the exact same format in terms of columns and data types.

Over the next couple of days, I will be posting more on this topic.

1 comment:

I like interaction, thank you!

Note: Only a member of this blog may post a comment.