9 Replies Latest reply on Sep 26, 2013 1:16 PM by Shawn Wallwork

    Using Stored Procedures (Openquery) as Data Source?

    Matt Lutton

      I've been asked by a colleague to check and see if anyone is using Openquery, or a similar approach as discussed in the following link (see Approach #2):

      Using Stored Procedures and Functions | Tableau Software

       

      Anyone that has experience, please contact me in this thread or via email at mluttonATgoodwilleducationDOTorg

       

      Thanks!

        • 1. Re: Using Stored Procedures (Openquery) as Data Source?
          russell.spangler

          I have used openquery numerous times to execute SQL queries, but I find it a lot easier to put SQL code into a stored procedure and just make a simple call to the stored procedure to get the data I need (similar to Approach #2 in your link).  Also that way everything is on the SQL server which I think makes things a lot easier to manage. 

           

          Do you have a detail question you want to ask?

          1 of 1 people found this helpful
          • 2. Re: Using Stored Procedures (Openquery) as Data Source?
            Matt Lutton

            No, just looking for feedback from other Tableau users, as a colleague was interested in the idea.  We have had a number of issues trying to get Tableau to place nice with our data so I was just looking for other people's input

             

            You say you have used Openquery to execute queries--have you done this or used stored procedures in conjunction with Tableau? 

            • 3. Re: Using Stored Procedures (Openquery) as Data Source?
              russell.spangler

              I've done both, but prefer executing stored procedures, because openquery isn't the best, and if you have a lot of queries in Tableau workbooks, it makes it very difficult to manage.  If everything is on the SQL server side then you don't have code floating around outside of your server.  So I either use openquery to execute a stored procedure on the SQL side, or use stored procedures to populate tables on schedule jobs and connect Tableau directly to the table, I rarely write SQL within Tableau.

               

              As you noted, Tableau doesn't play nice sometimes, so I find prepping all the data before making the Tableau connection is the best approach and makes it so all the code is accessible in SQL (and also I can use source control to track my code changes easier).

              1 of 1 people found this helpful
              • 4. Re: Using Stored Procedures (Openquery) as Data Source?
                Matt Lutton

                Thanks, that is the type of feedback I need.

                • 5. Re: Using Stored Procedures (Openquery) as Data Source?
                  Russell Christopher

                  Both techniques work well if your goal is to populate a Tableau extract.

                   

                  I find these approaches can be somewhat sub-optimal if you are connecting "live" to the data for the same reasons why using Custom SQL can sometimes negatively impact performance. (Search on "Custom SQL Performance" and you should find this topic discussed at length).

                   

                  Good luck!

                  1 of 1 people found this helpful
                  • 6. Re: Using Stored Procedures (Openquery) as Data Source?
                    Julia Hennelly

                    We are looking at passing filter parameters from Tableau to a stored procedure; having the stored procedure do the necessary calculations before having Tableau access the result set and display.  Would be really interested to talk to anyone about this.  We basically need to do two passes against the data to get monthly headcount (with filters applied) and then average it over the last 12 months.  We believe we can achieve this with Tableau and a Stored Procedure?  Wondering about performance?

                     

                    Anyone does this?

                     

                    Julia

                    • 7. Re: Using Stored Procedures (Openquery) as Data Source?
                      russell.spangler

                      My experience is that Tableau doesn't allow certain SQL commands in the custom SQL window, like variables, so I don't think you can pass a parameter to a stored procedure using a filter.  Also I think filters are created after data has been pulled into Tableau.


                      I think the best way to possibly approach your problem is pull all the data you need into Tableau, say 36 Months of data, then create calculations/filters to filter down to 12 month windows to be used within your report, using filters to adjust your viz. If you are worried about performance you can use the stored procedure to do all your calculations and use a SQL job to schedule it, that way everything is calculated pre-Tableau.  But, in general Tableau does a great job with calculations, and you can use extracts to improve any performance.

                      • 8. Re: Using Stored Procedures (Openquery) as Data Source?
                        Russell Christopher

                        FYI, 8.1 (due in Q4) will support calling stored procs directly without the use of openquery. Might want to contact you account exec and ask for a beta invite....

                         

                        Sent from my phone -- please excuse brevity, misspellings, and tone

                        • 9. Re: Using Stored Procedures (Openquery) as Data Source?
                          Shawn Wallwork

                          Does anyone know how I can append this to all my forum posts?

                           

                          brevity.png

                           

                          Especially the "tone" part.

                           

                          --Shawn

                           

                          PS: Ok the misspellings is probably a good one to include too.