I'm having difficulty connecting a dimension table (recursive/hierarchical) to a fact table as there are concerns/issues to deal with:
- The dimension table belongs to a parent-child relationship structure
- From the original table, it keeps growing
| id | item_name | parent_id |
|---|---|---|
| 1 | classification | null |
| 2 | category | null |
| 3 | group | null |
| 4 | modern | 1 |
| 5 | modified | 1 |
| 6 | tools | 2 |
| 7 | meters | 2 |
| 8 | metal | 3 |
| 9 | plastic | 3 |
| 10 | lead | 8 |
| 11 | alloy | 8 |
Denormalizing this kind of table is not suitable as a new entity type comes in, it would affect the dimension structure.
What is the best approach to this type? Kindly provide an example and what would be the query statement after connecting the fact and dimension.