Module with no dimensions

bolinl1

Is it possible to build a module with no dimensions that will allow us to import a file with thousands of rows?

The current file we receive does not have a unique code. We would like to import this file into an ANAPLAN module and then create a unique code using the data in the line items so that we could then create a list/dimension.

rob_marshall

@bolinl1

Why can't you build that unique key in the file? If you can do it in Anaplan, can you not do it in the source?

Dikshant

I agree with Rob, Create a list to load the data. Create the unique key in the file like a sequential number 12,3,4. And then load all attributes in the SYS module.

bolinl1

Rob Marshall - The unique key cannot be built in the source system. To add it means we will have to build tables between the source system and ANAPLAN to add the key with all the overhead of moving the data. I am being questioned by management why we have this additional build/cost so I am looking for a solution to avoid it.

bolinl1

Dikshant - As stated above, management is questioning why ANAPLAN cannot allow us to create the key within ANAPLAN itself. There is additional work/cost to getting the data out of the source system, then putting it into another table, adding the unique key and then loading into ANAPLAN vs. just loading into ANAPLAN.

CommunityMember111277

Hi @bolinl1, I think what you can do -

1. Import the file into a dummy/staging List
2. Create module using the dummy/staging list AND create a unique id basis on multiple line items (LI & "|" & LI "|" & etc..)
3. Then, create the list/dimension using the Import option (from the module>saved view) «Duplicate unique ID will be excluded as part of the Import process)

Hope it helps!

Thanks

AB

bolinl1

How do I dump a file into the dummy /staging list without having a unique code for the file first?

shivanandhonnalli

@bolinl1 , create a numbered list and import the items to dummy(numbered list), then you follow the method as @Abhishek_B mentioned

bolinl1

Even if you use a numbered list. You still have to have a unique code for the row.

JaredDolich

Not the most practical, but could you use a numbered list? Anaplan will pick the unique code for you. If the 1000 values are all unique and under 60 characters you can use a standard list.

To be honest, this method is not very efficient, nor is it very supportable long term. Youl will need a method to clear the list, and in the case of a numbered list, you'll eventually have to reset the index to 1.

Hopefully, you can use Rob's idea and take care of this outside of Anaplan.

JaredDolich

For numbered lists, you'll have to create a list property for each of the columns used to identify the row. Then you will use a combination of those columns when you import the file. Breaks a lot of best practice rules to do it this way though.

You're not alone in this request. Happens all the time @bolinl1 - IT grabs a file, from the source via an API call and it doesn't contain the unique column and there's typically an unwillingness to run it through an ETL process.

There are alternatives to using a numbered list but you'll have to modify the file before importing. You can even use a scripting language like PowerShell, or even better a simple, free, open-source programming language like Python.

Worst case scenario is you import the file into Excel, add the column, and resave the file. You can record a macro to make the process easier.

bolinl1

Thanks Jared but the file will have 3-4 million rows in it each night. Now, each night it is pretty much thes same data but with a few new rows added.

bolinl1

So, What I am hearing is…ANAPLAN does not provide a long term, sustainable process that allows for this request?

JaredDolich

@bolinl1 sadly, this is true. Only two exceptions I can think of:

1. In addition to the time dimension there is one, and only one, column that identifies the row. If you need more than one column (not including time) then you'll have to modify the file or manipulate it using an ETL process. Import into a module that has time and the unique identifier.
2. Create list properties for all the columns in your transaction table. Then use a system module to create a new list using the unique IDs. For the new list create a system module that prevents you from having to use list properties. This method will take a tremendous amount of memory since every list item created reserves 500 bytes + memory for all the properties. Screenshot example below.

CSV file.

Numbered List Import - combined Product ID and sales

System Module

rob_marshall

@bolinl1

Yes, Anaplan does provide a long-term sustainable solution for this, it just isn't what you are wanting. How would other products handle this request?

You can do what @JaredDolich stated, but that's against our best practices (The Planual). Here are the issues you will face if you do that:

a) the process to import the data will not perform well because the engine will have to read the entire list before it inserts a new member, then read the entire list again before it inserts another member).

b) you will not have a code on a numbered list (which is against best practices) which means you will then need to load data to list properties (again, against our best practices) because there will not be a way to import the transactional data to a module because you don't have a code.

c) the list will take up more memory because of the transactional data stored within the properties. For more information on this, please read Data Hubs: Purpose and Peak Performance.

d) since the list is longer (more members than needed due to the transaction data in the properties), the engine will have to work harder to get to the calculations than it really needs to if you had a proper list with codes.

Rob

bolinl1

This tells me why we should not do it :) Is there any way to do it? We receive a file every night from a source system that does not have a code on it. We want to bring this file into Anaplan and concatenate 3 of the fields create a code. We then would create a new list using this code….

JaredDolich

@bolinl1 I know we're not answering your question directly, and I apologize. The short answer is that there is only one way to accomplish this and that's to use the process I showed above. And @rob_marshall and I have explained the problems you'll likely face as a result.

It's not what you want to hear but adding that extra column to your file is not that complicated, even if you're uncomfortable with programming. I'll ask around. I suspect someone has already written an Anaplan Connect script. Stay tuned…