Order out of chaos : How to override Einstein Analytics' alphabetical order in dimension columns with SAQL

In the previous post we found out how to group and rename our data rows to display an Account Summary table. The grouping was successful but they weren't quite in the right order. For this recipe we'll adjust the SAQL projections and then use the Wavelabs app for an easy way to amend the XMD schema file.

The customer wants their data to be in the order of:

Summary Revenue
Top 20 12345
Others 12345
Total 12345

But because EA orders the our group dimension alphabetically in this case (numerically if they are numbers as strings), our table doesn’t meet this requirement. And we have this:

Summary Revenue
Others 6,023,252
Top 20 551,792
Total 6,584,044

We can remedy this by using a combination of some minor edits to the SAQL projections and to the Dataset’s XMD file. The XMD allows us to provide all sorts of default criteria to alter the presentation of the data in the designer layer of Einstein Analytics. Including a very powerful set of instructions called derived measures and derived dimensions. But just because they are powerful, doesn’t mean they are hard to configure.

There are two ways to edit the XMD, either by downloading it from the dataset’s details page or by using the XMD Editor feature in the Herokuu Analytics App.

Let’s start by making changes to our SAQL. Open the dashboard that contains your Summary table from the previous article.

For each stream where you named your group in the foreach statement add a number for the order in which you would like the groups to appear in your final table.

So for the q_Top20 projection, rename the Top 20 field to be “01 Top 20” and so on until you have renamed each of the groups. Like the example code below:

q_top20 = foreach q_top20 generate "01 Top 20" as 'Account', sum(Revenue) as 'Revenue';

You final group names should be:

01 Top 10
02 Other
03 Total

Now run the query, and your table of results should come back looking like this:

Screen-Shot-2018-01-28-at-13.08.51

Now that the order is fixed, we need to strip the numbers from the beginning of the dimensions. This is where the Derived Dimension section of the XMD can play its part.

Go to Wavelabs and log in to your Einstein Analytics developer org instance.

Screen-Shot-2018-01-28-at-13.10.37

Select the XMD editor option.

Screen-Shot-2018-01-28-at-13.16.15

Click on Open to load the sample account dataset or whichever dataset you are using in for your summary table.

Screen-Shot-2018-01-28-at-13.19.23

Select derived dimension then click the plus button to add a new set of derived dimension instructions for your custom grouping field.

Screen-Shot-2018-01-28-at-13.19.55

In the field labeled 'Field' - Enter the name you gave your grouping in the final projection of your account results. So in the case of our example this would be 'Summary'.

Screen-Shot-2018-01-28-at-13.56.46

Then click the '+ Value' link, (this sometimes causes some odd scrolling behaviour, if it does just scroll back up the page to the new field) You’ll see that there are 3 fields, Member, Label and Color.

Screen-Shot-2018-01-28-at-13.58.58

  • Member is the name of the derived group name (your custom name) you created in your Account Summary Table.
  • Label is name you wish your derived group to actually display in the table.
  • Colour is used if you want to give these values a custom colour when being displayed in a chart, but we will leave that for now.

So in our case an example would be
Member = 01 Top 20
Label = Top 20 (removing the number prefix)

Screen-Shot-2018-01-28-at-14.02.34

Create values for each of the custom group rows in the
Summary table by clicking '+ Value' and repeating these steps for the other two members.

Screen-Shot-2018-01-28-at-14.04.13

Save the changes you have made by clicking the disc icon in the top right.

Now go back to Analytics Studio in your developer org, make sure you have saved your work so far (s key), close the dashboard and reload or refresh the Analytics Studio page in your browser.

Now open the dashboard again and check you should see that the table is now displaying the values as you entered them in the XMD.

Screen-Shot-2018-01-28-at-14.08.28

Derived dimensions are very powerful and it’s well worth reading the documentation. If you think of it as a sort of find and replace, where the data is being told to do one thing in our SAQL, but the XMD is showing the user something that makes more sense to them in the UI. I’m sure there are lots more use cases out there.

Credit to Ziad Fayad at Salesforce for showing me the possibilities of XMD Derived Dimensions, it was exactly what I needed for this sort of use case.

Show Comments

Get the latest posts delivered right to your inbox.