1 2 3 Previous Next 88 Replies Latest reply on Feb 26, 2020 1:32 PM by brad.earle.1

    What's in your workbooks? The TWB Auditor

    Andy Cotgreave

      TWB Auditor - summary screen.gif

      Do you ever need to audit all your workbooks?

      Are you about to delete a table from your data warehouse, and don't know which workbooks will be effected?

      Have you got a workbook with so many sheets you've lost track of how it's made?

      Do you want to track the version history of a workbook?


      If you answer yes to any of those, then the TWB Auditor is for you. This is a tool I first developed back when I was at Oxford University and our data warehouse was extremely volatile. I've dusted it down, spruced it up and now can release it to you all as an unsupported community tool.


      [update Sep 2015: if you want to download workbooks from Server, check out this newer version by toby.erkson What's in your workbooks? The TWB Auditor v2.0]


      Ok, enough disclaimers, what does this app do? It goes through all your TWB and TWBX files and analyses their contents.


      Why would it want do that? I can see a few use cases:

      1. You need to change one of your key databases. Maybe you need to delete it. Or amend some key fields. Oh **** – which workbooks is that change going to effect? You need to find, amongst all your workbooks, which ones use that connection, or that particular column. You need to use the “Where’s that field used?” dashboard.
      2. You’ve finally migrated everything from that crappy excel file into a proper database. So which workbooks do I need to go back to and update with the new shiny connection? You need the “What’s connecting to which data?” dashboard.
      3. You’ve got 100s of workbooks, right? Of course you have. Everyone ends up with 100s of workbooks. Which are getting unwieldy? You can check how many worksheets are in which workbook using the “What’s in my workbooks?” dashboard.
      4. Or maybe you’re just curious to know which fields you use most? (I’ve used [Sales] over 1,000 times!)


      Sounds good. How does it work? Glad you asked:

      • Download and unzip the attached file
      • Open up the Access database (TWB Auditor - data) first.
      • Choose the relevant task (hint: it’s “Audit all workbooks in a folder”)
      • Navigate to a folder containing some workbooks. Click OK.
      • Choose your options:
        TWB Auditor - audit screen.gif
        • Include subfolders: this will trawl all the folders (600 TWB/TWBX files takes about 5 minutes)
        • Clear log table: there’s a log table. You can clear it out first
        • Include Packaged workbooks: TWBX files can also be audited. They get unzipped to a temp folder and deleted before the process finishes.
        • Stop execution on errors: if this is ticked, you’ll get a message box every time the auditor has a problem
      • Click Go. The TWB Auditor will count how many workbooks it will audit.
      • Go have a cup of tea.
      • TWB Auditor will show you the results when it’s finished. If any files don’t audit (they aren’t ticked) could you send them to me and I’ll work out why
      • Now close the Access file
      • Open up the TWB file (TWB auditor - viz) that was part of the package.
      • VERY IMPORTANT: regenerate the extracts first, and then save the TWB file
      • Enjoy - explore the dashboards


      The TWB Auditor is full of data I don't need anymore. Simple - just select the "Delete all data..." task.TWB Auditor - task list.gif


      I want to track version history of TWB files. If you run the TWB Auditor on a folder and then come back a week later and run it again, it will log the new version of the TWB file. If the file has not changed, it won't be logged again. There are no dashboards or access queries to spefically support version history, but the data's there so you can build them.


      System requirements: Windows, Microsoft Access. I've tested this on Windows 32 and 64 bit machines with no problems.


      I don’t have Microsoft Access on my machine. Ah. Sorry. My programming skills only go so far as VB. If you want to port the code to some other platform, please do! I'll buy you a beer.


      What happens if it gives me an error message? Oh pants. I’ve tried to make it work seamlessly. Let me know where it falls over. Post a screenshot, description, and the TWB file that caused the problem (just the TWB - we don't need your data). It doesn't edit your TWB files. It does unzip TWBX files to a temp folder (and then deletes them again). But remember, this is not a Tableau-supported tool. I hope it works for you and helps you out, but don't approach us for support. Think of this as a Community tool.


      This app's seriously cool. Why, thanks.


      But…? There’s a but?


      Yes, I wish it did x and y and z If you have ideas or issues make some comments below. I don’t have much time to work on this but will do what I can.


      I think I can do better. Cool - you are awesome. Feel free to edit and adapt the source code and the dashboards. If you make some cool vizzes or improvements, post them to this thread.


      Help! I need technical support! Did I say already that this is a community tool. It is not supported by Tableau. Post a message here, but I cannot promise to reply.


      Can I see a demo? Sure. Here's a link to a published version of the Viz workbook


      [update Sep 2015: if you want to download workbooks from Server, check out this newer version by toby.erkson What's in your workbooks? The TWB Auditor v2.0]

        1 2 3 Previous Next