1 Reply Latest reply on Apr 29, 2013 2:35 PM by Russell Christopher

    Is it possible to identify the SQL Server table at run time?

    Chris Gerrard

      My client has a complex web analytical app that guides the user through a series of screens to determine the what they want to see. The app is designed to create a new SQL Server table for each user session populated with the content specific to the user's declared needs.

       

      There is a common schema for all of the SQL Server tables - they differ only in their data content.

       

      The developers wanted to create 10 or 12 identical copies of the same Tableau viz, each connected to a distinct specific tableau and then round-robin these viz-table pairs among the users. It wasn't hard to convince them that this isn't a good solution.

       

      Now they want to be able to have a single Tableau viz that can be wired to dynamically access user-specific SQL Server tables at run time. I'm not sure whether or not this can be done. It doesn't feel like it, at least feels like it's outside Tableau's intentional design parameters.

       

      But it's not an area I'm comfortable in having a definitive opinion in, so can anybody shed some light on things?

      CAN it be done? If so, how? (this would be terrific)

      Can it be confirmed that it cannot be done? Also good, but harder to be definitive with.

       

      I have urged them to consider using a single table so that they can pass filter parameters to Tableau server identifying the user, which should work, but they've dug in their heels at that. Their claimed concern is that the processing load of adding the user-specific rows to the table will be too great.

       

      Thanks for any insights.

        • 1. Re: Is it possible to identify the SQL Server table at run time?
          Russell Christopher

          This can be done with an "it works, but isn't a particularly great thing to do" approach. It relies heavily on SQL Server technology.

           

          • Create a stored procedure in SQL Server that takes an input parameter of <name of table>. Essentially, all this sucker will do is SELECT * from <name of table> using an IF statement or something.

           

          On the SQL Server, create a "Linked Server" which points to itself. Mine is called "localhost":

           

          EXEC master.dbo.sp_addlinkedserver @server = N'LOCALHOST', @srvproduct=N'SQL Server'

          /* For security reasons the linked server remote logins password is changed with ######## */

          EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LOCALHOST',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

          GO

           

          • In Tableau, use Custom SQL to leverage SQL Server's OPENQUERY command to execute the stored procedure and pass in the <table name>:

           

                    SELECT * FROM OPENQUERY(LOCALHOST,'exec Catcher.dbo.Test t1 ') //or t2, or t3....

           

          You'll need to play around a bit with single and double quotes (or combinations of same) in order to be able to switch out the table name dynamically using Tableau's new parameterized Custom SQL:

           

          Your Custom SQL will probably look something like this:

           

          SELECT * FROM OPENQUERY(LOCALHOST,<Parameters.SecretSauce>)

           

          ...and your parameters (in Tableau)  will look like this:

           

          exec Catcher.dbo.Test t1

           

          exec Catcher.dbo.Test t2

           

          exec Catcher.dbo.Test t3