1 2 3 4 Previous Next 56 Replies Latest reply on Aug 8, 2017 6:54 AM by Revathy Shanmugam Go to original post
      • 15. Re: Editing workbook XML after publishing to Server
        Joe Mako

        Thanks to a Twitter connection, I was pointed to the thread Customized views API questions by Tamas Foldi .

         

        It proved most helpful!

         

        Here is what I found out:

         

        1. use the instructions at http://kb.tableausoftware.com/articles/knowledgebase/creating-custom-administrative-views

         

        This will set a password for the "tableau" user, and get you connected to the Tableau Server database (you could say this step in not really needed, but it was fun to do anyways )

         

        2. follow Tamas' instructions to make a super user (at this point it should be clear that this is NOT SUPPORTED, this is at your own risk, and every other disclaimer you can think of, this ONLY FOR NON-PRODUCTION use). I chose to make a new user with the superuser role instead of editing the tableau user.

         

        3. read about Large Object functions: http://www.postgresql.org/docs/8.3/static/lo-funcs.html and pg_largeobject: http://www.postgresql.org/docs/7.4/static/catalog-pg-largeobject.html

         

        4. Look at the table "workbooks" and the fields "repository_data_id" and "repository_extract_data_id", these are the ids for the .twb files that have been published. If you published a .twb, it will be in the "repository_data_id" column, and .twbx will be in the "repository_extract_data_id" column. So far, it looks like the .twbx in the pg_largeobject is only the .twb inside a zip file, I believe the .tde has been stripped out. (I am still poking around so I do not know any facts, just guesses and trial and error at this point)

         

        (for example, lets say the workbook wanted was published as a .twb and has an repository_data_id of 2706)

         

        5. run the following query:

         

        SELECT lo_export(repository_data.content, 'c:\\twb_export\\workbook_name.twb')

        FROM repository_data

        WHERE repository_data.id = 2706;

         

        The user that runs this query must be a super user.

         

        And that pulls the Tableau .twb XML from the Tableau Server!

         

        Next I need to figure out how to how to use the lo_ functions to update a large object.

        • 16. Re: Editing workbook XML after publishing to Server
          Tamas Foldi

          Hmm, there are a lot of possible solutions and hacks to your problem I guess. Here are the ones which are in my mind:

           

          1) Dimitri's solution, to use a small mat view or even a normal view and join it should be a good one. To join a 7 rows table even with billions of records should be no problem for a modern database.

           

          2) Modify the XML with tabcmd. You can write a small script to automate the process, retrieve the workbook, apply some sort of regexp search and replace stuff, and then re-publish the whole thing back to the server. Tabcmd will practically do the same as we do, read the XML from the database or from cache, and push it back. If you do not really like to script things on windows, you can use my instructions how to use tabcmd in linux/unix (http://community.tableau.com/thread/118422) and write a nice shell/ruby/python script for this purpose

           

          3) Instead of tabcmd, you can write your own script which takes the XML (even from the database or from the rest web rest interface), and publish the things back (thru db or web). If you choose direct database connection, you _may_ have problems with internal caching. For the database version you should also have some special grants which are not really problem, just an additional step. And yes, it is unsupported.

           

          4) This one is the biggest hack, thus, this is my favorite one. You can do the whole XML modification inside the tableau server's repository with stored functions. You can create a pgplsql (or any other trusted or untrusted stored language like plpythonu) stored function, which modifies the large object. This would be very easy since you are inside the database, thus you can work on LOs directly. This function can be called/used as a table (e.g.: select your_function()), so if you create a report on the top of this function, then you can execute the XML modifications, grab the function return values/log messages and show it on a nice tableau viz. Also, you can schedule it with tableau as well at every midnight. Drawback is the caching as usually, but if there are no modifications in the UI regarding your dashboards then it will not cause problems. I will also check the viz "api" how to flush the viz cache.

          2 of 2 people found this helpful
          • 17. Re: Editing workbook XML after publishing to Server
            Joe Mako

            Tamas, thank you!

             

            Your point 4 sounds ideal, can you point to an example of a stored function that does a find and replace on a large object?

            • 18. Re: Editing workbook XML after publishing to Server
              Tamas Foldi

              sure, an example how replace some chars from pure SQL:

               

              C:\Program Files (x86)\Tableau\Tableau Server\7.0\pgsql\bin>psql -p 8060  workgroup  tblwgadmin

              psql (9.0.5)

              WARNING: Console code page (437) differs from Windows code page (1252)

                       8-bit characters might not work correctly. See psql reference

                       page "Notes for Windows users" for details.

              Type "help" for help.

               

              workgroup=# \! type c:\temp\foobar.txt

              hello bello

              foo bar barfoo

              workgroup=# select lo_import('c:\\temp\\foobar.txt');

              lo_import

              -----------

                   25527

              (1 row)

               

              workgroup=# select encode(data,'escape') from pg_largeobject where loid =  25527;

                    encode

              ------------------

              hello bello \r  +

              foo bar barfoo\r+

              (1 row)

               

              workgroup=# update pg_largeobject set data =  DECODE(TRANSLATE( encode(data,'escape'), 'bhf','BHF'),'escape') where loid =  25527;

              UPDATE 1

              workgroup=# select encode(data,'escape') from pg_largeobject where loid =  25527;

                    encode

              ------------------

              Hello Bello \r  +

              Foo Bar BarFoo\r+

                (1 row)

               

                 workgroup=#

              From plpgsql it would be similar, if you'd like I can test it with my own workbooks.

              1 of 1 people found this helpful
              • 19. Re: Editing workbook XML after publishing to Server
                Joe Mako

                Thank you for the example and all your help on this, now I need to learn the basics of plpgsql :)

                • 20. Re: Editing workbook XML after publishing to Server
                  Andy Cotgreave

                  hey joe

                  i followed your steps and got a TWB file - but it's in a binary format, not XML that I know how to read. Any idea how to fix that?

                   

                  BTW - great instructions from you and Tamas - clear and easy to follow.

                   

                  Thanks

                  Andy

                  • 21. Re: Editing workbook XML after publishing to Server
                    Joe Mako

                    Andy,

                     

                    Same thing happened to me when I used Tamas' queries, and why I included the example:

                     

                    SELECT lo_export(repository_data.content, 'c:\\twb_export\\workbook_name.twb')

                    FROM repository_data

                    WHERE repository_data.id = 2706;

                     

                    For me, this query returns the XML with the published workbook was a .twb, and a .twbx if published as a packaged workbook. What is an example query that you are using?

                    • 22. Re: Editing workbook XML after publishing to Server
                      Andy Cotgreave

                      I was using the exact query you're using (with a different workbook id)! let me go check it again.

                      • 23. Re: Editing workbook XML after publishing to Server
                        Andy Cotgreave

                        I am running the same query as you, on a workbook i just published as a twb and I get the attached. Here's the query:

                         

                        SELECT lo_export(repository_data.content, 'c:\\temp\\workbook_name.twb')

                        FROM repository_data

                        WHERE repository_data.id = 484;

                        And i attached the twb file that is generated. I'm not missing anything obvious am I? When I open the file in a text editor I just get binary garbage.

                        • 24. Re: Editing workbook XML after publishing to Server
                          Joe Mako

                          That is not the id that you want, you will want to take a closer look at your tables.

                           

                          What you attached was a .png

                           

                          Message was edited by: Joe Mako

                          • 25. Re: Editing workbook XML after publishing to Server
                            Tamas Foldi

                            I have created a small plpgsql package to export one or more workbook into twb/twbx files. Source code attached.

                             

                            Usage:

                             

                            select save_workbook( sitename, projectname, workbookname, outputpath );

                             

                            To export Workbook1 from Default site, Published project into C:\temp\Workbook1.twb:

                             

                            select save_workbook('Default','Published','Workbook1', 'C:\\temp\\');

                             

                            You can use wildcards (%), so to export all workbook like Workbook* (Workbook1, Workbook2, etc) from all sites and all projects, use:

                             

                            select save_workbook('%','%','Workbook%', 'C:\\temp\\');

                             

                            Hope this helps

                            • 26. Re: Editing workbook XML after publishing to Server
                              Andy Cotgreave

                              Hi Tamas

                              That's a nice script. There's a syntax error on line 36 - should be ".TWB" not ".TBW".

                               

                              I got a bunch of files out, but i still have the text encoding problem - see the attached. Any ideas?

                              • 27. Re: Editing workbook XML after publishing to Server
                                Joe Mako

                                Andy, that is a .twbx, change the extension, it Tableau will open it just fine.

                                 

                                Did you know you can open any file in a text editor, and the first few characters will commonly clue you in on what the extension should be?

                                • 28. Re: Editing workbook XML after publishing to Server
                                  Tamas Foldi


                                  That's a nice script. There's a syntax error on line 36 - should be ".TWB" not ".TBW".

                                   

                                  True, it was a typo. Anyway, the result can be twb or twbx and from the repository I cannot really read which type of file is stored in the repo. Tableau guys, any idea? I can still try to read the first two characters (zip or xml header), but it would be quite unclean.

                                   

                                  I got a bunch of files out, but i still have the text encoding problem - see the attached. Any ideas?

                                   

                                  Yes, if you have extract or linked excel stuff then the file will be twbx, in other cases it will be simple twb. Try with both extensions

                                  • 29. Re: Editing workbook XML after publishing to Server
                                    dora dora

                                    Hi Tamas,

                                     

                                    Thank you for the info. But I do need some help. I'm pretty new to tableau and the guy who setup the tableau server and everything is no longer with the company and I have no clue where to look.

                                     

                                    The main  problem is with our date filter. We want some way to refresh our date filter to current date (we don't want the relative date filter).

                                    I read somewhere in this forum, that  I can write a script or use some stored functions and update the dates in all the xml files. Probably I can even scheduled it to every night.

                                     

                                    My question:

                                    1) How to connect to tableau internal postresql database.

                                    2) How to setup the stored function to update the xml file.

                                     

                                     

                                    any help highly appreciated !!!