Data model/structure for maximum data-visualisation potential?

I'm developing a data collection system for my parents' business and would like to put the data to good use by indexing it and visualising it. The data will be recorded as daily reports and will contain the following fields:

  • date (yyyy-MM-dd)
  • weather
  • cash_income
  • card_income

I've been thinking and attempting a few different ways of structuring this data in an index over the past week. The two visualisations that are giving me conflict at the moment are below:

  1. cash_income + card_income bar chart grouped by weather
  2. cash_income vs card_income pie chart

I know that if I was to simply throw my whole daily report into an index, then the first visualisation would be easily achieved through a scripted field that sums my income fields and a Terms agg on weather to determine the bars on my bar chart. However, my issue here lies with the second vis, as in a Kibana pie chart, you cannot determine slices by different fields.

On the other hand, if I was to normalise my daily reports into two reports each day, one containing cash_income the other containing card_income, then the second vis becomes doable as long as I specify which payment method the report is for and keep the amount in a commonly named field for both methods.

However, this time my issue is one of efficiency. I know that the way of Elasticsearch is to denormalise your data and that if I was to add more fields that I'd like to compare, say supplier bills for example, then I would have to make even more documents for each day which would all comprise of 90% of the same data.

For note, I have looked into both nested fields and parent/children relations, unless anyone can mention otherwise, I believe atm both of these won't do the job for me.

So my question is, how can I structure my data to maximise the comparisons I can make between fields, but also avoid repeating my data for the sake of changing one field?

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.