Remove Duplicate Entries in Custom Metric Data Using DQL

[knallapa]

How can I eliminate duplications in DQL when working with timeseries custom metric data filtered by conditions?
USECASE: Loading Custom metrics in below query 10 times in timeseries is killing DQL Storage bucket. I attempted to use variables to reuse the metric, but it didn’t help—each usage resulted in new data buckets being created. This caused the query to explode to over 500 million data points Error from Dynatrace Limitations, even though the source dataset only contains 43,000 records with 11 columns in the table.

For Above Query Nothing worked for me: Tried variables, Problems i faced is : 

• can’t fetch a metric once and reuse it with conditional logic applying filters.
• can’t assign a timeseries result to a variable inside fetch.
• can’t use let to define reusable filters inside fetch.

Query is : 

timeseries METRIC_A_approved = sum(
`custom.app-metrics.transaction.data`,
scalar: true,
filter: {
txType == "TypeA" AND
(responseCode == "0" OR responseCode == "00") AND
(isNull($Entity) OR $Entity == "" OR entity == $Entity)
}
), by: { entity }

| join [
timeseries METRIC_A_total = sum(
`custom.app-metrics.transaction.data`,
scalar: true,
filter: {
txType == "TypeA" AND
(isNull($Entity) OR $Entity == "" OR entity == $Entity)
}
), by: { entity }
], on: { entity }, fields: { METRIC_A_total }, kind: outer

| join [
timeseries METRIC_B_approved = sum(
`custom.app-metrics.transaction.data`,
scalar: true,
filter: {
txType == "TypeB" AND
(responseCode == "0" OR responseCode == "00") AND
(isNull($Entity) OR $Entity == "" OR entity == $Entity)
}
), by: { entity }
], on: { entity }, fields: { METRIC_B_approved }, kind: outer

| join [
timeseries METRIC_B_total = sum(
`custom.app-metrics.transaction.data`,
scalar: true,
filter: {
txType == "TypeB" AND
(isNull($Entity) OR $Entity == "" OR entity == $Entity)
}
), by: { entity }
], on: { entity }, fields: { METRIC_B_total }, kind: outer

| join [
timeseries METRIC_B_approved_subgroup = sum(
`custom.app-metrics.transaction.data`,
scalar: true,
filter: {
txType == "TypeB" AND
(responseCode == "0" OR responseCode == "00") AND
(categoryCode in ["1001", "1002", "1003", "1004", "1005"]) AND
(isNull($Entity) OR $Entity == "" OR entity == $Entity)
}
), by: { entity }
], on: { entity }, fields: { METRIC_B_approved_subgroup }, kind: outer

| join [
timeseries METRIC_B_total_subgroup = sum(
`custom.app-metrics.transaction.data`,
scalar: true,
filter: {
txType == "TypeB" AND
(categoryCode in ["1001", "1002", "1003", "1004", "1005"]) AND
(isNull($Entity) OR $Entity == "" OR entity == $Entity)
}
), by: { entity }
], on: { entity }, fields: { METRIC_B_total_subgroup }, kind: outer

| join [
timeseries METRIC_B_approved_domestic = sum(
`custom.app-metrics.transaction.data`,
scalar: true,
filter: {
txType == "TypeB" AND
(responseCode == "0" OR responseCode == "00") AND
categoryCode == "2001" AND
(isNull($Entity) OR $Entity == "" OR entity == $Entity)
}
), by: { entity }
], on: { entity }, fields: { METRIC_B_approved_domestic }, kind: outer

| join [
timeseries METRIC_B_total_domestic = sum(
`custom.app-metrics.transaction.data`,
scalar: true,
filter: {
txType == "TypeB" AND
categoryCode == "2001" AND
(isNull($Entity) OR $Entity == "" OR entity == $Entity)
}
), by: { entity }
], on: { entity }, fields: { METRIC_B_total_domestic }, kind: outer

| join [
timeseries METRIC_B_approved_crossborder = sum(
`custom.app-metrics.transaction.data`,
scalar: true,
filter: {
txType == "TypeB" AND
(responseCode == "0" OR responseCode == "00") AND
categoryCode == "2002" AND
(isNull($Entity) OR $Entity == "" OR entity == $Entity)
}
), by: { entity }
], on: { entity }, fields: { METRIC_B_approved_crossborder }, kind: outer

| join [
timeseries METRIC_B_total_crossborder = sum(
`custom.app-metrics.transaction.data`,
scalar: true,
filter: {
txType == "TypeB" AND
categoryCode == "2002" AND
(isNull($Entity) OR $Entity == "" OR entity == $Entity)
}
), by: { entity }
], on: { entity }, fields: { METRIC_B_total_crossborder }, kind: outer

| fieldsAdd
Rate_A = if(coalesce(METRIC_A_total, 0) == 0, 0, 100 * coalesce(METRIC_A_approved, 0) / coalesce(METRIC_A_total, 0)),
Rate_B = if(coalesce(METRIC_B_total, 0) == 0, 0, 100 * coalesce(METRIC_B_approved, 0) / coalesce(METRIC_B_total, 0)),
Rate_B_subgroup = if(coalesce(METRIC_B_total_subgroup, 0) == 0, 0, 100 * coalesce(METRIC_B_approved_subgroup, 0) / coalesce(METRIC_B_total_subgroup, 0)),
Rate_B_domestic = if(coalesce(METRIC_B_total_domestic, 0) == 0, 0, 100 * coalesce(METRIC_B_approved_domestic, 0) / coalesce(METRIC_B_total_domestic, 0)),
Rate_B_crossborder = if(coalesce(METRIC_B_total_crossborder, 0) == 0, 0, 100 * coalesce(METRIC_B_approved_crossborder, 0) / coalesce(METRIC_B_total_crossborder, 0))

| filter not(isNull(entity) or entity == "")
| fieldsAdd
Entity = entity,
A.Total = toLong(coalesce(METRIC_A_total, 0)),
A.Rate = concat(toString(round(coalesce(Rate_A, 0), decimals: 2)), "%"),
B.Total = toLong(coalesce(METRIC_B_total, 0)),
B.Rate = concat(toString(round(coalesce(Rate_B, 0), decimals: 2)), "%"),
B.Total.Sub = toLong(coalesce(METRIC_B_total_subgroup, 0)),
B.Rate.Sub = concat(toString(round(coalesce(Rate_B_subgroup, 0), decimals: 2)), "%"),
B.Total.Dom = toLong(coalesce(METRIC_B_total_domestic, 0)),
B.Rate.Dom = concat(toString(round(coalesce(Rate_B_domestic, 0), decimals: 2)), "%"),
B.Total.XB = toLong(coalesce(METRIC_B_total_crossborder, 0)),
B.Rate.XB = concat(toString(round(coalesce(Rate_B_crossborder, 0), decimals: 2)), "%")

| fields Entity, A.Total, A.Rate, B.Total, B.Rate, B.Total.Dom, B.Rate.Dom, B.Total.XB, B.Rate.XB, B.Total.Sub, B.Rate.Sub
| sort A.Rate desc

[IzabelaRokita]

Hey @knallapa ,
I just wanted to check in and see if you still need help with this. If so, I’d be happy to look into it for you! 😊
Please let me know what works best for you.