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:
(... 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.