How to establish many to many relationships?

Options

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.

Answers

  • 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.

  • 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
    edited July 2023
    Options

    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.

  • 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?

  • 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.

  • 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.

  • Thank you so much for your help!

  • 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?

  • @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.