Our Tableau Server Deployment Details:
Tableau Server 2023.3.1
5 Windows 2019 Servers (HA env)
No Data Management (i.e. no Tableau Catalog)
No Advanced Management
What I am being asked to do for my Tableau Server deployment:
I work for a company in the financial services sector. My company's governance, risk, and compliance team is mandating that our Tableau Server admin team put in place a process to ensure that data living on the server does not become stale, or fails top get updated beyond a certain timeframe to ensure any customer data is handled in compliance with certain regulations and standards. Apparently there is either an internal record retention standard at my company or a legal statute that requires that certain types of data that lives untouched in the same storage location for differing periods of time "must be treated as a 'record,' and therefore, must follow a certain record retention schedule..." which would require a ton of additional processes and strain on our Tableau Admin team to meet.
Extracted content and content with external files
I have a solid understanding of the postgresql repository. From the repository, I am able to capture details about the age of extracted content and the age of the extracts/hyper files themselves pretty easily. However, I cannot find any details about how or where Tableau Server stores "external files" and how it maintains a link between these files and the content it is tied to, either a published data source or a published workbook.
What do I mean by "external files?"
When a creator publishes a workbook to the server and leaves the box check for Include External Files (see attached screenshot). Note: even if the external file has a "live" connection, it appears that Tableau Server still uptakes a copy of that file (or data and metadata from the file).
How can the Tableau Community help?
I'd like to know:
- How Tableau Server handles workbooks that are published as a packaged workbook along with external files
- I'd also like to know where those files or that data are stored on the server (or how the data is stored i.e. what format)
- I'd also like to know if it is possible identify a workbook published to the server that contains an external file or is linked to an external file
What I have found in my research thus far:
Say I publish a workbook with one live connection to an Excel file, then I publish it to the server and leave the Include External Files box checked, it appears that Tableau Server may not actually store a hard copy of that Excel file on the server. Rather, it behaves as if it converts the data about the excel file into some encoded binary or XML data and likely stores this in the repository (there has to be a key or ID that somehow links the workbook XML to the external file data, but I cannot find it). Then, when a viewer opens up a dashboard form the workbook that uses the external Excel file data, Tableau creates a temporary copy of the actual excel file and places it in a temporary vizql folder that seems to disappear when the viewer's session has ended. The location of the temporary file might look something like this:
E:\Tableau Server\data\tabsvc\temp\vizqlserver_0.20233.23.1227.1336\TableauTemp\374687437\Data\Naleway\my_excel_file.xlsx
I was able to find this file on my server while I had a dashboard open online, but when I checked for it again, the folder and the file were gone.
Tip: I was able to find the file on the server due to the data contained in the repository in workgroup.public.data_connections.keychain for the connection to the excel file (see attached Data Connection Details - Live-Packaged Excel File.png). In that attached image, the top record is for an identical "live" connection in another workbook, however, for that one, I placed the file on an accessible fileshare and "did not check the include external files box." The bottom one corresponds to the connection where I did check the box for include external files.
Any and all help would be greatly appreciated!
Hi @Scott Naleway, the XML for the workbook is stored as a BLOB (Binary Large Objects) inside the PostgreSQL database along with the embedded file. So the complete packaged workbook. The pg_largeobject table in PostgreSQL is a system table that is not specific to Tableau but is part of PostgreSQL's internal structure. It is used to manage large objects (LOBs), such as BLOBs. Also the table stores large objects in chunks.
You can login against Postgres as sysadmin with "tblwgadmin" user (execute tsm configuration get -k pgsql.adminpassword to get the password).
To join from your Workbook down to the BLOB:
You have to join table "public.workbooks" column "data_id" to table "public.repository_data" column "tracking_id", then table "public.repository_data" column "content" to system table "pg_largeobject" column "loid". There is one pageno per binary chunk in "pg_largeobject" so you should be able to reassemble and convert the data (in pageno sequence) or more easily using function "lo_export" on the filesystem if you wanted to.
That should be enough to get you started and I'm sure you will be able to derive a lot in regards to your audit questions.
