8 Replies Latest reply on Oct 25, 2017 11:17 AM by Henry Zheng

    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.