Archive

Posts Tagged ‘tableau extract’

Publish Tableau Data Extract directly from Alteryx

December 29th, 2015 No comments

For those Alteryx and Tableau Server users the ability to publish Tableau Data Extract (tde) files directly from Alteryx to the Tableau Server is a huge benefit for report automation. There are a number of good posts already published advising how this should be done, such as this excellent guide from Interworks. In this post I’ll replicate some of what is said in the post, should that link break at some point, and also add to it where I ran into difficulties.

To publish to Tableau Server from Alteryx requires using Tableau’s tabcmd. I expect it’s also possible using the REST API from Tableau but I was unable to work it out, so this post will focus on the tabcmd solution, which, in my opinion, is far simpler. If you’re not a Tableau Server administrator in your role you may need to install tabcmd.

Do the following to set up a workflow to publish directly to Tableau Server from Alteryx, using the workflow configuration Events:

Step 1

Open the Alteryx workflow that creates the tde file.

AlteryxOutputToTde

Step 2

In the Events section of the Workflow – Configuration add a Run Command. Also ensure ‘Enable Events’ is checked.

AlteryxWorkflowEvents

Step 3

Choose “After Run Without Errors”

AlteryxEventRunCommand

Step 4

Enter the location of the tabcmd.exe in the Command box

AlteryxEventTabcmd

Step 5

Enter the command line to execute in the Command Arguments section. I would recommend entering this into Notepad first and copying into the Command Arguments section.

For some unknown reason when I typed directly into Alteryx or into MS Word, copying to Alteryx, when the command was passed into tabcmd by Alteryx, the quotation marks in the command string weren’t being recognised causing it to fail. Entering the command into Notepad and copying it into Alteryx helped me get around that problem.

AlteryxEventCommands

Example tabcmd command line:

publish “\\NetworkLocation\TableauDataToPublish.tde” -s TableauServer -u UserName -p Password -t TableauSite -o -r “Data Sources”

Important things to note about the string:

  • publish = the tabcmd being called
  • -s = tells tabcmd which Tableau Server to use. This doesn’t need to be the “https://TableauSite” URL, it is better as the server name as entered to the Tableau Postgres database
  • -u = the username used to login to the Tableau Server (also needs to be set up on the Tableau Server)
  • -p = the password used to login to the Tableau Server
  • -t = the name of the Tableau Site on the Tableau Server to use
  • -r = the project name to publish to. If this is not included the Default project is used.
  • -o = Overwrite the existing tde file with the same name

There are a number of things that can be specified in the tabcmd command string. The Tableau help documentation has a comprehensive list of those items.

Step 6

Ensure the Timeout (in seconds) is set appropriately to give enough time for the tde file to be published to the Tableau Server

 

Now it’s complete, run the workflow and you should no longer need to manually publish tde files.

Important notes:

  • If using the Alteryx server the Alteryx service account will need setting up on Tableau Server with a Publisher role.
  • If tabcmd fails use the tabcmd.log file to see the error message(s). Alteryx will only tell you it failed, the tabcmd.log will tell you why it has failed. This is copied from the tabcmd overview on the Tableau website advising where to find the tabcmd.log:Status messages and logsWhen a command is successful, tabcmd returns a status code of zero. A full error message for non-zero status codes is printed to stderr. In addition, informative or progress messages may be printed to stdout.A full log named tabcmd.log that includes debugging, progress, and error messages is written to C:\Users\<username>\AppData\Local\Tableau.

 

Alteryx output to Tableau Data Extract tde file

October 6th, 2015 No comments

To output data from Alteryx to Tableau is incredibly simple, Alteryx has the output File Format of a TDE file.

Alteryx Output Data Tool

Just select your data, drag in an Output tool and set the file type to be Tableau Data Extract (tde). Alternatively enter a .tde file name into the ‘Write to File or Database’ input box and it’ll default to a Tableau Data Extract File Format.

If you don’t have a tde file already created, Alteryx will create it for you – although this isn’t 100% clear. Type the name and location you want for your Tableau Data Extract in the ‘Write to File or Database’ location and Alteryx will create and put your tde file in that location.

Alteryx Tde Output Configuration

In the Output Options you can now append to an existing Tableau Data Extract file, meaning you don’t need to recreate the entire tde every time, particularly useful if you have incremental data. If not then go for the Overwrite Existing Extract File (Create if does not Exist) option.

Using Tableau Server groups for row level security

August 4th, 2015 No comments

In an earlier post I demonstrated some options on implementing row level security in Tableau.

When using a data extract, where permissions can be more challenging within Tableau (connecting live means you can implement your permissions in the underlying database, which is far cleaner and simpler), another way to manage permissions is to use the Tableau Server groups and ISMEMBEROF function. This can be used at a row level, as demonstrated below, and server groups can also be used with Tableau Project level permissions. Read more…

