0

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

Improve this question
10
  • Maybe it would help if you posted actual table and foreign key definitions
    – Tom V
    Commented Feb 4, 2016 at 19:30
  • I don't think the definition of the fact tables is relevant here - the question is just about the definition of the dimension. The 3 tables in the diagram above are are all used in the dimension.
    – Laurence
    Commented Feb 5, 2016 at 15:00
  • Foreign keys are: CONSTRAINT [FK_DimSellableItemVersion_SellableItemAvailability] FOREIGN KEY ([SellableItemAvailabilityId]) REFERENCES [SellableItemAvailability]([Id]) and CONSTRAINT [FK_SellableItemParent_SellableItemAvailability] FOREIGN KEY ([SellableItemAvailabilityId]) REFERENCES [SellableItemAvailability]([Id])
    – Laurence
    Commented Feb 5, 2016 at 15:02
  • That doesn't make much sense, so you have multiple records for the same itemid in your sellableitemversion table? What are you tracking in your availability table? versions or items?
    – Tom V
    Commented Feb 5, 2016 at 16:51
  • No, its like this .... The key for the Product dimension is DimSellableItemVersion.SellableItemId (which is the primary key). Each single product has a single availability status (a single record in SellableItemAvailability). Records in SellableItemAvailability are: In Stock, Out of Stock, Discontinued etc. Each single product also has a single parent product (a single record in SellableItemParent). Finally, each single parent product has a single availability status (a single record in SellableItemAvailability).
    – Laurence
    Commented Feb 5, 2016 at 17:13

1 Answer 1

Reset to default
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.