Isn’t everyones future looking a bit differently? Wouldn’t it be great if everyone could adjust the Time Series model to their needs and drill in the data to generate their own conclusions? You might say that is already available and you are absolutely right, you can spin off a Time Series dashboard with a provided Template in a few clicks or you leverage the new Time series function in the compare table (see blog from Rikke here).
In this post I want to push it one step further, I want to integrate all the model parameters dynamically into a dashboard so the user can adjust the model metrics on the fly and does not have to switch to the compare table or spin off a new template each time he wants to make a change.
You guessed it, we can easily achieve this with Bindings. But this is just the beginning. I don’t only want to see the general Time Series where I can see the high level trend, I want to break it down into my Dimensions/Groupings to identify patterns which influences the trend the most to get a better idea what I should action on or need to change. Sure I can run the same data set in Einstein Discovery, but that’s not what I want to solve here. Never the less, running Einstein Discovery first on your data set is always a good idea to get a better understanding of the underlying patterns so you can adjust your Einstein Analytics Dashboard flow and logic accrodingly.
Building the foundation
Let’s get started: To create the initial Time Series logic we can leverage the built in function for Time series in the compare table (yes, it can’t get much easier). This creates the foundation so we just need to create our static steps for the parameters and replace the parameters with our Bindings.
After this we need to transform it into a timeline chart and put the original measure and the prediction of this measure on a Single Axis. But we are obviously not quite there yet!
Static Steps
To make the dashboard dynamic I created Static Steps for the Length (months in the future) , the Prediction interval, inclusion of the last month, and the seasonality. For the break down I created a static step with the dimensions I want to see in the Trellis.
In the following code snippet you can see how I created the static steps. For the seasonality I integrated the commas already in the static step as this feature is optional for the Time Series function. In the Binding itself I just leave the comma away so it can be blank as well without braking the SAQL code itself. e.g. in display “Auto” you will see the value is blank with “”. For easy adjustments for your static steps I highly recommend the EADashboard Helper Chrome extension by Mathew Horvath (must have if you work with Bindings).
"Prediction_seasonali_1": {
"broadcastFacet": true,
"label": "Prediction_seasonality",
"selectMode": "singlerequired",
"type": "staticflex",
"values": [
{
"display": "4",
"value": "seasonality=4,"
},
{
"display": "12",
"value": "seasonality=12,"
},
{
"display": "None",
"value": ""
},
{
"display": "Auto",
"value": ""
}
],
"start": {
"display": [
"None"
]
}
},
"Prediction_IgnoreLas_1": {
"broadcastFacet": true,
"label": "Prediction_IgnoreLast",
"selectMode": "singlerequired",
"type": "staticflex",
"values": [
{
"display": "ignore",
"value": "true"
},
{
"display": "include last",
"value": "false"
}
],
"start": {
"display": [
"ignore"
]
}
},
"Timeseries_predictio_1": {
"broadcastFacet": true,
"label": "Timeseries_predictioninterval",
"selectMode": "single",
"start": {
"display": [
"95"
]
},
"type": "staticflex",
"values": [
{
"display": "80",
"value": 80,
"Prediction_high": "Prediction_high_80",
"Prediction_low": "Prediction_low_80"
},
{
"display": "95",
"value": 95,
"Prediction_high": "Prediction_high_95",
"Prediction_low": "Prediction_low_95"
}
]
},
"Trellis_Dimension_1": {
"broadcastFacet": true,
"label": "Trellis_Dimension",
"selectMode": "singlerequired",
"type": "staticflex",
"values": [
{
"display": "Type",
"value": "'Account.Type'"
},
{
"display": "Lead Source",
"value": "'LeadSource'"
},
{
"display": "Industry",
"value": "'Account.Industry'"
}
],
"start": {
"display": [
"Type"
]
}
},
SAQL
After we did this pre-work we can tie it together and make the dashboard dynamic. The SAQL which I have so far looks like this.
q = load "opportunities";
result = group q by ('CloseDate_Year', 'CloseDate_Month');
result = foreach result generate q.'CloseDate_Year' as 'CloseDate_Year', q.'CloseDate_Month' as 'CloseDate_Month', sum(q.'Amount') as 'A';
result = fill result by (dateCols=('CloseDate_Year','CloseDate_Month',"Y-M"));
result = timeseries result generate A as B with (dateCols=('CloseDate_Year','CloseDate_Month',"Y-M"), length=24, model="additive", ignoreLast=true);
result = foreach result generate 'CloseDate_Year' + "~~~" + 'CloseDate_Month' as 'CloseDate_Year~~~CloseDate_Month', A as 'A', B as 'B';
result = order result by ('CloseDate_Year~~~CloseDate_Month' asc);
result = limit result 2000;
What I need now is to bind the static steps into the SAQL. For the prediction interval I need to add to 2 more measures on the end which are translating into ‘Prediction_low_80’ and ‘Prediction_high_80’ (or if you selected the 95 interval those objects will end with _95 respective)
q = load "opportunities";
result = group q by ('CloseDate_Year', 'CloseDate_Month');
result = foreach result generate q.'CloseDate_Year' as 'CloseDate_Year', q.'CloseDate_Month' as 'CloseDate_Month', sum(q.'Amount') as 'A';
result = fill result by (dateCols=('CloseDate_Year','CloseDate_Month',"Y-M"));
result = timeseries result generate A as Prediction with (dateCols=('CloseDate_Year','CloseDate_Month',"Y-M"), length={{column(Timeseries_length_1.selection,["value"]).asObject()}}, ignoreLast={{cell(Prediction_IgnoreLas_1.selection,0,"value").asString()}},{{cell(Prediction_seasonali_1.selection,0,"value").asString()}} predictionInterval= {{column(Timeseries_predictio_1.selection,["value"]).asObject()}});
result = foreach result generate 'CloseDate_Year' + "~~~" + 'CloseDate_Month' as 'CloseDate_Year~~~CloseDate_Month', A as 'A', Prediction as 'Prediction', '{{cell(Timeseries_predictio_1.selection,0,"Prediction_high").asString()}}','{{cell(Timeseries_predictio_1.selection,0,"Prediction_low").asString()}}';
result = order result by ('CloseDate_Year~~~CloseDate_Month' asc
With those changes the following chart is already dynamic and we can test which model metrics is fitting the best to our data history.
Breakdown / Drill in
Now we alreay have a good idea how the values are trending….but I still don’t know on what I need to work on to improve the future. Of course we can add global filters on the top and drill in one by one, but this is not very efficient and I want to compare different Dimension elements with each other. Lucky me, Einstein Analytics has the Trellis functionality which I’m going to leverage for this.
Create the Trellis
All I need to do is to create a copy from the original chart we already created and has all the Bindings integrated. Now I need to enable the trellis functionality on this copy. I like to set it to wrap but this depends really on your liking.
When you do this you will see that your chart is broken as it misses the information at this time on which dimension you want to create the Trellis on. We already created the static step for the Dimension, we just need to integrate it as Binding now. As we don’t have an additional grouping yet in our SAQL we need to add it several times. Additionally we need to integrate the partition parameter in the time series function partition={{cell(Trellis_Dimension_1.selection,0,”value”).asObject()}} as Binding as well so it understands on which Dimension elements the Time Series needs to be individually created.
q = load "opportunities";
result = group q by ({{cell(Trellis_Dimension_1.selection,0,"value").asOject()}},'CloseDate_Year', 'CloseDate_Month');
result = foreach result generate q.{{cell(Trellis_Dimension_1.selection,0,"value").asObject()}} as{{cell(Trellis_Dimension_1.selection,0,"value").asObject()}} ,q.'CloseDate_Year' as 'CloseDate_Year', q.'CloseDate_Month' as 'CloseDate_Month', sum(q.'Amount') as 'A';
result = fill result by (dateCols=('CloseDate_Year','CloseDate_Month',"Y-M"),partition={{cell(Trellis_Dimension_1.selection,0,"value").asObject()}});
result = timeseries result generate A as Prediction with (partition={{cell(Trellis_Dimension_1.selection,0,"value").asObject()}},dateCols=('CloseDate_Year','CloseDate_Month',"Y-M"), length={{column(Timeseries_length_1.selection,["value"]).asObject()}}, ignoreLast={{cell(Prediction_IgnoreLas_1.selection,0,"value").asString()}},{{cell(Prediction_seasonali_1.selection,0,"value").asString()}} predictionInterval= {{column(Timeseries_predictio_1.selection,["value"]).asObject()}});
result = foreach result generate {{cell(Trellis_Dimension_1.selection,0,"value").asObject()}},'CloseDate_Year' + "~" + 'CloseDate_Month' as 'CloseDate_Year~CloseDate_Month', A as 'A', Prediction as 'Prediction', '{{cell(Timeseries_predictio_1.selection,0,"Prediction_high").asString()}}','{{cell(Timeseries_predictio_1.selection,0,"Prediction_low").asString()}}';
result = order result by ('CloseDate_Year~~~CloseDate_Month' asc);
Now we can switch the chart into Trellis so we can identify the patterns of each of the elements of the grouping next to each other which hopefully gives us the indicator we are looking for.
Your dynamic Time Series Dashboard
With a few steps I have been able to create my dynamic Time Series Dashboard and I can drill in to find out where I need to action on based on the trend line. In combination with Global Filters on top I can go as deep down in the data as I think it makes sense. This kind of Dashboard is especially useful when it comes to Forecasting and Planning questions and adds a lot of value for the business. Thank you for this new feature EA team!
0 Comments Leave a comment