6 Replies Latest reply on Feb 23, 2016 12:50 PM by Ben Neville

    Tableau report from Cross tab format

    Archana N

      Hi,

      My data source looks as below (Cross tab):

      Company201320142015
      C1768868
      C2927281
      C3845581
      C4519270

      In order achieve the output/report of my choice with filters on year dim, i need the data to be in the below format:

      CompanyYearValues
      C1201376
      C1201488
      C1201568
      C2201392
      C2201472
      C2201581
      C3201384
      C3201455
      C3201581
      C4201351
      C4201492
      C4201570

      I am expecting output as below - with filters on Year:

      Is there a way to achieve this through Tableau (not manually converting cross tab data to de-normalized data).

       

      If it was a small data-set - manual conversion would help ( 4 rows X 3 columns will result in 4*3= 12 rows of table)

      I have 250 million rows X 600 columns in Hadoop's HIVE (which would result in 250M * 600 = 150 Billion) - it's too huge to manually handle this.

      Any idea?

       

      Regards,

      Archana

        • 1. Re: Tableau report from Cross tab format
          Tharashasank Davuluru

          Hi,

           

          Did you try using the pivot option which is available from tableau 9.0

          • 2. Re: Tableau report from Cross tab format
            Ben Neville

            It's the same number of cells, either way. Whether it's 1,500,000,000 x 1 or 250,000,000 x 600, it's the same volume of data.

             

            The standard data best-practices recommendations would apply here:

            • Hide columns which are unnecessary
            • Aggregate to reasonable levels unless you absolutely need to report on the most granular data
            • Pull the data in a pivoted format (either how it's stored or via a SQL statement with a union)
            • 3. Re: Tableau report from Cross tab format
              Archana N

              Hi,

              Pivot - helps when data source is Excel and text file - right?

              We are using Hadoop HIVE.

              • 4. Re: Tableau report from Cross tab format
                Tharashasank Davuluru

                yes, You are right. If you want to do this in your data source you need to format the data base in a pivoted format

                • 5. Re: Tableau report from Cross tab format
                  Archana N

                  <<It's the same number of cells, either way. Whether it's 1,500,000,000 x 1 or 250,000,000 x 600, it's the same volume of data>>

                  No - it will be 4 rows(and 3 columns) before pivoting, after pivoting it becomes 12 rows - referring to my example of sample data.

                  • 6. Re: Tableau report from Cross tab format
                    Ben Neville

                    But 4 rows and 3 columns becomes 12 rows in your example. This is because 4 rows x 3 columns =12 cells. But the data is now in a single column, so 12 rows x 1 column = 12 cells. Which is why I said the same volume and clarified I meant cells.

                     

                    I can tell you there is no good way to do this in Tableau with that volume of data without pivoting it. I would make the same recommendation as my earlier post. There is no way you are using 600 columns - I would be surprised if you are using 10% of that. Cut down the data you are pulling back and pivot the result. If at all possible, aggregate the data as well, and you can trim both rows and columns.

                     

                    The only alternative is if you only ever want to show 1 year (or all) at a time. If that is the case, you could parameterize which measure you show, but you would have to update this every time you add a new year/measure. If you ever want to show some subset of years, this becomes very messy to write a calculation, and doesn't seem worth the effort for something that is going to cause you issues down the line with other dashboards anyway.

                    1 of 1 people found this helpful