Sql-server – SSAS MD dimension joins to same table twice (but processing fails as attribute has 2 names for same ID)

cubedimensiondimensional-modelingsql serverssas

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.

enter image description here

I defined the Product Availability attribute (i.e. the availability of products) with:

  • Key=DimSellableItemVersion.SellableItemAvailabilityId
  • Name=SellableItemAvailability.StatusName

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?

enter image description here

Best Answer

You have a product dimension using all 3 tables. The dimension key is DimSellableItemVersion.SellableItemId and you can use SellableItemAvailability.Id two times as an attribute, once for the product and once for the parent-product. But SSAS does not know which is which or when you want to use it only once, which on you mean. Do you want to go left or right along your foreign-key-circle.

When you want both SellableItemAvailability instances then I would use the data source view to create a second copy of the table SellableItemAvailability. Then you can break the circle and the dimension can work.

Related Question