How to deal with 3 dimensions having many to many relationship?
I am having some trouble connecting dimensions because I have a relationship with many-to-many and therefore I can not use LOOKUP as I am used to.
I feel like I had some trouble explaining the problem before so I will use some screenshots.
I have a model for a book retailer, and my final goal is to determine the association SKU - STORE (wich SKUs (books) are going to be sent for each store)
This model has 3 dimensions [Store, Subject, Book Type (book/workbook)] and the Line Item SKU [Format: List].
For this I have to intermediate relationships to establish.
[School - SKU] and [School - Store] but both of them are in the nature many-to-many.
[Store School] - 2 dimensions (School - Rows, Store - Columns) and the boolean Line Item "Match?" - This is not the best way to import the information but it was the best way I found since each school can be sold in many stores, and each store can sell more than one school
[School- SKU] - 3 dimensions (School, Subject, Type) and one Line Item SKU [Format: List].
Initially I though that it for the final modulo [Store - SKU] I would only have to "grab" the SKU from [School-SKU] using a lookup and the [Store-School] , but I realized this is not possible.
I am new to ANAPLAN, and I realize that I may not be using the best strategy to handle this situation. I was told to use SQL but I am also not sure how to do it.
I find it important to mention ( Store - School ad School-SKU) that this information needs to be updated every year by the final user, and is very critical to ensure this is a simple process for them.
Can someone please help me?