Solution to avoid TEMP tables and CTE's

Question

Solution to avoid TEMP tables and CTE's

Lord Slaagh

SSCertifiable

Points: 5727
More actions
March 24, 2017 at 9:30 am

#323232

Hello SSC!
I have a rather strange question to ask...
I am creating several reports in Tableau that use a very complex SQL script. These reports have been run manually for a long time, and I have been tasked with automating them in Tableau. My issue is that Tableau does not support TEMP's, CTE's, or Table variables (oh no!). This script uses several TEMP tables.
Due to the complexity, I was wondering if you guys could point me in the right direction as to what I can use besides, Temps, CTE's, Table vars). I dumped all the data into separate tables, and this seems to be a viable solution, but this solution is going to have too many moving parts. But it seems to work.
I have posted this topic on several Tableau forums, but I believe that this is a SSC question, since Tableau is more about visualization than actually grinding data. (I also think Tableau is a bit overrated, it just looks good, but that's my opinion). So, being restricted to not use Temp's, CTE's, and table vars, what do you guys recommend?
I know that you guys want to see code samples, but this is more of a "what other options are available" question.
Thank you as always and enjoy your weekends!
Everyone has a plan until they get punched in the mouth. --Mike Tyson

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

jasona.work SSC Guru Points: 50084 More actions · Answer 1

Not knowing anything about Tableau, could you convert the SQL script over to a Stored Procedure with parameters, and have Tableau call that?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

jasona.work - Friday, March 24, 2017 9:58 AM
Not knowing anything about Tableau, could you convert the SQL script over to a Stored Procedure with parameters, and have Tableau call that?

Jason, I wondered about this too since it seemed to be the obvious choice:
http://www.jenunderwood.com/2013/08/01/myth-tableau-cant-use-stored-procedures-reality-yes-it-can/

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lord Slaagh SSCertifiable Points: 5727 More actions · Answer 3

Hi Chris,

Tableau doesn't support SQL Procs either :(.

I see this more and more with new technologies. They release a product that is incomplete and say that it is cutting edge. I am just surprised that Tableau is so limited, yet popular. They claim that they are the "Big data" solution, but how can you make that claim when you don't even support basic TEMP tables?

Thanks for your response Chris and Jason.

Everyone has a plan until they get punched in the mouth. --Mike Tyson

Lord Slaagh SSCertifiable Points: 5727 More actions · Answer 4

I spoke to soon. That link looks like it might work.
Thanks Jason.

Everyone has a plan until they get punched in the mouth. --Mike Tyson

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Lord Slaagh - Friday, March 24, 2017 10:35 AM
Hi Chris,
Tableau doesn't support SQL Procs either :(.
I see this more and more with new technologies. They release a product that is incomplete and say that it is cutting edge. I am just surprised that Tableau is so limited, yet popular. They claim that they are the "Big data" solution, but how can you make that claim when you don't even support basic TEMP tables?
Thanks for your response Chris and Jason.

Yes it does. Read the link I posted. It's a very simple workaround.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lord Slaagh SSCertifiable Points: 5727 More actions · Answer 6

This solution would work, but its not realistic for my case. Most reporting environments just call the proc. This blog is talking about linked servers and Open query which is a no-no in my company for security reasons. Security is a blessing and a curse :). So the short answer to this question is that Tableau can support procs, but the solution is unrealistic in companies that are locked down with tight security.

Thanks again for your time.

Everyone has a plan until they get punched in the mouth. --Mike Tyson

cphite SSCarpal Tunnel Points: 4275 More actions · Answer 7

We have a few users who run Tableau and generally we do one of the following:

1. Create a view... you'd obviously have to use CTEs instead of temp tables, but Tableau can select from a view with no problem. The only caveat is that if the reason you're using temp tables in the first place is performance, you may need to rework your query to make it run acceptably fast.

2. Create a reporting table... Build a SQL job that uses your existing script to populate a table. Benefits of this is that it should be much faster on the Tableau side because it's reading from a single table. Caveat is that you have to store the entire result set of your existing script, and you're not giving your users "real-time" data anymore. This is generally my preferred option.

3. User subqueries... This certainly isn't ideal if you're dealing with a very complex script, but while Tablaeu will not support CTEs it will support a subquery. So for example, while this won't work:

with cteFoobar as ( select Foo from Bar )

select Foo from cteFoobar

This should work just fine:

select Foo from ( select Foo from Bar ) subFoobar

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

cphite - Friday, March 24, 2017 11:29 AM
2. Create a reporting table... Build a SQL job that uses your existing script to populate a table. Benefits of this is that it should be much faster on the Tableau side because it's reading from a single table. Caveat is that you have to store the entire result set of your existing script, and you're not giving your users "real-time" data anymore. This is generally my preferred option.

This is the method that I try to employ as well. It helps when they decide to run against a warehouse source where it may be perfectly fine to add these "reporting" tables.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Luis Cazares SSC Guru Points: 183698 More actions · Answer 9

A different solution is to use views or derived tables instead of CTEs.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

Table variables (oh no!).

If that oh no was related to not being able to use table variables note that you REALLY shouldn't use them. No statistics means BAD plans at least some of the time.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

Luis Cazares - Friday, March 24, 2017 2:58 PM
A different solution is to use views or derived tables instead of CTEs.

That was the first item (views) and 3rd (derived tables) that cphite listed.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Luis Cazares SSC Guru Points: 183698 More actions · Answer 12

SQLRNNR - Friday, March 24, 2017 3:16 PM
Luis Cazares - Friday, March 24, 2017 2:58 PM
A different solution is to use views or derived tables instead of CTEs.
That was the first item (views) and 3rd (derived tables) that cphite listed.

I know, but I didn't see his reply before posting mine. I had the tab open too long before replying.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

x SSC-Insane Points: 23660 More actions · Answer 13

Lord Slaagh - Friday, March 24, 2017 10:51 AM
This solution would work, but its not realistic for my case. Most reporting environments just call the proc. This blog is talking about linked servers and Open query which is a no-no in my company for security reasons. Security is a blessing and a curse :). So the short answer to this question is that Tableau can support procs, but the solution is unrealistic in companies that are locked down with tight security.
Thanks again for your time.

http://onlinehelp.tableau.com/current/pro/desktop/en-us/connect_basic_stored_procedures.html

Are you running an older version that is still limited? Just curious, I don't know the product.