I come from a Star Schema background and am evaluating Holistics. I have two dimension tables - offers and campaigns, and two fact tables - viewings and viewings users. I created data models and related them as shown below.
The granularity in viewings users is different to viewings and the info cannot be in the same table. When I then create a new Dataset using the above four data models, it throws an error saying that the relationship is ambiguous. I clicked on ‘Learn more’ and went through the link, but my use case is not similar to the ambiguous relation mentioned on that page.
Basically, in my case, the query needs to do a LEFT JOIN from the dimension table to the fact table, but I believe Holistics does a LEFT JOIN from the fact table to the dimension (many side table to one).
How can I use the same dimension table/model with two fact tables/models? I want to load measures from both of these model files at the same time and have them grouped by the same dimension values in my report.
How can I use the same dimension table/model with two fact tables/models? I want to load measures from both of these model files at the same time and have them grouped by the same dimension values in my report.
You can use the same dimension with two fact tables as long as there aren’t any ambiguous path.
Though, I think this is not ideal for your use-cases.
Currently, the best way to solve this is to consolidate the two fact tables, as I see they are very similar in shape. Can you check if that is possible in your set-up?
We do know this is an important issue with our current modeling layer and are currently working on a way to address this. Unfortunately, I cannot share much right now about that.
Hi @tan . Thanks for your reply. I am unable to combine the fact tables as I need to do a COUNT DISTINCT at report/query time, and the user_id column contains user ids and not user count.
I do this as aggregating, grouped by offers and campaigns, leads to double counting, if a user has viewed more than one offer / campaign and you do not filter for any of those.
So, I have two fact tables related to the same dimension table. The issue occurs even with one single dimension table linked to two fact tables that need to be aggregated at the same time.
I kept the relationship on my primary fact table active and disabled the two links to fact_viewings_users. Then when I try to add both metrics to the same chart, I get the following error.
Cannot combine selected fields due to potential fan-out issues.