Live Business Intelligence (BI) with Salesforce, Elastic Search, Heroku and Kibana – Part 2 – Dashboards

In the second article we saw the business context and how we index data of Salesforce and Heroku into ElasticSearch. 

In this article , we will discover the goal of all this, creating real time comprehensive Dashboards with Kibana, data visualization tool of Elastic.

User rights management

Wartner has 3 types of users for visulaisation for their business needs.

• Manager: 
  • Need to view all information
  • Need to filter information easily without any technical knowledge
• Production Team :
  • Need to access production Dashboards but not the financial ones
  • Need to have information very quickly
• Client :
  • Need to access relevant information just for his account
  • Need to filter information easily without any technical knowledge

In the first part of the article, we saw how to add automatic filters on clients’ users to secure access. 

For the production team, we just add restriction on index themselves, by restricting access to the financial information.

Kibana don’t yet allow to restrict access to specific dashboard or space. Let’s hope this will come soon.

Dashboards in Kibana

Designing of dashboard should be though carefully so as to arrange data visualizations intelligently.
For an optimal reporting, we need to think of each real use case.
And sometimes, you may need to create specific indexes. 

Note : with the 7.3, the brand new data frames can help you to create specifics indices for one visualisation.  

4 Dashboards have been created for the administration with 40 visualisations, 3 of them are indices centric : 

• Finance
• Production
• History

A dedicated dahsboard has been designed to have a ‘client’ view. It enables to have a resume of the whole client activity.
Financials and Production dashboards are among the easiests one. 
Indexes are small (< than 10Gb ) and requirements were straightforward. 

Note : Due to confidential information , the dashboard shown below are based on random data. In no circumstances, it shares any business or metiers information.

Financial Kibana Dashboards

Financial index is a good use case for Kibana,. We want to see information for a defined period of time : last month, last year, last fiscal year, etc…

So the field created at has been defined as @timestamp in the Kibana index definition. 

Production Kibana Dashboard

The Followup index is problematic, because it’s more a static index. The time period does not really make sense here because we want the status of one product, the products quantity in one store, or where are the products of a specific client. 

So the @timestamp has been set to the updated_at field, but is not really usefull. 

Histories Kibana Dashboard

For histories, the problem was more complicated. 

We are on a polymorphic object, with a JSON unstructured field that holds a lot of different data types, with repetitions and not easy to qualify before ingestion with our strategy.

One important detail here :
The JSON field is difficult to use in BI cases in Postgres, but adding it to ElasticSearch made it magically structured. It’s just a normal “Nested Data Type” with classical properties inside.
It’s now very easy to use them as usual.

For qualifying data, we use a trick with the powerful ‘scripted fields’ in Kibana. 

Here is an example of painless script for a field : 

 if(doc['audited_changes.step_id'].value==4){ 
return 'Réception'
 } else if(...) { 
 	… 
} 

It’s not always a good solution, as everything is done on memory. But for some specific use cases, it really helps us to give sense to numeric data.

And the volume is definitely not the same : 200Gb, growing quickly. 
It’s the index that defined our sizing needs.
For this one we have to be careful and unfortunately, request over more than one year can break the dashboard.

The whole elastic search cluster has been defined specifically for this index : A request on the History dashboard in Kibana, on 3 years must not break the cluster

It will be a good case for implementing a life cycle, or simply a time to live. 

It’s typically of log table, so the created_at database field is an equivalent to kibana @timestamp.

Then we got usable data, we can begin to use it. 

The Client needs was to know : 

• How many actions have been done in a day, or a month
• How many products has been sent on last february for a specific client
• Etc…

And here we are : 

Client multiple indices Kibana Dashboard

The Client Dashboard is very interesting case. 

• It’s based on multiple indices: Production, Invoicing, history…
• It exists in 2 versions, one for internal use, one for client usage, the only difference is that a client has simplified controls, and cannot filter on accounts (everything is already filtered by user role – see part 1) 
• It use one ad’hoc index for one visualization.
  For the visualization on ‘order vs shipping’ the business need was not easy to render with our current indexes. It’s typically a ‘I need a join’ problem
  And the need was to aggregate over aggregations.
  So we created a special index with data already grouped by day.

We wanted to have all the needed clients information in one simple view.

One interesting thing here is that we can embedded the dashboard directly in the Salesforce clients’ communities. 

So it’s totally transparent for them.

Her is what we got : 

Controls

In the part 1, we specified some custom fields on all our models : product_name, product_id, account_name and account_id

You can see in previous Dashboards that we add a ‘control visualization’ on the top of the dashboard.

That’s why we added normalized product and account data on each line of each document in elastic search. 

So the filter can be used to filter for account and product on all visualisation of the Dashboard, even for different indexes. 

It’s not perfect because other filters cannot be applied to everything. But it’s already very useful.

Note: Control visualization are in Beta so it’s limited for now

Summary

A common problem nowadays it to give meanings to the larger and larger datasets every company has. 

In 15 days of work, we manage to  

1. Give Real time view on moving data for the company
2. Lower the unedeed client orders caused by a lack of visibility
3. Protecting production database of heavy reporting request
4. Open a wild field of reporting possibilities in the near future.

Elastic Search is really powerful, and usage of the Elastic Cloud makes the cluster really easy to manage.  

Kibana is not perfect (we still wait for dynamic columns for table views) but allow us to do very powerful things easily. 

In this case, we did not need to use map, but playing with geo data is also a real pleasure in it. 

If you want Spoon Consulting Expert to help you on you business needs and implementation with Salesforce, Elastic Search, or Salesforce and Elastic search, don’t hesitate to contact us.