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

[shahidt (Shahid)]

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.

[tan (Tan Huynh)]

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,

[shahidt (Shahid)]

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.

[tan (Tan Huynh)]

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

Cannot combine fields due to fan-out issues? | Holistics Docs

Regards,

[shahidt (Shahid)]

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

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.

[shahidt (Shahid)]

Hi @tan. We are now a paying customer of holistics and are also using v4.0, but I still face the same problem.

I have two fact tables and they are joined to one single campaign dimension table. Below is my relationship definition, from “View as code”

  relationships: [
    // fact_campaign_performance as fact
    relationship(data_models_fact_campaign_performance.date > data_models_dim_calendar.date, true),
    relationship(data_models_fact_campaign_performance.campaign_id > data_models_dim_campaigns.id, true),
    relationship(data_models_fact_campaign_performance.offer_id > data_models_dim_offers.id, true),

    // fact_celtra_campaign_performance as fact
    relationship(data_models_fact_celtra_campaign_performance.date > data_models_dim_calendar.date, true),
    relationship(data_models_fact_celtra_campaign_performance.campaign_id > data_models_dim_campaigns.id, true),
  ]

Since the latter table does not have an offer_id level granularity, I cannot include it as a column in the first fact table.

I need it to be in the same dataset as I want to pull metrics from both tables together into one visual.

[tan (Tan Huynh)]

Hi Shahid,

If I understand correctly, your dataset relationship looks like this.

This would not work out of the box because of the ambiguous paths between the 2 fact models. We would usually suggest breaking down the dimension models tailored for each fact model and removing the ambiguity. However, in your scenario, it is not feasible as you need to visualize metrics from both fact models with essentially a many-to-many relationship between the fact models.

In this case, can you help check if a set-up like this is feasible for your use case? We propose a potential solution by creating a separate dimension model that will contain all the common and relevant dimensions shared by both fact models. With this, you can create the necessary relationships between the dimension model and the two fact models to allow you to visualize desired metrics.

This solution is not ideal, but we are continuously working on ways to address this use case better. We recommend keeping an eye on the upcoming revamp to our metric language ([Upcoming] Our brand new Analytics Query Language, AQL!), which may provide a better solution.

Regards,

[shahidt (Shahid)]

@tan excuse me for the late reply. I will try that approach. Was hoping to avoid that to not complicate the data model.

[Khai_To]

Hi @shahidt,

We come back with the great news.
Our team has officially supported with_relationship() function that helps you to control the relationship within your metric definition.

Could you refer to our post here Role Playing Models / Model Aliases - #12 by Khai_To for more information?

So in your case, you can disable the relationship path from dim_campaigns to one of your fact model.
And then when you need to define the metric that requires that relationship, you can forge enable that one within the metric context.

If you want to try out our AQL Expressions, please let us know.
Do let me know if you have any questions or concerns.