2012 Primary Schools Performance in Tableau

February 8th, 2014 Comments off

I wrote an earlier post as part of my Alteryx training using 2012 UK primary school data. As per this post on joining data in Alteryx, using the trade area tool in Alteryx and exporting the data into a TDE file, a Tablea Data Extract, I have now put the data into Tableau. In a couple of years when my son is a little older I’ll have to recreate this with up to date primary school data.

Alteryx and Tableau to display UK schools data

February 6th, 2014 Comments off

As part of my Alteryx training, following on from my starting out with Alteryx, I decided to try and use it for a real world example to test it out. I recently had a baby so have to start thinking about schools. Luckily the UK government make school performance data public and the excellent Guardian Datablog have tidied it up for me (the data is for 2012, hence now out of date, but good enough for my training in Alteryx). The data contains school address details, local authority details, school size, school religion and multiple measures of performance.

I would like to know which schools are performing well – i.e. their pupils have high attainment in their exams – and then see which streets are within X miles to help guide me which streets I would need to live to get my son into a chosen school. Alteryx has an in built function called Trade Area where it can map show you a chose radius from a point, which is ideal for the schools approximated catchment area analysis.

Radius of 0.3 miles around each school

Radius of 0.3 miles around each school

Each purple dot is a school with the cirle around the dot being the approximate catchment area, although looking at this it appears the catchment area in London will, in reality, be less than 0.3 miles. Read more…

Tableau Extracts are Slow to Refresh on the Server

July 19th, 2012 1 comment

On many occasions I have found Tableau data extracts are slow to refresh on the Tableau Server when pulling from SQL (and I’m sure this happens with all data connections). I always used to put this down to the slow execution of the query, which could definitely be a cause as if it’s slow to run on the SQL Server it will be slow to run when refreshing an extract. It also appears that Tableau can be the cause of the slow updating in some cirucmstances. I have just come across this write up on extract performance by one of the Tableau staff which they put on their forums. I think it is very useful so I’ve copied it into this article.

Basically it advises what to check to see what the cause is of the slow updating of extracts and advises what to do if the blame is on the Tableau side. Read more…

Tableau 6 Data Blending With Dates

May 10th, 2012 10 comments

I’ve just had a frustrating hour trying to work out why my attempts to blend data between an Excel file and a data extract wasn’t working. I was trying to blend on 2 fields – a text field called Metric, with the same field name in both datasets, and a date field called Date in the spreadsheet and InvoiceDate in the extract.

Tableau was clever enough to join the Metric fields automatically but I had problems joining the Date fields. I thought it would be a simple telling Tableau to join where Date = InvoiceDate but unfortunately there were a few more steps involved. Read more…

Info About Extracts on Tableau Server 6

May 3rd, 2012 No comments

My current employer is still using Tableau Server 6.1 – or 6.1.6 to be precise. I’ve been trying to work out how the extracts work when pulling from a SQL Server so the same extract can be shared by multiple dashboards. The reason for this is that some of my extracts take a long time to update which is not ideal when the same extract is used in multiple dashboards and instead of refreshing it multiple times for each dashboard it would be more efficient to update just once and all dashboards using it will be updated together.

What I was wondering was if when you connect to a data extract in Tableau is whether the published extract is updated when any dashboard based on this extract is updated. It seems this isn’t the case. Once the extract is published it remains as it was a publication time for all time I believe. It appears that when you connect to that extract and publish the workbook Tableau actually duplicates the extract and updates this duplicate whenever the dashboard is updated. In other words the extract published individually to the server doesn’t alter but the duplicate Tableau made embedded in the dashboard does update. Read more…

Tableau be careful using an Incremental Refresh

April 20th, 2012 No comments

Perhaps the title of this post is a little extreme but I have spent some time trying to speed up the refreshing of my Tableau extracts recently and began to use incremental extract refreshes where I thought it was appropriate – for example with order data just adding yesterdays orders each day.

On closer inspection I noticed that with the data warehouse I’m using the way it’s designed is to run updates on existing records rather than inserting a new record to reflect a change in the underlying data. I was reading an article on the Tableau site about Optimising Incremental Refreshes and noticed a warning towards the end of the article saying “Updates to existing data and deletions are only included in full refreshes

In other words if you’re using a data source where the data can alter historically and your Tableau report needs to match these changes to the underlying data then a Full Refresh must be used. The incremental refresh would miss the alterations and only insert new records.

Make sure the data source is checked to be sure that none of the data can change historically OR if it can change historically check these changes don’t have to be reflected in the reporting.

Note Tableau allows an extract to be refreshed both Incrementally and Fully so dependent on the requirements it could be possible to run daily incremental updates during the week and carry out full extracts on the weekend to capture historic changes, for example.