How to establish many to many relationships?

versilva · 2023-07-19T16:39:03+00:00

There was an error rendering this rich post.

Hello!
I am developing a Demand Planning model for a school book retailer.

I need to determine the SKU (the book identification) that each school uses (in order to purchase those SKUs for the stores around the school).
However, some schools utilize various SKUs for the same year/subject, resulting in multiple SKUs for the same combination:

How can I establish the relationship between school/grade/subject in Anaplan?

Thank you so much for your assistance.

Accepted answers

All comments

stevediaz

Hello

To establish the relationship between school/grade/subject in Anaplan, define modules for each entity (school, grade, subject, and SKU) and create line items to represent their relationships. Use lookup functions to associate SKUs with specific combinations of school/grade/subject.

versilva

Yes, but my issue is that I have mre than one match for each entity and in the line item I can only associate one. Do you know how can I handle this?

Thanhl13

You want to create a unique list with that possible combination. Your list would be YOU|SCHOOL|CLASS|GRADE, but your system module would have those as individual line items mapped. You, school, class, and grade are all different list. In a calculation module, if you need a specific area, you can use sum or lookup to pull the right mapping in using the sys module.

L1 List	Line items: Person	Line Item: School	Line Item: Class	Line Item: Grade
You\|ABC High School\|Pre Calc\|3.2	You	ABC High School	Pre Calc	3.2
You\|ABC High School\|Adv English\|3.6	You	ABC High School	Adv English	3.6
You\|ABC High School\|Biology I\|3.8	You	ABC High School	Biology I	3.8
You\|ABC High School\|Physic\|3	You	ABC High School	Physic	3
Friend A\|ABC High School\|Pre Calc\|2.5	Friend A	ABC High School	Pre Calc	2.5
Friend A\|ABC High School\|Adv English\|2	Friend A	ABC High School	Adv English	2
Friend A\|ABC High School\|Biology I\|2.6	Friend A	ABC High School	Biology I	2.6
Friend A\|ABC High School\|Physic\|2.8	Friend A	ABC High School	Physic	2.8

The line items above would be format to a list. Creating a unique key will be your friend.

versilva

So what you mean is that I have to create e new list with a key that makes it possible to have a one - on - one asociationa, instead of many to many, is that right?

Thanhl13

Yes, creating a general flat list will give you that many to many relationship. You're going to use 4 list. You main flat list, then 3 remaining list. Let see if this graph will help.

Thanhl13

If you can't create a new list with the concatenate already existing then you can create a number list. Then use Display Name property and link it to a concatenate line item based on the line items. I don't recommend the concatenate in Anaplan but it can be a temporary solution, otherwise, if you can make the primary key list in SQL or internal systems and upload the list into Anaplan would be more ideal.

versilva

Thank you so much for your help!

versilva

Hello @Tly13

I ended up going for the temporary solution, but right now I am trying to solve it definitely and I am having some doubts, particularly because the lists are updated by the final user (this is a information that changes every year and is very crucial to the business). I was hoping that that user could only upload the information in Anaplan, is it possible?

How should I manage the information flow from SQL to ANAPLAN?

Thanhl13

@versilva, sorry not always on here, but yes, you can. It depends on how you set up that list. You can put that list in a module and publish it to a UX then use an Action card and select Form. The module is for the end user to review.

Information flow from SQL to Anaplan, you need an API connector for automation and scheduling but I recommend that last since you'll have to work through any bugs that may arise. My typical approach is to start out with a SQL view for Anaplan, that means data is clean and is triaged, use the same column settings in SQL to create in Anaplan as line items and export it as a flat file from SQL. Upload the initial flat file into Anaplan. This allows Anaplan's to natively match the columns together(1 for 1 character match). When you upload this flat file, you create an action. That action can then be using a rest API or a connective API. Having a data module for this SQL will help with reconciliation.

Something to note, make sure to reconcile your upload if they have amounts. Sometimes you'll find that Anaplan could double the amount if there is already an existing number.

Quick Links

Can you help?

Unanswered questions

Hide the cards under "Models" section once connected
Hi Is there a way to hide the cards under the "Models" section once connected, as I would like the users to see only the links to the apps? Thank you
OData Rest API for
Hi, Does anyone know how to get an outbound OData Rest API, (OAuth2) to establish a connection between SAP and Anaplan. Thanks Khaled
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…