Hi, I want to follow up on the question of @Masha Akatieva. Thanks @Francisco Antunes for answering the initial question. By now we looked a bit more into explicit lifting and remodeling this, and are struggeling about the best way. I hope you have some ideas of how to do this right. Here’s our key Info: • We have a “Rewards” Fact table with multiple dates on them, e.g. “Tracked At”, “Confirmed At”. • Rewards belong to a Brand, modeled via a Brand Dimension. • In order to model SaaS Metrics, we have a history of SaaS States for each Brand, so e.g. a brand counts as “new” for a few months, then as “retained”, and at some point as “churned”. Especially, since Brands can also rejoin, there can be multiple changes in the state, and we decided to just list them per brand on a month by month level. • We already have built Metrics like “Reward Value tracked”, which just sum up Reward Values and can be used to e.g. group by brand or by tracked month. • The Goal: Get a Table that gives us the Reward Value tracked and confirmed (2 metrics) split by Brand and by SaaS state month. We are struggling, since we somehow need to model it so that the SaaS State Date can be compared to the “Tracked At” or “Confirmed At” in a MAQL expression. This seems to be tricky to do. Do you have ideas or proposals?

Hi Thomas, modeling and implementation is a bit out of scope of our support team. If explicit lifting isn't working and still some datasets remain disconnected, then you will indeed need to rearrange the LDM. Should you need hands on assistance with this, we can certainly put you in touch with your account owner to engage in discussions about utilizing our professional services team, who can assist with any new implementations within a workspace.

Hi Joseph, thanks for the info. I’m in contact with our account owner already on other topics, so I will check there

Hi Thomas, I am not sure I fully understand the requirement so let me try to be a bit broader: • If what you want to do is to display the Reward Value by SaaS State and SaaS state Date (but you do not need to match the reward created date with the SaaS state Date) ◦ you could convert the edge between “Dim Brand” and “Dim SaaS States” to a M:N edge and therefore allow slicing “Reward Value” by both “SaaS state” and “SaaS state date” ◦ but this might cause you multiple-counting as the only link will be the “Dim Brand” so any “Reward Value” will be displayed for any “SaaS State” and “SaaS State Date” that will appear in the data for that “Dim Brand”. ◦ So I am not sure if that is what you want from the business perspective. But if it is, this might be the easiest solution • If on the other hand what you need is actually to identify in what specific “SaaS State” the “Brand” was at the moment when the “reward” of that brand was created ◦ I believe the best option would be to pre-calculate this before loading it to GoodData and include the “Historical SaaS State” attribute directly into the “Fact Reward” dataset. For each row the value that was valid at that particular moment of reward creation. That way you can easily track the historical value and work with it in your reports. ◦ Also unless the “Dim SaaS Statuses” is a daily snapshot with status for every brand and every day, you would need some BETWEEN operation for dates and I am afraid this is not available in GD Platform MAQL. ◦ So I believe a pre-joining the data before loading to gooddata and tracking the historical saas state in the fact rewards dataset is the most reasonable option (without knowing other specific details of your solution).

Hi @Michal Hauzirek! Thanks for digging deeper, even though its out of scope for this chat. Really appreciated! So yes, I didn’t consider the option for a M:N edge. That opens a few more possibilities. We need to actually compare the dates and in my last conversation with @Masha Akatieva we came to a similar conclusion to what you proposed as the second option: directly embedding the saas state into the fact rewards or a related dimension. This will be more cumbersome, because we planned to use those states on more than just rewards, but probably still the easier way to do. Thanks again!

I see, thinking out loud here: if you need to use the historical "SaaS state" elsewhere, you might try some historization/SCD approach on the "Brand" dimension in your tables. Something like having a new record of Brand for each change of the "state" with some unique surrogate key and linnking the other tables to Brand not bu Brand ID but by this surrogate key (determined by Brand ID and the dates). Note you would need a surrogate key as you can not link by date range directly in LDM. Something like SCD Type 2 or possibly Type 6 as in https://en.wikipedia.org/wiki/Slowly_changing_dimension

It might actually be easier than that. Since our saas states are always on a full month basis. Wouldn’t it be possible to compare created at (month/year) = saas date (month/year)?

I had no luck with a condition that would work universally for comparing the dates in this setup. But I actually had an idea which might resolve this nicely. If you slightly change your data model like this: • make primary key of

Dim SaaS State

dataset a combination/concatenation of

Brand Key

and

SaaS State Month

• using this key connect

Dim SaaS State

directly to

fact rewards

(arrow from dim to fact) ◦ first part of the key -

Brand Key

is already present in the

Fact Rewards

◦ second part of the key - the date/month can be derived from the

CreatedAt

Date (this solves the

created_month=saas_state_month

condition) ◦ so i.e. a reward row with brand key ‘X’ and createdAt date ‘2024-08-19’ would have the combined foreign key ‘X-2024-08-01’ (date truncated to month) • completely remove the existing link between

Dim SaaS State

and

Dim Brand

(to avoid loop within the model) (see such model in the attachment - I only used the main columns for simplicity) This way, the

SaaS States

keeps monthly snapshots of SaaS State for each brand, the appropriate SaaS state is always referenced from the

Fact Rewards

dataset, without need for any complex metric. And you can simply view SUM of Reward Value by SaaS State, Brand and SaaS Date as @Masha Akatieva wanted. I hope I am not missing something, but to me this sounds like the most elegant solution. The limitation is, that you can no longer analyze the

SaaS States

by

Brand

without rewards, but I am not sure if that was needed And if it was, you could add new (factless?) fact table connecting these two together.

This sounds like it could be a solution that also can get extended to other facts! Thanks for giving this another push!