Create metrics API to provide grouped data

[danielkelemen]

Acceptance Criteria (Required on creation)

• Create new db query to fetch the required grouped data.
  • TU is an accumulated metric from the subscription start.
  • Other metrics are simply grouped for the year or month.
• Create new private metrics admin API in webapps.
  • Name TBD, maybe something along grouped or aggregated.
  • It returns data grouped either by month or year.
  • This way it could be used for both tables.
  • Result for months should mark the
  • Default behaviour TBD by developer.
  • It accepts the subscription start date.
  • This can be different from the date range so we can query the last rolling 12 months data.
  • This is used for the accumulated TU calculation.
  • It accepts an optional date range.
  • If no range is provided it returns the whole available data set. This could be used for the Annual usage metrics table.
  • It accepts a list metrics that are fetched.
  • So it only returns the needed metrics, not all of them. Needed for the first table, where checkbox controls which metrics are displayed.
  • Supports the 5 metric names.
  • Default behaviour TBD by developer.
• Add API documentation.
• Add test coverage.

Example response for months

- subscriptionStart = 15. Feb 2023 - groupBy = month - period = last 12 months - metrics = TU,PI,DI ```json [ { // TU shows value from contract start! "startDate": "2023.01.15", "subscriptionYear": 2022, "metrics": { "TU": 100, "PI": 123, "DI": 123 } }, { "startDate": "2023.02.15", "subscriptionYear": 2022, // last subscription month for 2022 "metrics": { "TU": 110, "PI": 123, "DI": 123 } }, { "startDate": "2023.03.15", "subscriptionYear": 2023, // first subscription month for 2023, TU resets "metrics": { "TU": 10, "PI": 123, "DI": 123 } }, { "startDate": "2023.04.15", "subscriptionYear": 2023, "metrics": { "TU": 20, "PI": 123, "DI": 123 } }, { "startDate": "2023.05.15", "subscriptionYear": 2023, "metrics": { "TU": 30, "PI": 123, "DI": 123 } } ] ```

Example response for years

- subscriptionStart = 15. Feb 2023 - groupBy = year - period = null (=all available years) - metrics = all ```json [ { "startDate": "2021.01.15", "subscriptionYear": 2021, "metrics": { "TU": 100, "PI": 123, "DI": 123, "FNI": 2, "EDE": 3 } }, { "startDate": "2022.01.15", "subscriptionYear": 2022, "metrics": { "TU": 110, "PI": 123, "DI": 123, "FNI": 2, "EDE": 3 } }, { "startDate": "2023.01.15", "subscriptionYear": 2023, "metrics": { "TU": 110, "PI": 123, "DI": 123, "FNI": 2, "EDE": 3 } } ] ```

Hints

• The query could be a simple group by for either year or month depending on the parameter. We already have something similar for the interval using MILLISECONDS_.
• For the accumulated TU we could override the period and get all the months from subscription start so we can calculate the accumulated result in java without a complex query or subquery.
  • We could also return all results from previous subscription year and even display them.

Links

Breakdown

### Tasks
- [ ] https://github.com/camunda/camunda-bpm-platform/pull/4084
- [ ] https://github.com/camunda/camunda-bpm-platform/pull/4155

[danielkelemen]

Decisions:

• The query result needs post processing: fill up missing months/years, accumulate TU metric.
  • Decided to have a minimal API that returns the query result and perform these on the frontend.
• TU metrics are in a separate and slightly different table (ACT_RU_TASK_METER_LOG)
  • Defined two separate queries (selectMetricsAggregated & selectMetricsAggregatedTU).
• Query needs to extract time units (year, month and day) and there is no globally supported feature for that.
  • Added a new database specific property called extractTimeUnitFromDate as MSSQL does not support the SQL EXTRACT() feature.
• MS SQLServer had a problem with case..when and group by in one select.
  • Added a sub-select that contains the case..when and the group by happens in the outer select.

How does the query work?

The query calculates and groups on two new key columns: SUBSCRIPTION_YEAR_ and SUBSCRIPTION_MONTH_. These are calculated based on the provided subscriptionStartDate and the meter log TIMESTAMP_. The subscriptionStartDate is always applied with respect to the meter log's year and month, it's not just one static date.

TL;DR: If a meter log is before the subscription start's month and day then it belongs to the previous subscription period, if it's after then to the current period.

Example:

Let's say our subscriptionStartDate=15/02/2024 and we have one meter log with TIMESTAMP_=14/02/2024, then:

• groupBy=year => meter log belongs to subscription year 2023 as it's within the 15/02/2023-14/02/2024 period.
  • Query returns: SUBSCRIPTION_YEAR_=2023, SUBSCRIPTION_MONTH_=null
• groupBy=month => meter log belongs to subscription month January as it's within the 15/01/2024-14/02/2024 period.
  • Query returns: SUBSCRIPTION_YEAR_=2024, SUBSCRIPTION_MONTH_=1
  • 💬 The SUBSCRIPTION_YEAR_=2024 is a bit confusing here because the log belongs to the subscription year 2023. However, we group by month and the meter log's month belongs to January in 2024. Alternatively, we could add another column to make it clearer.

If the meter log had TIMESTAMP_=15/02/2024, then:

• groupBy=year => meter log belongs to subscription year 2024 as it's within the 15/02/2024-14/02/2025 period.
  • Query returns: SUBSCRIPTION_YEAR_=2024, SUBSCRIPTION_MONTH_=null
• groupBy=month => meter log belongs to subscription month February as it's within the 15/02/2024-14/03/2024 period.
  • Query returns: SUBSCRIPTION_YEAR_=2024, SUBSCRIPTION_MONTH_=2

[danielkelemen]

Problem:

TU metric shows unique users within the period. In the monthly view we want to show the accumulated TU metric to which we need the unique users from subscriptions start until the end of the month. We can't just add up the monthly unique users because that can count users multiple times. TU query needs to be adjusted.

Solution: In the monthly TU query, we group by ASSIGNEEHASH and only count the earliest occurrence within the selected period.

[danielkelemen]

Problem:

In webapp/admin source package there are no classes for db queries and for this feature we need a new private API with query.

• Options:
  • Move the cockpit db package to one level up and use that.
    • Cons: This would be a braking change for cockpit plugins!
  • Use the cockpit packages.
    • Cons: It's a bit unusual to use packages from other webapps packages.
    • Pros: Simple solution. No duplicate class files.
  • Copy the classes to have them in webapp or admin too.
    • Cons: Duplicates, classes would be exactly the same.
    • Pros: No cross references between webapp packages.
• Decision: 3rd option, copied the required db, impl.db and rest.dto packages to webapp.