How to Transpose Data from Rows to Columns in a CRM Analytics Dataset using Dataflows

Oftentimes, Salesforce developers create object models that create additional complexity when building analytics solutions. Since there are certain limits and cost factors inherent to Salesforce, the Salesforce developer will develop their solution accordingly to best align with "their" needs. Sometimes the Analytics developer can influence these decisions to facilitate Analytics but, oftentimes, they cannot. A common example is where an object within Salesforce has related metrics stored horizontally in columns, instead of in rows. A simple use case is an object to store Survey responses where each row represents an entry (the attribute), and the fields represents the survey questions (the measures), and the cells contains the score (the value). How can I simply figure out which question in an entry has the highest or the lowest score, or what is an average score per entry without filtering and/ or using the compare table formulas etc.

A simple answer is by transposing the rows in to the columns (thanks to Dan Courtenay). This can be easily done using the dataflow. A simple 3 step dataflow will transpose your dataset for a simple row level aggregations. In this example, I am using a five-measure dataset as shown below.

Computing the max survey score by question or entry does not give the expected results as the data can only be aggregated on a single question level and appear as five bars like this:

On the other hand, if I want to see the scores of all questions by their respective entries (say in a stacked bar chart), it would look something like this:

If the fields get transposed by keeping the integrity of the score with their respective Entry and question, you will get a better aggregation for analysis. In order to acheive this, follow the simple 3 step approach listed below:

Step 1: Create one new computeExpression node per field/ measure. In this case there will be five compute Expression nodes. Every node will contain three Computed Fields as below (make sure to keep the field name consistent).

Entry (or any attribute name)

1. Type = Text

2. SAQL Expression = ‘Survey_Entry’ (This should be mapped to the Attribute field name from the source dataset

Score (or measure name)

1. Type = Numeric

2. SAQL Expression = ‘Question1’ (This should be mapped to the first measure field)

3. Make sure to set Precision and Scale according to your source data

Question (This is an optional field. Having it created may help improving the clarity of analysis. This represent the measure field label)

1. Type = Text

2. SAQL Expression = “Question 1” (This should be mapped to the first measure field label)

Create additional computeExpressions for the remaining measure fields with appropriate names and mappings.

Step 2: Append all newly created computeExpression nodes using the “Append” transformation. You may select any order. As all the computed field names are consistent, you will not be required to select “Apply disjoint schema”.

Step 3 (Optional but recommended): Drop unused measure fields using sliceDataset node to avoid confusion. Register the dataset and explore

Now you are able to visualize your Scores by entries or questions or both.

Bonus: If your use case requires only minimum or maximum score, you may add a computeRelative transformation after the slice node to add a computed field that contains minimum and maximum value for the entire dataset. Enjoy exploring!!