Scheduling a Tableau Server Data Source with metadata -calculations, aggregation, comments

N___

Hi all,

I’ve got an issue in getting largish data sets from a SQL Server data warehouse in the UK to an AWS Tableau Server deployment in the US (these can’t easily be moved due to other dependencies). I’ve originally posted with some more details on the Tableau community forums with limited success, but hoping there might be an Alteryx fix.

Analysis

It seems that the bottleneck is the slowness of the standard protocol (TDS over TCP) over this distance – see more detailed notes in other post referenced above. It’s taking 2 hours for 10M rows (4.5GB) and often timing out. We anticipate larger data sources in future, so really want a scalable solution.

My current solve is to get the data out locally using Alteryx (4 mins for extract) and then publish directly using the Publish to Tableau Server Alteryx tool. However, as stated elsewhere, this doesn’t allow the addition of some key metadata for dynamically calculated fields, default formatting, aggregation and comments, hierarchies, re-casting some measures as dimensions (e.g. hour of day), embedded data source filters etc.  I know that I can add this metadata in a tableau workbook, and could even publish this up as a data source with metadata included, but I don’t know a way to nicely automate this.  

Can anyone help? (We have desktop scheduler and may look at Alteryx Server soon).  Is Tabcmd something that could push up a workbook after the Alteryx flow has updated the TDE? All possible solutions welcomed!

Thanks in advance!

N___

For anyone coming to this later, we ended up using a US based snowflake instance, and workflow was:

1. Alteryx run locally in UK to pull down results via ODBC and then write to local csv
2. Using top 1000 rows, write direct to Snowflake via ODBC (limited set because protocol is slow), followed by Delete post-sql command. This resulted in table with dynamically created columns to match source with correct names and data types
3. Alteryx script to call snowsql to: create stage table, push csv to staging, copy from staging into table created in step #2, grant permissions

A slightly improved version would be to only run step 2 the first time this executes, since you lose history on the table if you drop it, so better to just truncate.