Problem with Dimension and Datamodel

[RicoP]

Hello,

I've been trying to get a sensible data model and calculations for weeks. I've been working with PQ for 2 years, but I can't get it right. The following scenario: I have several dimensions that build on one another hierarchically. This creates so many combinations that these test lines alone with 11 lines become 71 lines.





In the real data, however, there are an incredible number of combinations in Cat C/D/E, so that this ultimately results in a few GB in the data model.
So I have to manage to keep the data as small as possible and still be able to link the categories with each other, since pivot tables with slicers are ultimately required. Ultimately, I want to measure what percentage of the categories fall into which other categories.

I've tried synthetic keys and many others - I can't do anything because of the 1:N relationship in Excel Power Query. I really need help here.

Thanks for the help.
Rico

[lbendlin]

CatB (Sex) is independent, you can normalize that out.  For the other categories can you guarantee that each element only appears once in the hierarchy tree? Or are there duplicates?

[RicoP]

Hello,

Thanks for the answer 🙂 Well, category A can have different items from category C (see line 3 and 4 in the screenshot). These, in turn, are different items from category D and E. Basically, the individual items result from E + D + C = A. However, there should be no duplicates per line (line 8). These will then be deleted. And yes, actually it's like a family tree where category a is the root and then it branches further down. It is then counted how many items from the lower nodes lead to which node above it.

So I despair because the amount of combinations then becomes 1000 (A) x 1-2 lines (C) x 1-10 lines (D) x 1-10 lines (E) with about 1000 lines. Quite apart from the fact that I can't connect the dimensions because there is no 1:n connection. So: Questions over questions.....