Category: Tableau

Tableau — Who Deleted That Workbook?!?

While Tableau doesn’t have anything nice like a ‘dumpster’ from which you can restore a deleted workbook, it does at least keep tables for historic events like workbook deletion. The following query finds records where a workbook with FOOBAR in its name was deleted. It lists all of the event info as well as info on the user who deleted it. Near as I can tell, the “created” date for the historical_events table is the date the workbook was deleted (from my restore, I know the workbook itself was created last year!)

SELECT historical_events.*, hist_workbooks.*, hist_users.*
FROM historical_events
left outer join historical_event_types on historical_event_types.type_id = historical_events.historical_event_type_id 
left outer join hist_workbooks on hist_workbooks.id = historical_events.hist_workbook_id 
left outer join hist_users on hist_users.id = historical_events.hist_actor_user_id 
WHERE historical_event_types.name = 'Delete Workbook'
and hist_workbooks.name like '%FOOBAR%'
;

Tableau Error After Upgrading to 2023.3

I started upgrading our Tableau servers to 2023.3 this week. Several dashboards no longer rendered after the upgrade — throwing an error “TableauException: Incorrect data type real, getting expected integer type.” … resetting or not resetting the view did not help.

This is evidently a known issue (although the documentation prior to my reporting the issue seemed to go out of its way to say it is just the cloud platform being impacted)

https://issues.salesforce.com/issue/a028c00000iwtkXAAQ/~
https://kb.tableau.com/articles/Issue/error-incorrect-data-type-real-getting-expected-integer-type-occurs-intermittently-during-view-rendering

Both the server and desktop client are impacted — and, unlike their documentation that says it is intermittent? Not all workbooks are impacted, but the ones that are? A broken workbook is broken and will not render for anyone, anywhere, any time.

There is a workaround:

Server:
tsm configuration set -k features.EnableLogicalQueryBatchProcessor -v false
tsm pending-changes apply

Desktop:
“\Program Files\Tableau\Tableau 2023.3\bin\tableau.exe” -DOverride=EnableLogicalQueryBatchProcessor:off

Tableau: Upgrading from 2022.3.x to 2023.3.0

A.K.A. I upgraded and now my site has no content?!? Attempting to test the upgrade to 2023.3.0 in our development environment, the site was absolutely empty after the upgrade completed. No errors, nothing indicating something went wrong. Just nothing in the web page where I would expect to see data sources, workbooks, etc. The database still had a lot of ‘stuff’, the disk still had hundreds of gigs of ‘stuff’. But nothing showed up. I have experienced this problem starting with 2022.3.5 or 2022.3.11 and upgrading to 2023.3.0. I could upgrade to 2023.1.x and still have site content.

I wasn’t doing anything peculiar during the upgrade:

  • Run TableauServerTabcmd-64bit-2023-3-0.exe to upgrade the CLI
  • Run TableauServer-64bit-2023-3-0.exe to upgrade the Tableau binaries
  • Once installation completes, run open a ​new​ command prompt with ​Run as Administrator and launch “.\Tableau\Tableau Server\packages\scripts.20233.23.1017.0948\upgrade-tsm.cmd” –username username

The upgrade-tsm batch upgrades all of the components and database content. At this point, the server will be stopped. Start it. Verify everything looks OK – site is online, SSL is right, I can log in. Check out the site data … it’s not there!

Reportedly this is a known bug that only impacts systems that have been restored from backup. Since all of my servers were moved from Windows 2012 to Windows 2019 by backing up and restoring the Tableau sites … that’d be all of ’em! Fortunately, it is easy enough to make the data visible again. Run tsm maintenance reindex-search to recreate the search index. Refresh the user site, and there will be workbooks, data, jobs, and all sorts of things.

If reindexing does not sort the problem, tsm maintenance reset-searchserver should do it. The search reindex sorted me, though.

Tableau: Workbooks and Views Created or Modified By a Specific Individual

I had a manager looking to locate a ‘something in Tableau’ that was created by a specific individual — in this case, it was a terminated employee so “just ask the person” was not a viable solution. I put together a query to list all workbooks owned by or modified by an individual:

SELECT w.id, w.name, w.description, w.owner_id, w.modified_by_user_id, owner_system_users.email AS owner_email, modified_system_users.email AS modifier_email
     FROM  public.workbooks AS w
      LEFT OUTER JOIN public.users AS owner_users on w.owner_id = owner_users.id
      LEFT OUTER JOIN public.users AS modified_users ON w.owner_id = modified_users.id
      LEFT OUTER JOIN public.system_users AS owner_system_users ON owner_system_users.id = owner_users.system_user_id
		LEFT OUTER JOIN public.system_users AS modified_system_users ON modified_system_users.id = modified_users.system_user_id
      WHERE owner_system_users.name = 'UserLogonID';
--      WHERE owner_system_users.email LIKE '%Smith%' OR modified_system_users.email = '%Smith%'
		;

As well as a query to identify all views owned by an individual:

SELECT views.*, owner_system_users.email AS owner_email
     FROM  public.views 
      LEFT OUTER JOIN public.users AS owner_users on views.owner_id = owner_users.id
      LEFT OUTER JOIN public.system_users AS owner_system_users ON owner_system_users.id = owner_users.system_user_id

      WHERE owner_system_users.name = 'UserLogonID';
