7 Replies Latest reply on Dec 20, 2016 8:33 AM by Sudhakara Allam

    Refreshing datasources from within worksheet

    Sudhakara Allam

      Hi, I am developing a prototype of using multiple stored procedures for achieving a kind of a blended view. I have two data sources for the prototype.

      Both the stored procedures would have the same parameters. I would like to pass parameters from one procedure to other in a parent/child relationship.

      This requirement could be viewed as sub report with in the main report, though, it does not exactly follow sub report data refresh mechanism. The first thing

      I would like to achieve is to refresh procedure for new parameter values from within SHEET rather than going back to DataSource interface. Technically it should

      be possible as it is just replication of modal dialog of parameters to worksheet level.

       

      The second thing I would like to achieve is to have all the distinct parameters in this dialog so that all the data sources in a work sheet could be refreshed at once

      in a kind of connected way. Could somebody throw pros and cons and also benefits with this approach?

        • 1. Re: Refreshing datasources from within worksheet

          I'm skeptical that this could be achieved in the way you've described, as I believe you would have to pass the procedures on the actual connection and not in the workbook but maybe Toby Erkson knows a bit about this.

          • 2. Re: Refreshing datasources from within worksheet
            Toby Erkson

            I agree with Diego that this couldn't be done or at least easily.  It also sounds like it's being made more complex than it should be.  What's wrong with joining the data sources or using the traditional blending?  And using parameters?  It's okay to use the same data source(s) & same table(s) but as different selectable data sources.

            • 3. Re: Refreshing datasources from within worksheet
              Sudhakara Allam

              My main anticipation is to introduce dynamic SQL procedures and a blend of procedure parameters that create dataset in a more well defined fashion and compliment the parameters and filters from UI. I wanted to check if doing so greatly enhances performance, flexibility between client and server and as well improves the development time and provides more power to the developers.

              • 4. Re: Refreshing datasources from within worksheet
                Toby Erkson

                From what I'm understanding, you would have the SQL change what it pulls according to the parameters as altered by the user at any given moment, correct?

                • 5. Re: Refreshing datasources from within worksheet
                  Sudhakara Allam

                  That is somewhat correct. However, I would like to make generated sql lean and mean which otherwise becomes inefficient if you were to let Tableau engine prepare it for getting the similar results. It may be applicable only few circumstances. It is like complimenting adhoc engine by taking out some of its load as generated sql's get skewed for complicated scenarios. Thus, adhoc engine could be helped by incorporating some dynamic sql mechanisms where ever appropriate and the procedure parameters can compliment Tableau parameters and filters in such cases.

                  • 6. Re: Refreshing datasources from within worksheet
                    Toby Erkson

                    Sudhakara Allam wrote:

                     

                    That is somewhat correct. However, I would like to make generated sql lean and mean which otherwise becomes inefficient if you were to let Tableau engine prepare it for getting the similar results...

                    Oh really?  I'm sure many of us, including the Tableau data/query engine developers, would love to see proof of this!  It's my understanding that Tableau crafts the SQL according to the data source it's connected to in order to get the most responsive result.  It also utilizes cache so if the necessary data is already present it hits that instead of yet another query to the data source (assuming a live connection).  If the data source is an extract then there are even greater efficiencies in play in getting the data.  Also there's the fact that Custom SQL will slow things down in the majority of situations so it's definitely not a default option to jump into.

                     

                    I think this question may need more details and more expert knowledge...

                     

                    • 7. Re: Refreshing datasources from within worksheet
                      Sudhakara Allam

                      I think you mistook my reply. I did not mean the efficiency only towards performance but also on testing and ratifying generated results. At least in few occasions, i felt as if it is ideal to prepare results at server level for productivity and performance too and dynamic sql may help in this regard to comprehend and combine reporting requirements. I am referring to Tableau best practices published by one of the Tableau engineer wherein it is suggested to balance client server processing to the extent necessary based on the reporting environment and underlying data structures. Our reporting is not entirely Analytical so we would have lot of details too. Hope that helps. I too love Tableau.