Hierarchies with numbered lists

steve_*11057

I'm trying to create a list where:

(a) There are two levels in the hierarchy, let's say "Level 2" and "Level 1", where Level 2 consolidates up into Level 1;

(b) Level 2 and Level 1 are both numbered lists with a display name property

(c) The members of the lists will be added to over time, and I want to update the members and their consolidations from a CSV that has two fields, representing Level 1 and Level 2.

(d) The fields on the CSV match up with what's in the Display Name property of the two levels.

(e) In a small number of cases the length of the fields is greater than 60 characters, which is why I'm trying to use numbered lists. 

 

I'm trying to do build/update the lists by importing the CSV into the list that represents the lower level (Level 2), but how do I set up that import?  I've tried it as follows in the Anaplan Import dialog box:

Nothing maps to LEVEL 2

The field for LEVEL 1 maps to Level 2's "Parent"

Nothing maps to Level 2's "Code"

The field for LEVEL 2 maps to Level 2's "Display Name"

Items uniquely identified by "Combination of Properties" (specifically, "Display Name")

 

This doesn't work; Level 2 items don't get associated with the right Level 2 parent, and indeed I get an "invalid parent" error. I can understand this, because I'm not putting the real Level 2 key into the parent field, I'm putting the Display Name in there, but it's the only identifier I've got.

 

Is this possible to do correctly? 

VIGNESH.M

Hi Steve,

 

Map L1 Code as L2 Parent. L1 list is a number list, so we should consider code for mapping, right now you have mapped L1 Display name to L2 Parent , that's why you are getting invalid parent error.

 

Also in Combination of properties, include Parent and Display Name.

 

~Vignesh M

rob_marshall

@steve ,

 

First, please do not use a combination of properties as that means you have to have properties defined on the list which they shouldn't be and that you don't have a code for the member.  All metadata should be in SYS Property modules.  Second, you need to have a code for all lists, that is best practice.  If you don't have a code, then it is very hard to get data out of numbered lists, no lookups.  I would suggest the following:

• Create a flat list for all members of L2, this can be numbered, but you must have a code from the source system.
• Create a module using L2 in the Applies To, and if it is numbered, create a line item named Code with the formula code(item('L2 list name')).  You will use this field to anchor the import of metadata, like the parent as well as any other data.  
• Create new line items, using the function ISFIRSTOCCURRENCE() to only get the first occurrence of the L1 member.  Create a view of this and use this as the import into L1
• Create a view, again off the same module, to build out the L2 members.  In this, you will need code of the L2 member, the display name (if you want to do this correctly, make the Display Name list formatted back to the flat list you created in step 1 and match the code of the L2 member to the Display Name - it will automatically resolve), and supply the code of the parent for the parent.

Look at class 305: Hierarchy Management which demonstrates the above.

 

As for why you shouldn't use the Combination of Properties, please check out this article:

https://community.anaplan.com/t5/Best-Practices/Data-Hubs-Purpose-and-Peak-Performance/ta-p/48866

 

Hope this helps,

 

Rob

 

steve_*11057

Thank you @rob_marshall and @VIGNESH.M .

 

I've tried your suggestions and didn't have any success, but I think my problem might be a more fundamental one in that I don't have a unique code for the higher levels. In the source system the higher level members are uniquely identified by a string, but in some cases that string is longer than 60 characters, and my understanding is that I therefore can't use it as either a name or a code. Is that correct?

kavinkumar

@steve,

Yes you are absolutely right!! Lists code limits to 60 characters. If you really don't have the unique identity within 60 characters, then it is hard to bring in the list items. But still either you have to ask the 3rd party system to provide the unique identity within 60 or you have to do some brainstorm on how to compress that more than 60 characters to less then 60 characters. As @rob_marshall mentioned, it is always best practice to have codes and don't go with combination of properties(Use only in case of flat file upload without unique identifier). 

Tips to create codes:

1. You can take 1st 50 characters and length of the string (right(50)_Length), 