--      WHERE owner_system_users.email LIKE '%Smith%' OR modified_system_users.email = '%Smith%'
		;

The email address based search is most reasonable — our email addresses are algorithmically based on our names, so we always know what the address would have been. Many contractors, however, don’t have Office 365 licenses or mailboxes … so I have to fall back to finding their logon ID in those cases.

Tableau – Data Source Connection Info and Workbooks

I think I finally have a query that links workbooks where data sources are used and the connection information from the data_connections table!

-- Query to find all data sources and where they are used
select system_users.email
, datasources.id, datasources.name, datasources.created_at, datasources.updated_at, datasources.db_class, datasources.db_name, datasources.site_id
, data_connections.server, data_connections.dbclass
, sites.name as SiteName, projects.name as ProjectName, workbooks.name as WorkbookName
from datasources
left outer join data_connections on data_connections.datasource_id = datasources.id
left outer join users on users.id = datasources.owner_id
left outer join system_users on users.system_user_id = system_users.id
left outer join sites on datasources.site_id = sites.id
left outer join projects on datasources.project_id = projects.id
left outer join workbooks on datasources.parent_workbook_id = workbooks.id
order by datasources.name
;

Tableau Upgrade Failure

Attempting to upgrade Tableau from 2021.something to 2022.3.5 repeatedly failed with the following error in the upgrade log:

Failed with the error "Detected the old version of Tableau Server takes precedence on the system PATH".

 

And there were all sorts of things online to try — most of which involved changing the environment variables Tableau sets. But they weren’t wrong … or, rather, it was impossible to tell which was “right”: the new version I was trying to install or the old version that was still running. On a whim, I thought I’d try an admin-level command prompt. Opening the command prompt with the “Run as administrator” option allowed me to upgrade the server.

Except … I upgraded another server the next night. I knew to launch the command prompt in administrator mode, but I was incredibly dismayed to encounter the exact same error. Even using an admin level command prompt. Then it occurred to me — Windows evaluates the environment variables when you launch the shell (in fairness, every Unix/Linux variant I’ve encountered does the same thing). The installer must be changing the Tableau environment variables. If you use a command prompt that was open prior to running the installer … you don’t have the new values. You have the old ones — so the environment variable, as seen by the upgrade script, is pointing to an older version of Tableau. Launch a new command prompt, re-evaluate the environment variables, and the script now sees the proper version. Hopefully my next upgrade won’t include a panic inducing “yeah, that’s not gonna happen” error!

Tableau PostgreSQL Query: Finding All Datasources of Name or Type

I frequently need to find details on a data source based on its name and find all data sources of a particular type. Particularly, the Microsoft Graph permissions required to use Sharepoint and OneDrive data within Tableau changed — I needed to reach out to individuals who use those data types to build a business case for the Security organization to approve the new permissions be added to our tenant. 

-- Query to find all data sources of a specific type or name 
select system_users.email, datasources.id, datasources.name, datasources.created_at, datasources.updated_at, datasources.db_class, datasources.db_name
, datasources.site_id, sites.name as SiteName, projects.name as ProjectName, workbooks.name as WorkbookName
from datasources
left outer join users on users.id = datasources.owner_id
left outer join system_users on users.system_user_id = system_users.id
left outer join sites on datasources.site_id = sites.id
left outer join projects on datasources.project_id = projects.id
left outer join workbooks on datasources.parent_workbook_id = workbooks.id
-- where datasources.name like '%Sheet1 (LJR Sample%'
where datasources.db_class = 'onedrive'
order by datasources.name
;

Tableau PostgreSQL Query: Stats About Data Source Types

I wanted to report on the different types of data sources used in our Tableau instance — as well as show how many of each type are in use. 

-- Query to found how many of each data source type
select datasources.db_class, count(datasources.db_class) as count
from datasources
left outer join users on users.id = datasources.owner_id
left outer join system_users on users.system_user_id = system_users.id
left outer join sites on datasources.site_id = sites.id
left outer join projects on datasources.project_id = projects.id
left outer join workbooks on datasources.parent_workbook_id = workbooks.id
group by datasources.db_class 
order by count desc ;

Answer: About half of them are Oracle!

Tableau Query — Data Sources and the Workbooks Where They Are Used

I have found Tableau’s views of data sources to be … lacking. To provide a report of data sources, the database type, and where it is being used, I put together a query that locates all data sources (or filters by database type — specifically, I was trying to see who was using Snowflake) and lists the site, project, and workbook using the data source. 

-- Data sources and what workbook they are used in
SELECT system_users.email , datasources.id, datasources.name, datasources.created_at,  datasources.updated_at, datasources.db_class, datasources.db_name
, datasources.site_id, sites.name AS SiteName, projects.name AS ProjectName, workbooks.name AS WorkbookName
FROM datasources 
LEFT OUTER JOIN users ON users.id = datasources.owner_id
LEFT OUTER JOIN system_users ON users.system_user_id = system_users.id
LEFT OUTER JOIN sites ON datasources.site_id = sites.id
LEFT OUTER JOIN projects ON datasources.project_id = projects.id
LEFT OUTER JOIN workbooks ON datasources.parent_workbook_id = workbooks.id
-- WHERE datasources.db_class = 'snowflake' 
ORDER BY datasources.created_at;