Automatically generate Keys for Importing fact data into Data Hub module
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.
Status changed to: New0
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.0
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/1045820
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.0
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.1
@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 it0
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.0
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 this0
It would help also if the import actions would have available a system column with the Row number (RowID)0
Get Started with Idea Exchange
See our Submission Guidelines and Idea Evaluation Criteria, then start posting your own ideas and showing support for others!