4 Replies Latest reply on Aug 2, 2017 12:17 AM by Rajtilak Bhattacharjee

    How to store large data sets for benchmarking in Tableau?

    Rajtilak Bhattacharjee

      We are creating a benchmark analysis in Tableau for around 5000 employees. We have two sets of files, one for Actuals, another for the Benchmarks. I have attached a basic sample of the same. Let's say this file is for one employee. There would be another 4999 files in similar formats. My quetion is two-fold:

       

      1. At present, I am creating a Union of two worksheets, and then doing the benchmark. Please see attached Tableau dashboard for reference. Is there a better way to do this?

      2. Since this is for one employee, how will I feed in the data for another 4999 employees? These files are in .xlsx format. The individual files are not that heavy, however, in bulk they are simply not getting loaded into Tableau. How should I go about it? Will uploading to Big Query or likes help?

        • 1. Re: How to store large data sets for benchmarking in Tableau?
          Peter Fakan

          Hi Rajtilak,

           

          This will be a nightmare to connect 5,000 worksheets (if this is even possible), I'm assuming that your Emp ID field is the primary key.

           

          I'd suggest breaking off the benchmark (is this a KPI ?) into another sheet if this is fixed, otherwise you could just add the column to the Actual table as pictured.

          Doing some quick calcs, the maximum row limit in Excel is 1,048,576 rows. If you put all of your staff in one worksheet (and had 7 day operations) you would fill the worksheet every 29 days.

           

          Either break this up by Employee ID groups (e.g. Emp ID 1000-1500), or by their natural work groups.

           

          As for databases, any of them will handle this data and you would lose the restriction on row limits.

           

          HTH

           

          Peter

          • 2. Re: How to store large data sets for benchmarking in Tableau?
            Rajtilak Bhattacharjee

            It is indeed a nightmare! To reply to your query,Unit is a KPI. And there are about 20-30 KPIs. Against each KPI we have their respective benchmarks in another Worksheet. So I am not sure whether combining them into a single Worksheet would be a feasible option. So the other option I have is if I simply uploaded all these Worksheets into an Access database. Will that help? And after uploading it into the database, should I create a Union while connecting the Actuals and Benchmark data? Because a Join doesn't seem to work.

            • 3. Re: How to store large data sets for benchmarking in Tableau?
              Peter Fakan

              If you are going to the bother of creating an access database, you could consider including the KPI in the same dataset so that you don't have to do any joins, but don't let that stop you - either method will work fine for this size dataset.

               

              The union will be on employee id in both tables in Access.

               

              HTH

               

              Peter

              • 4. Re: How to store large data sets for benchmarking in Tableau?
                Rajtilak Bhattacharjee

                Thanks so much for your help. I created two different tables without any PKs in Access. Post that, while uploading on Tableau I used the following Custom Query:

                 

                Select * from Actuals

                Union All

                Select * from Benchmarks

                 

                This seem to work well on a small dataset. Let's see how it works on a larger dataset.