I am having difficulty achieving the desired result with a file import to a list and then module. I wanted to see what workarounds have been used. Below is a simplified example of what the file structure looks like.
Style Manufacturing Pieces
The information is only relevant at a style level, but the script that is being used for our integration will pull an update anytime something changes and create a new line. Meaning there could be duplicate occurrences of style in the file we are importing.
The goal is to first import into a list uniquely dimensioned by style called "Style" and then import the manufacturing pieces into the module "Style Properties", which is dimension by "Style" list. The manufacturing pieces are currently imported into a number formatted line item in the "Style Properties" module.
The issue is that the manufacturing pieces are being summed across the Style dimension during the import into the module. I have thought of a few different work-arounds, but am looking for some advice on the best approach. Please see below for Possible Solutions, Desired Result, and Current Result.
I appreciate the help!
Ask for the integration to only send one occurrence (Obviously the cleanest approach)
Import the manufacturing pieces into a list property, so that the list import rejects the duplicate style occurrence.
Import the manufacturing pieces into a text formatted line item in the "Style Properties" module and convert the text to a value with a formula in an additional line item.
You already have posted great solutions. I will personally go with approach 1 because that is typical case of "why should we aggregate the data in the source system or outside Anaplan.
Yes, we can handle it with approach 2 or 3 but I will stay away from these approaches because holding data in the list properties or creating text formatted line items to hold the data & Converting them goes against best practices. Would like to hear @rob_marshall 's take on it.
Thanks for Crafting this. I would say that this can definitely solve the issue but l would still recommend not to go with this approach. Reason being we are bypassing Best Practices at two places:
1. Creating unique keys within Anaplan using combination of properties. It can work fine as long as there are a few transactions but it might hamper performance as soon as the list increases.You are also prone to create text formatted properties. As we know we should try to minimize the usage of texts in Anaplan.
2. Deleting the list holding data using "Delete from list using Selection" function. This function has to create a log everytime action is run and increases the processing time and goes against best practice.
In my view, @Misbah is exactly correct, get the source to change to only have the correct singular value in the feed. Also, @mooreta, your alternatives are correct and I can see why you thought of those, but you also need to consider the creation of the debug file which will add time to the overall import. Remember, just because we can doesn't mean we should. A typical question I ask clients, how would you handle this in a different system? Usually, they say we can't which my response is, "Then why should Anaplan be held accountable to this when system XYZ can't handle this?"
Alternatives options mentioned:
Importing as a text and then converting to number: This will be tricky because a) you will get a debug file generated (which takes time) and b) I am not sure you can ensure the value will be correct because the first value in the file will be the one that is imported.
My sample load file:
Result after loading:
By the way, you will have the exact same issue when importing into a list property as well since a list property is essentially a line item:
Another option @anirudh referenced is importing into a Numbered list and using a combination of properties. While you won't get the debug file, you will be a) sacrificing performance due to the system finding the uniqueness of the data b) you will likely have the same issue in knowing which value to actually use, and c) as @Misbah mentioned, you are breaking more best practice rules (combination of properties, property members, transactional data in a list which will make your list larger).
Honestly, this is a great topic, but really you should push back for the source system to only give you the valid values or delta values.
Thank you for all of the input @rob_marshall@Misbah , and @anirudh. Your suggestions make complete sense and it is good to hear how others approach this situation. @rob_marshall I will definitely be using the question you pose in the near future.
As a model builder, the challenge is always being presented with an issue and a timeline for a solve while at the same time building in the best way that supports Anaplan structurally to align with best practices.
I am going to reach out to our team and see if they can support updating the script. If not, I will use an alternative approach that can support an update to the script at a later date.
You aren't wrong in that it can be done and does work, but it is not good for Hyperblock to load data into a list and then delete it. It not only takes additional time for the process to run (granted, a delete is very quick), but also adds more changes to the change log (both adding and then deleting) which if this is a very large list can lead to more model saves. Additionally, again, this depends on the volume, but you "could" eventually run our of room on the number of internal ID's (max is 1 billion -1). Yes, as an administrator, this can now be reset, but if you look at the big picture, what are you really gaining? You are doing a massive workaround for something that should be correct when it gets to Anaplan in the first place.