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
This is not the case anymore. With TableauFS which runs perfectly on Windows with Docker you can directly index/audit your remote 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:
116 117 118 119 120 121 122 123 124 125 126 127 128 |
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) strTemp = Dir(strFolder & "*.TW*") 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:
88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 |
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 Else ' 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 End If Next ' yes - delete it's folder fso.DeleteFolder (fso.GetFile(txtWorkbook).ParentFolder) End If End If |
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
There are 0 comments