- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Problem with Dimension and Datamodel
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.....

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
Anonymous
| 12-21-2018 12:11 AM | ||
10-24-2024 03:49 PM | |||
07-22-2024 04:11 PM | |||
10-11-2024 02:48 PM | |||
11-29-2024 04:27 AM |
User | Count |
---|---|
33 | |
18 | |
14 | |
11 | |
10 |