Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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 break-down 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

share|improve this question
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

migrated from stackoverflow.com Apr 27 '12 at 15:14

1 Answer

up vote 1 down vote

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.

share|improve this answer
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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