11 Replies Latest reply on Jan 17, 2012 2:11 PM by Richard Leeke

    Help Testing Map

    Shawn Wallwork

      Hi all, I could use some help testing this workbook. Richard worked out a way to put a circle over a filled ZIP code map. Originally it was slow to display, but as I've worked with it, it has gotten much faster. I'd really appreciate it if you would test it on your computer.


      On my computer it takes about 12-15 seconds when it first loads. Then changing the Demographics or the Miles Radius takes about 5 seconds most of the time, but 15 second every now and then. When highlighting a ZIP code all three panels update instantly.


      Please let us know how well it works on your computer. Thanks --Shawn

        • 1. Re: Help Testing Map
          Shawn Wallwork

          Forgot to mention this is in 7.0 so only Beta testers will be able to open this. --Shawn

          • 2. Re: Help Testing Map
            Richard Leeke

            I get about the same response times as you.


            The initial 12-15 seconds is mostly Tableau loading the entire zipcode geocoding data (for all countries) so that it can match them up.  It then caches those, which is why it's so much faster as you then browse around.  Tableau occasionally flushes the cache (just depending on other memory usage) which is why it sometimes takes 15 seconds as you browse around.


            Something which is interesting is that because of the way that the geocoding data is structured it actually loads 8 copies of US zip code data and multiple copies of other countries' zip or postcode data - which means it caches nearly half a million rows of geocoding data when you first display it.  Whilst that's not bad, there is a very grubby under-the-covers unsupported hack you can do to make that much faster - I'll explain and/or post back an example later.

            • 3. Re: Help Testing Map
              Shawn Wallwork

              Richard & Joe thanks for testing it. Appreciate the help. --Shawn

              • 4. Re: Help Testing Map
                Richard Leeke

                Here's the snappier version I promised. 


                Basically all I've done is created a custom geocoding database by adding a new (dummy) geographic role.  It just has a single row, with the location of the store.  I've added a sheet referencing that role, to force Tableau to use the custom geocoding database instead of the default database for the workbook.


                Having got Tableau to use a custom geocoding database, I then purged all geocoding data except for the 50 odd Zip Codes referenced from your viz.  So now instead of loading half a million rows of gecoding data in order to look up the shapes and locations of 50 Zip Codes it now only has to load the 50 rows you are interested in.  So that phase now takes a few milliseconds rather than 10 seconds or more.



                I just happen to have been exploring how this all works recently to see what else can be achieved with filled maps - so I've automated the whole process, which means this was pretty trivial to do.  I feel a bit of a blog posting coming on sometime soon on all of that.


                As I say, it is an unsupported hack - but it can make the workbook noticeably more responsive, and it reduces the memory footprint for the geocoding cache significantly, which I guess probably frees up more memory for Tableau to do other things with, if you have a demanding workbook.  Although it's a hack, it's completely non-destructive.  Worst comes to the worst, if there's something wrong you just need to remove the custom geocoding and you're back to how it was.  The only change I made to the actual workbook was to add one calculated field for the geocoding field.

                • 5. Re: Help Testing Map
                  Jerry Wetherall

                  For me, loading took about 14 seconds, demographic change 3 seconds, radius change 2 seconds, and zipcode change a split second.

                  • 6. Re: Help Testing Map
                    Shawn Wallwork

                    Jerry, thanks for testing. I really appreciate the help.


                    Richard, very snappy indeed! I'm very interested in reading a blog about this. A step-by-step would be great, because I'm not sure how you go about 'purging' the geocoding data to get to the target ZIP codes only. And it's definitely worth the extra effort, especially when the cache flushes in the middle of a session. Thanks for the help and the education.



                    • 7. Re: Help Testing Map
                      Shawn Wallwork

                      Richard, the custom geocoding persists even in newly created workbooks until specifically removed.  Once removed the new workbooks are able to display ZIP codes outside of Colorado Springs, so all is well. But interestingly after removing the geocoding in a new workbook I closed it and then opened the latest version of the workbook you hacked (attached), and there isn't any geocoding listed, yet the workbook performs super fast. Obviously there is a lot more going on here than I know/understand. But I don't think removing geocoding in one workbook should effect another workbook. (I'll leave that to you to mention to the Beta Team if you think it's relevant.)


                      I don't see any real problem with the workbook as long as I leave as is, which is not a problem. But I am now more curious than ever to find out how this hack works. I've got ten more markets to do :) BTW, I turned your Store Location sheet into the Single Point sheet (since unless I used it in a dash I wasn't going to be able to hide it from the client).


                      This project gets more and more interesting at every turn.



                      • 8. Re: Help Testing Map
                        Richard Leeke

                        > the custom geocoding persists even in newly created workbooks until specifically removed.


                        Hmmm - I can't see how.  How did you create the new workbooks?  Did you copy them from the "hacked" one or start from scratch?  Did you still have the "hacked one open?".


                        I'll just explain what I understand about where Tableau is getting its geocoding data from - maybe that will let you work out what happened.  But this really demonstrates that it's unwise to do unsupported stuff like this.


                        Normally, if you don't define any custom geocoding, Tableau gets all of its geocoding data from a database in the Tableau installation directory.  I never touch that.


                        When you define custom geocoding Tableau creates a clone of the basic geocoding database and adds in your custom roles.  That gets stored in your personal respository and will be referenced by any workbooks that use geocoding.


                        When you save a workbook as a packaged workbook, Tableau checks to see whether the workbook uses any of your custom geographic roles.  If it doesn't use any geocoding, or only references the built-in roles, then the workbook will work fine on any system and Tableau doesn't need to do anything special to make the workbook transportable.


                        But if it does use custom roles, Tableau has to bundle up a copy of your custom geocoding database as part of the packaged workbook, so that the workbook will be useable on another system.  When the packaged workbook is opened on another system, the contents are unpacked into a temp directory and Tableau uses the custom geocoding from there for that one workbook only.  But it shouldn't have any impact on any other workbooks.


                        When you use the Import or Remove Custom Geocoding menu options you are dealing with the version in your repository.  But I don't see how my hacked geocoding database could have got itself installed there.


                        The reason that the modified version of the "hacked" workbook is still fast is just that the workbook still references the custom geographic role, so Tableau continues to bundle my hacked copy of the database.  If you change the geographic role of the Store Location field to none and resave the workbook it will no longer need to reference the hacked workbook, so will revert to the default geocoding database.


                        In terms of how it works - the custom geocoding is held in a local firebird database and I "just" pared it back to the essentials for this particular viz by deleting unreferenced rows.  You might remember that I posted about Filled Maps on the beta forum a while back with some ideas about custom geocoding.  That was talkig specifically about filled maps, but also talking about the fact that as more geocoding is built into the product the geocoding database would grow and suggesting some ideas about exposing control of the amount of geocoding data to the user.  Well this is just the results of me having a bit of a dig around and seeing what I could achieve "via the back door".


                        But I'm really feeling that I've opened a can of worms here and shouldn't have mentioned it.  Without support built into the product I really wouldn't advocate using this technique for client work. The way geocoding works is bound to change in future releases, so this sort of hack won't work going forward. And if you ever have any issues with geocoding, the first thing you'd need to do is remove the hack to make sure that I hadn't broken something. You don't want to go setting expectations with your client that you can't continue to deliver.


                        I really only posted the example to demonstrate where the time was going.  I thought it was going to be completely self contained in that one workbook - but it seems there is a lot more scope for confusion here than I realised.

                        • 9. Re: Help Testing Map
                          Shawn Wallwork

                          Got it Richard, thanks for the explanation, it makes sense. FYI: Started with a new workbook, but the hacked one might have been opened, I just don't remember. It could also have been open when I removed the custom geocoding in the new workbook as well. I'll mess around a bit with importing and removing geocoding, and see if I can figure anything out.



                          • 10. Re: Help Testing Map
                            Shawn Wallwork

                            So Richard do you know where the ZIP code boundaries (polygons) are being stored? We opened up GOECODING.FDB and didn't find anything there. --Shawn

                            • 11. Re: Help Testing Map
                              Richard Leeke

                              > I'm really feeling that I've opened a can of worms here and shouldn't have mentioned it...


                              Yes I've worked out how that's all structured and have been experimenting with it, but there's absolutely no guarantee that it won't change in future versions (in fact it's almost certain that it will), so I really don't think it's a good idea to mess with it.


                              Drop me an email at richard(dot)leeke(at)equinox(dot)co(dot)nz and I'll try harder to persuade you not to go there.  ;-)