Data Integration

Hi all,

I need to import data from files provided by business. Since the files contain duplicates and i have to import all records so i am using numbered list for the import.

But moving forward, I need to import single unique keys and ignore the duplicates but retain their values. I am finding difficulty in accomplishing this

The above image shows the source data imported to numbered lists.
Point to note here - Long Description are texts and have length more than 100 hence concatenation is not working if used as code for non numbered lists.

The desired output is as below

Is there any work around to accomplish this.

Many Thanks!

Accepted answers

JaredDolich

@Pranav

Yeah, you're close. Here's how you can handle this. Turn each of the Country, Channel, SubSector, Benefit Segment, & Long Description into smart keys. For simplicity purposes, you can make Country, Channel, SubSector, and Benefit Segment 3 letter codes. Long description should not be used as a primary key, rather, use the SKU or some other identifier for that. Long description is a a property of the SKU. So let's use your example and, again, we'll use @rob_marshall's approach which is best practice. It also uses DISCO which is the most efficient way to leverage the Anaplan calculation engine.

A list for each primary key: country, channel, subsector, benefit and SKU.
A standard list for your combinations
A system module for each of those lists above including the combination list
A transaction module that only uses the the combination list and TIME if you need it.
Use a second module to parse out the combination so you can relate each record to whatever list you want.

When you load this data, only one record will be created because you only have one combination. The values automatically get added together. Compare to your numbered list this is much smaller and more supportable. Remember, each list item requires 500 Bytes of overhead plus the length of your name and code. Notice our transaction module only has two columns. One for combination and one for the transaction data. Much smaller! Here's a super simple example.

Data loads with no errors and duplicates are consolidated.

System module allows us to FINDITEM and relate combinations to any of the dimensions.

Here we sum up the data by country!

All comments

JaredDolich

@Pranav

Great use case. I suggest starting with a standard list (not numbered). Map the primary keys to a unique code and then either concatenate the codes or map the codes to a smart key of some kind. Smart key is the most efficient. See @rob_marshall best practice article on how to import transaction data in combination with a system module to parse the smart key. Avoid using keys that have no meaning if you can - it makes support very difficult in the long run.

Also, note that by using a structured list instead of numbered, Anaplan will automatically add numeric values where there are duplicates, solving your initial interest.

Pranav

Thanks for the timely reply Jared.

But the issue still persists.
Let me give you the complete picture. Say I imported all records to a non numbered list. I now have around 1 million records.
Now for these there are 5 properties - Country, Channel, SubSector, Benefit Segment, & Long Description.
The Long Description is a text formatted property with character length over 100 and if i try to concatenate it by taking say 'x' or 'y' number of characters by using Left/Right functions so that for code length < 60 then all unique keys are not getting imported.

Some keys still getting left out due to redundancy.

In the above screenshot I have 32 records with same properties viz., Country, channel Subsector, Ben Seg & Long Desc for all these 32 are same but their code(from list) are different.
Now if i try to create a new key by concatenation it is affecting other records and I am unable to use FINDITEM correctly and hance my values are not getting summed up.

Can you please share me alternatives to create smart keys.

Thanks in advance

JaredDolich

@Pranav

A list for each primary key: country, channel, subsector, benefit and SKU.
A standard list for your combinations
A system module for each of those lists above including the combination list
A transaction module that only uses the the combination list and TIME if you need it.
Use a second module to parse out the combination so you can relate each record to whatever list you want.

Data loads with no errors and duplicates are consolidated.

System module allows us to FINDITEM and relate combinations to any of the dimensions.

Here we sum up the data by country!

Pranav

Thanks for the detailed solution.
My issue is solved!! 😀

Quick Links

Can you help?

Unanswered questions

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.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
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…