Forum Discussion
Help with semantic model structure
Hi klev28 ,
The main challenge here isn't with the relationships themselves, but rather that the current model requires the Subscriptions table to serve as both a fact and a dimension, which creates conflicts. For income calculations, Subscription data needs to function as a fact table, while for scenarios where a subscription is linked to a campaign without activity, it needs to serve as a dimension. Using a single physical table for both roles leads to issues, such as indirect results disappearing when Activity-level filters are applied.
A clear solution is to separate these roles. Retain your existing fact table for subscription income, and create a separate copy of the Subscriptions table to use as a dimension. This dimension table should include only the necessary keys and attributes for segmentation and campaign mapping.
The Campaign table would then relate to this Subscription Dimension, while Activity and Transaction tables maintain their appropriate fact-level relationships.This structure ensures indirect results are retained, as they no longer rely on Activity. Segmentation can be applied consistently, and direct results continue to flow as before. There is no need to duplicate or maintain separate measures, as filter propagation is handled more predictably. This approach also supports all your reporting requirements and offers scalability for future enhancements by clearly separating descriptive and transactional logic.
Thank you.
Hi klev28 ,
The main challenge here isn't with the relationships themselves, but rather that the current model requires the Subscriptions table to serve as both a fact and a dimension, which creates conflicts. For income calculations, Subscription data needs to function as a fact table, while for scenarios where a subscription is linked to a campaign without activity, it needs to serve as a dimension. Using a single physical table for both roles leads to issues, such as indirect results disappearing when Activity-level filters are applied.
A clear solution is to separate these roles. Retain your existing fact table for subscription income, and create a separate copy of the Subscriptions table to use as a dimension. This dimension table should include only the necessary keys and attributes for segmentation and campaign mapping.
The Campaign table would then relate to this Subscription Dimension, while Activity and Transaction tables maintain their appropriate fact-level relationships.This structure ensures indirect results are retained, as they no longer rely on Activity. Segmentation can be applied consistently, and direct results continue to flow as before. There is no need to duplicate or maintain separate measures, as filter propagation is handled more predictably. This approach also supports all your reporting requirements and offers scalability for future enhancements by clearly separating descriptive and transactional logic.
Thank you.
Thank you for taking time to understand my requirements and outline what I need to re-work. It's not yet clear to me how to implement your solution, but I do now feel more confident that I know the general direction to head in.
Meanwhile I did manage to get some insights out of the data by adding the segment to the two results table (using lookup values). This at least allowed me to quantify the direct vs. indirect results by creating a transaction results visual split by segment where populated. And the same for subscriptions.