Issue with ISFIRSTOCCURANCE funtion

Issue: One of our Integration task failed with an Error :

Too many cells for ISFIRSTOCCURRENCE calculation: actual=50144479 maximum=50000000

Steps taken to avoid this was to chunk the files to less number of rows. The error still appears.

What is the actual reason for the error? Or what need to be done get any error that indirectly point to this message.

Accepted answers

All comments

jasonblinn

It looks like you are using the ISFIRSTOCCURRENCE function in a line item. That function has a limit of 50 Million Cells. By running the import Integration action, you are likely adding to a list which is one of the dimensions on the line item using the ISFIRSTOCCURRENCE, which is causing the cell count of the line item to exceed 50 million (By 144,479 cells).

Without knowing how/why that function is being used, it is hard to recommend a workaround. I would confirm if it is required, or if there is a way to do that calculation on a smaller line.

Jason

alexpavel

@deepskay :

Anaplan Classic Engine has a limit of 50 million cells for this function, which is why you’re encountering this error. https://help.anaplan.com/isfirstoccurrence-f3a4e998-fda4-42e5-aa48-05bcf6afd852

Some time ago, this limit was smaller (around 10 million, if I correctly remember).

As far as I know, the only way to avoid this issue is by keeping the number of cells within this limit.

If you encounter this situation in an Anaplan module and use the first occurrence method, I assume you need to import a very large number of rows and then filter out duplicates based on specific criteria or identify distinct values for certain columns.

These transformations are more to a relational database environment (datawarehouse, data lake) than to a multidimensional database.

This could be one of those cases: if Anaplan can do it, it's not necessarily the best place where to do it :).

You could think about to "move" these transformations on the datasource environment.

If you want to maintain and do the transformation on the Anaplan system, I suggest looking into Anaplan Data Orcherstrator (ADO). https://help.anaplan.com/anaplan-data-orchestrator-2e8a2870-b6a3-46b7-b0d3-b3d0a146953e

This seems to be a classic case where all the transformation needed could be solved in ADO.

Hope it helps,

Alex

devrathahuja

Hi @deepskay ,
It is linked to the maximum limit for that function as specified in the error message. It means the cell count is too high (Which would be as a result of the list members applied)

You could try to re-evaluate if you need all the members in that module? Try using subsets, time ranges (in case it applies to that dimension) or split the import to different source modules/staging layer where you carry IFO & then a final source module.

deepskay

Hi All,

After reading the comments before posting this issue, the first step I took was to chunk my import file to most granualar level I could. I chunked the files to 5000 rows and 21 columns. few files were successful and few files failed. I am not sure if that low number should hit the limit. If it was limit issue then other files shouldn't have been successful. At this point I think there is some different underlining error that is causing this but Anaplan is throwing this error.

devrathahuja

Hi @deepskay ,
Can you share a screenshot of the module (blueprint view) in which you're trying to import it to?

Additionally, it need not only be the contents in the new file but it is the total that is getting exceeded. As an example, if there's a limit of 100 rows & there are already 98 records, only 4 records in the new file would hit that limit & cause the issue whereas in the past a file with 98 records would have gone through as well.

Quick Links

Can you help?

Unanswered questions

Missing Column Headers in Anaplan Export When Using API
Hi Community, I’m encountering an issue where an export created in Anaplan is missing column headers when retrieved via the API. However, when I manually run the same export from the Actions tab, the downloaded file includes the headers. Has anyone experienced this behavior before? I’d appreciate any insights into the root…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…
CERTIFICATION BADGES - ANNOUNCEMENT
Anaplan Champions! The Community team just posted this announcement that certification badges may not be showing up on your profile probably until next year. Rest assured, you will get credit once you complete and pass the exams. https://community.anaplan.com/t5/Blog/Badges-Back-Soon/ba-p/123385