Accessing parent attributes by the child's eyes

[valpassos]

Hi community!

I feel like I'm in a sort of modelling pit, but I believe this would have a very straightforward solution.
I'm trying to model the following scenario/relationships:

I have a Product table with hundreds of attributes, in which Type is one of them. I have one certain Type of product that links the Product to a Master Product - like an aggregator. Imagine this type of Product is an Image that links to a MasterProduct type called Presentation (so, an Image goes into a Presentation). This MasterProduct (Local or Global) is exactly like a Product, so I'm only doing an ApplyMap directly in the Product table.

But this only covers the LocalProduct-LocalMasterProduct relationship.
I have yet another aggregator at an higher level, GlobalMasterProduct, which is like a parent of the LocalMasterProduct and grandparent of the LocalProduct, exactly like in the image.

What I want is, when I select a LocalProduct, I want to have access to all the GlobalProducts. So, I want to count 2 distinct images at Local level and 4 distinct images at Global level, either seeing by Local or Global attributes.

Right now, I have everything collapsed in the Product table, like this:

Product:
LocalProduct_KEY,
ApplyMap(...) AS LocalMasterProduct_KEY
ApplyMap(...) AS GlobalMasterProduct_KEY
...

So, how can I access the GlobalProducts when viewing at LocalProduct level? How should the GlobalProduct table be created? Could you please guide me in some direction, please?

Thank you in advance!,
Lisa

[valpassos]

Would someone like to jump in and offer some insight? 🙂

[valpassos]

I could really use your input on this, no matter how small you think it would be! 🙂

[valpassos]

Ok, so here it goes what I got at the moment.

I have a mapping table that gives me how many children (Global Products) the parent (Global Master Product) has:

MAPPING LOAD

GlobalMasterProduct_KEY

,COUNT(GlobatTable;lProduct_KEY) AS [# Global Products]

FROM MasterProductTable;

I then do an ApplyMap() in my fact table, where the GlobalMasterProduct_KEY will be the parent of my  LocalMasterProduct_KEY.

But I cannot sum up this metric on the front-end, because it works as a dimension. I'm doing an AGGR() over an ONLY()  to show some number (like MAX([# Global Products]), but what I actually want (and what makes sense) is the sum of all the GlobalProducts, and, when I filter by LocalMasterProduct, I'm given the sum of GlobalProducts for that specific LocalMasterProduct.

How can I achieve this?

Thanks!,

Lisa

[valpassos]

I'm still struggling with this issue... Doesn't anyone want to take a shot? Suggesting some kind of approach 🙂

Appreciated!