When we configure Einstein Analytics Dataset, we not realize have been playing around with XMD (Extended Metadata).
When you explore dataset to a lens, and click "Fields" for:
- Rename
- Edit Values
- Number Format
Save the changes will update Dataset XMD.
Let us load a CSV file
Here is the blank structure of XMD file:
{
"dataset": {},
"dates": [],
"derivedDimensions": [],
"derivedMeasures": [],
"dimensions": [],
"measures": [],
"organizations": [],
"showDetailsDefaultFields": []
}
Let us create a dashboard based on that CSV file
Now, let us do "Fields" edit from lens with Rename, Edit Values, and Number Format. Here is the updated XMD file
{
"dataset": {},
"dates": [],
"derivedDimensions": [],
"derivedMeasures": [],
"dimensions": [
{
"conditionalFormatting": {},
"customActions": [],
"field": "Column1",
"label": "Name",
"members": [
{
"label": "John",
"member": "Lee"
}
],
"recordDisplayFields": [],
"salesforceActions": []
}
],
"measures": [
{
"conditionalFormatting": {},
"field": "Data_1",
"format": {
"customFormat": "[\"0.00%\",100]"
}
},
{
"conditionalFormatting": {},
"field": "Data_2",
"format": {
"customFormat": "[\"#,###\",1]"
}
}
],
"organizations": [],
"showDetailsDefaultFields": []
}
Highlight notes:
- Yellow = rename
- Green = edit value
- Aqua = number format as Percent
- Fuchsia = number format as Number
Now, open back the dashboard (you need to close it first if the dashboard still open).
Usage in SAQL field
Let us do a simple SAQL to the widget to produce total Data-1 + Data-2 and Data-1 / Data-2.
q = load "percent_data";
q = group q by 'Column1';
q = foreach q generate 'Column1' as 'Column1', sum('Data_1') as 'sum_Data_1', sum('Data_2') as 'sum_Data_2';
q = foreach q generate 'Column1' as 'Column1', 'sum_Data_1' + 'sum_Data_2' as 'Total', 'sum_Data_1' / 'sum_Data_2' as 'Result_1';
q = order q by 'Column1' asc;
* notice that we still use Column1 as this is referred to the field API name
The SAQL produce two new fields which do not exist in the dataset: Total and Result_1.
However, we need to show them in the percentage format, so we need to add in the XMD file, because this is a measure field and it does not exist in the dataset, we should add it into derivedMeasures.
{
"dataset": {},
"dates": [],
"derivedDimensions": [],
"derivedMeasures": [
{
"conditionalFormatting": {},
"field": "Total",
"format": {
"customFormat": "[\"#.##%\",1]"
},
"label": "Total",
"showInExplorer": false
},
{
"conditionalFormatting": {},
"field": "Result_1",
"format": {
"customFormat": "[\"#.##%\",1]"
},
"label": "Result",
"showInExplorer": true
}
],
"dimensions": [
{
"conditionalFormatting": {},
"customActions": [],
"field": "Column1",
"label": "Name",
"members": [
{
"label": "John",
"member": "Lee"
}
],
"recordDisplayFields": [],
"salesforceActions": []
}
],
"measures": [
{
"conditionalFormatting": {},
"field": "Data_1",
"format": {
"customFormat": "[\"0.00%\",100]"
}
},
{
"conditionalFormatting": {},
"field": "Data_2",
"format": {
"customFormat": "[\"#,###\",1]"
}
}
],
"organizations": [],
"showDetailsDefaultFields": []
}
Notes:
1. add those field under derivedMeasures
2. field name can be different with label
3. you can set the field visibility in explorer
Here is the end result:
When you browse the dataset into a lens and open the Fields
JSON Online Editor
When you download XMD JSON file from Einstein Analytics and open in Notepad++, it would be show as one row only, where it is impossible for you to read.edit it. You can use JSON Online Editor to show as tree format, by copy and paste the JSON text into the left text area, click the right arrow to show as tree format.
Then to get human eye friendly JSON format, click left arrow and the text at the right will reformat.
Reference:
No comments:
Post a Comment