File to Module Import Doubling Number

mooreta

Hey Anaplan Community,

 

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.

 

File:

Style         Manufacturing Pieces

12345       2

12345       2

12346       3

12346       3

 

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!

 

Possible Solutions:

1. Ask for the integration to only send one occurrence (Obviously the cleanest approach)
2. Import the manufacturing pieces into a list property, so that the list import rejects the duplicate style occurrence.
3. 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.

Desired Result:

 

List:

Name    Parent    Code

12345                  12345

12346                  12346 

 

Module:

Name     Manufacturing Pieces

12345     2

12346     3

 

Current Result:

 

List:

Name    Parent    Code

12345                  12345

12346                  12346 

 

Module:

Name     Manufacturing Pieces

12345     4

12346     6

rob_marshall

@mooreta @Misbah ,

 

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.

 

Hope this helps,

 

Rob

Misbah

@mooreta 

 

First of all thanks for putting it out 

 

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,

Misbah

anirudh

Hi mooreta,

 

As pointed out by Misbah, it would be advisable to stay away from your approach number 3 as it is not recommended to have text formatted line items unless absolutely required.

Absent any solution from the data source and in the vein of approach #2, I would suggest the following:

 

• Create a numbered list for staging the styling data in this list 
• Import all rows of your file into this list using combination of properties of Style and Pieces (Action #1)
• Create a module with the dimension of the list created and have 4 line items, Style, Pieces, Style IFO and Delete All. The last 2 line items should be Boolean formatted.
• Formulas

 

Style: Staging List.Style
Pieces: Staging List.Pieces
Style IFO: ISFIRSTOCCURRENCE(Style, Staging List)​
Delete All: TRUE​

 

• Now create a saved view in the module with all rows and columns exposed and then apply the Style IFO line item as a filter to this saved view
• Import into your actual Styles List and Module using the saved view (Action #2)
• Create a delete using selection action and delete the Staging List using the Delete All Boolean (Action #3)
• Create a Process and add the Actions #1, #2 and #3
• Going forward, use this process to update the list and module 

This should give you your desired result and avoid unnecessary calculations and space consumption.

Let me know if this helps!

Regards,

Anirudh

Misbah

@anirudh 

 

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.

 

Thanks,

Misbah

mooreta

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.

anirudh

Agree entirely, my method should be used only if source data correction is not possible.

However, I would assume the disadvantages you mentioned are reduced as I suggest deleting the staging list as a final step in the process. Would like to know more if I'm wrong here!

Thanks,
Anirudh

rob_marshall

@anirudh ,

 

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.

 

Thanks,

Rob