4 Replies Latest reply on Jun 6, 2013 7:57 AM by Russell Christopher

    Can you create a single view that swaps out complete data connections based on a user action?

    Leigh Fonseca

      I have a client who has 2 databases, let's call them coke and pepsi.  These two databases are on different servers and have different data connections.

       

      Both databases have similarly named tables and field names.

       

      For example, both the coke_db and the pepsi_db:

       

         -  have tables called attributes, country and sales

         - the attributes tables has fields: product_id, name and size

         - the country tables has fields: country and region

         - the sales tables has fields: product_id, region and sales

       

      The data connection joins these three tables.  There are two data connections because one is for coke and one is for pepsi.  The tableas and data connections have exactly the same design.

       

      The data CANNOT be blended.  At no time is it desirable to mix data from one data source and other.  Rather, the desire is to create one view of the data that can load the data for either coke or pepsi.

       

      I have tried doing this with Parameters but it is ATTR aggregating my dimensions.  I've also tried it with Actions but I'm having difficulty with the need for each view to require a separate sheet.  This has a high development and maintenance cost because my customer has many products and for each view they want to develop a different custom data connection.  As you can image this means even though they want the same reporting across multiple products that have to duplicate their entire report stack every time they add a product.  What's more, any changes to the existing views also have to be replicated across all products.

       

      I can imagine many occasions where a business would segregate their data sources but deliver the same reports/dashboards to multiple customers/products/etc.  Given this, they would want to develop one view/dashboard and swap out the reporting with a click.


      I've created a sample workbook to illustrate the complexity and the issues with parameters and actions.  And I opened  support case but Tableau doesn't current support swapping out entire data sources.  Creating a single template workbook and then Replacing Data Sources may be an option but in the real-world workbooks there are already ~10 data sources supporting 10 views and more sodas than coke and pepsi.  This means there's extra complexity and maintenance with this approach.


      I hope someone has encountered this before and has some suggestions.  Thanks in advance for your help.


      Cheers,

      Leigh


        • 1. Re: Can you create a single view that swaps out complete data connections based on a user action?
          Robert Morton

          Hi Leigh,

           

          I've heard similar questions in the context of development vs. production database servers. Your question has an interesting angle, and I like your example of using Tableau as a multi-tenant view over disparate systems. Unfortunately I don't have an answer for accomplishing this directly in Tableau.

           

          You should consider using a data federation system like Cirro or Composite. This will add expense to your project but such a system may be able to handle the challenge you describe. If you can make the federated system present tables from each database server to Tableau as if they were simply tables in the same database, you could use Parameters in Custom SQL to change which table (i.e. database system) you wish to query. You'll need to connect to the federated system from Tableau using the general-purpose ODBC connector, since we do not have first-class support for these systems.

           

          I'd love to hear what solution you wind up coming up with (either here in the forum or on your blog, which I follow).

           

          Good luck!

          Robert

          • 2. Re: Can you create a single view that swaps out complete data connections based on a user action?
            Russell Christopher

            Hey Leigh -

             

            Since Tableau workbooks are nothing more than XML documents, you could easily take your "template" idea and simplify the "connection switching" so that an author didn't need to touch each of the 10 connections. Tableau doesn't support manually hacking the XML, but it's quite easy to do.

             

            You'd essentially open up your template workbook in notepad or any text editor, search for the database name of "coke" and replace all with "pepsi". Done - a 3 second operation.

             

            I believe there are several "Ideas" in the idea forum about what you're trying to get accomplished, If I were you, I'd do a quick search and "vote up" the ideas you like.

            • 3. Re: Can you create a single view that swaps out complete data connections based on a user action?
              Robert Morton

              Hi Russell,

              I had interpreted the question as requiring a dynamic solution, with Tableau switching data sources based on user interactions. Have you come across any techniques for handling that requirement?

              -Robert

              • 4. Re: Re: Can you create a single view that swaps out complete data connections based on a user action?
                Russell Christopher

                Hi Robert!

                 

                I wish! I hope that you gents will have to wrangle this one for us all at some point

                 

                I was responding to Leigh's "Plan B" (non-dynamic approach):

                 

                Creating a single template workbook and then Replacing Data Sources may be an option but in the real-world workbooks there are already ~10 data sources supporting 10 views and more sodas than coke and pepsi.  This means there's extra complexity and maintenance with this approach.