Einstein Analytics: Getting started with XMD

XMD stands for Extended Metadata, it enables you to customize the formatting of dataset fields and their values in Analytics dashboards and lenses. If you modify the XMD for a dataset, every UI visualization that uses the dataset shows the modified format.

You can customize the following with XMD:

• Format measures. Example: Show the decimal and grouping separators for currency.
• Add prefixes and suffixes to measures. Example: Show the percent symbol (%) after each percentage.
• Multiply measures by a factor. Example: Multiply by 100 to convert a decimal to a percent.
• Change display labels for dimensions and measures.
• Customize colors in charts based on field values.
• Define the first day of the week for the calendar year.
• Add action menus to dimensions that let dashboard viewers invoke actions from lenses and dashboards.

Once a dataset created, by default the XMD will have no values, let's look at this. You can download XMD (which is also in JSON format) by editing the dataset.

Open the JSON file and here is the content

{"dataset":{},"dates":[],"derivedDimensions":[],"derivedMeasures":[],"dimensions":[],"measures":[],"organizations":[],"showDetailsDefaultFields":[]}

Tips: use jsoneditoronline.org to reformat the file into a human-friendly format

{

  "dataset": {},

  "dates": [],

  "derivedDimensions": [],

  "derivedMeasures": [],

  "dimensions": [],

  "measures": [],

  "organizations": [],

  "showDetailsDefaultFields": []

}

In this blog, I'll share how to rename field name with XMD for all dashboard and lenses, or in particular dashboard with SAQL.

Setup field format and label

Let us format Amount field to be in currency format $9,999.99

- Click the Dataset to open as a Lens

- Click Fields

- Set Amount format

- Rename label Amount to Initial Amount, and Region to Sales Region

- Click Save button

- Change the Lens into Values Table and now you will see Amount is in currency format with header Initial Amount and Region become Sales Region

Note that this change will change all existing dashboard and lens.

Back to XMD

Let's us re-download the XMD file and see what how it looks like now:

{

  "dataset": {},

  "dates": [],

  "derivedDimensions": [],

  "derivedMeasures": [],

  "dimensions": [

    {

      "conditionalFormatting": {},

      "customActions": [],

      "field": "Region",

      "label": "Sales Region",

      "members": [],

      "recordDisplayFields": [],

      "salesforceActions": []

    }

  ],

  "measures": [

    {

      "conditionalFormatting": {},

      "field": "Amount",

      "format": {

        "customFormat": "[\"$#,##0.00\",1]"

      },

      "label": "Initial Amount"

    }

  ],

  "organizations": [],

  "showDetailsDefaultFields": []

}

** you can use jsoneditoronline.org to re-format it.

Edit Amount with SAQL

In case that we need to change the Amount header for a particular dashboard, you can edit the lens/widget with SAQL. In this example, we are going to change from Initial Amount to MRR.

- Open the dataset with Lens

- Change to Values Table

- Click SAQL Mode button

If you notice now MRR does not have currency format we have set earlier. To enable this, we need to edit the XML file again. We need to add following conditional formatting in XMD under derivedMeasures.

A measure is a quantitative value, like revenue and exchange rate. You can do the math on measures, such as calculating the total revenue and minimum exchange rate.

Derivated measures are the ones that you get from the measures, it is calculated fields so they don’t really exist in your dataset.

{
  "dataset": {},
  "dates": [],
  "derivedDimensions": [],
  "derivedMeasures": [
  {
      "conditionalFormatting": {},
      "field": "MRR",
      "format": {
        "customFormat": "[\"$#,##0.00\",1]"
      },
      "label": "MRR"
    }
  ],
  "dimensions": [
    {
      "conditionalFormatting": {},
      "customActions": [],
      "field": "Region",
      "label": "Sales Region",
      "members": [],
      "recordDisplayFields": [],
      "salesforceActions": []
    }
  ],
  "measures": [
    {
      "conditionalFormatting": {},
      "field": "Amount",
      "format": {
        "customFormat": "[\"$#,##0.00\",1]"
      },
      "label": "Initial Amount"
    }
  ],
  "organizations": [],
  "showDetailsDefaultFields": []
}

Upload back the XMD file from Replace menu in Dataset edit. Let's reload the lens/widget.

Now realize that Amount now called MRR for the particular lens and it has current formatting too. Check Fields from Lens and now MRR is added as Derived Measures

