Automatically generate Keys for Importing fact data into Data Hub module

paul.rosal

Importing fact data into "flat modules" in a Data Hub is challenging when your data source is unable to provide a single unique Key to act as a List Item.  That work ends up taking place between the extracted data and the import into Anaplan and oftentimes involves concatenating Key columns into a single column, sometimes manually (e.g. Excel).  And there will be times when the length of the concatenated Keys can exceed the maximum supported for a unique Item Name or Code.  It would be great if during import, Anaplan can let a model builder choose the Key fields and Anaplan can automatically generate something like an MD5 Hash on those Key fields that can be imported into the List first and then the fact data into the Module afterwards.

Stan

Status changed to: New

hendersonmj

Let's upvote this to put a hash function on the roadmap!

In any SQL data source it is quite simple to generate a short (<60 characters) unique key.  It would be extremely useful to be able to match up with that key from Anaplan-originated data.  If we had a function that generates a SHA or MD5 hash from a string, it would make large data sets a lot easier to deal with.  As a high sparsity data engine comes out of development, multi-million item lists are going to become more common. 

MarinaDunets

Agree, we really need this function to make life of model builders easier and avoid huge sparsity! 

 

I've faced the same problem a few weeks ago. We have 2 modules - one is for items generation (expected code length is 70-90 symbols) and the second is for data import. So we have only 3 possible ways of solution: 

- create work around of hash function in Anaplan (then we use text functions that can slow down the model) 
- create code outside Anaplan (how to integrate this?)
- unite these two modules (huge sparsity issues, because we use different fake time scales for code generation and for data calculations - technical needs) + import by combination of properties 

 

Looks like I need the code even before the import is run to avoid the combining these two modules into one. It means I need the function that works in 'Formula' field of Blueprint View and always gives the same result when identical inputs are given. Something very familiar is described here: https://community.anaplan.com/t5/Idea-Exchange/An-Anaplan-Hash-Function/idi-p/104582

 

 

TimWard70

To work around this you can load into a flat module dimensioned by an empty numbered list, use a combination of properties to define uniqueness and load everything as List Properties.  You can then pull the properties into line items in a module and go from there.  I agree its not best practice but is sometimes all you can do.

paul.rosal

Unfortunately Combination of properties doesn't always work: a lot of blank properties and true duplicates based on defined properties at which point you start losing record details.  Note: when loading to properties Anaplan will not automatically sum up numeric data based on your Key, unlike when loading to Modules.

MarinaDunets

@TimWard70, thank you for mentioning this idea! We also found this solution, but it seemed too complicated for knowledge transfer to CoE and we also worry about performance... So I even forgot to write it out here. But it can work for somebody...

 

@paul.rosal you're so right! We also discovered the problem with summing up numeric data. In case when we don't have any hash function, we are forced to create redundant items, although business don't always need it 

david.savarin

The hash key md5 is the way

We do all our data that goes to anaplan have a anaplan hashkey column for that purpose the code is always < 32 characters.

david.savarin

To further comment on this : if anaplan allowed to create unique id upon file load based on a combination of columns instead of just one, would go along way to help doing this

alexpavel

It would help also if the import actions would have available a system column with the Row number (RowID)

shad.30

this is definitly needed. the only workaround today is to use numbered list and use associated anaplan id in the code but this generates more actions and more works for the model builder.