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?

alt text

  • If the Person table contains the key for the State dimesion it should be as simple as setting up a regular relationship between the State dimension and the Person measure group. there should be no need to create referenced or fact relationships.
    – Darren Gosbell
    Jun 2 '09 at 6:43
  • As I understand Person has no key to State, but one Person can open many cases and each case is linked to a State. So one Person can be related to cases linked to two or more States. Then you would need a m-n relationship type between Person and State over Case. Jun 7 '17 at 6:44

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.

  1. Select the empty box at the intersection of Person and State and click on the button
  2. Select Referenced from the relationship type dropdown
  3. Select Case as the Intermediate dimension
  4. Selecting the matching attributes in the two attribute dropdowns
  5. 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.

  • You are correct in what I want to do, but wouldn't I be recreating yet another state-person dimension? I tried referenced dimensions, and that's what happend. Maybe my expectations is just wrong. If I work with a cube that has multiple measure groups from different tables, I would want a dimension to appear only once, so that you don't have to know which one is the right one for a particular query.
    – cdonner
    May 26 '09 at 0:58
  • You want to be able to generate a query something like this? The State dimension, and for each State, a count of Cases and a count of Persons. You need a single dimension so you can compare multiple measures side-by-side. I don't know why this didn't work when you tried it. You don't want the State-Person dimension you have now, indicated by the green line. Try deleting that dimension and then setting up the referenced dimension.
    – Dave Bauman
    May 27 '09 at 17:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.