[TABLEAU] Using Stored Procedures and Functions

Using Stored Procedures and Functions

Tableau does not support stored procedures. If the stored procedure creates a permanent object, Tableau products can run queries against the final object. The permanent data objects resulting from the processes below can be queried from Tableau products.  You can also use the first approach to improve performance when retrieving data from SQL server. Note :These examples are specific to MS SQL 2005. Approach #1: Table-Valued Function You may need to create a new table with a stored procedure. We recommend using a stored procedure if the situation meets any of the criteria below: The data needs a: Dynamic view Custom SQL data connection Complex multiple table joins With a dynamic view, performance in Tableau can be poor because SQL must rebuild the entire view with each query. If you switch the dynamic view to a materialized view, SQL returns the data to Tableau more efficiently. With a complex Custom SQL connection, Tableau cannot optimize the queries and must complete the entire SQL statement each time. If you change the complex SQL statement to a materialized view, Tableau can optimize performance. In the case of a complex join, the SQL Query may take significant time to return the results of the query. By front-loading the query into a materialized view, SQL can provide the data to Tableau faster. A table-valued function allows the return of an actual table object that can be connected to in Tableau. A quick example of such a function is provided below. The key part of this function is RETURNS TABLE AS RETURN, which brings back the result as a table Tableau can read. To invoke this function from the Edit Custom SQL dialog box in Tableau Desktop, use this kind of query syntax: SELECT * FROM [Superstore 4.1].[dbo].[SuperstoreTableFunction] ('east') This query returns a subset of the total data set. There is a wide variety of potential solutions when you use table functions to pre-aggregate or otherwise filter data on certain criteria.   EXAMPLE OF TABLE-VALUED FUNCTION IN SQL SERVER: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- =============================================   -- Author: Tableau Example Function -- Create date: 12/12/2008 -- Description: Examples -- this function assumes a table exists called dbo.Orders -- =============================================   CREATE FUNCTION [dbo].[SuperstoreTableFunction] ( -- Add a parameter for this function with seeded default value @region varchar(20)='west' ) RETURNS TABLE AS RETURN ( select [Order ID], [Order Date], [region], [Order Priority], [Order Quantity], [Sales], [Discount], [Ship Mode], [Profit], [Unit Price], [Shipping Cost], [Customer Name], [Customer State], [Zip Code], [Customer Segment], [Product Category], [Product Sub-Category], [Product Name], [Product Container], [Product Base Margin], [Ship Date] from dbo.Orders where region=@region ) Dynamic view Custom SQL data connection Complex multiple table joins Approach #2: Openquery The second approach is to use the command"openquery" in conjunction with a stored procedure. In Tableau, you cannot directly invoke a stored procedure with an EXEC statement. You can, however, invoke"openquery," which in turn can invoke a stored procedure. To use this approach, you need a few prerequisites in place: One SQL server that acts as the pass-through. This server needs a"Linked Server" configured and pointing to item #2. A second SQL server that acts as the originating server and runs the stored procedure. Permissions correctly configured between these two servers. Tableau Desktop, which accesses item #1 above. In this type of scenario, you might have the following in the Tableau Custom SQL connection: SELECT * from openquery(SALESDB, 'exec [Sales Demo 4.1].[dbo].[SuperstoreStoredProcedure]') WHERE region = 'East' This statement selects from a built-in function"openquery," the purpose of which is to trigger remote code. In this example, the"remote code" resides on a server called SALESDB. The word SALESDB is the"Linked Server" you configured, and the remote code itself is a stored procedure called “[Sales Demo 4.1].[dbo].[SuperstoreStoredProcedure]". This stored procedure expects one variable,"region", so your call to"openquery" includes this variable in the WHERE clause. The entire remote stored procedure is included here as an example. EXAMPLE OF STORED PROCEDURE IN SQL SERVER: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: -- Create date: -- Description: -- =============================================   CREATE PROCEDURE [dbo].[SuperstoreStoredProcedure] ( -- Add parameters for the function here @region varchar(20)='west' ) AS ( select [Order ID], [Order Date], [region], [Order Priority], [Order Quantity], [Sales], [Discount], [Ship Mode], [Profit], [Unit Price], [Shipping Cost], [Customer Name], [Customer State], [Zip Code], [Customer Segment], [Product Category], [Product Sub-Category], [Product Name], [Product Container], [Product Base Margin], [Ship Date] from dbo.Orders where region=@region )