The Model View Controller (MVC) Method of Building EA Dashboard Interactivity With Bindings – Part 2
In Part 1 we looked at the MVC method of building Einstein Analytics dashboards conceptually. Now we are going to review specific examples and code to put it into practice. First, let me say that I’m not sure everything that is accomplished is fully supported by the platform. So know that it could potentially break at any point in time. However, it has worked for a few years and while some examples for how you could use this can be accomplished via other methods, especially now with the ‘advanced editor’ via the UI – there are use cases where I’ve found this to be the only method that works.
A Few Use Cases
I have used this MVC design pattern in other programming languages so when I ran into roadblocks in trying to design a dashboard with certain functionality I tried it as a workaround and I was very happy when it ended up working! I have used this in cases where we have a single widget (or multiple that leverage the same step/query) but need a variety of control from the end user. Sure, if they just want to switch the grouping and/or the measure we would be fine. But what if at the same time they needed to toggle the specific function that is being calculated on the fly (for example a windowing function). These functions need to already have the stream organized and ‘grouped’ properly prior to the calculation. Moreover, you need to specify that grouping and measure within the function. On top of all of this, I’ve had to build dashboards that toggle between not only various groupings but completely different timeframes (hour, year-week, year-month-day, month) and use different summary calculations (avg, running total, etc.) and these all have to be specified within the function and the stream already ‘prepped’ with the proper information.
A Relatively Simple Example
What follows is a relatively simple example where we are only changing the measure, toggling between ActivityDate and CreatedDate and also toggling whether we are viewing the ‘actual’ count of records or a Moving Average period of 10, 15, 30, 45 or 60 days. Just know that you could easily expand on this and add additional toggles for some of the use cases I mentioned above. Once you set up the dashboard in MVC you have laid the groundwork to add additional functionality relatively easily.
In order to build this we need our visual components which are the widgets and the static steps for the Toggles. These are fairly simple and can be input via the UI:
Then, you need to create a Controller Step that will use toggles to ‘feed’ code into the main step that feeds the chart.
Below is the full code for the Controller Step. Essentially it takes a single ‘row’ (limit 1) of the dataset that we are using so we ‘know’ we will have at least one result and then we use a series of case statements in order to evaluate the user selections and output code based on those:
"ControllerStep": { "broadcastFacet": true, "groups": [], "label": "ControllerStep", "numbers": [], "query": "q = load \"activity\";\nq = foreach q generate case when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"Actual\" then \"sum(count)\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"10 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"ActivityDate\").asString()}}\" == \"ActivityDate\" then \"avg(sum(count)) over ([-10..0] partition by all order by ('ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"15 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"ActivityDate\").asString()}}\" == \"ActivityDate\" then \"avg(sum(count)) over ([-15..0] partition by all order by ('ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"30 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"ActivityDate\").asString()}}\" == \"ActivityDate\" then \"avg(sum(count)) over ([-30..0] partition by all order by ('ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"45 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"ActivityDate\").asString()}}\" == \"ActivityDate\" then \"avg(sum(count)) over ([-45..0] partition by all order by ('ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"60 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"ActivityDate\").asString()}}\" == \"ActivityDate\" then \"avg(sum(count)) over ([-60..0] partition by all order by ('ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"Actual\" then \"sum(count)\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"10 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"CreatedDate\").asString()}}\" == \"CreatedDate\" then \"avg(sum(count)) over ([-10..0] partition by all order by ('CreatedDate_Year~~~CreatedDate_Month~~~CreatedDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"15 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"CreatedDate\").asString()}}\" == \"CreatedDate\" then \"avg(sum(count)) over ([-15..0] partition by all order by ('CreatedDate_Year~~~CreatedDate_Month~~~CreatedDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"30 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"CreatedDate\").asString()}}\" == \"CreatedDate\" then \"avg(sum(count)) over ([-30..0] partition by all order by ('CreatedDate_Year~~~CreatedDate_Month~~~CreatedDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"45 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"CreatedDate\").asString()}}\" == \"CreatedDate\" then \"avg(sum(count)) over ([-45..0] partition by all order by ('CreatedDate_Year~~~CreatedDate_Month~~~CreatedDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"60 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"CreatedDate\").asString()}}\" == \"CreatedDate\" then \"avg(sum(count)) over ([-60..0] partition by all order by ('CreatedDate_Year~~~CreatedDate_Month~~~CreatedDate_Day'))\" else \"avg(sum(count)) over ([-60..0] partition by all order by ('ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'))\" end as 'WindowFunction', 'ActivityDate_Year' + \"~~~\" + 'ActivityDate_Month' + \"~~~\" + 'ActivityDate_Day' as 'ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day', count() as 'count';\nq = limit q 1;", "receiveFacetSource": { "mode": "all", "steps": [] }, "selectMode": "single", "strings": [], "type": "saql", "useGlobal": true, "visualizationParameters": { "parameters": { "borderColor": "#e0e5ee", "borderWidth": 1, "cell": { "backgroundColor": "#ffffff", "fontColor": "#16325c", "fontSize": 12 }, "columnProperties": {}, "columns": [], "customBulkActions": [], "header": { "backgroundColor": "#f4f6f9", "fontColor": "#16325c", "fontSize": 12 }, "innerMajorBorderColor": "#a8b7c7", "innerMinorBorderColor": "#e0e5ee", "maxColumnWidth": 300, "minColumnWidth": 40, "mode": "variable", "numberOfLines": 1, "showActionMenu": true, "showRowIndexColumn": true, "totals": true, "verticalPadding": 8 }, "type": "table" } },
Then, we use those outputs from the ‘ControllerStep’ in our primary step that feeds the visual chart on the dashboard:
"ActivityDate_Year_Ac_1": { "broadcastFacet": false, "groups": [], "label": "ActivityDate_Year_Ac_1", "numbers": [], "query": "q = load \"activity\";\nq = filter q by date('ActivityDate_Year', 'ActivityDate_Month', 'ActivityDate_Day') in [dateRange([2018,4,28], [2018,8,12])];\nq = group q by ({{coalesce(cell(static_2.selection, 0, \"FirstGroup\"), \"'ActivityDate_Year', 'ActivityDate_Month', 'ActivityDate_Day'\").asString()}});\nq = foreach q generate {{coalesce(cell(static_2.selection, 0, \"Year\"), \"'ActivityDate_Year'\").asString()}} + \"~~~\" + {{coalesce(cell(static_2.selection, 0, \"Month\"), \"'ActivityDate_Month'\").asString()}} + \"~~~\" + {{coalesce(cell(static_2.selection, 0, \"Day\"), \"'ActivityDate_Day'\").asString()}} as {{coalesce(cell(static_2.selection, 0, \"2ndGroupOrder\"), \"'ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'\").asString()}}, count(q) as 'count';\nresult = group q by {{coalesce(cell(static_2.selection, 0, \"2ndGroupOrder\"), \"'ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'\").asString()}};\nresult = foreach result generate {{coalesce(cell(static_2.selection, 0, \"2ndGroupOrder\"), \"'ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'\").asString()}}, {{coalesce(cell(ControllerStep.result, 0, \"WindowFunction\"), \"sum(count)\").asString()}} as 'MovingAvg';\nresult = order result by ({{coalesce(cell(static_2.selection, 0, \"2ndGroupOrder\"), \"'ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'\").asString()}} asc);\nresult = limit result 2000;", "receiveFacetSource": { "mode": "none", "steps": [] }, "selectMode": "single", "start": [], "strings": [], "type": "saql", "useGlobal": false, "visualizationParameters": { "parameters": { "autoFitMode": "keepLabels", "showPoints": false, "legend": { "descOrder": false, "showHeader": true, "show": true, "customSize": "auto", "position": "right-top", "inside": false }, "axisMode": "multi", "tooltip": { "showBinLabel": true, "measures": "", "showNullValues": true, "showPercentage": true, "showDimensions": true, "showMeasures": true, "customizeTooltip": false, "dimensions": "" }, "visualizationType": "time", "missingValue": "connect", "dashLine": { "measures": "", "showDashLine": false }, "timeAxis": { "showTitle": true, "showAxis": true, "title": "" }, "title": { "fontSize": 14, "subtitleFontSize": 11, "label": "", "align": "center", "subtitleLabel": "" }, "trellis": { "flipLabels": false, "showGridLines": true, "size": [ 100, 100 ], "enable": false, "type": "x", "chartsPerLine": 4 }, "fillArea": true, "showActionMenu": true, "showZero": true, "measureAxis2": { "sqrtScale": false, "showTitle": true, "showAxis": true, "title": "", "customDomain": { "showDomain": false } }, "measureAxis1": { "sqrtScale": false, "showTitle": true, "showAxis": true, "title": "", "customDomain": { "showDomain": false } }, "valueType": "none", "theme": "wave", "applyConditionalFormatting": true, "drawArea": { "measure": "", "showDrawArea": false, "bounding1": "", "bounding2": "" } }, "type": "chart" } },
I didn’t do it here but for a more polished look you can even update all display names on the chart axis with the proper string based on the user selection.
Please let me know if you have had success with this. Please let me know if you have a better solution! Honestly this is a bit of a hack that I’ve come up with but it has saved me countless times! Every time I think I can do it via normal binding functionality I run into gaps and limitations due to issues with having to already have the stream prepped for the function or specifying the measure and grouping in the function which you cannot do with normal binding syntax. This has been a lifesaver and I hope it is helpful to you. Until next time, keep riding the Wave (or Einstein Analytics)! Once Wave Always Wave!