How to calculate metrics from formula builder?

Problem Statement
We are developing a metric store builder for our FP&A tool, Mantys, to enhance our customers’ data analysis capabilities. While we have successfully implemented simple metric aggregations, such as sums and averages, using cube.dev as our semantic layer, we face challenges when scaling for more complex formula metrics.

Simple Metrics
Formula Metrics

Example Formula Metric:
CAC Metric = OFFSET((S&M Expenses – Customer Marketing Expenses), -1)/ New Customers

Solution
Our simple metrics involve straightforward aggregations from column values, which work well with cube.dev. However, for more advanced requirements, we need formula metrics that are derived from other metrics rather than directly from tables or columns. These formula metrics incorporate various analytical functions such as Offset, If, Equals, Min, Max, Sum, BoMonth, and EoMonth.
Challenges

Scalability: Cube.dev handles simple metrics efficiently but struggles with the complexity and performance requirements of formula metrics.

Complexity: Formula metrics require combining multiple metrics and applying advanced analytical functions, which is beyond the current capabilities of our cube.dev setup.

Performance: Ensuring that these complex calculations perform well and return results in a timely manner is a significant challenge.

Flexibility: We need a solution that allows for the dynamic creation and adjustment of formula metrics without extensive rework or customization.

Approaches:

Compose SQL and apply database UDFs
Compose Dataframes and apply pandas/spark UDFs
Low Level Metric Store implementation using Apache Druid/Apache Pinot

enter image description here

Pros:

This can be run against the databases as the final output is just plain SQL
No need for any Deployment and CI/CD pipeline automation

Limitations:

UDFs and UDAFs supported natively by databases can be limited
- Functions like EOmonth won’t be supported
Still in this approach we depend on Cube.dev Semantic Model for creating JSON to SQL

Tools used:

Cube (Simple Metrics)
SQL (complex metrics)

Where are we in experimentation?

We are trying to check whether we can store the existing metrics SQL query against the store Metrics and compose the CTEs dynamically
- Doing through trial and error for now, an agency might be able to give us the right sequence

enter image description here

Pros:

Since we are using programming languages like Python, we can create all the necessary UDFs and UDAFs for the metric builder.

Limitations:

This can’t be run against the databases, instead need to be executed against Spark/AWS EMR Clusters for large datasets: Infra challenge
Deployment and CI/CD pipeline automation can be complex
Delay in data- not real time

Tools used:

Cube (Simple Metrics)
Spark/Pandas
GCP DataProc/AWS EMR

Where are we in experimentation?

We are trying to check whether we can store the existing metrics SQL query against the store Metrics and compose the CTEs dynamically
How to store it effectively against the clusters? How to store and run it against dataframes?
Github POC Link: https://github.com/mohitPanwar1996/metric-store-builder

Approach 3: Low Level Metric Store Implementation using Apache Pinot/Druid
In this approach we explored Apache Pinot(Star Tree) and Apache Druid(Imply) for building metrics in real time during streaming/batch ingestion. But we were not able to make any progress due to the following reasons.

Pros:
Build Metrics in real time (the value will reflect in real time)

Limitations:

Streaming Ingestion(mostly all of these services built on Kafka)
Complex cloud setup
Table Transformations not supported
OBT(One Big table) approach is recommended mostly; Joining tables is not there
Only SQL REST API are supported not like cube.dev(check the below screenshot for reference)

Tools Used:

Star Tree Cloud/Imply Cloud – Processing
Kafka/AWS MSK/Confluent – Streaming
Apache Presto/Trino – Querying

Where are we in experimentation?

We connected with Imply and StarTree cloud team for understanding their offerings
Tried creating measures, dimensions and metrics but we were not able to create derived metrics or formula based metrics on the fly. Need to explore.

enter image description here

Request for Advice
We are seeking advice, solutions, or recommendations from other consultants, forums, and consultancies on how to effectively implement and scale formula metrics in our system. Specifically, we are interested in best practices, tools, or strategies that could help overcome the limitations we are experiencing with cube.dev in handling these advanced use cases.

Questions where we need help?

How to effectively implement the formula based metrics?

a. We are trying to check whether we can store the existing metrics SQL query against the store Metrics and compose the CTEs dynamically for the first approach (Approach 1)

b. How to store it effectively against the clusters? How to store and run it against dataframes? (Approach 2)
How to implement the analytical functions against the formulas?
How to scale this across our multi tenant architecture?

References:

In our R&D we have gone through various formulas builder that other companies are using:

Mosaic: https://www.mosaic.tech/metric-builder
Drivetrain: [https://docs.drivetrain.ai/formulas/formulas-in-drivetrain
Causal: https://www.causal.app/](https://stackoverflow.com)
IBM Formula Builder – https://www.ibm.com/docs/en/mas-cd/maximo-monitor/continuous-delivery?topic=calculations-using-simple-functions

Screenshots:
enter image description here

enter image description here

Filed under: Kiến thức lập trình - @ 23:02

Thẻ: pythonexpressionmetricsfinanceolap-cube

Thiết kế website giá rẻ

Danh mục

How to calculate metrics from formula builder?