How to deal with 3 dimensions having many to many relationship?

versilva

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?

ramonito

Why don't you just create a list with the Code (Store) and Code(School) combination from your first module with the Store as the parent

Then create a subset for the School and Store that will be TRUE, if the combination is legal.

Then create a third module with the dimensions Store_School subset, Subject and Type.

Create a line item (list format) from the Code to get the School

Create a line item for Subject (if sys module is not available)

Create a line item for Type (if sys module is not available)

Create a line item in this third module as SKU, and the lookup using School, Subject and Type.

To filter, create a line item to check the first occurence of the SKU per Store.

Please tell me if this seems fine :)

versilva

Thank you very much!

ramonito

Here is what I tried:

1. Store School

2. School , Book Type, Book Subject with Line Item of SKU

3, 3rd module to do the combinations (after subsets and lookups)

4. To clean up the 3rd module (using filters and avoid duplicate SKUs per Store), you now have the list on what books (SKUs) the Stores should have:

Please tell me if this follows your objective :)

versilva

Thank you so much, I believe this was what I needed!

I am only having trouble doing the "Creation of a line item (list format) from the Code to get the School" , how did you do this?

I was doing a look up but I have a error of dimension mismatch

versilva

Please ignore my previous message :)

At first I though the School Line Item, in the third module, would come from a Lookup, and I couldn't do that.
I've only than realized what you meant whith "obtain it from the code", and completed it :)

Just out of curiosity, did you do something like this or did I overcomplicate it?

ramonito

We did the lookup per each of the dimensions to be able to lookup on your 2nd module (School , Book Type, Book Subject with Line Item of SKU) . If you already have a System Module for each of your list, you can use that directly instead of creating line items like I did below (your module will look cleaner).

Also , I noticed that it is possible to have the same SKU on different book type or subjects but still on the same store. Do you require to control this?

versilva

1. SKU Line Item in the 3rd module → Lookup with source in the 2nd module (School , Book Type, Book Subject with Line Item of SKU) and the target the 3rd module (Store_School subset, Subject and Type, with Line Item of SKU)

• Why did you include Book Type and Book Subject in the mapping?
  Considering that this dimensions are in both the source and in the target modules shouldn't be enough to use Lookup:School? I've tried it to see what would change and it was not possible to do. Am I missing something?
• 

2. The same SKU on different book type or subjects but still on the same store?

Since each SKU can only have one book type and one subject by definition, I did not anticipate that happening.
However, I could add a verification step when importing the SKU list (it needs to be updated every year) to prevent mistakes like this. Is that what you're saying?

Thank you very much once more! Your assistance was essential to me because I am just getting started.

ramonito

To answer your question in Number 1:

1. Because you need to accurately LOOKUP the data entered per each dimension combination.

In order to have no error, you need to create two line items with Subject and Type List (in case you do not have SYS modules for these lists) , and use that for the LOOKUP in Third Module to the 2nd Module.

Please see how we did for the example below: