3 Replies Latest reply on Dec 10, 2018 6:45 PM by Chris Gerrard

    How to remap a detail table to a an aggregate table

    Kevin McCann

      I have an aggregate table that I created and now want to remap the existing detail table to this new table without losing all of my data model. The aggregate table is in the same SQL Server database as the other table. If I try to create a new connection Tableau does not give me 2 different data models, it shows the same one for both connections. So I cannot built a second model that is the aggregate model and repoint my worksheets to use this connection/model.

       

      If I delete the existing table and add the new table it breaks all of my sheets.

       

      If i try to drag the summary table into the existing data model it wants to connect the 2 facts together.

       

      Ultimately i want both models in the work book, one for the detail sheets and the other for the aggregate sheets that are less granular.

       

      Can someone please explain how I create 2 connections to the same server and database but use different models to support the aggregate versus detail data sets?

        • 1. Re: How to remap a detail table to a an aggregate table
          Patrick Van Der Hyde

          Hello Kevin,

           

          Were you able to find a solution?  I will move this post to Server Administration for other Server Admins to assist as they may have additional ideas but have you tried using the original data source as a Tableau Server data source and then connecting the new source to the existing one?

           

          Patrick

          • 2. Re: How to remap a detail table to a an aggregate table
            Chris Gerrard

            Patrick, I think this isn't a question about Tableau Server, so moving it there will only punt it without addressing it.

            • 3. Re: How to remap a detail table to a an aggregate table
              Chris Gerrard

              Hi Kevin, I'm not clear on what you're trying to achieve. Perhaps its because I'm out of touch but some of what you've said isn't familiar to me. Specifically 'model' in this context.

               

              Here's a stab at it.

              It feels like you have an existing Tableau connection to a MS SQL Server table that contains data aggregated from another table - your details table.

              You'd like to have a replica of the existing connection that accesses the details table.

               

              You -could- simply create a new connection to SQL Server and access the new table. However, this wouldn't give you any Tableau 'extras' in the aggregate table connection, e.g. calculated fields, comments, formats, defaults aggregations, etc.

              Your "If I delete the existing table and add the new table it breaks all of my sheets." seems to be evidence for this, in that replacing the aggregate table with the details table would break your vizzes that reference anything that's not there.

               

              Assuming that the tables are structurally similar - contain the same columns/fields (same name & data type), and differ in level of granularity...

              If you're trying to create a second connection, identical (or nearly so) to the aggregate connection, with the table replaced there are (at least) a couple of ways to approach it.

               

              The first is laborious:

              - create a new connection pointing to the details table, and then

              - copy the calculated fields from the existing connection to the new one
              - make any other adjustments needed, e.g. hierarchies, folders, formats, comments, default aggregations, etc. - this is tedious and error prone.

               

              The second is risky, and dependent upon the tables' fields being similar, but it preserves all of the Tableau-specific data connection stuff.

              This should only be done carefully, and in a test workbook, best if it only contains the data connection and a worksheet or two to validate the new connection.

              - duplicate the connection, creating one that will point to the details table

              - make sure the workbook is saved as a TWB file (not TWBX)

              - edit the TWB's code (it's an XML file, so the table referenced is the details table)

              - save the workbook

              - test the connection

              Hope this helps.