If you never heard about this powerful and extremely useful tool by Andy Cotgreave then you missed something serious. It makes life easier with auditing all your tableau workbooks on your computer, getting information like which workbook connects to where, what are the used tables and fields, calculations – things what we desperately need during our daily professional life. This is an example what can you do with it: Here’s a link to a published version of the Viz workbook. However, it missed one important function:
Audit workbooks directly on the server
So, let’s get it started
Firstly get the latest version of TWB Auditor from (http://community.tableau.com/thread/118450) and mount your Tableau Server as window share. For more information about mounted drives of Tableau Server please check my previous note here: https://databoss.starschema.net/tableaufs-on-windows-with-docker/ Mount it as a drive and check if you can access the files.
Adding stuff to make it “TableauFS compliant”
Due to two things (first is file naming differences on local, windows file systems vs network file systems and second is read only file system adds read only flag for all files) you need slightly change Andy’s tool. No worries, few lines only.
You should change the file mask in FileList module:
Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant
Dim strSQL As String
'Add the files to the folder.
strFolder = TrailingSlash(strFolder)
Do While strTemp <> vbNullString
gCount = gCount + 1
SysCmd acSysCmdSetStatus, gCount
strSQL = "INSERT INTO FilesToAudit " _
& " (FName, FPath, Extension) " _
The DeleteUnpackagedWorkbook function was unable to delete temp files from my temp folder(c:\usres…\temp\..). It is caused by that, on TableuFS shared drive every file is read only. So when the files were unzipped by the TWB Auditor was also read only. I have inserted a short code to remove read-only attribute from files if there is any:
Public Function DeleteUnpackagedWorkbook(txtWorkbook As String) As Boolean
Dim fso As FileSystemObject
' set up some variables
On Error GoTo DeleteUnpackagedWorkbook_Error
Set fso = New FileSystemObject
If g_bDidNotUnpackagedThisTWBX Then
' Don't need to delete anything because we used the unpackaged version of the workbook
DeleteUnpackagedWorkbook = True
' does the TWB exist
If fso.FileExists(txtWorkbook) Then
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(fso.GetFile(textWorkbook).ParentFolder)
Set colFiles = objFolder.Files
For Each objFile In colFiles
If objFile.Attributes And ReadOnly Then
objFile.Attributes = objFile.Attributes Xor ReadOnly
' yes - delete it's folder
Just change/add the highlighted lines and move forward.
How to use the TWB Auditor
After downloading & unzipping TWB Auditor, open the TWB Auditor - data.accdb Please note that you will need an MS Access to use it, obviously.
Here you can easily start with a double click the audit of your workbooks, with the “Audit all workbooks in a folder”. Then select the proper option for audit:
As you can see I would like to search for TWBX files (check the “include packaged workbooks”) on my Z:\ shared drive where my Tableau Server was previously mounted.
After the audit is completed you will see the results of the audit, which workbooks has been audited, and what is the workbook path. After the run is completed, just close TWB Auditor.
Using the results
Open the TWB auditor - viz.twb workbook which is located where you have unzipped the Twb Auditor. Please do read the TWB Auditor introduction worksheet.
And refresh all the data sources. (Data / Refresh all extracts)
On “What’s in my workbooks” will provide you information about each workbook and their worksheets.
On other sheet you can check the connection type of the workbooks:
Or you can easily check “Where is that field used” :
One word: lovely.
Rerunning the audit
Just one more important thing: if you need to refresh the audit database and you don’t want to accumulate your audit history, just hit “Delete all data from TWB Auditor” before the next run.
That’s it, simple and easy (sometimes even fast). In case if you have any questions or issues just drop a comment.
- Tableau Extensions Addons Introduction: Synchronized Scrollbars - December 2, 2019
- Tableau External Services API: Adding Haskell Expressions as Calculations - November 20, 2019
- Scaling out Tableau Extracts – Building a distributed, multi-node MPP Hyper Cluster - August 11, 2019