Roll up not working correctly when using custom key columns

Question

I have a dimension that I flattened, the data looks like:

This is a simplification of the data, I actually have 7 "Detail" values that are the same for each PrimaryValue and 2 "SubDetail" values. Each SubValue has one blank (not a null, there are no nulls in the table) for each PrimaryValue.

I am trying to setup a hierarchy so the users see:

• PrimaryValue

  • SubValue

The cube does not like the data and complains about duplicate keys so I changed the key columns and I got it to process but my hierarchy shows one PrimaryValue for each SubValue so It looks like this in the Browser:

ABC

this one is the blank value

ABC

A

ABC

Q

ABC

G

And so on. When I what I want is:

ABC

A

Q

G

XYZ

7

F

1E

I am new to cubes but I have tried everything I can think of and read about on the web. I think my issue is that I have multiple "Detail" and "SubDetail". I have the Hierarchy defined as:

PrimaryValue

SubValue

I setup the Attributes so PrimaryValue relates to SubValue and Detail1 and Detail2 I have SubValue relating to SubDetail1.

I have tried every KeyColumns combination I can think of and either the cube wont process due to duplicate key values, or it will process and I get the issue I am describing here or I get and error in the Browser "The key1 dimension attribute was not found"

I could not find anything on the web about this issue, I am probably wording it wrong. I have found a lot about setting up key columns but none address the extra detail fields in their examples or describe the rollup issues I am seeing.

Answer 1

This is a simple answer, I am surprised I could not find this error on the web. I had my attribute relationships backward. Should be most specific to most general but I had the most general (the one with the fewest unique values) to the most specific (the one with the most unique values). Surprised it worked at all!!

So in my example I should have had SubValue related to PrimaryValue.