My cube has a product dimension, and each product has an "availability" status attribute. In the database, SellableItemAvailabilityId in DimSellableItemVersion (which contains products) joins to Id in SellableItemAvailability (which contains the statuses). See the image below for the tables used in the dimension.
Each product also has a parent product. These are not the same as the products mentioned above (this is not an SSAS parent-child relationship) - think of them as ways to group related products together (e.g. a product style that comes in a number of colours & sizes).
Each parent product also has an "availability" status attribute. In the database, SellableItemAvailabilityId in SellableItemParent (which contains parent products) joins to Id in SellableItemAvailability.
Because of the foreign keys between the tables, the DSV automatically makes the relationships as seen in the image below.
I defined the Product Availability attribute (i.e. the availability of products) with:
However processing fails due to the attribute having more than 1 name for the same key. I guess this might be due to products where the parent product has a different availability to the products inside it. If the relationship between SellableItemParent and SellableItemAvailability is deleted from the DSV, processing succeeds.
I want to keep the relationship between SellableItemParent and SellableItemAvailability so I can add a "parent product availability" attribute to the dimension. What do I need to do to make this work?