There seems to be a myth out there that Tableau can’t work with SQL Server stored procedures. That may have been true in Tableau v6 but not it is not true in Tableau v7 or v8. I have done it. I do admit that the stored procedure solution is not the simplest one around. At the upcoming Tableau Customer Conference, I will ask about this along with assumed predictive improvements that I am hoping are shown at the much anticipated Day 1 TCC Key Note. The bottom line right now = you can indeed use SQL Server stored procedures with and without parameters.
The example Tableau workbook above calls a stored procedure [Federal].[dbo].[uspSQLSprocDemo] with two parameters, @State=’FL’ and @Progam=’Water’. Although I hard coded them in my quick sample above, starting in Tableau v8 you can use Tableau parameters in a Custom SQL statement to make the stored procedure call truly dynamic.
Here is a quick explanation of how I did it.
1. Enabled SQL Server ‘Ad Hoc Distributed Queries’ a SQL Server server level configuration setting.
exec sp_configure ‘show advanced options’, 1;
sp_configure ‘Ad Hoc Distributed Queries’, 1;
2. Added a Linked Server with a log in to the existing SQL Server instance. Note I used localhost and my account since I have no users set up on my dev laptop. In a production environment, you can use any of the other Linked Server log in account mapping options.
EXEC sp_addlinkedserver @server=’localhost’, @srvproduct=’SQL Server’;
— create a login map to a single account
@rmtuser = ‘jen’,
@rmtpassword = ‘mysupersecretpassword’;
3. I created the demo stored procedure with default parameters.
/****** Object: StoredProcedure [dbo].[uspSQLSprocDemo] SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
— Author: Jen Underwood
— Create date: 01/28/2012
— Description: Sproc Demo
— exec [dbo].[uspSQLSprocDemo] ‘FL’, ‘Water’
CREATE PROCEDURE [dbo].[uspSQLSprocDemo] — Defaults only used when no parms passed in
@State varchar(4) = ‘FL’,
@Program varchar(100) = ‘Water’
SET NOCOUNT ON;
SELECT [State],[City],[Program],[Date],[Cost],[Jobs],[ProposedCost] FROM [dbo].[StimulusProjects] WHERE [State]=@State AND [Program]=@Program
4. I then created a SQL Server data connection in Tableau with the Advanced option for Custom SQL and copied the following OPENQUERY snippet. In v8 you can use Tableau parameters to dynamically pass in SQL Server stored procedures parameters within the live data connection Custom SQL statement.
SELECT * FROM OPENQUERY
(localhost,’SET FMTONLY OFF; exec [Federal].[dbo].[uspSQLSprocDemo] ”FL”, ”Water”;’) X
If I did use Tableau parameters, this is what it would look like:
SELECT * FROM OPENQUERY
(localhost,’SET FMTONLY OFF; exec [Federal].[dbo].[uspSQLSprocDemo] @State, @Progam’;’) X
And that is it… This was not hard to do but there is a myth that it is not possible. MYTH BUSTED. Enjoy and happy querying to all the stored procedure lovers like me.