SSAS - Inferred Dimension Attributes - are they necessary?

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?

I'm not sure I understand your question fully, so please clarify if necessary.

If you want to query the cube to get persons by state, you should add a Referenced relationship between Person and State, using Case as the Intermediate dimension.

• Select the empty box at the intersection of Person and State and click on the button

• Select Referenced from the relationship type dropdown

• Select Case as the Intermediate dimension

• Selecting the matching attributes in the two attribute dropdowns

• Click OK.

Deploy and process the cube, and it should work the way you want. Then you can get rid of the (State - Person) dimension.

StackExchange Database Administrators Q#17168, answer score: 4