2 Replies Latest reply on Mar 14, 2013 8:06 AM by Toby Erkson

    Best setup for data connection / import

    Devin Brown

      I'm new to Tableau, and I'm hoping to use it to more efficiently access and analyze data from an MS Access database I have been using to store large amounts of transaction data. Because of the amount and size of the data, I've thus far been keeping the data split into separate quarterly files (roughly 2million rows each), with another database file that simply connects to each of these to work from using joins.

       

      I'm now trying to determine the best way to access this data in Tableau, and I'm having a hard time finding relevant suggestions/instruction. I tried importing one database file and then "extract -> add data from file" to add data from the other files to it, which seemed to work fine, but I will always have one Access file that is being updated each week, so I imagine a linked file would be best for at least that one file. Before I put too much time into this I want to be sure I'm taking the best approach from the beginning. Any suggestions or direction would be greatly appreciated.

        • 1. Re: Best setup for data connection / import
          Devin Brown

          To add a bit more information to this, here is what I'm running into and perhaps someone can help me find the best solution. I've tried to live-link to each of the separate databases, but I can't seem to figure out how to join them all together within Tableau for a seamless data set. Just for giggles, unsure how Tableau would handle such a task, I've also tried live-linking to the database that simply contains the connections to each separate database and consolidates them in a single join query, but it appears Tableau can't live-link to multiple tables in a single Access database that exceed Access' 2gb file limit (even though they are simply links).

           

          I've gone back to looking at importing the first access DB and adding the rest to the extract using "add data from file", but I'm unsure how I will handle the single access file that gets updated with new data each week. I saw an option to "refresh", but got an ominous warning that doing so will replace all of the data. Is this option going to actually re-create the extract from each of the files all over again, each time I use it? How will it handle this updating file being renamed once the quarter is finished, at which point I begin a new updating Access file?

           

          I apologize if these are terribly ignorant questions, but I am having a difficult time finding information that addresses this sort of situation. If there are already resources that cover this problem, please point me in the right direction.  Thanks!

          • 2. Re: Best setup for data connection / import
            Toby Erkson

            I think the MS Jet data engine is your limiting factor, not Tableau.  You probably don't want to hear this (or you probably already know this ) but you'd be far better off using a db application that can actually handle large amounts of data.  Access is good for small databases but becomes painfully weak when you start working with large data sets like where you're heading.

             

            Have you tried data blending?  It's in the instruction manual but basically you plop EACH table into the Data pane and they are connected (you can think of it as "joined") by one or more common fields.  Since it sounds like you have each required table in its own Access db this should work for you, definitely since each Access db is a separate connection.  I did a little bit of data blending a long time ago so I don't remember the actual steps but read about the procedure and you'll get it.

             

            If you solve your issue please let us know!  Then we all learn something and it can help someone else