KNOWLEDGE BASE

Working with Stored Procedures and Functions in Tableau


Product(s): Tableau Desktop, Tableau Server
Version(s): 8.0, 7.0, 6.1
Last Modified Date: 16 Aug 2016

Article Note: This article is no longer actively maintained by Tableau. We continue to make it available because the information is still valuable, but some steps may vary due to product changes.


 

Tableau cannot query stored procedures directly, but you can take advantage of programmability features in certain databases to work around this constraint.

Microsoft SQL Server

In Microsoft SQL Server, you can use the OPENQUERY function to invoke a stored procedure.

This approach requires the following setup:

  1. Configure one SQL Server instance to act as a pass-through. This server must point to a second SQL Server instance, configured as a Linked Server, which could be another SQL Server instance or even the original instance itself (a configuration sometimes referred to as a loopback server). Linked servers can be added by the database administrator using the sp_addlinkedserver procedure. See sp_addlinkedserver (Transact-SQL) on MSDN.

  2. Configure permissions on SQL Server to enable data access through linked servers and execution of stored procedures.

  3. Configure Tableau Desktop to access the first SQL Server instance.

  4. Deploy the stored procedure on the SQL Server instance that is acting as the linked server.

In this scenario, your Tableau Custom SQL connection would look like the following:

SELECT * FROM OPENQUERY(SALESDB, 'EXEC [Sales].[dbo].[SampleStoredProcedure] @region=East, @minquantity=3')

This statement selects from a built-in function OPENQUERY, the purpose of which is to trigger remote code. The remote code is the stored procedure named SampleStoredProcedure residing on a linked server with the alias SALESDB. This stored procedure expects two variables, region and minquantity, so your stored procedure call provides values for these variables. The entire stored procedure is show below.

Example of stored procedure in SQL Server

CREATE PROCEDURE [dbo].[SampleProcedure]
(
@region varchar(20),
@minquantity int
)
AS
(
SELECT
[Order ID],
[Order Date],
[Order Quantity]
FROM dbo.Orders
WHERE [Region]=@region and [Order Quantity] > @minquantity
)

SAP Sybase ASE

In SAP Sybase ASE, you can invoke stored procedures using proxy tables. This requires a linked server setup like the one for SQL Server. In Sybase, the procedure for creating the linked server is sp_addserver.

A proxy table that invokes the stored procedure would look like this:

(
[Order ID] int,
[Order Date] date,
[Order Quantity] int,
_region varchar (20) null,
_minquantity int null
)
EXTERNAL PROCEDURE AT "SALESDB.Sales.dbo.SampleStoredProcedure"

The variable region is defined as a column in proxy table’s definition. Once the proxy table is created in the database, you can query it from Tableau using a Custom SQL connection, as you might any other table—but with one difference: the variables need to be passed to the where clause of the query, as shown below.

SELECT [Order ID], [Order Date], [Order Quantity] FROM dbo.OrdersResults where _region='West' and _minquantity = 3

One common scenario for the use of stored procedures is to generate results dynamically. You can easily achieve this using parameters in Tableau. Here is how to set up the parameter in Tableau Desktop:

Create a second parameter named QuantityFilter. To take advantage of the parameters, the query would be written in the following way.

SELECT [Order ID], [Order Date], [Order Quantity] FROM dbo.OrdersResults where _region=<Parameters.Regions> and _minquantity = <Parameters.QuantityFilter>r>

Parameters can be exposed to users as widgets on Tableau dashboards. Users can run the stored procedure by making selections, and can then view the results.

In SQL Server, using parameters requires minor modifications since Tableau parameters are passed to queries as literals, whereas the query definition in OPENQUERY is a string. Parameters that are strings need to be wrapped between quotation marks; no special treatment is necessary for numeric parameters.

SELECT * FROM OPENQUERY(SALESDB, 'EXEC [Sales].[dbo].[SampleStoredProcedure] @region='<Parameters.Regions>', @minquantity=<Parameters.QuantityFilter>')

Table-valued functions

A table-valued function allows the return of an actual table object that can be connected to from Tableau. An example of such a function for SQL Server is provided below. Most major database vendors (for example, Teradata, Oracle, and IBM DB2) support table functions.

The key part of this function is RETURNS TABLE AS RETURN, which brings the result back as a table Tableau can read. To invoke this function from the Edit Custom SQL dialog box in Tableau Desktop, use query syntax like the following:

SELECT * FROM [Sales].[dbo].[SampleTableValuedFunction]('East')

This query returns a subset of the total data set. There is a wide variety of potential solutions when you can use table functions to pre-aggregate or otherwise filter data on certain criteria.

Using a parameter, the query syntax would look like this:

SELECT * FROM [Sales].[dbo].[SampleTableValuedFunction](<Parameters.Regions>)

Example of a table-valued function in SQL Server

CREATE FUNCTION [dbo].[SampleTableValuedFunction]
(
@region varchar(20)
)
RETURNS TABLE
AS
RETURN
(
SELECT
[Order ID],
[Order Date],
[Order Quantity]
FROM dbo.Orders
WHERE Region=@region
)

Scalar functions

A scalar function takes input argument(s) and returns a single value result. Use scalar functions to create dimensions or measures that Tableau can read by generating data (for example, the NOW() function in SQL Server) or by transforming values from other columns (for example, the ABS and ACOS functions in SQL Server). Scalar functions provide a great way to take advantage of advanced functions supported by most databases.

The example below shows how you could use spatial functions in Teradata to create a map to show all customers within a specified distance of a store. The scalar functions used in this example are STDistance, which takes two geography objects and calculates the distance between them in meters, and STPointFromText, which takes a string containing the latitude and longitude of a point and creates a geography object from it. The Geo column in the GeoTable table contains the latitudes and longitudes of customer addresses, and the Tableau parameter Store contains a list of store names with their corresponding latitudes and longitudes. The datum for the geography object is set using EPSG code 4326, and miles are converted to meters using the appropriate multiplier.

The query is:

SELECT geo.Lat as Latitude, geo.Long as Longitude FROM [dbo].[GeoTable] [GeoTable] 
WHERE geography::STPointFromText('POINT('+<Parameters.Store>+')', 4326).STDistance([GeoTable].geo) < <Parameters.Distance (miles)>*1609.344

The resulting parameter and map look like this:

 

Alternate Search Terms:sql, sql server, sap, sybase, sap sybase, ase, stored procedures, functions
Did this article resolve the issue?