2. Take 1st 25 letters and last 25 letters and then length of the string (left(25)_right(25)_Lenght)

3. Take last 50 characters and length (right(50)_length) etc...

Note: I am suggesting this approach cos you don't have unique identifier less than 60. Try with any approach and make sure the codes are unique before you load into the list so that you will get all the items.

After creating this list L1, if you try to do the upload into the L2, by mapping Disp Name to parent, it will not work. Hence get that file into the system as a flat file (Create list and module)and apply the same condition to get the code of L1 (In one of the line item) then do your import into L2 from this module's saved view.

I hope this helps you and in this way you can get rid of you concern.

Regards,

Kavin.

kjacokes

Hi Steve,

 

I've run into similar issue to what you described, and depending on the situation different approaches will make sense.

 

While I won't advertise these as a flawless approaches, we've had success:

1. Identifying items based on a unique combination of properties, as well as
2. Having a module in Anaplan that takes long, text-based unique identifiers, and assigns a shorter numeric code (e.g. "000123"), and implementing logic across the model that enables us to work with those shorter codes as opposed to the longer text-based identifiers

 

Both required an understanding of the pros and cons, and a well-thought-out approach, and at the end of the day worked well for our purposes.

 

I just wanted to call out that these approaches can work, so long as the cons/down-sides are well-understood, sufficiently-mitigated, and don't pose risks to scalability of the model over time.

 

Let me know if you would like to discuss further.

 

Cheers,

Kevin

DavidSmith

All

I was having a similar conversation with someone this morning

When you don't have a code or the code is greater than 60, you really are limited in the options and pretty much have to use the combination of properties route.

The best advice, aside from the above, is to keep the initial data in a separate model and try and generate your own code for use in the downstream models. You can have the large key as an attribute and use that as a display name if you need to

I've also used the numbered list ID as the code in the downstream model to ensure that it always matches (which is fine as long as you don't delete anything in the source).  Even if you are creating a number list in the target, you can populate it with a code from the hub

So in you hub the first item may be #75, but when you create this for the first time in the spoke you can force the code to #75 instead of #1

Hope this helps

David

kavinkumar

@DavidSmith, I have quick question on your thoughts. If I use the system generated Id as a code from Hub to Spoke Model then how can I manage the future imports regarding that list. Ex. Say if I have created an item with the code as "#75" in spoke model. Now assume, I am getting another file to import other attributes of this list. In this case again the uniqueness is only my name which is more than 60 characters and if I go with the above approach how can I get the data respectively. Or yo can take this use case where we have to map the parent item.

Hope my question is clear!!

Regards,
Kavin.

DavidSmith

Future imports (using the same combination of properties) should not generate a new item in the list as the combination of properties is the same as already exists, so the data against #75 should still be imported correctly (hence why I said don't delete this item)

Any new combination of properties will generate a new # number at the bottom of the list

 

All of this, as we have discussed, is a bit of a pain, hence, why it is so much better and cleaner to use a code in the import itself

 

I hope that helps

David

kavinkumar

Thanks for the response. But I can't use combination of properties when I go to next level of hierarchy. Consider I have created the List L1 with the code from Hub. Now I want to create L2 items under L1 using different file. How will I proceed in this scenario? I think the whole conversation is regarding this scenario. 

DavidSmith

The discussion did get a little sidetracked, I was covering more general points as the options had already been covered above

But you are correct, you can't bring data in from outside and expect it to match codes you've created internally in the data hub, so all you can do is try and bring in all the data first and then create all of the codes for the downstream models.  It might be better to bring in the lower levels and then generate codes for the parents.  Bringing in a flat list of the ultimate child should give you enough information to create the structures above, rather than the traditional top down

 

Again, this is why it is so, so important to get the code done in the source.  Personally I do not think this is too much to ask for.  When exporting data from Anaplan, often the format is prescriptive, the same should apply to bringing in the data.  We know what format we want and this should be passed to the source owner to provide

David