I have a cube with fact tables that have a parent-child relationship:
Person-Case. Some dimensions are inferred from the parent, e.g.
State in the example below.
Person does not have a foreign key to the
State dimension, but
Case does, and through the
Person-Case relationship the
State is inferred. By doing it this way, I end up with lots of "redundant" dimension attributes from these inferred dimensions.
If I want to query the cube, for instance to obtain persons by state, I have to use the dimension attribute of the fact dimension that I am querying, but it is not possible to mix them with attributes from other dimensions. In other words, the red relationship below is not created, and if I drop the
State dimension by itself into the Pivot control, I don't get a breakdown by state. I have to use the inferred State attribute of the Person dimension.
Is there a better way of doing this, so that
State exists only once in the cube? Is it not valid to have hierarchical facts in a single cube?