A Santa Claus Tale
Here I was at home and out of nowhere I heard a voice, when I looked back all I could see was multicolored smoke. When it started to fade out a figure started to take shape. I could not believe my eyes, I was mesmerized: it was Santa! I froze and slowly grabbed my mobile as I was ready to call the police. He walked in my direction and said with a soft but firm voice: "Hi Tamara, I was wondering if you could help me.", I was so shocked that I stammered "Meee? How could I ever help Santa?". I could barely believe that I said "Santa". He looked straight into my eyes, with kind eyes and said, "Can you help me to monitor my gifts production for Christmas?". I pinched myself myself and said "Yes".
In a blink of an eye, here I was at Santa’s place, in the last hours before Christmas. Everything was so bright and colorful and red. I heard voices and laughs. I could barely move, and then I heard a reindeer speaking: "Hi, Tamara, I am so happy you are here, please follow me", I moved my hand over my eyes and said to myself: "a talking reindeer", I still cannot believe it. He started to walk and I just followed. He showed me the magic machine. It looked a bit outdated I have to assume, but it seemed to be working. There was a brand new sensor in the machine, the sensor would create an event after each batch of gift was created, with information what sort of gift is it.
After some work with "the reindeer responsible for the machine" we managed to send the data through the API to elasticsearch. The metric was very simple:
{
'timestamp': '2019-12-23T19:36:36.337204+00:00',
'gift': 'educational',
'quantity': 3205
}
The gifts are categorized as educational, creative, animals and coal (for the naughty kids). And there is a 'quantity' field, because the machine produces a certain quantity of gifts as soon as a kid has "earned" the Christmas gift.
The elves are the ones monitoring the process, the machine needs some manual input once in a while as some kids start to misbehave and move to the "naughty" list, or they can leave it as well. The manual assessment of the elves is very complex as they connect mentally to those kids and perform a deep analysis to decide if they actually will change gift category. This process can not be automated.
The elves and I after a long conversation decided how we would ingest the result of the analysis. The document looks like this:
{
'timestamp': '2019-12-23T19:36:36.337204+00:00',
'kid_id’: '123746',
'changed_gift': 'yes',
'new_gift': 'creative',
'old_gift': 'coal'
}
Now is where my job started. In order to help Santa and elves I felt I needed to add extra data to make some nice visualizations for Santa. First, how many kids there are around the world, according to the World Bank there are 1.958 billion kids.
I wanted to have a nice Canvas to show in real time the production and how far it was from the goal. Everyone looked so happy and cheerful that I decided to make a personalized Canvas. In order to get the whole power of canvas I have used the powerful expression language that Canvas is driven by, with dozens of functions and other capabilities, including table transforms, type casting, and sub-expressions.
The Canvas is composed of many elements. The most complicated one is the "Manual Input Table", the table that shows the amount of gifts that has changed gift category, needing to add to the production and subtract to the creation of the new ones.
Creating the Manual Input Table
- Click on the "Add element" button
- Choose the "Data Table" element
- In the editing panel on the right, select the "Data" tab
- Click "Change your data source"
- Select "Elasticsearch SQL"
- Type the following into the SQL query editor:
SELECT new_gift, old_gift, COUNT(*) as total
FROM "change_gift"
WHERE
"changed_gift"='true' AND
timestamp < DATE_ADD('hour', -1, NOW()::datetime)
GROUP BY
New_gift,old_gift
- Click "Save"
- You should now have a data table that looks something like the following:
Updating a Column
The "total" is just an update of the column total, making it negative if a kid moved to the "coal" category (naughty list) from another category and positive column when moved out from it.
- Make sure the data table element is selected and the expression editor is open
- We need to add a mapColumn expression, the mapColumn create a new field or update an existing one, we are going to update the "total" column
| mapColumn name="total"
fn={if {getCell "new_gift" | eq "coal"} then={math "multiply(total, -1)"} else={getCell "total"}}
name: the name of the new column
fn: the result of the new column
- We will use the if expression
fn={if {getCell "new_gift" | eq "coal"} then={math "multiply(total, -1)"} else={getCell "total"}}
- In combination we will use the math expression, the math expression enables performing complex calculations. It accepts a string using Tiny Math as input
math "multiply(total, -1)"
We can have the values of the columns explicit inside the string.
The column updated
Creating a Column
The "To-From" is a new column. It is created to facilitate the joining of the rows. The data is normalized using a concatenated string. This string is composed of the "new_gift" and "old gift", ordered such that the concatenated string does not start with "coal".
- We need to add a mapColumn expression again, to add the "To-From" column
| mapColumn "To-From"
fn={if {getCell "new_gift" | eq "coal"} then={string {getCell "old_gift"} "-" {getCell "new_gift"}} else={string {getCell "new_gift"} "-" {getCell "old_gift"}}}
You can omit the name and name the column directly.
- We will use the if expression
fn={if {getCell "new_gift" | eq "coal"} then={string {getCell "old_gift"} "-" {getCell "new_gift"}} else={string {getCell "new_gift"} "-" {getCell "old_gift"}}}
- In combination we will use the string expression, the string expression concatenate strings, in this case 3.
string {getCell "old_gift"} "-" {getCell "new_gift"}
- Also we are going to use the getCell expression, the getCell expression retrieves the value of the column that is being analyzed
getCell "new_gift"
The new column created
Joining Rows
In the step above you should see the results of the tables just concatenated and the total negative or positive. Now, we will join the rows that have the same value using the "To-From" field we have just created for this purpose.
- We need to add a ply expression, the ply expression creates a new table with the values joined like a "group by" from SQL
| ply by="To-From" fn={math "sum(total)" | as "Total"}
by: the new column with the distinct values concatenated
fn: a sequence of new fields, in this case the sum of the total field
as: naming the column
- All together
filters
| essql
query="SELECT new_gift,old_gift,COUNT(*) as total FROM "change_gift" WHERE
"changed_gift"='true' AND
timestamp < DATE_ADD('hour', -1, NOW()::datetime)
GROUP BY new_gift,old_gift"
| mapColumn name="total"
fn={if {getCell "new_gift" | eq "coal"} then={math "multiply(total, -1)"} else={getCell "total"}}
| mapColumn "To-From"
fn={if {getCell "new_gift" | eq "coal"} then={string {getCell "old_gift"} "-" {getCell "new_gift"}} else={string {getCell "new_gift"} "-" {getCell "old_gift"}}}
| ply by="To-From" fn={math "sum(total)" | as "Total"}
| table
font={font family="'Open Sans', Helvetica, Arial, sans-serif" size=24 align="left" color="#000000" weight="bold" underline=false italic=false}
| render
metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center" lHeight=48}
labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"} metricFormat="0,0"
| render
The End Result Element
Complete Canvas
After a lot of time working in the canvas this is my end result:
After some time running the Canvas I noticed by the projections that all the gift produced would not be enough for Christmas. I sadly showed Santa the result of my work and asked: "I am worried about the production, because by the projection we won’t be able to supply all the gifts needed for the kids". Santa looked at me calm and serene, and said "I know, that’s why I rely on parents, family and donations to make the magic of Christmas real all over the world". I smiled at him, said goodbye to everyone and went back home with a heart full of Christmas spirit.
The magic of Christmas is up to all of us. So let’s help Santa spread the magic of Christmas all over the world, donating to food banks, organization or even to a stranger.