10

At first glance, it seems that with Elasticsearch as a backend it is easy and fast to build reports with pivot-like functionality as used in traditional business intelligence environments.

By "pivot-like" I mean that in SQL-terms, data is grouped by one to two dimensions, filtered, ordered by one or two dimensions and aggregated by several metrics e.g. with sum or count.

By "easy" I mean that with a sufficiently large cluster, no pre-aggregation of the data is required, which saves ETLs and data engineering time.

By "fast" I mean that due to Elasticsearch's near real time capability report latency can be reduced in many instances, when compared to traditional business intelligence systems.

Are there any reasons, not to use Elasticsearch for the above purpose?

user1091141
  • 581
  • 7
  • 19

1 Answers1

13

ElasticSearch is a great alternative to a cube, we use it for that same purpose today. One huge benefit is that with a cube you need to know what dimensions you want to create reports on. With ES you just shove in more and more data and figure out later how you want to report on it.

At our company we regularly have data go through the following life cycle.

  1. record is written to SQL
  2. primary key from SQL is written to RabbitMQ
  3. we respond back to the customer very quickly
  4. When Rabbit has time, it uses the primary key to gather up all the data we want to report on
  5. That data is written to ElasticSearch

A word of advice: If you think you might want to report on it, get it from the beginning. Inserting 1M rows into ES is very easy, updating 1M rows is a bigger pain.

jhilden
  • 12,207
  • 5
  • 53
  • 76
  • 5
    This is a very interesting insight, thank you! It is often considered that the benefit of an OLAP cube is OLAP like complex queries over a big data set. In your example, it seams that you are serving single record related data to the clients. That's not a typical use case for an OLAP DB, but rather a case for an OLTP DB. I'm curious, whether you succeeded to efficiently support OLAP queries over all data volume available to you in Elastic Search? At which concurrency level and for which amounts of data? – Sergey Shcherbakov Feb 03 '17 at 10:40