March 24, 2017 at 9:30 am
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!
The are no problems, only solutions. --John Lennon
March 24, 2017 at 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?
March 24, 2017 at 10:23 am
jasona.work - Friday, March 24, 2017 9:58 AMNot 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/
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
March 24, 2017 at 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.
The are no problems, only solutions. --John Lennon
March 24, 2017 at 10:38 am
I spoke to soon. That link looks like it might work.
Thanks Jason.
The are no problems, only solutions. --John Lennon
March 24, 2017 at 10:39 am
Lord Slaagh - Friday, March 24, 2017 10:35 AMHi 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.
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
March 24, 2017 at 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.
The are no problems, only solutions. --John Lennon
March 24, 2017 at 11:29 am
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
March 24, 2017 at 1:53 pm
cphite - Friday, March 24, 2017 11:29 AM2. 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
March 24, 2017 at 2:58 pm
A different solution is to use views or derived tables instead of CTEs.
March 24, 2017 at 2:58 pm
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
March 24, 2017 at 3:16 pm
Luis Cazares - Friday, March 24, 2017 2:58 PMA 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
March 24, 2017 at 3:46 pm
SQLRNNR - Friday, March 24, 2017 3:16 PMLuis Cazares - Friday, March 24, 2017 2:58 PMA 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.
March 27, 2017 at 6:27 am
Lord Slaagh - Friday, March 24, 2017 10:51 AMThis 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.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply