3 Replies Latest reply on Feb 16, 2017 7:17 PM by Mark Gemmell

    Maintaining common and bespoke fields across multiple reports

    Mark Gemmell

      I'm attempting to have a 'single version of the truth' for a dozen reports which share many fields in common (revenue, user count, etc), but also each contains many fields unique to itself. 

       

      Aims:

       

      • Ensuring metrics used in multiple places are calculated the same way.
      • Bug fixes/changes to core calculated fields propagate through all reports
      • As new reports are developed and calculated fields are created, those fields become available to any report.
      • Fields have preset default properties (colors, number formats, aliases, etc) when creating new reports.
      • The common set of fields are found in standard folders in each report.
      • Bespoke fields for each report do not contribute to extract size or workbook clutter of other workbooks
      • Calculated fields don't have to be created twice, duplicated or copy/pasted (as doing so loses default properties....sigh).

       

       

      Design:

       

      Setup:

      • One datasource published on Tableau Server with all fields for all workbooks, each with correct default settings. "Template Datasource"
      • Fields unique to any one report are marked hidden. [are hidden fields a property of datasources or just workbooks?]
      • A "Template Report" published on Tableau Server with 12 sheets showing the use of most of the standard fields.

       

      New Reports come about in this way

      • Download the "Template Report".
      • Hide all the standard sheets that aren't helpful.
      • Save Datasource as Local Copy.
      • Replace reference to original datasource with new local copy and close original.
      • Create an extract. [to make it quicker while creating the report]
      • New fields would be created, formatted etc as the report is developed.
      • Existing fields may get edits.  New fields may get added to existing folders.
      • Remove Extract  [this shouldn't cause problems, as formats/defaults and folder layout aren't tied to the extract itself, right?]
      • Publish local datasource as "Template Datasource", over-writing the original and elect to update the workbook to use the published datasource.
      • Hide all unused fields
      • Create new extract  [Hidden fields will be excluded from extract?]
      • Published report as "Report X".  [Extract will exist for Report X only with the fields it uses, but published datasource will not have an extract and will contain all fields.]

       

      Now to do house-keeping on Template report/datasource (effectively re-create Template report from current state).

      • Remove extract.
      • Create Local Copy of datasource  [so we can hide fields, no edits possible with published datasources, including show/hide fields?]
      • Unhide all fields.
      • Delete all sheets only relevant to "Report X"
      • Unhide original template sheets
      • Hide fields only relevant to any one report.
      • Publish Datasource as "Template Datasource" over-writing the original and elect to have workbook update to use published datasource.
      • Publish workbook as "Template Report" over-writing the original.
      • Download "Report X" and check it still works, given the datasource is references and extracts has changed since it was last published.

       

       

      This feels like a long-winded and error prone process, infact I hate it.  But without the ability to edit published datasources, or copy/paste fields without losing their default properties, I can't think of a better way.  Before I embark on this I'd love some input or any false [assumptions] I've made pointed out.

        • 1. Re: Maintaining common and bespoke fields across multiple reports
          Tim Hughes

          Hi Mark,

           

          It sounds like you you have two primary goals: (1) a data source that serves as the single source of truth across multiple workbooks and that can be updated with new calcs, etc., and (2) a way to communicate the common/appropriate usage of fields in this data source.

           

          1. A single source of truth

          My recommended workflow is to connect to your data, take an extract (optional), create calcs/defaults, and publish to Server (pretty standard).  I also recommend saving your data source as a .tds (connection information) or a .tdsx (connection information + data extract).  You can edit and republish this .tds/.tdsx data source at any time, instead of downloading a local copy from the published data source.  This workflow relies on a single data source for all reports rather than customized data sources that exclude unnecessary fields as you have described.  How many distinct non-common fields are there among your dozens of reports?  How many distinct fields are there total among your dozens of reports?  How is the performance using one wider data source for all reports?  Obviously narrowing a data source will increase performance, but if you are using an extract then the fields get loaded into memory one at a time as needed when building a report in Desktop or interacting with a report in Server (as opposed to loading the whole extract into memory), so it's only the first drag/drop/click of a field that should be impacted by the width of the extract.  Furthermore, connecting to a published extract allows the user to leverage site-wide caching in Server, so that if another user recently used the same field as you, that field will already be loaded in memory.  As far as data organization goes, you can create folders in the published data source for the common fields and then each other segment of fields.

           

          2. Communicating field usage

          Publishing a template workbook works as one solution.  However, instead of downloading the template workbook and building a report in Desktop you could edit the report in Server and save as a new workbook on Server.  You can control who is allowed to web edit and who can save over the original template workbook versus just save as a new workbook.  Another solution to communicate field usage would be to add comments to the fields in the published data source.

           

          If you use a wider data source with folders you have only one data source to manage.  If you add comments to the fields you don't need a template workbook.  If you save your data source as a .tds or .tdsx then you have a local copy that you can edit and republish.  All of this in combination means that you manage the entire experience through the published data source and that you never download anything from Server.

           

          Hope this helps,

          Tim

          2 of 2 people found this helpful
          • 2. Re: Maintaining common and bespoke fields across multiple reports
            Steve Farrell

            Mark,

             

            Here's a good example of a company pursuing more structure around data curation, management, and governance.

             

            Best of luck to you.

             

            Cheers,

            Steve

            • 3. Re: Maintaining common and bespoke fields across multiple reports
              Mark Gemmell

              Thanks Tim, this is helpful. 

               

              I'm trying to take this approach now and will let you know how it pans out.  

               

              Sadly, I'm suffering a different bug that's frustrating my progress, and I doubt I'll be able to isolate the issue given the complexity of the workbook.