Need to break down Module into Multiple column Flat List

BrandonEmbrey

I need some help breaking down data that has been inserted into Anaplan via the excel add in, so that each component in the existing module can be listed as individual line items as in a data list.  The attached photo lays out the dimensions I need, but I'd like to see it in a form that has the Cost center, Ledger code, date of the forecast item, and the amount each on an individual line, much like you would see in an excel sheet.

 

I just want to have it essentially flattened so that I can use the 'raw' final state to be used as an export for other modules.

 

Thanks in advance!

prabhu

Hi @BrandonEmbrey,

 

I also have encountered this.

 

What I used to do for such cases are.

 

Step 1: Create a text formatted line item in your existing module.

                                         The formula should be the concatenation of the name of Dimension 1 & "-" & name of the dimension 2 & "-" & name of dimension 3.

 

for example, as referred to your screenshot, this line item should produce the output for the first record as Sales~10504004-Travel Fare~711005-Jan 20. and the second record should produce the output as Sales~10504004-Travel Fare~711005-Feb 20.

 

if the name exceeds 60 characters then replace it with code.

 

Step 2:

Create an import action into a numbered list marking this line item as a code for the numbered list.

 

Step 3:

Create a new module with this numbered list a dimension and 4(3 list format and 1 number format) line item.

These 4 line items should be formula-driven, which means the first line item should extract  Sales~10504004, and the second line item should extract Travel Fare~711005 and the third line item should extract Jan 20 and fourth should extract its value from the module which you currently have.

 

Thus it helps you to produce multiple columns.

 

Thanks,

JaredDolich

@BrandonEmbrey 

Great question and @prabhu provided a really good answer. 

If you want to optimize that solution though, I would recommend you import the date (time hierarchy) as a dimension and not as part of a concatenated list item. It is shown quite conclusively, while this may introduce some sparsity, it is vastly more efficient. You can see the performance statistics here, from @rob_marshall's most excellent best-practice article on peak performance.

Also, in case you aren't familiar with D.I.S.C.O., you should always import just data and use system modules to handle your properties. Rob handles that point quite well in the above article.

Good luck! Great to see the Excel Add-In being used - you can find a TON of curated articles and helpful tips and tricks at the Anaplan extensions landing page provided by @MagaliP. 

VIGNESH.M

Hi @prabhu @JaredDolich , 

Why should not we create 3 more(Cost Center, Ledger Code, and Time) line items in the existing module, and while export just simply exclude the dimension names from the labels, this will also provide us the flat export file right?

 

Why are we creating another list and extracting the same information again in the line items?

 

~Vignesh M 

JaredDolich

@VIGNESH.M 

I guess it depends on the use case. My understanding was that the requirement was to import (not export) the transactions in flat. The dimensions are "encoded" in the UID so there's no need to make the extra line items. You can create a system module to parse the UID and break out the dimensions. And, because the months, or weeks, are dimensional (even if it creates sparsity), the system modules for the UID will be fast and efficient. Another advantage is that you can just as easily implement incremental changes as you would if the entire module was dimensionalized. Just how the hyperblock works, I guess.

If you already have the module in the various dimensions I suppose you don't have much choice without re-architecting which isn't much fun. So perhaps your idea is the more practical one.

Misbah

@VIGNESH.M @BrandonEmbrey 

 

Why not simply export the current view via "Tabular Multiple Column" ? If I get the question right then it should flatten the module by exporting it thru Multiple Column option.

VIGNESH.M

Hi @Misbah,

In this case, the simple grid or multiple column export will give the flat module data with CC Ledger ID & Time as separate line items/columns. 

 

~Vignesh M

Tom_H

Hello @prabhu 

 

I am looking to implement something similar and found your helpful solution. Could you point me in the right direction regarding how to extract the list items from the numbered list name? E.g. how to look up numbered list code Sales~10504004-Travel Fare~711005-Jan 20 and convert it back into list item Sales~10504004 ?

 

Many thanks

Tom