There are a few options here, but none of them are great
1. If you have the original TDS files from when you published the Data source, you can use Tableau Desktop to edit them, point at new DB, and republish.
2. If you have the TDS files, you can edit them (they are just XML), and make the changes, and publish using TABCMD.
3. If you don't have the TDS Files, you can download them from Tableau Server (as TDSX files), make the changes inTableau Desktop and republish.
You can't directly edit the TDSX file downloaded from Tableau Server, so to get a TDS file out of Tableau Server (that you can edit) you can do the following.
i. Ensure that you have readonly access to the Repository by using tabadmin dbpass -u readonly <password>
ii. using a command prompt, change to the C:\Program Files\Tableau\Tableau Server\9.3\pgsql\bin>
iii. Run the following command to get a list of all Object IDs for Data sources.
psql -h localhost -p 8060 -U readonly -d workgroup -c "select content,name from repository_data";
(You might find a better way of filtering just for Data sources, as this will give you all the views aswell.
iv. For Each Object ID (content field) you run the following
psql -h localhost -p 8060 -U readonly -d workgroup -c "\lo_export <OID> 'D://tableau//<filename>.tds'";
And Voila you have a TDS files
v. Edit the TDS, make DB name changes, and publish using TABCMD
Rinse and repeat for each of the Data Sources
Hope this helps
Thanks, Glen, but that doesn't give me what I'm looking for. I'm looking for a way to do it without republishing.
With over 300 data sources (and their attached visuals), I'm trying to avoid the massive amount of manual time that will take. All I need is to point the connection to a new location. Everything else is the same.
It surprises me that I can't update both the server name and database name through the web interface.
I dont think that it is possible to update without re-publishing. (I agree that it would be great to be able to do so)
The best that you will get (I think) is to script it.
ie create a query in postgres to list out all the Datasources, run an export to download the TDS files, update the Database name in the TDS file, and publish using TABCMD.
Maybe one way to narrow down the list is looking at what items have a tds. Since public.repository_data.content = public.datasources.repository_data_id you could just look for non-NULL entries in public.datasources.repository_data_id and use those in Glen's example query as part of the automated process*.
* Which you should share if you end up successfully doing it
I will be happy to share my final solution.
Since about 95% of our data sources have an attached TDS, finding which ones need updating isn't really the challenge.
With that said, your suggestion is helpful.
Update: I've been playing with Glen's suggestion. I finally got a file to download (. The file will be saved as a .tds but it's really a .zip file so change the file extension of the downloaded file from .tds to .zip and then you can open the unzipped file in a text editor or via scripting Or alter Glen's script to:
psql -h localhost -p 8060 -U readonly -d workgroup -c "\lo_export <obj_id> 'D://temp//testing.zip'";
Next, this fails:
Select * from public.repository_data, public.datasources WHERE public.repository_data.content = public.datasources.repository_data_id
Looks like the data types are different!
Thats curious. When I run the export command, the file definitely comes out as a tds file (which can be opened straight away in Tableau Desktop)
Am running this on 9.3.5
Hmm, this probably isn't correct or else needs further definition. On two table checks it looked like the IDs matched but others I'm finding to not match. Weird. So I think I spoke too soon about public.repository_data.content = public.datasources.repository_data_id
1 of 1 people found this helpful
If you connect to the database with the readonly account and run this:
FROM datasources ds
INNER JOIN data_connections dc ON ds.id = dc.owner_id
INNER JOIN projects p ON ds.project_id = p.id
WHERE p.name = '<insert project name here>'
AND dc.dbclass = 'sqlserver'
ORDER BY ds.repository_url,
You'll see that each datasource (ds) has a db_class and a db_name.
Each data_connection (dc) has a server, dbname and keychain. Each of those fields contains information about the actual location of the data for the datasource.
Those are the places that I need to change and that need to point to the new database locations.
Again, the goal is to avoid doing it RBAR (or in this case DataSourceByAgonizingDataSsource)...especially because nothing is changing except the location of the data.
I'm going to try something naughty and I'll let you know if it is successful.
I'm thankful for a restored backup on an isolated machine...
Okay, I got it working:
public.datasources.repository_data_id = public.repository_data.id
public.repository_data.content is then the <OID> to use in Glen's command.
So here's the modified SQL to get the repository_data.content needed for getting the OID (returned as the first column):
SELECT rd.content AS "OID", dc.* FROM datasources ds INNER JOIN data_connections dc ON ds.id = dc.owner_id INNER JOIN projects p ON ds.project_id = p.id INNER JOIN repository_data rd ON ds.repository_data_id = rd.id WHERE p.name = 'Default' AND dc.dbclass = 'sqlserver' ORDER BY ds.repository_url, ds.name
< edit > FYI for anyone coming into the conversation, change the project name in line 6 above to match which ever project you wish to pull from or just remove that piece of the WHERE clause if you want all .tds files.
2 of 2 people found this helpful
I have successfully performed what will definitely be an unsupported hack, but which has resulted in exactly what I need.
A refresher of the background:
- We have around 300 data sources, all connected to SQL Server, each connected to state-specific views in a single database. They are all also connected to extracted data.
- We are splitting our single database into a new SQL instance, one database per state. We're doing this to increase performance and decrease development bottlenecks. (Don't get me started on table partitions or snapshots, we can't afford SQL Enterprise.)
- Every state database is the exact same structure as the original, single database (which means we have no metadata changes to worry about). Each state database contains only that state's data.
- We need every data source in Tableau server to point to the new instance and database name
Things we don't want:
- We don't want the data_repository to change, because that data is the same in both the old and the new databases. I only want to update the connection for when the next extract update happens.
- We don't want to re-extract and republish every datasource, because that would literally take us days, because of the volume of data we're handling.
I directly updated the references to the connection in the datasources and data_connections tables in the workgroup database. I then ran an extract refresh from the server web UI, and it successfully pulled the data from the new location.
I'm hesitant to put my actual solution here, but am happy to share with those who are interested.Toby Erkson