Planual Explained - Day 7

Misbah
Misbah
in Modeling

"Rule 1.05-12": Article 1, Chapter 5, and Rule 12 – “Use formulae to derive property of the list”. Based off the code of the list it should be possible to derive the properties, calculating the values is more efficient than storing text fields”. It goes against P elements of PLANS if you wish to do so.

Here is how it was done in Pre Planual Era:  

If source file had a unique code or name then during data load we used to load the list and dump everything else in list properties. If source file had 5 different columns, Anaplan used to have 5 properties to store data for those 5 columns.

Misbah_0-1589366012352.png

 

If source file didn’t have unique code or name then we used to make it even worse for engine to create a unique code by combination of properties and then load the columns again into the list properties.

Misbah_1-1589366012458.png

 

To give you an idea how terrible this is for an engine: Importing 10 million transactions into Anaplan based on the combination of properties never finished and it timed out after 24 hours, locking the model. Had to raise a ticket with Anaplan Support to cancel the job and unlock the model

What is wrong with this method?

  1. It impacts the performance of the model.
  2. System has to create change logs and then record it in history thus taking time.
  3. Since it is just an import we are not exploiting the potential of Hyperblock

Here is how it should be done in Planual Way:

 Prerequisites: You need to ask your source data provider to concatenate every column and create a code out of it and to provide you two files – one which have just Codes and another file which will have data associated to these codes. Remember this code has to be less than 60 characters otherwise Anaplan will kick out entries with more than 60 characters

Step 1: Once you have your file ready load the name and code into the list and nothing else gets loaded..

Misbah_2-1589366012493.png

 

Step 2: Create a system module dimensioned by the same list that you just created in Step 1 and insert line items to write the formula to split the code and ultimately converting text formatted line items into list formatted line items with the help of Flat lists

While performing Step 2 do not clutter expressions, it is a system module and it won’t have much impact on the model size. It will boost the performance of the model if you keep the expressions neat and clean. Also try to go sequentially and find all the delimiters first and do not hardcode the position of delimiters in your formula. It will save you from a lot of pain. For example instead of writing LEFT(Code, 2) it is better if we keep it dynamic as Left(Code, Find 1st Delimiter -1)

Misbah_3-1589366012499.png

 

Misbah_4-1589366012508.png

 

Tagged:

Answers

  • einas.ibrahim
    einas.ibrahim

    Hi @Misbah 

    I can't start my day now without reading the Planual Explained with my coffee🙂. Thank you

    Question: Why did you suggest the data to be provided in 2 files? One for the concatenated codes and another for the properties?

    We can use the same file to first load the list elements with the concatenated codes and then use the same file to import the properties into the SYS module.

    My preference to do it this way is to ensure data integrity, we eliminate the potential of having 2 mismatched files.

  • Misbah
    Misbah

    @einas.ibrahim 

     

    I am glad, made my day.

     

    To answer your question- 

     

    First file - Only Concatenated Codes for all attributes. This file goes to the list and was relevant to the mentioned rule

    Second file - Data file and not the properties/attributes file This file will go directly into the module and was not relevant - hence didn't talk about it.

     

    Reason for doing so is because most of the time we get the data in tabular format meaning your concatenated key is in one column, all your periods are in another column and data in third column. Now if you have such a source file you probably may have a huge single file.Imagine same key being repeated for every month, every key will have atleast 12 instances for one year.

     

    On the other hand If your key is in one column and monthly data is split across the columns then there is no need for two files. One file should suffice

     

  • Ellen.Morley
    Ellen.Morley

    Thanks for all your Planual posts - very insightful!

     

    I do have a question here.  I completely agree with using a concatenated code field rather than having Anaplan track unique field combinations.   But why is performance better when parsing the codes into text fields rather than loading the codes into the text fields during the import?   Does it take more processing power to load a field than calculate it?

  • Misbah
    Misbah

    @Ellen.Morley 

     

    Yes it is faster when you let Anaplan calculate it but as per best practices (like Splitting the Formulae, not daisy chaining etc) as compared to Importing. Here are the Tests results that have been published in Data Hubs: Purpose and Peak Performance - Anaplan Community

    Misbah_0-1591368424318.png

    Hope that clarifies it.

     

  • rob_marshall
    rob_marshall

    @Ellen.Morley ,

     

    In addition to @Misbah stated, you are "saving" an action to load the data vs. having the system calculate it (much faster because the calculations are in memory).  Also, when you write data, it is being saved in the change log vs a formula is not saved, it is calculated upon model open.

     

    Hope this helps,

     

    Rob

Categories