Need to break down Module into Multiple column Flat List

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!

TableExample_78175.JPG

Accepted answers

All comments

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

Quick Links

Can you help?

Unanswered questions

Introducing Anaplan Python API SDK
Hey everyone, I wanted to share the Anaplan Python SDK with you. It's a Python Library that wraps all Anaplan APIs and makes it easier to interact with Anaplan programmatically. It is mostly designed with Data Integration Scenarios in mind, but it does support all APIs including ALM, SCIM, Audit, and Cloud Works. Please…
Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…