0

I have an excel like table with a should value and an is value for each day of a month:

descrip. |        | 01 | 02 | 03 | 04 |
_______________________________________
column 1 | should | 60 |  0 | 60 |  0 |
         | is     | 60 |  0 | 60 | 60 |
_______________________________________
column 2 | should |  0 | 15 |  0 | 15 |
column 3 | is     |  0 |  0 |  0 | 15 |

I need the values of this table for two purposes:

  1. Extract some statistics (total; should / is ratio; etc)
  2. Based on the values see if the actions (entry in column) are done
    • Just once
    • Once every week
    • daily
    • periodically

Should I get the statistics with SQL queries or calculate them with JavaScript based on the JSON response? What are the (dis)advantages

Some additional information:

I'm using SpringBoot with JPA and a PostgreSQL database.

Tables:

  • Chart
  • Column
  • Action (should / is values)

Here is a part of my JSON response:

"columns": [{
        "id": 12,
        "should": [{
            "id": 13,
            "date": 1438552800000,
            "min": 60
        }],
        "is": []
        }
    }]
6
  • 1
    How often does the data change, how often will it be queried and are there any other uses for this data as is? Commented Sep 15, 2016 at 20:31
  • Here a little restriction of JPA and native Sql result mapping stackoverflow.com/a/13013323/5934037. Can't remember if it still happens in JPA 2.x
    – Laiv
    Commented Sep 15, 2016 at 21:27
  • @MatthewWhited: 1) The date does change once or twice a day per different planning. In total this would be like 20-40 queries/day 2) The queries for the statistics are somewhere around 100/month 3) Previously the statistics were on the same page as the data input. But the statistics are now in an own page (Maybe later in a dashboard - but for this a data warehouse would be the better solution).
    – devz
    Commented Sep 16, 2016 at 11:41
  • Currently I'm using the native SQL / JPA approach. I've been able to map the results to non entity objects.
    – devz
    Commented Sep 16, 2016 at 11:43
  • 1
    Based on your volume you can probably get away with just querying over your data. Going though the added crazy for ETL or cubes probably isn't worth the cost. Commented Sep 16, 2016 at 13:56

2 Answers 2

1

As @Ewan has said, it would be better to have these statistics already calculated (we have here different options. Ewan pointed to one of them).

Back to the question, here some disadvantages of each solution:

  • Client side: Depending on the complexity of the calcs and the volume of the data to retrieve; to load the page may take longer than usual. In mobile clients, it also has an impact on the memory and the battery (the charge is limited here). Then comes the fact that statistics should be calculated every time the page is loaded.

    Important to say that, if data is not persisted in any sort of cache, is quite easy to overload the DBMS just by clicking F5 like crazy.

    Summarising, it affects the UX, and in some cases, it has an impact on the resources (memory, battery, CPU, ...)

  • DB-side: DB is a shared resource. Some calcs may cause a serious problem of performance to the DBMS and any application connected to the DB is going to suffer the consequences.

    At this point is good to know the concurrency (possible number of simultaneous requests to the DB asking for the statistics) and the frequency of these requests. The volume of the data and its complexity also matters.

    Then comes the maintenance. Depending on the complexity of the SQL statements, the task could be dramatic.

    However, DBMSs are good at doing calcs, aggregates, etc. But you should be careful because you can end up taking down the DBMS (true story).

0

Neither, You should use a data warehouse, calculate your statistics via a MDX query and store the result.

your website can then present the stored results without having to recalculate on each page request.

1
  • 2
    You can calculate with either of the OP's methods and store the results for later use. Not sure why MDX would be required. It's nice if you know how to use it.
    – JeffO
    Commented Aug 17, 2016 at 16:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.