I'm having issues getting a level-based hierarchy for one of my dimensions (College) to work properly. Since I am able to create level-based hierarchies for other dimensions, I am guessing that this is because the College dimension is modeled differently than the others.
In the physical model, I have a fact table called F COLLEGE_ENROLMENT, which is joined to a dimension table, D COLLEGE_INSTITUTION. The dimension table D COLLEGE_INSTITUTION is in turn joined to another dimension tale, D COLLEGE.
In the business model, because I wanted to create a dimensional schema using the "star" model, I have created a single logical table called D College with two logical sources: LTS College Institution and LTS College. This logical table has three columns: College Institution ID (which is the key and which comes from the physical COLINST table), College Name and College ID (both of which come form the physical COLLEGE table).
When I right-click on the logical College dimension and select Create Logical Dimension > Dimension with Level-Based Hierarchies, the BI Admin tool does not complain. But when I try to use the dimension hierarchy in an analysis, it does not drill down properly.
How can I model the logical College dimension so that it can be used to create a level-based hierarchy?