To GoodData homepage
Solved

Some metrics don't work

  • 7 March 2022
  • 7 replies
  • 117 views
V

We're having trouble with a metric that calculates the ratio of the number of candidate_id with the status "Hired" to the total number of candidate_id. We first tried to evaluate this MAQL query, but it doesn't work.

SELECT(SELECT COUNT({attribute/dshb_recruiting_ratio.job_posting_candidate_id}) WHERE {label/dshb_recruiting_ratio.application_status} = "Hired")/COUNT({attribute/dshb_recruiting_ratio.job_posting_candidate_id})

Then we tried to calculate two independent  metrics


and then to calculate the ratio

SELECT TRUNC({metric/recruiting_ratio_status_hired} / {metric/recruiting_ration_total}, 4) or SELECT TRUNC({metric/recruiting_ratio_status_hired}, 4) / TRUNC({metric/recruiting_ration_total}, 4)

This didn't work either. We received an error message

java.sql.SQLException: exception while executing query: UNSUPPORTED_OPERATION ERROR: This query cannot be planned possibly due to either a cartesian join or an inequality join. If a cartesian or inequality join is used intentionally, set the option 'planner.enable_nljoin_for_scalar_only' to false and try again.
icon

Best answer by Ulku Kijasev 7 March 2022, 15:32

View original

7 replies

U
Rank

What is the error log you’re receiving. Normally in other data sources these metrics should work fine. But with Apache Drill there are some problems with cartesian (cross joins), have you set planner.enable_nljoin_for_scalar_only=false. Since Apache drill is not a stable product, some of the generated SQL’s don’t work with it. If you send me the error log, we can troubleshoot it better. Thanks

V

The metric 1 and metric 2 work alone

But they don’t work when being put on the same insight 
 

 

The metrics composed in this ways don’t work either: 
SELECT ({metric/metric_hired} / {metric/metric_total})

SELECT TRUNC({metric/metric_hired} / {metric/metric_total}, 4)

SELECT TRUNC({metric/metric_hired}, 4) / TRUNC({metric/metric_total}, 4)


SELECT(SELECT COUNT({attribute/v_gd_recruiting_ratio.job_posting_candidate_id}) WHERE {label/v_gd_recruiting_ratio.application_status} = "Hired")/(SELECT COUNT({attribute/v_gd_recruiting_ratio.job_posting_candidate_id}) WHERE {label/v_gd_recruiting_ratio.application_status}  IN ("Rejected", "Contacted", "Interviewing", "New Applicant", "Offered", "Hired",  "Knockout Rejection"))

U
Rank

Hi Vasiliy,

Are the below parts working when alone used?
SELECT COUNT({attribute/dshb_recruiting_ratio.job_posting_candidate_id}) WHERE {label/dshb_recruiting_ratio.application_status} = "Hired" (Metric1)

SELECT COUNT({attribute/dshb_recruiting_ratio.job_posting_candidate_id})

If they're working when used alone, then maybe it could be due to empty rows being selected in cross joins, i would try to check if below is working

SELECT COUNT({attribute/dshb_recruiting_ratio.job_posting_candidate_id}) WHERE {label/dshb_recruiting_ratio.application_status} IN ("Hired", "Not Hired") (Metric2)

Then try to get ratio of SELECT (Metric1/Metric2)

And also what is the error you're receiving in console? This could help us to check if it is related to Apache Drill.
 

Thank you,

Ulku

V

Hi Ulku
Could you please also give us an advice how to make this query work?
SELECT(SELECT COUNT({attribute/dshb_recruiting_ratio.job_posting_candidate_id}) WHERE {label/dshb_recruiting_ratio.application_status} = "Hired")/COUNT({attribute/dshb_recruiting_ratio.job_posting_candidate_id})

Now it doesn’t work

 

U
Rank

Hi Vasiliy/Yuriy,

It is true that in certain circumstances, SQL containing CROSS JOIN (cartesian join) is generated from MAQL statements. We would suggest two way to overcome this error:

  • do what the error message suggests, and set planner.enable_nljoin_for_scalar_only=false on Drill data source.
  • use only such reports which do not generate CROSS JOINs. While it is not entirely straightforward to explain when that happens, basically it's when: the dimensionality is empty (i.e. no attributes in "Rows"), when there are multiple metrics in the report or when one metric contains another nested metric on empty dimensionality (possibly as a result of BY ALL OTHER). So our suggestion would be: try adding attributes in Rows, use metrics in separate reports, and review if possibly BY ALL OTHER can be avoided.

Thank you,

 

Y

Hi @Ulku Kijasev,

Yes we use Apache Drill as our data source. Do you have any ideas how we can overcome this issue?

Thank you.

U
Rank

Hi Vasiliy,

Are you receiving the same error for the first MAQL query? The second one seems to be some limitation in Apache Drill related to cartesian join or an inequality join. Are you using Apache Drill as your data source manager?

Thank you,

Reply


Join us on
Welcome to the GoodData Community!Cookie settings