Hello, I have a question about MAQL and `BY ALL OT...
# gooddata-cloud
m
Hello, I have a question about MAQL and
BY ALL OTHER WITHOUT PF
, which is not acting how I expect. I have an "Avg. Customer Value" metric, which alone in an insight is displaying $9,179.61 (see pic below). However, when I combine it with another metric, which filters the same dataset, the value drops to $2,306.22 (see image 2). It appears the second metric is affecting the first, even though I thought I had prevented that with the "by all other" clause. Should I be using some other piece of MAQL to calculate the Avg. Customer Value metric, which is supposed to be independent of the current dimensions and filters?
j
This looks suspicious. Things like this may happen if the engine assumes it can perform optimization and compute two metrics using common SELECT statement based on knowledge of relationships in data model. Detail debug information may be needed to analyze it further. Is it possible that there is broken referential integrity in the model (e.g. foreign key references non-existing primary key value or NULL is present in place of foreign key)? I think it may be one additional inner join to dimension table which is causing this in case foreign key reference contains NULL instead of reference to special dimension table value representing “N/A” value. Inner join with dimension dataset shall not impact results in case referential integrity is not broken and each row of fact dataset references existing row of dimension dataset.
m
I was refactoring our data model and lifting fields out of the "Value Attribution" dataset, shown below. All our models are generated with DBT. "Journey ID" in the "First Touch Credit" dataset has "not_null" and "unique" tests (that pass). It is possible that the "Journey ID" reference in "Value Attribution" is null. I was under the impression this was fine, however?
However, I was missing a relationship check from Value Attribution back to First Touch Credit on "journeyId". I just added one and saw that there are references to PKs that do not exist. I see how this could cause the problem. I will track this down and see if things straighten out.
j
Ok, please let us know if it helped. I will mark this thread as product feedback because our LDM currently does not let you specify if foreign key contains NULLs or not and the engine assumes it is does not.
p
🎉 New note created.