Unable to relate two dimension models to two fact models in dataset

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.

Hi Shahid,

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.

Regards,

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.

Hi @shahidt, Sorry I missed this follow-up response. Can you check if the following guide answers your question?

Regards,

Hi @tan. I am not sure whether I am using v4.0, so I just tried using Aggregation via Dataset Exploration. I now get this error
image

However, I can’t resolve that as you had suggested to de-activate relationships to the second fact table and I am unable to set that anyway.

Since we are only in trial mode and user acceptance stage with Holistics, I will pick this up, if we go ahead with a proper subscription.

Thanks for your time.