To begin with this article is based on a bug I submitted.
Bug 22302005 - LOGICAL LEVEL KEYS GET MESSED UP WHEN DIMENSION IS SET TO VALUE BASED HIERARCHY
When OBIEE 11.1.9 was released it took a while for me to actually start ‘Net New’ with my RPD development. I just upgraded and kept working. Well the day came that I needed to bring a new cube into my OBIEE 11.1.9 environment. When I attempted to query a hierarchy dimension that I had set to Value Based / Parent-Child I got the following error.
So I looked at the Log to see the Logical SQL.
I know I had used this cube on my 22.214.171.124 instance and I had no issues so I went and looked at the RPD in my 126.96.36.199 instance.
Physical layer there is the Measures Member Key…
So I went to my 188.8.131.52 instance and re-ran the same analysis
Then I looked at my Business Model
And the Physical Layer. Hmmm… no Measures Member Key
A Side by Side view often helps to see the difference.
Using my OBIEE 184.108.40.206 instance as my reference
I edited the Detail Level properties of the Measures Hierarchy.
Then added the Gen3,Measures as a new Logical Key
Next I edited the properties of the Measures Dimension to set the Primary Key - DetailKey to the Gen3,Measures.
Finally I deleted the the offending column.
The last thing to do was to save and check in my changes then test the hierarchy in an OBIEE Analysis and now no errors.
As a point of reference this bug is in current release of OBIEE 12c as well.
So until Oracle fixes Bug 22302005 this is a simple work around.