9 Replies Latest reply on Mar 6, 2019 8:14 PM by Rod Burke

    Stored Procedures In SQL Server Without all the Tableau Overhead

    greg.jones.8

      So you have decided to use a stored procedure as to pull data from SQL Server. And why not, it can be more efficient than views, contain logic, take advantage of temp tables, indexes, better statistics and therefore better estimates and faster queries.

       

      Then you realize that Tableau has a ton of limitations. It doesn't like temp tables, it runs the proc dumps the results into tempdb and then queries it again. No surprise, it takes double or more time to return the results.

       

      No More!

       

      UPDATE!: After the below comments I wanted to add some context. This approach I have found useful for extract heavy environments or environments that operate under a policy of keeping database logic in the database for optimization, code management, and accessibility. This approach was designed to haul large data sets into extracts. If you do not operate in an environment described here, it may be beneficial to use Tableau's "Initial SQL" feature. See comments or search the forum.

       

      ****For reference I am using SQL2014 and Tableau 10.1

      So lets start out by creating an offensive stored proc. You may need to get your dba to do some of these steps if you don't have permissions.

       

      ****Also use at your own risk. I've not experienced any errors, but maybe you may?

       

      CREATE PROCEDURE dbo.EatThisTableau

      AS

      BEGIN

       

      BEGIN TRY

        -- drop our temp table if it already exists

        IF EXISTS(SELECT TOP(1) 1 FROM tempdb.sys.tables WHERE object_id = OBJECT_ID(N'tempdb..#Appetizer'))

        BEGIN

             DROP TABLE #Appetizer

        END

       

      --Create a local #temp table that tableau doesn't play nice with

        CREATE TABLE #Appetizer

        (

             ID INT IDENTITY(1,1),

             Animal VARCHAR(24),

             Price MONEY

        )

       

      -- create index on temp table

        CREATE UNIQUE CLUSTERED INDEX  uix_Appetizer ON #Appetizer(id);

       

        --populate it

      INSERT INTO #Appetizer (Price,Animal)

        VALUES (55.17,'Monkey')

             ,(14.27,'Cat')

             ,(15.47,'Dog')

             ,(35.27,'Horse')

             ,(17.57,'Elephant')

             ,(99.29,'Curelom')

       

        -- some random query the business wants

      ;WITH cte_MainCourse AS (

             SELECT id, animal, a.Price

             FROM #Appetizer AS a

        )

        SELECT a.Animal, SUM(a.price) AS InflatedPrice

        FROM cte_MainCourse AS a

        CROSS JOIN cte_MainCourse AS b

        GROUP BY a.Animal

       

      END TRY

      BEGIN CATCH

        -- rollback open transactions

      WHILE @@TRANCOUNT > 0

        BEGIN

        ROLLBACK TRANSACTION;

        END;

       

        -- re-throw exception

        THROW;

      END CATCH;

      END

       

      So we execute this normally... and it works:

      EXEC dbo.EatThisTableau

       

      AnimalInflatedPrice
      Cat85.62
      Curelom595.74
      Dog92.82
      Elephant105.42
      Horse211.62
      Monkey331.02

       

      We don't want the overhead that tableau creates when referencing stored procs directly so... we select from it using an open query.

      Then tableau will treat it like a normal query

      SELECT * FROM OPENQUERY([SQLServerName],'EXEC dbo.EatThisTableau' )

       

      Msg 7411, Level 16, State 1, Line 49

      Server 'SQLServerName' is not configured for DATA ACCESS

       

      It errors out.

      So now we get around the DATA ACCESS error.

       

      OPTION 1: LinkedServer loopback (limited to one database)

      sp_addlinkedserver @server = N'LinkedServerName',

          @srvproduct = N' ',

          @provider = N'SQLNCLI',

          @datasrc = N'SQLServerName',

          @catalog = N'SandBox'

       

      OPTION 2: Enable DATA ACCESS (server wide)

      EXEC sp_serveroption [SQLServerName], 'DATA ACCESS', TRUE

       

      Reference this link to determine which path is right for your environment

      DATA ACCESS setting on local server - Piotr Rodak

       

      We try it again

      OPTION 1:

      SELECT * FROM OPENQUERY(LinkedServerName,'EXEC dbo.EatThisTableau' )

       

      OPTION 2: **** notice the database/catalog reference on the stored procedure call

      SELECT * FROM OPENQUERY(SQLServerName,'EXEC Sandbox.dbo.EatThisTableau' )

       

      But it blows up in our face again!!

      WHY? cause no meta data can be determined.... that's why!

      When executed normally (EXEC dbo.EatThisTableau) tableau results into tempdb (in a very in efficient way) and then selects the result from that table so meta is established.

       

      So how do we avoid this?????

      1. turn fmtonly off... sometimes tableau turns it on and it breaks stuff

      2. DEFINE THE META DATA FOR TABLEAU by using "WITH RESULT SETS" on your stored proc call.

       

      OPTION 1:

      SELECT * FROM OPENQUERY(LinkedServerName,'set fmtonly off; EXEC dbo.EatThisTableau WITH RESULT SETS

      ((

        Animal VARCHAR(24),

        InflatedPrice MONEY

      ))' )

       

      OPTION 2:

      --Notice the Database reference on the stored proc call!

      SELECT * FROM OPENQUERY(SQLServerName,'set fmtonly off; EXEC Sandbox.dbo.EatThisTableau WITH RESULT SETS

      ((

        Animal VARCHAR(24),

        InflatedPrice MONEY

      ))' )

       

      Msg 7357, Level 16, State 1, Line 109

      Cannot process the object "set fmtonly off; EXEC Sandbox.dbo.EatThisTableau WITH RESULT SETS

      ((

        Animal VARCHAR(24),

        InflatedPrice MONEY

      ))". The OLE DB provider "SQLNCLI11" for linked server "SQLServerName "indicates that either the object has no columns or the current user does not have permissions on that object.

       

      Still another error.... why is it saying the object has not column or I don't have permissions to my own stored procedure... Thank NOCOUNT being off for that. Alter your stored proc ADD "SET NOCOUNT ON".

       

      ALTER PROCEDURE dbo.EatThisTableau

      AS

      BEGIN

      SET NOCOUNT ON;

      BEGIN TRY

        -- drop our temp table if it already exists

        IF EXISTS(SELECT TOP(1) 1 FROM tempdb.sys.tables WHERE object_id = OBJECT_ID(N'tempdb..#Appetizer'))

        BEGIN

             DROP TABLE #Appetizer

        END

       

      --Create a local #temp table that tableau doesn't play nice with

        CREATE TABLE #Appetizer

        (

        ID INT IDENTITY(1,1),

        Animal VARCHAR(24),

        Price MONEY

        )

       

      -- create index on temp table

        CREATE UNIQUE CLUSTERED INDEX  uix_Appetizer ON #Appetizer(id);

       

        --populate it

      INSERT INTO #Appetizer (Price,Animal)

        VALUES (55.17,'Monkey')

             ,(14.27,'Cat')

             ,(15.47,'Dog')

             ,(35.27,'Horse')

             ,(17.57,'Elephant')

             ,(99.29,'Curelom')

       

        -- some random query the business wants

      ;WITH cte_MainCourse AS (

             SELECT id, animal, a.Price

             FROM #Appetizer AS a

        )

        SELECT a.Animal, SUM(a.price) AS InflatedPrice

        FROM cte_MainCourse AS a

        CROSS JOIN cte_MainCourse AS b

        GROUP BY a.Animal

       

      END TRY

      BEGIN CATCH

        -- rollback open transactions

      WHILE @@TRANCOUNT > 0

        BEGIN

        ROLLBACK TRANSACTION;

        END;

       

        -- re-throw exception

        THROW;

      END CATCH;

      END

       

      And try it again...

       

      OPTION 1:

      SELECT * FROM OPENQUERY(LinkedServerName,'set fmtonly off; EXEC dbo.EatThisTableau WITH RESULT SETS

      ((

        Animal VARCHAR(24),

        InflatedPrice MONEY

      ))' )

       

      OPTION 2:

      --Notice the Database reference on the stored proc call!

      SELECT * FROM OPENQUERY(SQLServerName,'set fmtonly off; EXEC Sandbox.dbo.EatThisTableau WITH RESULT SETS

      ((

        Animal VARCHAR(24),

        InflatedPrice MONEY

      ))' )

       

      Success!!!!

      AnimalInflatedPrice
      Cat85.62
      Curelom595.74
      Dog92.82
      Elephant105.42
      Horse211.62
      Monkey331.02

       

      Now you can wrap complicated stored procedures in OPENQUERY and utilize them just like any other custom SQL or View.

       

      When the query is kicked off, 2 statements will appear in active requests.

      The first query acts as a shell, it goes into OLEDB wait, requests no memory, and just sits and waits for all the results to be returned from the second thread.

        • 1. Re: Stored Procedures In SQL Server Without all the Tableau Overhead
          Chris McClellan

          That is SERIOUSLY COOL, I don't think I'll be using it, but the fact that it's possible is awesome

           

          Thanks for the writeup and adding it here so others can reference it when required.

          • 2. Re: Stored Procedures In SQL Server Without all the Tableau Overhead
            Bora Beran

            Hi Greg,

            OPENQUERY was the workaround we recommended (see below) before introducing stored procedure support and later adding initial SQL support on SQL Server.

             

            Re: Using Stored Procedures (Openquery) as Data Source?

             

            Given these two new features, openquery approach has many shortcomings.

             

            1. Since you're querying using custom SQL, it will run the stored procedure unnecessarily when you add filters etc. The built in approach can have extra overhead when it runs the stored procedure and creates the table but as you build your viz, it will issue selects directly to temp table instead of the custom SQL that calls to stored proc so it will be faster than this approach as it won't run the stored procedure over and over again.

             

            2. Now that SQL Server supports initial SQL you can do this all in Initial SQL which won't run the stored procedure unnecessarily like this approach and won't require admin to do anything like configuring linked servers.

             

            Initial SQL

            CREATE TABLE #TestTable ([name] NVARCHAR(60), [ID] INT);

            INSERT INTO #TestTable EXEC MyStoredProcedure

             

            Custom SQL

            SELECT * FROM #TestTable

             

            Did you give this approach a shot?

             

            I wrote about using this approach in the context of calling CTEs directly from Tableau but technically it could be used with other methods that can output tables as well.

             

            Using Common Table Expressions | Tableau Software

             

            Thanks,

             

            Bora

            2 of 2 people found this helpful
            • 3. Re: Stored Procedures In SQL Server Without all the Tableau Overhead
              greg.jones.8

              I have seen the OPENQUERY option used as a true linked server or in a limited  self referencing way, but not like this.  We are an extract heavy environment (extracts running for 1 to 4 hrs). This encapsulates everything so that the normal "Short Comings" of OPENQUERY are a non-issue. Also, filtering won't run the query again.

               

              Initial SQL is a great option. We have found it most useful for smaller queries or live queries as you assumed.

              We also operate under a policy that all SQL is contained within the DB (not uncommon for large environments) which allows us easy source control, code management, etc.

               

              We also have temp tables we dump to and then pull from them referencing them multiple times (5 for example).

              In our stored procedure we build the temp table, reference it 5 times in a select and it begins returning rows to tableau.

               

              In the Initial Query situation we call the stored proc which would build the temp table, and select from it 5 times. Then the only options is to dump it into another Temp Table in the Initial Query... and then select from the temp table to finally start returning rows. While I understand this is kind of our choice, it make code management and optimization by our data warehouse team much easier.

              • 4. Re: Stored Procedures In SQL Server Without all the Tableau Overhead
                Bora Beran

                We replaced the contents of the article so it is nowhere mentioned in the documentation anymore. You can only find references to how stored procedures were ran through openquery scattered across the forums.

                 

                But Jen used that template for a blog post at the time which you can find below.

                 

                Myth: Tableau can't use Stored Procedures. Reality: Yes it can! - Analytics Industry Highlights

                 

                I agree that if you're creating extracts, live query shortcomings won't apply and for a stored procedure that takes 4 hours to run an create an extract, you would want to shave off all possible processing overhead. So this is great solution for your particular use case.

                 

                But many customer use cases we see feed parameters to the stored procedures and they don't take hours to run to begin with so the convenience of doing something in a few clicks, without having to ask DB admin for changes and better performance in subsequent queries outweigh an extra 4-5 second for first time load.

                 

                That being said, I think it is great that it is captured here as it better serves your particular use case and there might be others with similar setups/use cases.

                • 5. Re: Stored Procedures In SQL Server Without all the Tableau Overhead
                  greg.jones.8

                  Thanks for your comments Bora.

                   

                  I did run across the scattered forum posts and the post from Jen. They mainly fell short with defining the meta-data and therefore failed quickly in our environment. To a lesser extent fell short in just creating a linked server loop back instead of enabling "Data Access" for the server. "Data Access" circumvents some possible failures. Maybe the original reference covered all this?

                   

                  At any rate, I will probably add some context to the beginning of my post.

                  • 6. Re: Stored Procedures In SQL Server Without all the Tableau Overhead
                    Henry Zheng

                    Hi Bora, I've just tried your method which works for a regular SP, but not for a SP that has a nested SP. So what am I supposed to do now?

                    • 7. Re: Stored Procedures In SQL Server Without all the Tableau Overhead
                      Bora Beran

                      Is this a Tableau limitation or database limitation?

                       

                      I did a quick search and it looks like SQL server in general has this problem with or without Tableau and some workarounds exist.

                       

                      https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e66c9e71-4424-4cf3-920c-6725ffc40162/insert-exec-statement-cannot-be-nested?forum=transactsql

                       

                      sql server - nested insert exec work around - Stack Overflow

                      • 8. Re: Stored Procedures In SQL Server Without all the Tableau Overhead
                        Henry Zheng

                        Yes, this is a SQL server limitation.

                         

                        Thanks, Bora.

                        • 9. Re: Stored Procedures In SQL Server Without all the Tableau Overhead
                          Rod Burke

                          Thanks for this very helpful post! I had already figured out the NOCOUNT thing, but using OPENQUERY is a very interesting approach. I'm hoping of course, to get Tableau to "just call my stored procedure" without all the funny overhead that comes with using the standard stored procedure feature and not needing the benefits(?) attached to those overheads.

                           

                          One glitch I came across using your method is inserting parameters, especially dates.

                          The varchar parameter 'Smith,A,1 Jan 1990' gets inserted successfully if I surround it in quotes so that it gets double quoted in the SQL string.

                          But the date value gets inserted using this strange {ts...} syntax that makes the string into a bad format.

                          The workaround here for me is to change the date parameter to character type and then use it as a date.

                           

                          SELECT TOP 1 *

                          FROM (

                            select * from openquery([myserver\myinstance],'set fmtonly off; exec myproc ''Smith,A,1 Jan 1990'','{ts '2018-12-31 00:00:00'}',1

                            with result sets (

                            (....