2 Replies Latest reply on Apr 21, 2016 8:56 AM by Joe Oppelt

    Design ideas, reshaping my data.

    Joe Oppelt

      I have a data source that is used in ways that have grown since its inception.

       

      I really have only one measure:  [MeasureValue].

       

      But through a combination of various dimensions and of various parameter settings that the user selects, I derive scores of individual measures.

       

      Besides dates and other dimensions I have two dimensions that I use to pick out what I need for a particular sheet:

       

       

       

       

       

      MetricNameMetricTimeUnit
      DirectExpensesMonth
      DonorHouseholdCountFYTD
      DonorHouseholdCountRolling12
      DonorRegainRateFYTD
      DonorRegainRateRolling12
      DonorRetainRateFYTD
      DonorRetainRateRolling12
      DonorValueFYTD
      DonorValuePerNonPledgeDonorFYTD
      DonorValuePerPledgeDonorFYTD
      FOF.comDonorConversionRateFYTD
      GiftCountFYTD
      GiftFrequencyFYTD
      JanSOHLCampaignFYTD
      LapsedDonorCountFYTD
      LoadedExpensesMonth

       

       

      (... and dozens more measures ...)

       

      This data is ETL'd from huge quantities of transaction data and rolled into MONTHLY, FYTD or Rolling12 values before being fed to Tableau.

       

      Chunks of this data are added monthly.  When I want to display a monthly chart of [GiftCount] I use a calc that essentially says, for the metric time unit the user has selected in the TIME parameter, and for the date range the user has selected via the DATE parameters, if [MetricName]="GiftCount" then [MetricValue] END.

       

      And the proper data is displayed.


      The use of this data source has grown beyond initial concept, and the number of calcs I generate for this data source has grown with it.  I think this is contributing to a serious performance lag.

       

      Before I request a reshaping of this thing (and therefore have to rewrite 150 sheets to accommodate the re-shaping), I want to hear some confirmation that reshape will make a difference.

       

      What I envision for the reshape is this:

       

      For each fiscal month, instead of having the MetricName dimension, I should have individual columns for each metric, and even further, individual columns for each metric for each time unit.  Right now there are over 40 rows per fiscal month.  This will only continue to grow.  Adding columns will remove rows.  So the file will become way shorter, but way fatter.

       

      And when I create these calcs, I get a virtual column per calc in the sheet's table anyway.  So wouldn't I end up with one-fortieth the size of the table with this reshape?

       

      Looking for thoughts and ideas here.

        • 1. Re: Design ideas, reshaping my data.
          Shawn Wallwork

          Hey Joe, I'm not exactly sure I'm with you all the way on this, but I'll take a shot. I'm fairly certain if you unwind your MetricName dimensions you'll get better performance. I say this because there will be fewer rows for Tableau to churn through to do the calc for a single metric (measure).

           

          However, I'm not sure you want to go the step farther and unwind your time units. Because as you say you'll end up with a very wide data structure. And you'll hit all the challenges an unpivoted date field presents.

           

          Unless, of course, I'm completely misunderstanding your situation...

           

          --Shawn

          1 of 1 people found this helpful
          • 2. Re: Design ideas, reshaping my data.
            Joe Oppelt

            You're right, Shawn.  Because I have that dimension (Time Unit), I might be able to filter the sheets appropriately based on the parameter selection.