As you see, actually we can add Derived Measures directly when you edit the dataset from the lens, without the need to edit the XMD manually.

Reference: 

• Analytics Extended Metadata (XMD) Reference
• Format Measures

Einstein Analytics: Chart Type Binding

In the blog shared earlier Einstein Analytics: The quest for Binding in Dashboard, we discussed in a glance that we can let user change the type of chart on the fly by using binding, it will work for most of the chart type, but not all, this is because specific chart need to have additional parameters, such as for Heatmap chart type, it requires highColor and lowColor.

Let's see the steps and sample:

1. Add a chart to the dashboard and select the initial chart type.

2. Add static step with values of chart type, such as Stack Vertical Bar, Stack Horizontal Bar, etc. You need to know the value for each chart type, sample
              "values": [
                    {
                        "display": "Stack Vertical Bar",
                        "value": "stackvbar"
                    },
                    {
                        "display": "Stack Horizontal  Bar",
                        "value": "stackhbar"
                    },
                    {
                        "display": "Heatmap",
                        "value": "heatmap"
                    },
                    {
                        "display": "Line",
                        "value": "line"
                    }
                ]

3. Change columnMap to null, you need to change only columnMap under chart/widget (not the one under the step).

4. Change visualizationType under chart/widget (not the one under the step) to {{coalesce(cell(Chart_Type_1.selection, 0, \"value\"), cell(Chart_Type_1.result, 0, \"value\")).asString()}}
** Chart_Type_1 is the step name for static step

5. For some type of charts, they required additional parameters, you need to add them, otherwise, it will not work. You can monitor the additional parameters when you add a chart with the selected chart type, some of the parameters are required, and some are not required.

all values in parameters below are for samples only

Heatmap
  "lowColor": "#E2F4FC",
  "highColor": "#009DDF",
  "x": {
          "showAxis": true,
          "showTitle": true,
          "title": ""
       },
  "y": {
          "showAxis": true,
          "showTitle": true,
          "title": ""

       }


Horizontal Bar, Vertical Bar, Stack Horizontal Bar, Stack Vertical Bar
  "trellis": {
                       "enable": false,
                       "showGridLines": true,
                       "flipLabels": false,
                       "type": "x",
                       "chartsPerLine": 4,
                       "size": [
                           100,
                           100
                        ]
             },
  "showValues": true,
  "axisMode": "multi",
  "dimensionAxis": {
                        "showAxis": true,
                        "showTitle": true,
                        "title": "",
                        "customSize": "auto",
                        "icons": {
                            "useIcons": false,
                            "iconProps": {
                                "column": "",
                                "fit": "cover",
                                "type": "round"
                            }
                        }
                    },
  "measureAxis1": {
                        "sqrtScale": false,
                        "showAxis": true,
                        "customDomain": {
                            "showDomain": false
                        },
                        "showTitle": true,
                        "title": ""
                    },
                    "measureAxis2": {
                        "sqrtScale": false,
                        "showAxis": true,
                        "customDomain": {
                            "showDomain": false
                        },
                        "showTitle": true,
                        "title": ""

                    },
  "normalize": false,

  "exploreLink": true


Line
  "showPoints": false,
  "showZero": true,
  "drawArea": {
                        "showDrawArea": false,
                        "bounding1": "",
                        "bounding2": "",
                        "measure": ""
              },
  "dashLine": {
                        "showDashLine": false,
                        "measures": ""

              },
  "exploreLink": true


Donut
  "trellis": {
                        "enable": false,
                        "showGridLines": true,
                        "flipLabels": false,
                        "type": "x",
                        "chartsPerLine": 4,
                        "size": [
                            100,
                            100
                        ]
            },
  "exploreLink": true,
  "showMeasureTitle": true,
  "centerValueType": "number",
  "centerText": "",
  "inner": 60


In summary: if your binding contains such chart type, make sure to add all of them to the parameters, especially the required parameters, otherwise, the binding for that chart type will just not working.

Einstein Analytics: computeRelative getting started

computeExpression is a well-known transformation tool in Dataflow, you expect to get new fields as result, based on data in the same row. While computeRelative is also transformation tool in Dataflow, but you can compare with data in the same or the other row (first, last, next, and previous).

In computeRelative, you need to specify:
- input source
- partition by field: this is unique key field such as Opportunity Id, Account Id, etc.
- sort field: this is to tell data order
- computed fields: this would be the result fields from computeRelative.

Let's use this sample dataset:

Create a dataflow using edgemart to get existing dataset in Einstein Analytics:
- add computeRelative to get Previous Amount and Previous Closed Date
- using Opportunity Id as partition field
- order by Snapshot_Date

Here is the field attribute:

In this case, we would like to leave the Previous Amount and Previous Close Date as blank if there is no previous data for the particular records. If we leave the Default Value in Field Attribute as blank, there will be no default value defined in the JSON and this will cause errors when running the dataflow.

To fix that, download the JSON file and edit it manually, then upload it back to the dataflow.

  "computeRel1": {
    "action": "computeRelative",
    "parameters": {
      "source": "EdgeProspect",
      "computedFields": [
        {
          "name": "PreviousAmount",
          "label": "PreviousAmount",
          "expression": {
            "sourceField": "Amount",
            "offset": "previous()",
            "default": null
          }
        },
        {
          "name": "PreviousCloseDate",
          "label": "PreviousCloseDate",
          "expression": {
            "sourceField": "Closed_Date",
            "offset": "previous()",
            "default": null
          }
        }
      ],
      "orderBy": [
        {
          "name": "Snapshot_Date",
          "direction": "asc"
        }
      ],
      "partitionBy": [
        "Opportunity_ID"
      ]
    }
  }


Here is the final result:

Reference: computeRelative Parameters

Einstein Analytics: Binding syntax

Binding is one of the most powerful techniques to implement when building a dashboard, although it is also one of the most difficult as you need to edit the JSON directly. In a few blogs post earlier here and here, you may notice a function call coalesce(), let's see what is this function do before we jump further into the binding technique.

coalesce returns the first non-null source from a list of sources. This function is useful for providing a default value in case function returns a null value.
syntax: coalesce(source1, source2,...)

example: coalesce(cell(step1.selection, 0, "column1"), "green")

  output: the output is the result returned by cell(step1.selection, 0, "column1"). However, if cell(step1.selection, 0, "column1") returns null, then the output is "green".

We can use cell, row and column function for binding, let's see them below:

cell 
returns a single cell of data as a scalar, like "This salesperson rocks", 2, or null. 

syntax: cell(source, rowIndex, columnName)

An error occurs if the rowIndex is not an integer, the columnName is not a string, or the cell doesn’t exist in the table.

example: assume that following rows from the step.

[ 

    {stateName: ‘CA’, Amount:100}, 

    {stateName: ‘TX’, Amount:200}, 

    {stateName: ‘OR’, Amount:300}, 

    {stateName: ‘AL’, Amount:400}, 

] 

Although Einstein Analytics doesn’t store this data as a table, let’s show the data in this format to make it easier to understand the example

(row index)	stateName	Amount
0	CA	100
1	TX	120
2	OR	115
3	AL	105

sample function: cell(myStep.selection, 1, "stateName")

result: "TX"

column
returns one column of data (as a one-dimensional array) or multiple columns of data (as a two-dimensional array) -- allow users to select multiple values.
Syntax: column(source), [columnNames...])

Let's use a similar step with cell functions above

[ 

    {stateName: ‘CA’, Amount:100}, 

    {stateName: ‘TX’, Amount:200}, 

    {stateName: ‘OR’, Amount:300}, 

    {stateName: ‘AL’, Amount:400}, 

] 

And here is the presentation virtual table format.

(row index)	stateName	Amount
0	CA	100
1	TX	120
2	OR	115
3	AL	105

sample function: column(myStep.selection, "stateName")

result: ["CA", "TX", "OR", "AL"]

Let us continue with binding. There are two types of bindings: selection binding and results binding, the selection or results of one step triggers updates in other steps in the dashboard.

Selection binding is a method used to update a step based on the selection in another step. Selection bindings are interaction-driven, where it’s evaluated each time the user selects something in a widget.

Results binding is a method used to update a step based on the results of another step.

syntax: {{ cell(<stepName>.<result|selection>, <rowIndex>, columnName>).<asString()|asObject()> }}
example: {{ cell(Static_Step_1.selection, 0, \"value\").asString() }}
--> 0 from above is from 1st row
--> {{ }} = binding

syntax: {{ column(<stepName>.<result|selection>, columnName>).asObject() }}
example: {{ column(Static_Step_1.selection, [\"value\"]).asObject() }}

If you saw that coalesce function is to return the first not null function, we can combine it in our cell binding.
{{ coalesce(cell(Static_Step_1.selection, 0, \"value\"), cell(Static_Step_1.result, 0, \"value\")).asString() }}


From the above samples, notice there are 2 other functions always used in binding: asString() and asObject().

asString() function serializes a scalar, one-dimensional array, or two-dimensional array as a string, escapes double quotes in strings.
syntax: <input data>.asString()

example: cell(stepOpportunity.selection, 1, \"measure\").asString()

--> 1 from above is from 2nd row

{{ cell(color_1.result, 0, \"color\").asString() }}

asObject() function passes data through with no serialization, returns data as an object (an array of strings).
syntax: <input data>.asObject()

example:
{{column(static_1.selection, [\"value\"]).asObject()}}

{{cell(static_1.selection, 0, \"value\").asObject()}}

Reference:

• coalesce Function
• cell Function
• column Function
• asString Function
• asObject Function
• Change the Map Type Based on a Toggle Widget
• Bindings in Analytics Dashboards
• Einstein Analytics Tech Lounges Series E31 Bindings

Einstein Analytics: Connect Data Sources and Binding between Dataset

Einstein Analytics allow us to have more than 1 dataset in a dashboard. With Connect Data Source, we can link the dataset using a common field from both datasets, but the field must be used in the dashboard.

Let us using following sample of datasets:

Connect the data source using "Region"

With Connected Data Source, when we select a Region in Target, the Actual Region picklist will be filtered too, if Apply filters from faceting is enabled, this is enabled by default.

The same when we select Actual Region with a value, Target Region will be filtered too -- if "Apply filters from faceting" is enabled.

Without adding Region (actual) dropdown list, Actual value will be still filtered correctly. That's all about Connect Data Source, but in some cases, we can't connect data source, binding is powerful for this, but you need to get your hand dirty with JSON.

Let's see the result first:

Selecting a Region in Target do not filter picklist value in Actual, but the Actual value is changed accordingly, the same as in Connect Data Source.

In this sample, we need to add a filter into the step of Actual value.
This is the original query:
 "query": {
            "measures": [
                    [
                       "sum",
                       "Sales_Actual"
                    ]
            ],
            "groups": [
                       "Sales_Region"
            ]
          }

Add filters to the query:
"query": {
            "measures": [
                    [
                      "sum",
                      "Sales_Actual"
                    ]
            ],
            "groups": [
                       "Sales_Region"
            ],
            "filters": [
                    [
                       "Sales_Region",
                       "{{column(list_Target.selection,[\"Region\"]).asObject()}}",
                       "in"
                    ]
             ]
          }

• Sales_Region is API field name for the column Region from Actual dataset -- we will need to use that field as filtering
• list_Target is the step name for Target Region drop-down.
• Region is the API field name for the column Region from Target dataset.

Reference:

• Configure Cross-Dataset Faceting with Connected Data Sources

Einstein Analytics: Multi-Select Picklist to Text

As per the Summer '18 release, Einstein Analytics is not really friendly with the Multi-Select Picklist field from Salesforce. One of the issues, when you have multi-values in a record, it will show only the 1st value when you show the data as Value Table in a Lense or Table widget in a Dashboard.

Service Type contains multi-values in Salesforce (screenshot from Report)

EA shows the 1st value only of Service Type

As per this document, we can customize JSON dataflow to treat multi-select values as text. Once, you have the dataflow built:

• Download the JSON file
• Edit the file and add ,"isMultiValue": false after the field name and save it (you should backup the original JSON file)
• Upload back the edited JSON file to Dataflow

Here is the result the multi-select values show as text in Einstein Analytics

Note: if you do not update the JSON dataflow as above, the data will still flow into EA as a multi-values field, it will work if you use it as a List filter, and it filters all values (not just the 1st value) for the result, let's see a sample here.

all data

filter North America

filter EMEA

Summer '21 release introduces mv_to_string() function, this function returns an alphabetically sorted, delimited string representation of a multivalue field. But, you need to enable indexing of multivalue fields in Tableau CRM from the setup menu, also notice that 'Indexing multivalue fields can slow down dataflows", so watch your dataflow run once this is enabled. This feature still in Beta as per the Summer '21 release. 

Reference: 

• Limitations of Multi-Value Fields in Einstein Analytics
• Opt Into Multivalue Fields in Query Results
• mv_to_string() (Beta)