10 Replies Latest reply on Jan 26, 2018 6:08 AM by Karel Sedlacek

    Writeback to reporting database in Tableau 8 - hack or feature?

    Tamas Foldi

      As in several other BI tools in Tableau 8 we have the chance to substitute parameters in Custom SQL, then practically we can store user input to database. Let me just show an example.

       

      Lets assume we have oracle with the following extra objects in SCOTT schema:

       

      CREATE table tab_comment ( val VARCHAR2(10) );

       

       

      CREATE OR REPLACE FUNCTION fnc_save_my_parameter (p_val VARCHAR2)

        RETURN VARCHAR2 IS

        PRAGMA AUTONOMOUS_TRANSACTION;

      BEGIN

        IF p_val IS NOT NULL THEN

          MERGE INTO tab_comment

               USING (SELECT p_val nv FROM DUAL)

                  ON (1 = 1)

          WHEN MATCHED THEN

            UPDATE SET val   = nv

          WHEN NOT MATCHED THEN

            INSERT     VALUES (nv);

       

          COMMIT;

        END IF;

       

        RETURN 'TRUE';

      END;

       

      And we have the following Custom SQL in Tableau:

      tableau writeback.png

       

      Then, whatever you input into the parameter input box will be stored in the database, together with your reporting data. With some functions you can add user security and other things which could make it more advanced.

       

      My questions is, what do you think? Is there any scenario when it could be useful? Like administrators can configure some visualization constants for all the vizes from the tableau server by using a single worksheet? Any other thoughts?

        • 1. Re: Writeback to reporting database in Tableau 8 - hack or feature?
          Toby Erkson

          Tableau is a reporting tool and it should remain that way.  Building in functionality to write to a db is fraught with danger and makes many a dba nervous.  I don't want to be a db developer/admin nor even have a glimmer of such functionality for my end users.  I'm totally against it.

          • 2. Re: Writeback to reporting database in Tableau 8 - hack or feature?
            Tamas Foldi

            DBA should not be nervous at all. Database security (what to write where) is absolutely not a front-end question, so regardless if your using tableau or something else, your database user should reflect your effective rights.

             

            If you have a user which CAN write into the database due to database security, then even if Tableau "denies" the writeback, you can always log on to the database even from standard microsoft tools and change data. Therefore, if you have rights then your can use this feature, otherwise you cannot. It's about the rights, and not about the tool or feature.

             

            I think adding possibility for persistent annotation is generally a good idea and in Tableau 8 with the some tricks we are able to come with this functionality. But again, it should not change your administrators mind, as they should keep the database security regardless the frontend tool features.

            1 of 1 people found this helpful
            • 3. Re: Writeback to reporting database in Tableau 8 - hack or feature?
              Toby Erkson

              Yes Tamas, I understand that.  For advanced server admins such as yourself that may be a neat thing but then the overall ease-of-use that is Tableau diminishes.  I'm still against it.

              1 of 1 people found this helpful
              • 4. Re: Writeback to reporting database in Tableau 8 - hack or feature?
                Russell Christopher

                I'm with Toby. While this is a fun hack, it opens up a huge can of worms - It enables SQL injection attacks, and forces anyone who uses this technique to implement lots of logic to make sure the user doesn't do anything "stupid" inadvertently and wreck the underlying data.

                 

                There are other techniques which are better for this sort of work

                1 of 1 people found this helpful
                • 5. Re: Writeback to reporting database in Tableau 8 - hack or feature?
                  Tamas Foldi

                  There are other techniques which are better for this sort of work

                  Could you please share a few with me? With my method I can add support for row level persistent annotations, like comments for individual points in graphs.

                   

                  I'm with Toby. While this is a fun hack, it opens up a huge can of worms - It enables SQL injection attacks, and forces anyone who uses this technique to implement lots of logic to make sure the user doesn't do anything "stupid" inadvertently and wreck the underlying data.

                   

                  First of all, I cannot repeat enough time, if you have a database user and you can attack the database, then the database security is obviously wrong. Second, if Tableau Software wants to deny this behaviour then they probably design this new data stuff differently. This implementation give space a lot of interesting cases like this one.

                   

                  1. To avoid database writing from Tableau the best would be to use a read only connections. Many database servers (oracle, sql server, teradata, etc.) supports read only sessions, so Tableau might be able to switch the user's session to read only mode. The problem, that it cannot. Sometimes it requires to create temporary tables, like for contexts, etc.. My most wanted feature in Tableau is to use WITH inline views instead of temporary tables, but until that, you should live with a read-write sessions or do a lot of extracting.

                   

                  2) SQL Injection. Yes, if someone create a function which is totally against the security rules, then there might be a chance for sql injection. But as Tableau itself also escapes the strings (and you cannot inject with boolean and integer types which are not escaped) the chances are reduced to zero. If for some reason there will be a problem, then it will be inside Tableau as it allows to pass unsecure strings to the database.

                   

                  And for this, let me ask why Tableau 8 passes my strings directly to the SQL instead of using bind variables where it supported? Is there any reason for that? From security perspective it is more reliable, from performance point of view it is unclear (SQL reparse times vs. effetiveness of new execution plan).

                   

                  And now the tricks.You can have tables/views/synonyms which starts and ends with ' (apostrophe sign), thus you can create a parameter which dynamically changes the table under the vizualization. It is not a big deal but in some cases you can do some pretty stuff with this (change schema/table/fields and the best part: even change databases from parameters). The other trick relies on Oracle's MODEL clause where you name your measure and dimension columns with ' and not ", thus, your parameters can real time change your SQL's MODEL behaviour.

                  • 6. Re: Writeback to reporting database in Tableau 8 - hack or feature?
                    Russell Christopher

                    Other ways to write to databases:

                     

                    Ryan's idea is cooler than mine, but they both work:

                     

                    http://tableaulove.tumblr.com/post/27627548817/another-method-to-update-data-from-inside-tableau

                     

                    Writing to the database is an edge case scenario in my opinion. The points that you raise are all valid, but I personally would rather the dev team work on features that more people are asking for -- Mac support, for example - or Server running on Linux.  To make write-back work in a fast, friendly, and reliable manner just  isn't worth the time in my opinion.

                    • 7. Re: Writeback to reporting database in Tableau 8 - hack or feature?
                      Tamas Foldi

                      Ryan's idea is cooler than mine, but they both work:

                      First of all, restful service is a fine method for storing user supplied data. BUT. If we are speaking about security, than this solution is like a disaster, in the example there are no authentication at all. The only secure solution is to deploy this restful service to Tableau's own application server and use its own authentication method - otherwise it is more complicated and less secure.

                      . The points that you raise are all valid, but I personally would rather the dev team work on features that more people are asking for -- Mac support, for example - or Server running on Linux. 

                       

                      Yes, agreed. However, I am running one of my Tableau server since a while in Linux and works pretty good. Some of the servers run natively on Linux (with some mods) while others are running winelibs, and performance are comparable. If someone interested I can describe the steps how I am running the things on my linux box.

                      • 8. Re: Writeback to reporting database in Tableau 8 - hack or feature?
                        Eric McDonald

                        Take a look at VWMare Fusion (http://www.vmware.com/products/fusion/overview.html) which lets you run Windows on a Mac - it even lets you run a single window in Mac OS so should let you run Tableau on a Mac without seeing the Windows o/s. VMWare Fusion is like Parallels but looks a lot better.

                        • 9. Re: Writeback to reporting database in Tableau 8 - hack or feature?
                          Chris Gerrard

                          I'm with Toby and Russel - database write-back isn't something Tableau should be doing. I'll go further and say Tableau Software should definitely NOT be taking the products down that path.

                           

                          Purely from a product perspective, there's an awful lot of work to be done with the analytical side of things to be diverting time, attention, and resources away from continuing to perfect the product's reason for existing.

                           

                          Pushing the responsibility for ensuring that nothing untoward happens to the database down onto the DBA's shoulders in terms of providing and managing the access controls misses a huge point: people who use tools that aren't fully featured in terms of ensuring that nothing bad happens, either through design flaws, side effects, or any number of others are huge liabilities. Even more so when the tools are tempting enough to use by people who aren't in a position to understand the full scope and consequences of their actions.

                          • 10. Re: Writeback to reporting database in Tableau 8 - hack or feature?
                            Karel Sedlacek

                            Tamas,

                            I am very interested in seeing the custom sql example.  The png link is broken and I can't view the code.

                             

                            Best,

                            Karel

                            kvs1@cornell.edu

                             

                            Like all the work you have done.