Please see my comments inline:
I have written the stored procedure and using it in tableau to generate the report, But the report is very slow. I am getting the performance issue. Any Idea why is it like this....?
RS - What is the performance of the SPROC if you pass params locally in SQL Server (assuming that's your data source).
I want to ask one query, can we write the stored procedures in the Custom sql box, i tried but i am getting error....?
RS - You can't writ the entire SPROC in the Custom SQL box.
Could you please help how to improve the performance when i am using Stored procedure, It seems whenever i am using live connection its getting very slow and i cant make it extracted data because i have to create dynamic report .....?
RS - What piece of your report is dynamic and doesn't allow you to extract? A lot of the reports we write are very dynamic.
Thank you for helping me on this.
i have not used any parameter for this report in stored procedure - Please Correct me if i am missing something?
i have deployed my report on the web page and whenever i am making any changes on the front end of web page, this should replicate in the tableau report also. Its taking much time and sometimes i am getting the error "Session ended by user" also.
1 of 1 people found this helpful
You can try the following approach that involves using Initial SQL to write results of the stored procedure to a temp table (this table will be session scoped and SQL server allows all users to create these so dbadmin doesn't have to tweak anything for you) and then doing a select from the temp table.
CREATE TABLE #TestTable ([name] NVARCHAR(60), [ID] INT);
INSERT INTO #TestTable EXEC MyStoredProcedure
SELECT * FROM #TestTable
You can also CTEs directly from Tableau this way as documented here.
Nice thing about this approach is that Initial SQL doesn't run every time you take an action in Tableau. It will run in the beginning and create the table and as you drag fields into your viz or add filters etc. Tableau will query the results that are already stored in the temp table.
I have set up a dashboard with live connection, and I have to use Stored Procedure as a data source (there is no other alternative due to performance considerations, not even UDF). The stored procedure takes 1-2 sec to execute on the SQL Server, and the workbook on the Tableau Desktop seems pretty fast as well. But when I publish the workbook on the server, it takes around 20 sec to load suddenly.
That is when I came across your solution above, and it seemed like worked initially, but then after I refresh the dashboard, it seems like Tableau caches two copies of the data and displays them alternatively on refresh. The data is no longer coming directly from the source it seems.
Is there a way to get the stored procedure working close to its performance in SQL Server?