Hey gang, can I get some support? We are trying to report on "count of completions" by "tag name" The LDM has completion <- content -> taggings <- tag name, hopefully foreign key direction is clear from that. We have a metric 'count of completions' defined as

SELECT COUNT({label/completions.id})

We are able to report on f.ex content.content_type by tag name, but not on the metric (or anything to do with completions). What are we doing wrong?

Hi Thomas, the M:N definition may lead indeed to troubles down the road, specifically in the way Analytical Desginer aggregates facts and attributes, you may see duplicated results. Not to say, the computation may take longer and it could cause unnecessary stress on the data warehouse. You may need to redefine your metric so it works in the correct context, for example, by adding the USING keyword:

SELECT COUNT({label/inventory.productid}) USING {label/purchases.purchaseid}

. There are more details as to how to properly work with COUNT in the documentation here: https://www.gooddata.com/docs/cloud/create-metrics/maql/aggregation/count/ Lastly, please take into account the direction of the arrows, as it determines which dataset’s data can be analyzed (sliced) by the data from the other dataset. Please take a look at the following documentation which goes over some tips for data modelling: https://university.gooddata.com/tutorials/data-modeling/logical-data-model-five-pro-tips-for-data-modelling/

does the direction of the arrows NOT need to reflect the direction of the 1:n?

Hi Thomas, can you please provide a direct link to the metric? I will take a closer look and will report back with my findings

how would you like things set up? M:N, 1:N?

Thanks for the links

done

The issue arises because there is no direct reference to the "Tags" dataset in the "Completions" dataset. A simple way to verify this is by adding the "Id" attribute from the "Completions" dataset to an insight and then attempting to break it down by the "Tag name" attribute from the "Tags" dataset. You’ll notice that the "Tag name" attribute will not even be available, as it does not directly relate to the attribute already added. From our logs, the count metric fails with the error

Multiple contexts for count, attribute='attribute/completions.id/df566d0354e04365b659c67b12c34766'

when not using a M:N connection, as the analytic engine cannot determine where the attribute "Tag name" is located. While context errors can often be resolved by introducing the

USING

keyword in the metric definition, you are correct that this would not work here, as the root cause lies in how the datasets are connected. With this said, the issue should be resolved by reviewing and adjusting the dataset connections.

reviewing and adjusting them to...

Hey @Thomas, I see no changes in the LDM. Do you need a hand with anything?

yeah

the issue should be resolved by reviewing and adjusting the dataset connections.

adjusting them to what? What is your recommendation in this case?

I would say connecting the datasets directly, but unfortunately I am not familiar with your business use case, this will ultimately be up to you to decide

why does this not work, but f.ex completions by team name does?

As pointed out previously, the issue here is with the connection between the dataset. I am afraid I would not be able to advise further, but if you run into any more issues, let us know.

but the connection is set up identically (can you at least confirm??) to one that DOES work just fine - completions by team name. Your explanation of "why it's wrong", which you said is within your remit, is either wrong or incomplete in light of this

Hi Thomas, let me try to help here. First let me try to clarify how M:N, arrows in model and foreign keys work together in GoodData: Each arrow between datasets in the model is defined by the primary and foreign key and has this “initial” direction (pointing from primary to foreign key). These “normal” 1:N arrows are treated by the engine in a way that metrics can be sliced against their direction, not the other way. The “M:N” or the “double-sided” arrow is technically just a “normal” 1:N arrow with additional property telling the system that it can also allow slicing in the opposite direction. By using a M:N arrow, you are basically telling the system to treat the dataset to which the arrow points to as a bridge table that is actually handling the M:N relationship between attributes. So

content --> taggings

(with foreign key in taggings) means that each tagging has exactly one content; and each content can have zero or more taggings. And

content <-->> taggings

(still with foreign key in taggings) means exactly the same thing, plus it allows to slice content by tags (with the double-sided arrow it is still slicing against the arrow). (I am using

->>

here to mark the “initial” direction based on the foreign key; in the UI it is the black tip of the arrow vs the other being white). Now to your questions If I understand you correctly, you have elsewhere in the model :

f.ex teams <-> team users <-> users -> completions

and you are able to slice

completions

by

team name

That is expected and it is because in this model you can go from

completions

to

f.ex teams

against the direction of those arrows. But In case of

completion <- content -> taggings <- tag name

you can not get from tag to the completion against the direction of the arrow. Some arrows go in different direction and there is no path "against the arrow" from completion to tag. How to fix that I assume from the name that

taggings

is truely a M:N bridge table between content and tag name and therefore it makes sense to have the M:N there. You can set it on both arrows going to the taggings. So changing it to

completion <- content <-->> taggings <<--> tag name

should do the trick. Now you should be able to slice metrics from completion by tags, because it will be against the direction of the arrow - there is now a path from completion all the way to tag name going against the direction of the arrow. I was able to do this in a very simplified model matching yours and am able to slice COUNT(completion ID) metric by tag name - see the screenshots: I hope this helps.

Hi @Thomas did Michal's post help?

Yes, a great deal! Thank you so much @Michal Hauzírek

Happy to help. If you feel that some part of the answer is difficult to understand from the documentation or even completely missing there, please let us know so we can improve it.