I have a Sales cube defined and working with several measures related to a customer dimension. This is all working fine as of now.
One of the attributed of the Customer Dimension is a Sales Rep ID.
I now need to be able to query the cube and get results by Region.
So I Created a Sales Persion Dimension w/ two attributes of Rgion and Sales person and added the dimension to the cube. The problem is in setting the Dimension usage for this new dimension. There is no direct relationship of the Sales Person dimension to the fact table except through the Customer Dimension.
In my estimation this should be a referenced relationship type. I have the Relationship type = referenced, reference dimension = Sales Person Dim, Intermediate Dimension = Customer Dim, and Reference dimension attribute = sales persion. When I click on the Intermediate dimension attribute I expect to have an option of selecting the Salesperion attribute from the customer but it is not in the dropdown list.
What am I missing here?