I have a problem with a many-to-many relationship.
My fact table has a structure like this:
CompanyID
OfficeID
DepartmentID
Amount
1
10
200
55
1
20
100
700
1
10
200
300
2
50
700
150
Now I would like to be able to create a dimension to filter out different areas. However, these dimensions have a sort of hierarchical structure. For example, choosing area A would filter out all transactions with CompanyID=1 and OfficeID=10 regardless of DepartmentID. But if I choose are B I want the same result but only DepartmentID 100.
How do I achieve this? I can't create composite keys as the dimension values overlap and one transaction may belong multiple areas.
