6 Replies Latest reply on Jul 23, 2018 3:27 AM by Scott Carman

    How to aggregate a data connection to row level data???

    Scott Carman

      Morning all,

       

      Ok so I have a question - How am I supposed to aggregate my data so that it is most efficient in my viz?  Right now it is slow, slow, slow and I believe it has to do with a LOD expression and too many ATTR that I am using.  In case your wondering - yes, I did extract my data!

       

      What I am most interested in for this data set is the period of performance information which is the version number, start and end dates.  Any financial information can be pulled from a different table though a join.  But I think the financial information is part of the problem why I can't see the data at the row level that I want because every time there is a transaction against a contract number, it creates a new row in the table.

       

      I have tried to use 3 LOD expressions at once to achieve the max version number, min start date, max start date.  That works but the performance in a dashboard is slow.  I have also tried a calculated field to show the max and mins and that works but when I blend that data with the financial data, the filters don't connect across the data sources.

       

      I have also tried to pull multiple occurrences of the same table via a customSQL statement "SELECT DISTINCT CONTRACT NUMBER...".  That works but I am not sure I have the joins correct to do what I want. 

       

      I was watching a YouTube Video on shaping data. I think if I made a couple of custom views I can make this work. The problem is that I don't know how to do this at the data source level.

       

      • So if I pull data in to the following data source:
        • Data Source #1: Select distinct of the contract number,
        • Data Source #2: Select distinct of the contact number with the max and min date.

       

      Attached is a made up sample of my source data.  If this is what your source data was, what would you do?

       

      I am using Tableau version 10.5

       

      Thank you in advance for your help!

       

      Scott

        • 1. Re: How to aggregate a data connection to row level data???
          Don Wise

          Hi Scott,

           

          Perhaps I'm over simplifying this, but then again, I'm a bit of a simple guy sometimes. Your desire is to take an existing data set which has multiple contracts, multiple versioning with multiple start/end dates and aggregate it to a more simpler view, going from this:

          Sample Data.png

          To this:

          Desired Result.png

           

          Which I was able to achieve in Tableau by simply using existing Min/Max for each dimension, which would be a bit more simple than multiple LOD's.

          Screen Shot 2018-07-22 at 10.32.31 AM.png

          Please see attached 2018.1 version workbook.  If this is helpful or correct, please mark this response as such so that others may find it useful in the future.  Thx, Don

          • 2. Re: How to aggregate a data connection to row level data???
            Scott Carman

            Don,

             

            I am a simple guy to and you, my friend are exactly correct!  Except here's where I am at the cross roads, is there anyway to add a filter to the datasource so that only the max and min dates for each contract are shown or is a LOD express or a min/max calculation the only way to achieve my goal?

             

            Scott

            • 3. Re: How to aggregate a data connection to row level data???
              Don Wise

              Hi Scott,

              Okay I now get what you're after.  So yes, you can use Tableau Prep to get to what you want.  Tableau Prep is a part of your maintenance, so download and use the attached .flow file and steps as necessary from the following screenshots.  The flow I provided should work for your data source tho'. 

               

              You can subsequently save the (work)flow output as .hyper or .csv file for subsequent use in Tableau.

               

              This work took me all of 5 minutes to do.  In the future you would open the established flow, click the refresh button and get fresh values from your data source and then a new output file.  I've attached all necessary files.  Hope this is the solution you're looking for!  Thx, Don

               

              Drag Sample Data To Pane.png

              Aggregation Steps.png

              Final Output.png

              • 4. Re: How to aggregate a data connection to row level data???
                Scott Carman

                Don,

                 

                I so appreciate your answer and efforts but there is only one issue - no Tableau Prep!  Dang!  My company is waiting on the next major update to test Tableau's software to make sure that is safe.  They take a conservative approach to say the least.  I talked to our administrator a few days ago and he said that he hopes in the next few weeks we would have it.  He did say that it has taken longer than expected.

                 

                So I'll keep your answer for that magical day we get Tableau prep and I wish my answer was that easy.  Any other suggestions?

                 

                Thanks,

                 

                Scott

                • 5. Re: How to aggregate a data connection to row level data???
                  Don Wise

                  Hi Scott,

                  You could use the 14-day free trial for Tableau Prep located here:  Download Tableau Prep  Aside from Tableau Prep, there are some other ETL (Extract, Transform, Load) software tools on the market such as Talend or Matillion or FME (Safe Software) or Alteryx; each having their usefulness, but each are also quite different in terms of capabilities and of course cost...hopefully your folks can make the transition to Tableau sooner than later !  Thx, Don

                  • 6. Re: How to aggregate a data connection to row level data???
                    Scott Carman

                    Don,

                     

                    I appreciate your time and knowledge in helping me get to my goal.  I was hoping that the answer was going to be different than this.

                     

                    Thank you,

                     

                    Scott